Spring、Mybatis、Mysql 通过存储过程实现分页--工程demo

Mybatis的分页功能可不可以通过数据库中的存储过程动态执行查询来帮助实现？

Spring、Mybatis、Mysql 通过存储过程实现分页博客一共有3部分
第一部分：存储过程动态分页之存储过程实现
第二部分：存储过程动态分页之Mybatis实现
第三部分：存储过程动态分页之实际工程demo

目前这篇讲的是
第三部分：存储过程动态分页之实际工程demo


项目介绍

eclipse，maven，spring4，mybatis3，c3p0，mysql

用到的mybatis插件：

MyBatis Velocity 链接：http://www.mybatis.org/velocity-scripting/index.html

Mybatis spring 链接：http://www.mybatis.org/spring/

MyBatis Generator 链接：http://www.mybatis.org/generator/



已上传至GitHub。
链接:https://github.com/noobthinker/spring4_mybatis3_mysql_dynamic_paging

简单的在test库中建立了2个测试表

#user 表
CREATE TABLE  `test`.`user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) DEFAULT NULL,
  `userAge` int(11) DEFAULT NULL,
  `userAddress` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

#fun_log 表
CREATE TABLE  `test`.`fun_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `info` text DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

插入了一些数据

user表

@Test
	public void testAddUser(){
		for(int i=1;i<=100;i++){
			User user = new User();
			user.setId(i);
			user.setUserAddress(i+"--this is a test b.");
			user.setUserAge(10+i);
			user.setUserName(i+"--xkorey");
			userService.addUser(user);
			System.out.println(user.getId());
		}
	}

user 表内容

id   userName  userAge     userAddress   
1, '1--xkorey', 11, '1--this is a test b.'
2, '2--xkorey', 12, '2--this is a test b.'
3, '3--xkorey', 13, '3--this is a test b.'
4, '4--xkorey', 14, '4--this is a test b.'
5, '5--xkorey', 15, '5--this is a test b.'
6, '6--xkorey', 16, '6--this is a test b.'
7, '7--xkorey', 17, '7--this is a test b.'
8, '8--xkorey', 18, '8--this is a test b.'
9, '9--xkorey', 19, '9--this is a test b.'
10, '10--xkorey', 20, '10--this is a test b.'

fun_log表内容

id  info         user_id
1, '1--log info', 1
2, '2--log info', 1
3, '3--log info', 1
4, '4--log info', 1
5, '5--log info', 1
6, '6--log info', 1
7, '7--log info', 1
8, '8--log info', 1
9, '9--log info', 1
10, '10--log info', 1

fun_log表

@Test
	public void testAddFunLog(){
		for(long i=1;i<=100;i++){
			FunLog log = new FunLog();
			log.setId(i);
			log.setUser_id(i);
			log.setInfo(i+"--log info");
			logService.addLog(log);
		}
	}

userservice

public interface UserService {
/**
	 * 分页获取用户
	 * @param begin 开始位置
	 * @param size  获取数量
	 * @return
	 */
	public List<User> getUserByList(Integer begin,Integer size);
/**
	 * 分页获取用户log信息
	 * @param userId 用户id
	 * @param begin 开始位置
	 * @param size 获取数量
	 * @return
	 */
	public List<FunLog> getUserLogsList(Integer userId,Integer begin,Integer size);
}

Test 执行结果

获取3条user记录

@Test
	public void testGetUserByPage(){
		List<User> users = userService.getUserByList(0,3);
		System.out.println(users.size());
		for(User user:users){
			System.out.println(user.getUserName());
		}
	}

log 打印出的Mybatis 执行日志

==>  Preparing: CALL dynamic_paging('select * from user',?,?) 
==> Parameters: 0(Integer), 3(Integer)
<==    Columns: id, userName, userAge, userAddress
<==        Row: 1, 1--xkorey, 11, 1--this is a test b.
<==        Row: 2, 2--xkorey, 12, 2--this is a test b.
<==        Row: 3, 3--xkorey, 13, 3--this is a test b.
<==      Total: 3
<==    Updates: 0

console 输出获取到的记录

3
1--xkorey
2--xkorey
3--xkorey

例如：获取user表从第4条开始，获取6条记录

修改Test类

#List<User> users = userService.getUserByList(0,3);
List<User> users = userService.getUserByList(4,6);

Mybatis 执行日志

==>  Preparing: CALL dynamic_paging('select * from user',?,?) 
==> Parameters: 4(Integer), 6(Integer)
<==    Columns: id, userName, userAge, userAddress
<==        Row: 5, 5--xkorey, 15, 5--this is a test b.
<==        Row: 6, 6--xkorey, 16, 6--this is a test b.
<==        Row: 7, 7--xkorey, 17, 7--this is a test b.
<==        Row: 8, 8--xkorey, 18, 8--this is a test b.
<==        Row: 9, 9--xkorey, 19, 9--this is a test b.
<==        Row: 10, 10--xkorey, 20, 10--this is a test b.
<==      Total: 6
<==    Updates: 0

控制台输出

6
5--xkorey
6--xkorey
7--xkorey
8--xkorey
9--xkorey
10--xkorey

刚才演示了单个表的数据分页情况。

下面演示2个表数据分页情况。


先演示获取用户日志的sql 和执行情况

SELECT * FROM user,fun_log f where f.user_id=user.id and user.id=1;

执行结果

user.id userName userAge userAddress     fun_log.id info  fun_log.user_id
1, '1--xkorey', 11, '1--this is a test b.', 1, '1--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 2, '2--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 3, '3--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 4, '4--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 5, '5--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 6, '6--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 7, '7--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 8, '8--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 9, '9--log info', 1
1, '1--xkorey', 11, '1--this is a test b.', 10, '10--log info', 1

Test 方法

@Test
	public void testGetUserLogsByPage(){
		List<FunLog> logs = userService.getUserLogsList(1,0,2);
		System.out.println(logs.size());
		for(FunLog log:logs){
			System.out.println(log.getInfo());
		}
	}

Mybatis 执行日志

==>  Preparing: CALL dynamic_paging(?,?,?) 
==> Parameters: SELECT * FROM user,fun_log f where f.user_id=user.id and user.id=1(String), 0(Integer), 2(Integer)
<==    Columns: id, userName, userAge, userAddress, id, info, user_id
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 1, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 2, <<BLOB>>, 1
<==      Total: 2
<==    Updates: 0

console 输出

2
1--log info
2--log info

可以看到关键部分：

SELECT * FROM user,fun_log f where f.user_id=user.id and user.id=1(String), 0(Integer), 2(Integer)
是Mybatis实现动态查询的关键部分。

再次修改下测试类，获取从user id 是1 的log 第4条开始，获取6条记录

#List<FunLog> logs = userService.getUserLogsList(1,0,2);
List<FunLog> logs = userService.getUserLogsList(1,4,6);

Mybatis 执行日志

Preparing: CALL dynamic_paging(?,?,?) 
==> Parameters: SELECT * FROM user,fun_log f where f.user_id=user.id and user.id=1(String), 4(Integer), 6(Integer)
<==    Columns: id, userName, userAge, userAddress, id, info, user_id
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 5, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 6, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 7, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 8, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 9, <<BLOB>>, 1
<==        Row: 1, 1--xkorey, 11, 1--this is a test b., 10, <<BLOB>>, 1
<==      Total: 6
<==    Updates: 0

console输出

6
5--log info
6--log info
7--log info
8--log info
9--log info
10--log info