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

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

3篇博客是分开的，合一块放到论坛，和大家讨论下。

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

在Mysql中可以动态的执行SQL 例如：

CREATE PROCEDURE `dynamic_sql`(sql varchar(500))
BEGIN
PREPARE stmt FROM sql;
EXECUTE stmt;
END

调用该存储过程

CALL dynamic_sql('select * from table');

是可以正确的返回SQL执行的结果。


数据库存储过程可以动态的执行SQL，Mybatis只需要负责调用该分页的存储过程即可。

需要额外做处理的部分是：禁止动态的SQL执行修改数据的操作。

具体实现方式是过滤Mysql修改数据的字符串，例如：update，delete等。


初步实现dynamic_paging，代码为

CREATE PROCEDURE `dynamic_paging`(sql varchar(500),page_begin int,size int)
BEGIN
set @lowercase:=lower(sql);
if(!LOCATE('call',@lowercase) && !LOCATE('delete',@lowercase) && !LOCATE('drop',@lowercase) && !LOCATE('truncate',@lowercase) && !LOCATE('update',@lowercase) && !LOCATE('delete',@lowercase) && !LOCATE('alter',@lowercase) )then
set @temp:='';
if(LOCATE('select',@lowercase))then
set @temp:=concat(@lowercase,' limit ',page_begin,',',page_end);
PREPARE stmt FROM @temp;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
end if;
END

参数及逻辑说明：
参数：
sql：动态查询的分页语句
page_begin,size：为limit的2个参数（limit page_begin,size）
逻辑：
将分页语句转为小写后，做修改数据关键字过滤，然后再分页语句最后加上 limit 操作。



调用该存储过程

CALL dynamic_sql('select * from tableA',0,10);
##或者
CALL dynamic_sql('select * from tableA,tableB where tableA.id=tableB.uid',0,10);
##或
CALL dynamic_sql('select * from tableA order by id desc',0,10);

至此数据库部分工作告一段落。


Mybatis是可以调用存储过程的。例如，在Mybatis的mapper文件中：

<select id="get***" resultMap="**Map"
		parameterMap="procMap" statementType="CALLABLE">
        CALL proc(?,?)
</select>
<parameterMap type="java.util.Map" id="procMap">
		<parameter property="param1" mode="IN" jdbcType="INTEGER" />
		<parameter property="param1" mode="IN" jdbcType="INTEGER" />
	</parameterMap>

分页的存储过程

CREATE PROCEDURE `dynamic_paging`(sql varchar(500),page_begin int,size int)

问题就在于dynamic_paging该存储过程第一个参数(sql)是需要在调用前动态生成。
例如：

select * from tableA,tableB where tableA.id=tableB.uid and id=10 

Mybatis 调用时的sql为：

select * from tableA,tableB where tableA.id=tableB.uid and id= ?

id=10这个是由程序传入的。是一个具体的业务数据。

而这部分又是调用dynamic_paging的第一个参数。

具体的解决方法为：MyBatis Velocity，链接http://www.mybatis.org/velocity-scripting/index.html



在配置文件中动态填充业务逻辑值，然后传给存储过程


例如：

<select id="get***"  resultMap="***Map"
		parameterMap="procMap" statementType="CALLABLE" lang="velocity" >

		#set( $sql = 'select * from tableA,tableB where tableA.id=tableB.uid and id='+$_parameter.id ) 
		#set( $begin=$_parameter.pageBegin)
		#set( $size=$_parameter.fetchSize)
		CALL dynamic_paging(@{sql},@{begin},@{size})
</select>

<parameterMap type="java.util.Map" id="procMap">
                <parameter property="id" />
		<parameter property="pageBegin" />
		<parameter property="fetchSize"/>
</parameterMap>

用到的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

附件图片为，JUnit 测试结果



存储过程实现，参见：http://xkorey.iteye.com/admin/blogs/2367209

Mybatis 实现，参见：http://xkorey.iteye.com/admin/blogs/2367212

工程demo：http://xkorey.iteye.com/blog/2367215

GitHub 链接:[url]https://github.com/noobthinker/spring4_mybatis3_mysql_dynamic_paging [/url]