This is an attempt to convert the pagination query using rownum in Oracle to
an equivalent pagination query using limit in mysql.
I have an existing application that uses an Oracle database in production. I
am trying to run the same application using mysql on my laptop. The entire
application and sql statements work perfectly fine, except for those parts
and sql's that use pagination. The pagination is achieved using oracle's
rownum feature and takes as input #from# and #to#.
I understand that there is no equivalent in mysql except for the limit
clause. So after some effort I have been able to get the same results in
mysql using a set of sql statements that accept the #from# and #to# input
parameters.
So just to illustrate, one of my oracle pagination queries was something
like (as an entry in my sqlmap):
**********************
<select id="getUserApprovers" resultClass="ad.UserApprover"
parameterClass="java.util.Map">
select iams_id as iamsId ,division_name as
divisionName ,region_name as regionName ,isactive as isActive
from (
select iams_id
,division_name ,region_name ,isactive
,row_number() over (order by division_name, region_name) rn from
user_approvers )
where rn between #from# and #to#
order by rn
</select>
**********************
Converted, the same sql in mysql was something like the following:
**********************
<select id="getUserApprovers" resultClass="ad.UserApprover"
parameterClass="java.util.Map">
set @sql = concat( "select
iams_id as iamsId
,division_name as divisionName
,region_name as regionName
,isactive as isActive
from user_approvers
limit ", #from#, ",", (#from#-#to#+1) );
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
</select>
**********************
The mysql sql seems to work just like I want when I try it at the mysql
prompt.
When I tried it in the sqlmap I got errors like this:
**********************
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near '; prepare stmt from
@sql; execute stmt; drop prepare stmt' at line 1
**********************
what is the correct way of using it in the sqlmap? Should I use
executeBatch? I do not want to have to change my application logic to make
it work with both oracle and mysql - is there any other way? And of course I
do not want to install oracle on my laptop either.
Any help is much appreciated.
Thanks,
Anoop
