mybatis怎么实现物理分页啊,有没有什么插件之类的,类似于hibernate那样好用的方法
你可以写mybatis分页插件,mybatisConfig.xml
中加
<plugins>
<plugin interceptor="com.xxx.xxx.utils.MyBatisPagePlugin">
<property name="dialect" value="oracle"/>
<property name="pageMatch" value=".*Page$"/>
</plugin>
</plugins>
插件类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
*
* @ClassName MyBatisPagePlugin
* @Description mybatis分页插件
*
*/
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class MyBatisPagePlugin implements Interceptor{
private static final Logger log = LoggerFactory.getLogger(MyBatisPagePlugin.class);
private String dialect="oracle";
private String pageMatch=".*Page$";
@Override
public Object intercept(Invocation invocation) throws Throwable {
if(invocation.getTarget() instanceof StatementHandler){
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
while(metaObject.hasGetter("h")){
Object object = metaObject.getValue("h");
metaObject = SystemMetaObject.forObject(object);
}
while(metaObject.hasGetter("target")){
Object object = metaObject.getValue("target");
metaObject = SystemMetaObject.forObject(object);
}
MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
if(mappedStatement.getId().matches(pageMatch)){
BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");
String sql = boundSql.getSql();
Assert.notNull(metaObject.getValue("delegate.boundSql.parameterObject"), "parameterObject is null");
PageBean pageBean = (PageBean)metaObject.getValue("delegate.boundSql.parameterObject.pageBean");
Object needCountObj = metaObject.getValue("delegate.boundSql.parameterObject.needCount");
Object needPageObj = metaObject.getValue("delegate.boundSql.parameterObject.needPage");
boolean needCount = true;
boolean needPage = true;
if(needCountObj!=null){
needCount = (Boolean)needCountObj;
}
if(needPageObj!=null){
needPage = (Boolean)needPageObj;
}
if(needPage){
String pageSql = buildPageSql(sql, pageBean);
metaObject.setValue("delegate.boundSql.sql", pageSql);
if(needCount){
Connection conn = (Connection)invocation.getArgs()[0];
setPageParameters(sql, pageBean, conn, mappedStatement, boundSql);
}
}
}
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
if(target instanceof StatementHandler){
return Plugin.wrap(target, this);
}else{
return target;
}
}
@Override
public void setProperties(Properties properties) {
this.dialect = properties.getProperty("dialect");
this.pageMatch = properties.getProperty("pageMatch");
}
private String buildPageSql(String sql,PageBean pageBean){
Assert.notNull(pageBean, "pageBean is null");
return buildPageSqlForDialect(dialect, sql, pageBean).toString();
}
private StringBuilder buildPageSqlForDialect(String dialect,String sql,PageBean pageBean){
StringBuilder builder = new StringBuilder();
if("oracle".equalsIgnoreCase(dialect)){
builder.append("SELECT * FROM (SELECT A.*,ROWNUM R FROM (");
builder.append(sql);
builder.append(") A WHERE ROWNUM<=").append(pageBean.getPageLast());
builder.append(") WHERE R>").append(pageBean.getStart());
}else if("mysql".equalsIgnoreCase(dialect)){
builder.append(sql);
builder.append(" LIMIT ").append(pageBean.getStart()).append(",").append(pageBean.getPageLast());
}else{
builder.append(sql);
}
log.debug("分页sql:{}",builder);
return builder;
}
private void setPageParameters(String sql,PageBean pageBean,Connection connection,MappedStatement mappedStatement,BoundSql boundSql){
String countSql = "SELECT COUNT(*) FROM ("+sql+")";
PreparedStatement statement = null;
ResultSet rs = null;
try {
statement = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
setParameters(statement, mappedStatement, countBS, boundSql.getParameterObject());
rs = statement.executeQuery();
int totalCount = 0;
if(rs.next()){
totalCount = rs.getInt(1);
}
pageBean.setTotal(totalCount);
} catch (SQLException e) {
log.error("PagePlugin error get total count:",e);
}finally{
try {
rs.close();
statement.close();
} catch (SQLException e) {
log.error("PagePlugin error close rs or stmt:",e);
}
}
}
private void setParameters(PreparedStatement statement,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject)throws SQLException{
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(statement);
}
}
Mybatis sqlmap中已Page结尾的会加分页,oracle的你看着改吧
我们这边只需要用实体类生成诸如 dao,service,action, sqlmap,struts配置等等,基本不需要写太多代码,而是否分页取决于你sql的id上是否带有Page后缀
我们公司写mybatis分页直接是在SQL语句中做的。如果靠mybatis分页,会增加很多代码。limit #{limit} offset #{offset},基本上大部分数据库都支持这种分页写法。如果不想分页加个if判断。