ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin <kevin_...@yahoo.com>
Subject Re: Changing result set metadata on the fly.
Date Fri, 10 Dec 2004 08:35:16 GMT

Hi

Thanks for answering my questions, thanks for forwding
it to the forum.

What u are suggesting is exactly what i am doing i.e
using 2 queries 1 for count and 1 for the result set.

But i was hoping to use 1 dynamic sql instead of 2 to
achieve the above result.

U can look at my sql example and u will see that the
condition after the Select does not work  and that is
actually my question.

Why does not work ?

Thanks
Navin
--- Brandon Goodin <brandon.goodin@gmail.com> wrote:

> Paginated list only returns a subset of the results.
> This is
> determined by the pageSize. It does not retain a
> total count.
> 
> If you perform a count using the resultset it will
> force the cursor to
> traverse the whole resultset. Most databases do not
> load up large
> results sets completely. So, when you perform your
> count using the
> resultset you are already impacting your system in a
> negative way and
> not saving yourself any performance.
> 
> My suggestion would be to use two sql calls. One
> using a count (as a
> count sql statement would be more performant than a
> resultset
> traversal) and the other sql statement to retrieve
> the results you
> want from the resultset. The PaginatedList in iBatis
> will only
> traverse the rows in the resultset that it needs to
> in order to 
> populate the corresponding objects with. Then when
> you call the next
> page on the paginated list it retains the
> appropriate information to
> reconnect to the database and grab the next section
> of the results
> that you need.
> 
> With this strategy you save yourself on memory and
> cpu.
> 
> Brandon
> 
> 
> On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin
> <kevin_405@yahoo.com> wrote:
> > Thanks for taking time to respond to my question.
> > 
> > Here is an example of what i am trying to do.
> > 
> > I have a query that serves a dual purpose
> > 1>
> > getting me a count of all the records that meet a
> > certain criteria
> > 2>
> > return a subset of the records of whole list i
> used
> > for getting the count as i donot need the whole
> result
> > set for display.
> > 
> > The type of query executed depends on a field in
> the
> > parameter class.
> > 
> > I have attached my query below , if u look at it
> > carefully u will see that the Select clause has a
> > condition on "countQuery" and so does the where
> > clause.
> > 
> > Essentially the resultset columns change on the
> fly
> > depending on the query selected.
> > 
> > This as far as i am told is not supported in
> Ibatis.
> > 
> > I was wondering why this is not supported and what
> are
> > the alternatives  rather than having 2 queries.
> > 
> > Your suggestion of doing a size on the list will
> not
> > work because the result set is only a subset. i.e
> i
> > only display 100 out lets say 10000 records on the
> web
> > layer to make it more effecient , so i set my
> where
> > clause to give me the range of records whose list
> size
> > will be 100 but the count should return 10000. The
> > reson i need 10000 is for creating a web layer
> based
> > paginated list.
> > 
> > Does the paginated list in Ibatis support this?
> > 
> >                 <select
> > id="getEmployeeAndLastCourseCount"
> > 
> >
>
parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p
> > aram.EmployeeAndLastCourseParam"
> > 
> >
>
resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train
> > ingRequestListModel">
> >            Select
> > 
> >                              <isNotNull
> > property="countQuery">
> > 
> >                                              
> count(*)
> > as count
> > 
> >                              </isNotNull>
> > 
> >                              <isNull
> > property="countQuery">
> > 
> >            rownumber,
> > 
> >            employeeId,
> > 
> >            employeeName,
> > 
> >            managerId,
> > 
> >            managerName,
> > 
> >            managementUnitId,
> > 
> >            managementUnitName,
> > 
> >            courseId,
> > 
> >            courseTitle,
> > 
> >            courseDate
> > 
> >                              </isNull>
> > 
> >            FROM
> >            (
> >                SELECT
> >                    rownum as rownumber,
> >                    e.emp_employee_id as
> employeeId,
> >                    e.emp_name as employeeName,
> >                    e.emp_manager as managerId,
> >                    e.manager_name as managerName,
> >                    e.muid as managementUnitId,
> >                    e.name as managementUnitName,
> >                    cS.course_Id as courseId,
> >                    cS.course_title as courseTitle,
> >                    cS.start_datetime as
> courseDate,
> > 
> >                              cS.module_status_id
> >                FROM
> >                    (select e.emp_employee_id,
> > e.emp_name,
> > em.emp_employee_id
> > 
> >                              as emp_manager,
> > em.emp_name as manager_name, mu.muid, mu.name
> > 
> >                                 from employee e,
> > employee em, management_unit mu,
> > 
> >                 mgmt_unit_association ma
> > 
> >                              where
> >                    e.emp_manager =
> em.emp_employee_id
> > and
> >                    e.emp_employee_id =
> ma.employee_id
> > and
> >                    mu.muid = ma.muid
> > 
> >                              ) e
> > 
> >                 left outer join
> > 
> >                              (select
> emp_employee_id,
> > start_datetime, a.course_id, a.sched_id,
> > 
> >                              module_status_id,
> > c.course_title, rank from
> >                    (select b.emp_employee_id,
> > a.start_datetime,
> > a.course_id,
> > 
> >                              a.sched_id,
> > b.module_status_id, dense_rank() over
> > 
> >                 (partition by b.emp_employee_id
> > 
> >                 order by b.emp_employee_id,
> > a.start_datetime) rank from
> > 
> >                 course_schedule a,
> course_assignment 
=== message truncated ===


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Mime
View raw message