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

yangjianzhouctgu

浏览: 428601 次
性别:
来自: 上海

最近访客更多访客>>

xubiao8716

Demo_

jiazhigang

chenweichu

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

mybatis
postgresql

使用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