想用MyBatis根据参数运算实现分页：
第一种：Select * from user limit #{p1},#{p2}-1
用这种方式是不行的，因为在MySQL中limit后面是不允许接表达式的;
在网上查了下MySQL还有一种动态分页，用MyBatis试了下报语法错误，直接在MySQL中运行是可以的，如下：
<select id="findUsers" resultType="sun.bean.dome.entity.User" statementType="PREPARED">
PREPARE PAGE FROM 'SELECT * FROM USER LIMIT ?,?';
SET @START=1;
SET @SIZE=3+1;
EXECUTE PAGE USING @START,@SIZE;
</select>
其实只想Limit后面的分页参数，是根据传递过来的参数动态运算出来的，求助～！
（数据库迁移Oracle到MySQL,所需分页参数不同）
Oracle分页：
select b.* from
(select a.* ,rownum rnum from (select * from bi_indexmlk) a where rownum<=5)
b where rnum>=3
sql server分页：
--显示第3个至第5个入学的学生信息
select top 3 * from student where sname not in
(select top 2 sname from student order by entertime )
order by entertime
MySQL分页：
Select * from 表名 limit startrow,pagesize
(Pagesize为每页显示的记录条数)
你可以参考下我的这篇博客http://leeyee.github.io/blog/2013/05/26/mybatis-simple-pagination/
使用拦截器实现的简单分页，是基于oracle的，你可以将拼凑分页的语句替换成mysql的分页语句。
以上只是一种处理mybatis分页的思路，你可以参考下。
本人很不喜欢把sql写的很复杂，更不喜欢把业务逻辑写到sql中去，所以能在程序代码解决的就别往sql里塞。当分表分库的时候sql级别基本解决不了什么问题的。所以建议算好再传进去。
2条评论
关注(1)
/**
* 模糊查询(分页)
*
* @project apqp
* @author liud JIRA:APQP-23
* @date 2013-04-16
* @param flow
* @param page
* 分页参数包装器
* @return pageData<Flow> 分页结果集
* @history
*/
@Override
public PageData<Attach> queryFlow(Attach attach, Pagination page) {
if (page.isReadTotal())
page.setTotal(this.selectOneByTotalCount("flow.queryList", attach));
RowBounds rowbounds = new RowBounds(page.getStart(), page.getLimit());
List<Attach> result = this.sqlSession.selectList("flow.queryList",
attach, rowbounds);
PageData<Attach> pageData = new PageData<Attach>();
pageData.setResult(result);
pageData.setPagination(page);
return pageData;
}
/**
* 获得分页的总条数
*
* @author liud
* @date 2013-2-18
* @param s
* mybatis对应的命名空间
* @param obj
* 参数
* @return
*/
protected int selectOneByTotalCount(String s, Object obj) {
final BaseEntity baseEntity = new BaseEntity();
RowBounds rowbounds = new RowBounds(-1, -1);
this.sqlSession.select(s, obj, rowbounds, new ResultHandler() {
@Override
public void handleResult(ResultContext context) {
Object object = context.getResultObject();
if (object != null) {
BaseEntity base = (BaseEntity) object;
baseEntity.setTotal(base.getTotal());
}
}
});
return baseEntity.getTotal();
}
-- mybatis.xml flow.queryList
<select id="queryList" resultType="com.eman.flow.entity.Attach"
parameterType="com.eman.flow.entity.Attach">
SELECT f1.fileID
, f1.fileName
, f1.fileType
, f1.fileSize
, f1.uploadUser
, f1.uploadTime
, f2.userName uploadUserName
FROM
t_attachmentOfAPQPFlowPhase p INNER
JOIN t_attachment f1
ON p.fileID =
f1.fileID
INNER JOIN t_user f2 ON f1.uploadUser =
f2.userID
WHERE
p.phaseID = #{phaseID}
order by f1.uploadTime desc
</select>
-- mybatis.config.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> <properties resource="sysconfig.properties" /> <plugins> <plugin interceptor="com.eman.core.interceptor.PaginationInterceptor" /> </plugins> </configuration>
package com.eman.core.interceptor;
import java.sql.Connection;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.eman.core.dialect.Dialect;
import com.eman.core.dialect.SQLServer2005Dialect;
import com.eman.core.dialect.SQLServer2008Dialect;
/**
* 拦截mybatis执行sql,以用于转换分页sql
*
* @author liud
*
*/
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PaginationInterceptor implements Interceptor {
private static final Logger log = LoggerFactory.getLogger("controller");
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler sh = (StatementHandler) invocation.getTarget();
MetaObject mo = MetaObject.forObject(sh);
RowBounds rb = (RowBounds) mo.getValue("delegate.rowBounds");
if (rb == RowBounds.DEFAULT
|| (rb.getLimit() <= 0 && rb.getLimit() != -1)
|| rb.getLimit() > RowBounds.NO_ROW_LIMIT)
return invocation.proceed();
Configuration conf = (Configuration) mo
.getValue("delegate.configuration");
Dialect dialect = this.invokeDialect(conf.getVariables()
.getProperty("jdbc.db.type").toUpperCase());
if (dialect == null)
return invocation.proceed();
String bSql = (String) mo.getValue("delegate.boundSql.sql");
if (rb.getLimit() == -1 && rb.getOffset() == -1)
bSql = dialect.getTotalString(bSql);
else
bSql = dialect.getLimitString(bSql, rb.getOffset(), rb.getLimit());
mo.setValue("delegate.boundSql.sql", bSql);
mo.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
mo.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 通过配置获得sql数据库对象
*
* @author liud
* @date 2013-2-18
* @param param
* @return
*/
private Dialect invokeDialect(String param) {
Dialect.Type type = null;
try {
type = Dialect.Type.valueOf(param);
} catch (Exception e) {
log.debug(
"the value of the dialect property in mybatis-config.xml is not defined:{}",
param);
}
Dialect dialect = null;
switch (type) {
case SQLSERVER:
break;
case SQLSERVER2005:
dialect = new SQLServer2005Dialect();
break;
case SQLSERVER2008:
dialect = new SQLServer2008Dialect();
break;
case ORACLE9I:
break;
case ORACLE10G:
break;
case ORACLE11G:
break;
case MYSQL5:
break;
}
return dialect;
}
}
package com.eman.core.dialect;
/**
* sql2008数据库的实现
*
* @author liud
*
*/
public class SQLServer2008Dialect implements Dialect {
private static final String SELECT = "select";
private static final String FROM = "from";
private static final String DISTINCT = "distinct";
@Override
public String getTotalString(String sql) {
StringBuilder sb = new StringBuilder(sql.trim().toLowerCase());
int orderbyIndex = sb.indexOf("order by");
if (orderbyIndex != -1) {
sb.delete(orderbyIndex, sb.length());
}
sb.insert(0, "WITH query AS (").append(
") SELECT count(*) total FROM query ");
return sb.toString();
}
@Override
public String getLimitString(String sql, int offset, int limit) {
if (offset > 1 || limit > 1)
return getLimitString(sql, offset, limit, true);
return sql;
}
/**
* Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for
* Paging)
*
* The LIMIT SQL will look like:
*
* <pre>
* WITH query AS (
* SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __liud_row_nr__,
* original_query_without_orderby
* )
* SELECT * FROM query WHERE __liud_row_nr__ BEETWIN offset AND offset + last
* </pre>
*
*
* @param querySqlString
* The SQL statement to base the limit query off of.
* @param offset
* Offset of the first row to be returned by the query
* (zero-based)
* @param limit
* Maximum number of rows to be returned by the query
*
* @return A new SQL statement with the LIMIT clause applied.
*/
private String getLimitString(String querySqlString, int offset, int limit,
boolean hasOffset) {
StringBuilder sb = new StringBuilder(querySqlString.trim()
.toLowerCase());
int orderByIndex = sb.indexOf("order by");
CharSequence orderby = orderByIndex > 0 ? sb.subSequence(orderByIndex,
sb.length()) : "ORDER BY CURRENT_TIMESTAMP";
// Delete the order by clause at the end of the query
if (orderByIndex > 0) {
sb.delete(orderByIndex, orderByIndex + orderby.length());
}
// HHH-5715 bug fix
replaceDistinctWithGroupBy(sb);
insertRowNumberFunction(sb, orderby);
// Wrap the query within a with statement:
sb.insert(0, "WITH query AS (").append(") SELECT * FROM query ");
sb.append("WHERE __liud_row_nr__ BETWEEN ").append(offset + 1)
.append(" AND ").append(offset + limit);
return sb.toString();
}
/**
* Right after the select statement of a given query we must place the
* row_number function
*
* @param sql
* the initial sql query without the order by clause
* @param orderby
* the order by clause of the query
*/
protected static void insertRowNumberFunction(StringBuilder sql,
CharSequence orderby) {
// Find the end of the select statement
int selectEndIndex = sql.indexOf(SELECT) + SELECT.length();
// Insert after the select statement the row_number() function:
sql.insert(selectEndIndex, " ROW_NUMBER() OVER (" + orderby
+ ") as __liud_row_nr__,");
}
/**
* Utility method that checks if the given sql query is a select distinct
* one and if so replaces the distinct select with an equivalent simple
* select with a group by clause. See
* {@link SQLServer2005DialectTestCase#testReplaceDistinctWithGroupBy()}
*
* @param sql
* an sql query
*/
protected static void replaceDistinctWithGroupBy(StringBuilder sql) {
int distinctIndex = sql.indexOf(DISTINCT);
if (distinctIndex > 0) {
sql.delete(distinctIndex, distinctIndex + DISTINCT.length() + 1);
sql.append(" group by").append(getSelectFieldsWithoutAliases(sql));
}
}
/**
* This utility method searches the given sql query for the fields of the
* select statement and returns them without the aliases. See
* {@link SQLServer2005DialectTestCase#testGetSelectFieldsWithoutAliases()}
*
* @param an
* sql query
* @return the fields of the select statement without their alias
*/
protected static CharSequence getSelectFieldsWithoutAliases(
StringBuilder sql) {
String select = sql.substring(sql.indexOf(SELECT) + SELECT.length(),
sql.indexOf(FROM));
// Strip the as clauses
return stripAliases(select);
}
/**
* Utility method that strips the aliases. See
* {@link SQLServer2005DialectTestCase#testStripAliases()}
*
* @param a
* string to replace the as statements
* @return a string without the as statements
*/
protected static String stripAliases(String str) {
return str.replaceAll("\\sas[^,]+(,?)", "$1");
}
}
把#改成$, mybatis会把#{}的变量变成PreparedStatement的参数, 而${}是字符串替换
Select * from user limit ${p1},${p2}-1
Mybatis可以直接把参数p1,p2传过来
Select * from user limit #{p1},#{p2}
想的那么麻烦干嘛
可以把select语句写到存储过程里，然后把p1,p2-1作为存储过程的参数。
或者你可以先把p2-1先算出来，再传给limit后面
相关推荐
SpringBoot整合mybatis-plus实现多数据源的动态切换且支持分页查询，案例以postgresql和oracle数据库为数据源，分别使用mybatis-plus分页插件和pagehelper分页插件实现分页查询。
但是，在参考写的同时也发现有很多地方都不解不能直接用的问题，导致实际使用的过程中经常会出错，参考原来做的项目，以及网上的资料，整理了一个比较简单的自己理解的spring+spring mvc+mybatis+mysql实现的分页的...
mybatis 分页 mybatis-generate Mysql数据库 大家知道mybatis自动生成代码是没有分页功能的 我在网上找了很久 有很多内容 但正真可以使用的少之又少 本人整合了网上的资源 整理了基于Mysql数据库的mybatis插件 经...
mybatis支持MYSQL分页 暂只支持MYSQL且不适用与MYBATIS-SPRING一起使用 调用方法 List selectList(String statement, int start ,int end) 与 List selectList(String statement, Object parameter, int start ,int ...
分页功能是我们日常开发中经常会遇到的，下面这篇文章主要给大家介绍了Spring MVC+MyBatis+MySQL实现分页功能的相关资料，文中介绍的非常详细，对大家具有一定的参考学习价值，需要的朋友们下面来一起看看吧。
基于Springboot+MyBatis+MySQL实现多功能个人博客系统 项目经过严格测试，确保可以运行！ 功能点： 用户注册登录功能 Markdown文章发布功能 文章分页显示 文章分类 文章标签/标签云 文章点击量 阅读排行 赞...
Spring+SpringMVC+MyBatis+Mysql 使用PageHelper 实现分页，里面包含数据库文件和项目的源码。数据库是mysql 5.5 的,项目加载进去就可以运行。
亲测可用mysql+mybatis分页 自己建一个数据库 名字test 表test 三列：包括id text1 text2 多添加几条记录 设置不同的page值就可以见到分页效果
适合学习mybatis，从初步到提升，包括增删改查，调用存储过程及分页，使用的是mybatis+mysql
主要给大家介绍了关于利用Spring MVC+Mybatis实现Mysql分页数据查询的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面跟着小编来一起学习学习吧。
自己封装的mybatis分页jar包，实现了mybatis的物理分页，目前只支持mysql和oracle两种数据库。
使用MyBatis的PageHelper插件实现分页查询的功能，主要涉及到的技术Spring+MyBatis+SpringMvc+Mysql+Druid+PageHelper。
springMvc+myBatis+mysql+分页，带架包，适合初学者！
springboot2.0.6+mybatis+mysql+分页+日志+web+模板页面+属性参数,可以直接作为项目框架进行开发
mybatis spring sqlserver mysql 框架 实现分页技术
MyBatis Plus 的多表联接、分页查询实现方法 http://localhost/common/getAllQuestionByPage/1/10 多表关联、分页查询接口 http://localhost/common/getAllQuestionWithStudentByPage/1/10 多表关联、分页带参数查询...
如果你也在用Mybatis，建议尝试该分页插件，这个一定是最方便使用的分页插件。 该插件目前支持Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库分页。
本套源代码实现了Spring 和 mybatis 和 layui 集成情况下的基础操作，包括： 增删改查、分页、导入导出等功能。数据库为mysql，脚本在源代码根目录下，mybatis_crud.sql
里面详细写了注释，灰常适合想进阶自己的程序员们，有什么不懂可以直接联系我的csdn账户哟
本项目前端采用jQuery-bootstrap-bootstraptable，后台采用Spring-SpringMVC-Mybatis-Mysql架构。由bootstraptable的设置实现数据库查询分页，页面显示上采用分页显示。并实现了数据的增删改查的弹窗以及操作。是...