环境
基于Mybatis
的动态SQL。
使用fastjson格式化输出数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| @Repository public interface StudentDao { List<Map<String, Object>> getStudent(@Param("sex") String sex, @Param("page") int page, @Param("pageSize") int pageSize); }
public class MyTest { private StudentDao dao; public void test() { List<Map<String, Object>> data = dao.getStudent("男", 2, 10); System.out.println(JSONObject.toJSONString(data)); } }
|
SQL Server 2005
在2005之后, 使用ROW_NUMBER()
函数标记记录的行号, 然后再使用where
进行筛选。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| <select id="getStudent" resultType="Map"> <bind name="skip" value="(page-1)*pageSize"/> <bind name="record" value="page*pageSize"/> SELECT * FROM ( SELECT TOP 100 PERCENT tmp.*, ROW_NUMBER() OVER (ORDER BY tmp.birthday desc) AS row_num FROM ( SELECT DISTINCT s.id, s.name, CONVERT (VARCHAR(10), s.birthday, 120) AS birthday FROM student s WHERE s.sex = ${sex} ORDER BY s.birthday ) tmp ORDER BY row_num ) tmp <![CDATA[WHERE row_num > ${skip} and row_num <= ${record}]]> </select>
|
SQL Server 2012
在2012之后, 使用OFFSET FETCH 子句进行分页处理。
1 2 3 4 5 6 7 8
| <select id="getStudent" resultType="Map"> <bind name="skip" value="(page-1)*pageSize"/> SELECT DISTINCT s.id, s.name, CONVERT (VARCHAR(10), s.birthday, 120) AS birthday FROM student s WHERE s.sex = ${sex} ORDER BY s.birthday OFFSET ${skip} ROWS FETCH NEXT ${pageSize} ROWS ONLY; </select>
|