ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Changing result set metadata on the fly.
Date Fri, 10 Dec 2004 13:19:29 GMT
Kevin,

Dynamic ResultSet metadata is not supported.  There are a number of
very good reasons, which are mostly performance related.  In the
future, I would hope to solve this by supporting SQL fragmets, so that
you can maintain the SQL in one place, but have 2 statements that use
it.  That way you don't even need the dynamic SQL part, nor do you
need to imply the count by passing a null parameter.

Cheers,
Clinton


On Fri, 10 Dec 2004 00:35:16 -0800 (PST), Kevin <kevin_405@yahoo.com> wrote:
> 
> 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