mybatis实现postgresql数据库的分页查询
使用mybatis来实现数据库的分页查询
底层数据库为postgresql，实现分页查询的语句为：
select * from pg_userInfo limit pageSize offset os;
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><setting name="cacheEnabled" value="true" /><setting name="lazyLoadingEnabled" value="true" /><setting name="multipleResultSetsEnabled" value="true" /><setting name="useColumnLabel" value="true" /><setting name="useGeneratedKeys" value="true" /><setting name="defaultExecutorType" value="SIMPLE" /><setting name="defaultStatementTimeout" value="25000" /></settings><typeAliases><typeAlias type="com.mybatis.domain.User" alias="User" /></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="org.postgresql.Driver" /><property name="url" value="jdbc:postgresql://localhost:5432/mybatis" /><property name="username" value="postgres" /><property name="password" value="admin" /></dataSource></environment></environments><mappers><mapper resource="com/mybatis/domain/User.xml" /></mappers></configuration>
package com.mybatis.dao;import java.io.Reader;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.mybatis.domain.User;/** * 切记：每次增删改查时都要commit一下，提交事务，即session.commit(); * @author yangjianzhou * */public class UserDao {private String resource = "com/mybatis/configuration/mybatis.xml";/** * * @param user * @throws Exception */public void insert(User user) throws Exception{Reader reader = Resources.getResourceAsReader(resource);SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);SqlSession session = ssf.openSession();reader.close();session.insert("UserDaoMapping.insertUser", user);session.commit();session.close();}public List<User> pageSelect(int pageSize,int offset) throws Exception{Reader reader = Resources.getResourceAsReader(resource);SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);SqlSession session = ssf.openSession();reader.close();Map<String,Integer> params = new HashMap<String,Integer>();params.put("pageSize", pageSize);params.put("offset", offset);List<User> userList = session.selectList("UserDaoMapping.pageSelect",params);session.commit();session.close();return userList;}public int getTotalUsers() throws Exception{Reader reader = Resources.getResourceAsReader(resource);SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);SqlSession session = ssf.openSession();reader.close();int totalUsers = session.selectOne("UserDaoMapping.selectTotalRecord");session.commit();session.close();return totalUsers;}}package com.mybatis.domain;public class User {private Integer id;//用户idprivate String username;//用户名private String password;//密码private String address;//地址public User(){}public User(String username,String password,String address){this.username = username;this.password = password;this.address =address;}public User(Integer id,String username,String password,String address){this.id = id;this.username = username;this.password = password;this.address =address;}public int getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String toString(){return "当前用户为：id = "+id+",username = "+username+",password = "+password+",address = "+address;}}<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="UserDaoMapping"><resultMap type="User" id="user"><constructor><idArg column="id" javaType="int" /><arg column="username" javaType="string" /><arg column="password" javaType="string" /><arg column="address" javaType="string" /></constructor></resultMap><insert id="insertUser" parameterType="User">insert into pg_userInfo(username,password,address) values(#{username},#{password},#{address});</insert><select id="pageSelect" parameterType="map" resultMap="user">select * from pg_userInfo limit #{pageSize} offset #{offset};</select><select id="selectTotalRecord" resultType="int">select count(*) from pg_userInfo;</select></mapper>package com.mybatis.service;import java.util.List;import com.mybatis.dao.UserDao;import com.mybatis.domain.User;public class UserService {private UserDao userDao = new UserDao();public UserDao getUserDao() {return userDao;}public void setUserDao(UserDao userDao) {this.userDao = userDao;}public int getCountUsers() throws Exception {return userDao.getTotalUsers();}public List<User> getPageUsers(int pageSize,int offset) throws Exception{return userDao.pageSelect(pageSize, offset);}public void insertUser(User user) throws Exception{userDao.insert(user);}}package com.mybatis.utils;import java.util.List;public class PageModel {private int currentPage;//当前页private int pageSize;//每页显示条数private int totalPage;//总页数private int totalRecord;//总记录数private List dataList;//分页数据private PageModel(){}private PageModel(final int pageSize,final int page,final int totalRecord){this.pageSize = pageSize;this.totalRecord = totalRecord;setTotalPage();setCurrentPage(page);}public static PageModel newPageModel(final int pageSize,final int page,final int totalRecord){return new PageModel(pageSize, page, totalRecord);}public void setCurrentPage(int page){currentPage = page;if(currentPage<1){currentPage =1;}if(currentPage>totalPage){currentPage=totalPage;}}private void setTotalPage(){if(totalRecord%pageSize==0){totalPage = totalRecord/pageSize;}else{totalPage = totalRecord/pageSize+1;}}public int getOffset(){return (currentPage-1)*pageSize;}public int getFirst(){return 1;}public int getPrevious(){return currentPage-1;}public int getNext(){return currentPage+1;}public int getLast(){return totalPage;}public int getCurrentPage() {return currentPage;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getTotalRecord() {return totalRecord;}public void setTotalRecord(int totalRecord) {this.totalRecord = totalRecord;}public List getDataList() {return dataList;}public void setDataList(List dataList) {this.dataList = dataList;}}package com.mybatis.test;import java.util.List;import com.mybatis.domain.User;import com.mybatis.service.UserService;import com.mybatis.utils.PageModel;public class Test {private static UserService userService = new UserService();public static void main(String[] args) throws Exception{ int pageSize = 10; int offset = 2; int totalRecord = userService.getCountUsers(); int page =1; PageModel pm = PageModel.newPageModel(pageSize, page, totalRecord); int totalPage =pm.getTotalPage(); for(int i=1;i<=totalPage;i++){ pm.setCurrentPage(i); offset = pm.getOffset(); List<User> listUsers = userService.getPageUsers(pageSize, offset); System.out.println("=======================第"+i+"页数据如下============================"); for(int j=0;j<listUsers.size();j++){ System.out.println(listUsers.get(j)); } } //testInsert(); }public static void testInsert() throws Exception{for(int i=0;i<100;i++){User user = new User("yangjianzhou:"+i,"password:"+i,"shanghai-songjiang:"+i);userService.insertUser(user);}}}=======================第1页数据如下============================当前用户为：id = 1,username = yangjianzhou:0,password = password:0,address = password:0当前用户为：id = 2,username = yangjianzhou:1,password = password:1,address = password:1当前用户为：id = 3,username = yangjianzhou:2,password = password:2,address = password:2当前用户为：id = 4,username = yangjianzhou:3,password = password:3,address = password:3当前用户为：id = 5,username = yangjianzhou:4,password = password:4,address = password:4当前用户为：id = 6,username = yangjianzhou:5,password = password:5,address = password:5当前用户为：id = 7,username = yangjianzhou:6,password = password:6,address = password:6当前用户为：id = 8,username = yangjianzhou:7,password = password:7,address = password:7当前用户为：id = 9,username = yangjianzhou:8,password = password:8,address = password:8当前用户为：id = 10,username = yangjianzhou:9,password = password:9,address = password:9=======================第2页数据如下============================当前用户为：id = 11,username = yangjianzhou:10,password = password:10,address = password:10当前用户为：id = 12,username = yangjianzhou:11,password = password:11,address = password:11当前用户为：id = 13,username = yangjianzhou:12,password = password:12,address = password:12当前用户为：id = 14,username = yangjianzhou:13,password = password:13,address = password:13当前用户为：id = 15,username = yangjianzhou:14,password = password:14,address = password:14当前用户为：id = 16,username = yangjianzhou:15,password = password:15,address = password:15当前用户为：id = 17,username = yangjianzhou:16,password = password:16,address = password:16当前用户为：id = 18,username = yangjianzhou:17,password = password:17,address = password:17当前用户为：id = 19,username = yangjianzhou:18,password = password:18,address = password:18当前用户为：id = 20,username = yangjianzhou:19,password = password:19,address = password:19=======================第3页数据如下============================当前用户为：id = 21,username = yangjianzhou:20,password = password:20,address = password:20当前用户为：id = 22,username = yangjianzhou:21,password = password:21,address = password:21当前用户为：id = 23,username = yangjianzhou:22,password = password:22,address = password:22当前用户为：id = 24,username = yangjianzhou:23,password = password:23,address = password:23当前用户为：id = 25,username = yangjianzhou:24,password = password:24,address = password:24当前用户为：id = 26,username = yangjianzhou:25,password = password:25,address = password:25当前用户为：id = 27,username = yangjianzhou:26,password = password:26,address = password:26当前用户为：id = 28,username = yangjianzhou:27,password = password:27,address = password:27当前用户为：id = 29,username = yangjianzhou:28,password = password:28,address = password:28当前用户为：id = 30,username = yangjianzhou:29,password = password:29,address = password:29=======================第4页数据如下============================当前用户为：id = 31,username = yangjianzhou:30,password = password:30,address = password:30当前用户为：id = 32,username = yangjianzhou:31,password = password:31,address = password:31当前用户为：id = 33,username = yangjianzhou:32,password = password:32,address = password:32当前用户为：id = 34,username = yangjianzhou:33,password = password:33,address = password:33当前用户为：id = 35,username = yangjianzhou:34,password = password:34,address = password:34当前用户为：id = 36,username = yangjianzhou:35,password = password:35,address = password:35当前用户为：id = 37,username = yangjianzhou:36,password = password:36,address = password:36当前用户为：id = 38,username = yangjianzhou:37,password = password:37,address = password:37当前用户为：id = 39,username = yangjianzhou:38,password = password:38,address = password:38当前用户为：id = 40,username = yangjianzhou:39,password = password:39,address = password:39=======================第5页数据如下============================当前用户为：id = 41,username = yangjianzhou:40,password = password:40,address = password:40当前用户为：id = 42,username = yangjianzhou:41,password = password:41,address = password:41当前用户为：id = 43,username = yangjianzhou:42,password = password:42,address = password:42当前用户为：id = 44,username = yangjianzhou:43,password = password:43,address = password:43当前用户为：id = 45,username = yangjianzhou:44,password = password:44,address = password:44当前用户为：id = 46,username = yangjianzhou:45,password = password:45,address = password:45当前用户为：id = 47,username = yangjianzhou:46,password = password:46,address = password:46当前用户为：id = 48,username = yangjianzhou:47,password = password:47,address = password:47当前用户为：id = 49,username = yangjianzhou:48,password = password:48,address = password:48当前用户为：id = 50,username = yangjianzhou:49,password = password:49,address = password:49=======================第6页数据如下============================当前用户为：id = 51,username = yangjianzhou:50,password = password:50,address = password:50当前用户为：id = 52,username = yangjianzhou:51,password = password:51,address = password:51当前用户为：id = 53,username = yangjianzhou:52,password = password:52,address = password:52当前用户为：id = 54,username = yangjianzhou:53,password = password:53,address = password:53当前用户为：id = 55,username = yangjianzhou:54,password = password:54,address = password:54当前用户为：id = 56,username = yangjianzhou:55,password = password:55,address = password:55当前用户为：id = 57,username = yangjianzhou:56,password = password:56,address = password:56当前用户为：id = 58,username = yangjianzhou:57,password = password:57,address = password:57当前用户为：id = 59,username = yangjianzhou:58,password = password:58,address = password:58当前用户为：id = 60,username = yangjianzhou:59,password = password:59,address = password:59=======================第7页数据如下============================当前用户为：id = 61,username = yangjianzhou:60,password = password:60,address = password:60当前用户为：id = 62,username = yangjianzhou:61,password = password:61,address = password:61当前用户为：id = 63,username = yangjianzhou:62,password = password:62,address = password:62当前用户为：id = 64,username = yangjianzhou:63,password = password:63,address = password:63当前用户为：id = 65,username = yangjianzhou:64,password = password:64,address = password:64当前用户为：id = 66,username = yangjianzhou:65,password = password:65,address = password:65当前用户为：id = 67,username = yangjianzhou:66,password = password:66,address = password:66当前用户为：id = 68,username = yangjianzhou:67,password = password:67,address = password:67当前用户为：id = 69,username = yangjianzhou:68,password = password:68,address = password:68当前用户为：id = 70,username = yangjianzhou:69,password = password:69,address = password:69=======================第8页数据如下============================当前用户为：id = 71,username = yangjianzhou:70,password = password:70,address = password:70当前用户为：id = 72,username = yangjianzhou:71,password = password:71,address = password:71当前用户为：id = 73,username = yangjianzhou:72,password = password:72,address = password:72当前用户为：id = 74,username = yangjianzhou:73,password = password:73,address = password:73当前用户为：id = 75,username = yangjianzhou:74,password = password:74,address = password:74当前用户为：id = 76,username = yangjianzhou:75,password = password:75,address = password:75当前用户为：id = 77,username = yangjianzhou:76,password = password:76,address = password:76当前用户为：id = 78,username = yangjianzhou:77,password = password:77,address = password:77当前用户为：id = 79,username = yangjianzhou:78,password = password:78,address = password:78当前用户为：id = 80,username = yangjianzhou:79,password = password:79,address = password:79=======================第9页数据如下============================当前用户为：id = 81,username = yangjianzhou:80,password = password:80,address = password:80当前用户为：id = 82,username = yangjianzhou:81,password = password:81,address = password:81当前用户为：id = 83,username = yangjianzhou:82,password = password:82,address = password:82当前用户为：id = 84,username = yangjianzhou:83,password = password:83,address = password:83当前用户为：id = 85,username = yangjianzhou:84,password = password:84,address = password:84当前用户为：id = 86,username = yangjianzhou:85,password = password:85,address = password:85当前用户为：id = 87,username = yangjianzhou:86,password = password:86,address = password:86当前用户为：id = 88,username = yangjianzhou:87,password = password:87,address = password:87当前用户为：id = 89,username = yangjianzhou:88,password = password:88,address = password:88当前用户为：id = 90,username = yangjianzhou:89,password = password:89,address = password:89=======================第10页数据如下============================当前用户为：id = 91,username = yangjianzhou:90,password = password:90,address = password:90当前用户为：id = 92,username = yangjianzhou:91,password = password:91,address = password:91当前用户为：id = 93,username = yangjianzhou:92,password = password:92,address = password:92当前用户为：id = 94,username = yangjianzhou:93,password = password:93,address = password:93当前用户为：id = 95,username = yangjianzhou:94,password = password:94,address = password:94当前用户为：id = 96,username = yangjianzhou:95,password = password:95,address = password:95当前用户为：id = 97,username = yangjianzhou:96,password = password:96,address = password:96当前用户为：id = 98,username = yangjianzhou:97,password = password:97,address = password:97当前用户为：id = 99,username = yangjianzhou:98,password = password:98,address = password:98当前用户为：id = 100,username = yangjianzhou:99,password = password:99,address = password:99