mybatis实现postgresql数据库的分页查询

linuxx

使用mybatis来实现数据库的分页查询
底层数据库为postgresql，实现分页查询的语句为：

select * from pg_userInfo limit pageSize offset os;


解释：pageSize是一个页面上显示的数据的条数，os为这一页的第一条记录到这个表的第一条记
录的偏移量
为了实现分页查询，我们现在数据库表里面插入了100条数据。
代码结构如下：

mybatis.xml配置文件内容如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//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>


UserDao.java代码如下：

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;
}
}


User.java代码如下：

package com.mybatis.domain;
public class User {
private Integer id;//用户id
private 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;
}
}


User.xml代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//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>


UserService.java代码如下：

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);
}
}


PageModel.java代码如下：

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;
}
}


Test.java代码如下：

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