ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Fwd: Changing result set metadata on the fly.
Date Fri, 10 Dec 2004 07:36:42 GMT
This message was sent directly to me therefore i am forwarding it back
to the list.

Brandon


---------- Forwarded message ----------
From: Kevin <kevin_405@yahoo.com>
Date: Thu, 9 Dec 2004 23:31:15 -0800 (PST)
Subject: Re: Changing result set metadata on the fly.
To: Brandon Goodin <brandon.goodin@gmail.com>


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 b
where

                             <isNotNull
property="moduleStatusId">

b.module_status_id = #moduleStatusId#

                             </isNotNull>

                             and a.sched_id =
b.sched_id

                             ) a, course c where rank
= 1 and a.course_id = c.course_id ) cS

                on

                                 e.emp_employee_id =
cS.emp_employee_id

                             where

                             <isNotNull
property="rownum">

                                              rownum
>= #rownum#

                             </isNotNull>

                             <isNotNull prepend="AND"
property="managerName">

                                              and
e.manager_name like #managerName#

                             </isNotNull>

                             <isNotNull prepend="AND"
property="employeeName">

                                              and
e.emp_name like #employeeName#

                             </isNotNull>

                             <isNotNull prepend="AND"
property="managementUnitName">

                                              e.name
like #managementUnitName#

                             </isNotNull>

                             <isNotNull prepend="AND"
property="managerId">

e.emp_manager = #managerId#

                             </isNotNull>

                             <isNull
property="countQuery">

                                              <isNull
property="orderByColumn">

        order by employeeName

</isNull>

<isNotNull property="orderByColumn">

                        order by #orderByColumn#

</isNotNull>

                                              <isNull
property="orderByDesc">

        asc

</isNull>

<isNotNull property="orderByDesc">

                        desc

</isNotNull>

                             </isNull>

                             )

                             <isNull
property="countQuery">

       WHERE

           rownumber between

           #startRownum# and #endRownum#

                             </isNull>
                </select>



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

> I'm not completely sure what you are asking. But, if
> you want to count
> your results you could simply check the .size()
> method on your
> returned list. Maybe you could provide a clearer
> example.
>
> Brandon
>
>
> On Thu, 9 Dec 2004 22:15:44 -0800 (PST), Kevin
> <kevin_405@yahoo.com> wrote:
> >
> > Hi
> >
> > I just found out from someone that Ibatis does not
> > allow changing the resultset metadata on the fly.
> >
> > This does not allow me to have the same query for
> > count of records as well as getting the result
> set. Is
> > there any alternate way ibatis does something like
> > this.
> >
> > It is not a big issue considering the power of the
> > what Ibatis actually provides , but was curious to
> > learn the reason for leaving this out.
> >
> > Thanks
> > Kevin
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - Find what you need with new enhanced
> search.
> > http://info.mail.yahoo.com/mail_250
> >
>


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

Mime
View raw message