This message was sent directly to me therefore i am forwarding it back
to the list.
Brandon
---------- Forwarded message ----------
From: Kevin <[EMAIL PROTECTED]>
Date: Thu, 9 Dec 2004 23:31:15 -0800 (PST)
Subject: Re: Changing result set metadata on the fly.
To: Brandon Goodin <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]> 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
