场景是一个后台的管理项目,突然要把数据库从oracle改成sqlserver,于是把表啊数据之类的,完全迁移到sqlserver之后,代码中的ibatis的写好的oracle的分页,在基于sqlsever数据库的系统上,不能运行,比如oracle里的dual表,rownum等,sqlserver里没有。
- <select id="getLogList" resultClass="monitorLog" parameterClass="monitorLog">
- SELECT *
- FROM (SELECT c.*, rownum r
- from (select
- LOG_ID logID,
- LOG_OPERTOR logOpertor,
- OPERTOR_TIME opertorTime,
- LOG_DESC logDesc
- from MONITOR_LOG a
- WHERE 11 = 1
- <isNotEmpty property = "dateStart" prepend = "and" >
- to_char(a.OPERTOR_TIME,'yyyy-MM-dd hh24:mi:ss') >=#dateStart# and to_char(a.OPERTOR_TIME,'yyyy-MM-dd hh24:mi:ss') <=#dateEnd#
- </isNotEmpty>
- <isNotEmpty property = "logOpertor" prepend = "and" >
- a.LOG_OPERTOR =#logOpertor#
- </isNotEmpty>
- order by OPERTOR_TIME desc) c)
- where r >= #startRow#
- AND rownum <= #pageSize#
以上是oracle的下,根据rownum来分页的,startRow和pageSize分别是实体类中对应的字段,开始行和页大小。
迁移到sqlserver之后,因为startRow和pageSize已经定了,所以修改的话,也得基于这两个字段。修改代码如下:
- <select id="getLogList" resultClass="monitorLog" parameterClass="monitorLog">
- SELECT TOP $pageSize$
- LOG_ID logID,
- LOG_OPERTOR logOpertor,
- OPERTOR_TIME opertorTime,
- LOG_DESC logDesc
- from MONITOR_LOG a
- WHERE 11 = 1
- <isNotEmpty property = "dateStart" prepend = "and" >
- CONVERT(varchar(100), a.OPERTOR_TIME, 23) >=#dateStart# and CONVERT(varchar(100), a.OPERTOR_TIME, 23) <=#dateEnd#
- </isNotEmpty>
- <isNotEmpty property = "logOpertor" prepend = "and" >
- a.LOG_OPERTOR =#logOpertor#
- </isNotEmpty>
- and LOG_ID>=(
- select max(LOG_ID) from (
- select top $startRow$ LOG_ID from MONITOR_LOG order by LOG_ID
- ) as t
- ) order by LOG_ID
- </select>