select * from pg_userInfo limit pageSize offset os;

解释：pageSize是一个页面上显示的数据的条数，os为这一页的第一条记录到这个表的第一条记

录的偏移量

为了实现分页查询，我们现在数据库表里面插入了100条数据。

代码结构如下：

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

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

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 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 params = new HashMap();

params.put("pageSize", pageSize);

params.put("offset", offset);

List 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代码如下：

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

insert into pg_userInfo(username,password,address) values(#{username},#{password},#{address});

select * from pg_userInfo limit #{pageSize} offset #{offset};

select count(*) from pg_userInfo;

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 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 listUsers = userService.getPageUsers(pageSize, offset);

System.out.println("=======================第"+i+"页数据如下============================");

for(int j=0;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

大小: 43.6 KB

分享到：

2013-06-29 18:11

浏览 13160

评论