cn.easyproject.easymybatis.pagination.PageBean Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of easymybatis-pagination Show documentation
Show all versions of easymybatis-pagination Show documentation
EasyMyBatis Pagination is a generic paging plugin for the MyBaits framework. Provides the PageBean automatic paging data encapsulation, the EasyCriteria paging condition object, and the automated paging SQL that supports common databases.
package cn.easyproject.easymybatis.pagination;
import java.io.Serializable;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
*
* MyBatis PageBean Demo:
*
*
* **DAO interface:**
* public List pagination(PageBean pb);
*
* **SQL Mapper:**
* <select id="pagination" resultType="Account">
* ${autoSQL}
* </select>
*
* // **Service:**
* // EasyCriteria
* AccountCriteria accountCriteria=new AccountCriteria();
* accountCriteria.setAccountName("1");
*
* PageBean pb=new PageBean();
* // FROM Clause; optional; default is *
* pb.setSelect("*");
* // Table Name; rquired
* pb.setFrom("Account account");
* // WHERE Clause; optional; default is ''
* pb.setCondition(" and account.qxid>=10");
* // Append where clause condition; optional; default is ''
* // * pb.addCondition("");
* // Append EasyCriteria condition
* pb.setEasyCriteria(accountCriteria);
* // SortName; optional; default is ''
* pb.setSort("account.accountid");
* // SortOrder; optional; default is 'asc'
* pb.setSortOrder("desc");
* // Page Number; optional; default is 1
* pb.setPageNo(1);
* // Rows per page; optional; default is 10
* pb.setRowsPerPage(4);
*
* accountDAO.pagination(pb)
* // Pagination data
* System.out.println(pb.getData());
* System.out.println(pb.getPageTotal());
* System.out.println(pb.getPageNo());
* System.out.println(pb.getRowsPerPage());
* System.out.println(pb.getRowsCount());
*
* // **Direct set pagination SQL**
* PageBean pb=new PageBean();
* pb.setSql("select * from Account where accountName like #{accountName} limit 0,5");
* pb.setCountSQL("select count(*) from Account where accountName like #{accountName}");
* // SQL Parameter Values
* Map<String, Object> values=new HashMap();
* values.put("accountName", "%1%");
* pb.setSqlParameterValues(values);
*
* accountDAO.pagination(pb)
*
* // Pagination data
* System.out.println(pb.getData());
* System.out.println(pb.getPageTotal());
* System.out.println(pb.getPageNo());
* System.out.println(pb.getRowsPerPage());
* System.out.println(pb.getRowsCount());
*
*
*
* @author Ray
* @author [email protected]
* @author easyproject.cn
* @since 1.0.0
* @see https://github.com/ushelp/EasyMyBatisPagination
*/
@SuppressWarnings("unused")
public class PageBean implements Serializable {
public void setRowsCount(int rowsCount) {
this.rowsCount = rowsCount;
}
public void setData(List data) {
this.data = data;
}
private static final long serialVersionUID = 5229229913348492552L;
/**数据库类型（使用ORACLE，SQLSERVER，MYSQL常量标识）**/
public int dbType=MYSQL;
/**ORACLE数据库**/
public final static int ORACLE=1;
public final static int ORACLE_12C=10;
/**SQLSERVER数据库**/
public final static int SQLSERVER=2;
public final static int SQLSERVER_2012=20;
/**MYSQL数据库**/
public final static int MYSQL=3;
/*
* Default CONSTANT
*/
private final static int DEFAULT_PAGE_NO=1;
private final static int DEFAULT_ROWS_PER_PAGE=10;
private final static String DEFAULT_SELECT="*";
private final static String DEFAULT_SORT_ORDER="ASC";
/**
* 当前是第几页，可写
*/
private int pageNo = DEFAULT_PAGE_NO;
/**
* 一共有多少页，可读
*/
private int pageTotal;
/**
* 一共有多少行，可读
*/
private int rowsCount;
/**
* 每页多少行，可写
*/
private int rowsPerPage = DEFAULT_ROWS_PER_PAGE;
/**
* 本页中显示的数据集合，可读
*/
private List data;
/**
* 要排除的行数，内部使用
*/
private int rowStart;
/**
*
* 前置条件， 可写主要用于查询组合列，如 u.name,u.sex
*/
private String select = DEFAULT_SELECT;
/**
* 要查询的表名名，可写，必须
*/
private String from = "";
/**
* 使用 Oracle 数据库时设置事实主表的别名，单表可不设置
*/
private String primaryTable="";
/**
* 查询条件，无需使用where开始，通过setCondition,addCondition可写, getCondition读取
*/
private StringBuffer conditions = new StringBuffer();
/**
* 排序方式，可写
*/
private String sortOrder = DEFAULT_SORT_ORDER;
/**
* 排序字段，可写，必须
*/
private String sort = "";
/**
* 语句最后的附加排序，可写 例如语句后的第二个排序条件，jqpl+",time desc"
*/
private String lastSort = "";
/**
* 直接指定分页 语句，覆盖getAutoSql()自动生成的 SQL 语句，可写
*/
private String sql;
/**
* 直接指定分页的总条数查询语句，覆盖getAutoSql()自动生成的 SQL 语句，可写
*/
private String countSQL;
/**
* SQL 语句的参数值
*/
private Map sqlParameterValues=new HashMap();
/**
* 分页查询条件对象，如果设置了condition，会被覆盖
*/
EasyCriteria easyCriteria;
public String getAutoSQL() {
if(isNotNullOrEmpty(sql)){
return parameterHandler(sql);
}
int start=(pageNo-1)*rowsPerPage; //开始量
int end=start+rowsPerPage; //结束量
StringBuilder sql=new StringBuilder("");
if(dbType==MYSQL){
/*
* SELECT c,c2 FROM tablename
* WHERE 1=1
* ORDER BY col
* LIMIT 10,5
*/
sql
.append("select ")
.append(select)
.append(" from ")
.append(from)
.append(" where 1=1 ");
if (isNotNullOrEmpty(conditions.toString())) {
sql.append(conditions);
}
if(easyCriteria!=null){
System.out.println(easyCriteria.getCondition());
sql.append(easyCriteria.getCondition());
}
if (isNotNullOrEmpty(sort)) {
sql.append(" order by ").append(sort);
if (isNotNullOrEmpty(sortOrder)) {
sql.append( " " + sortOrder);
}
}
if (isNotNullOrEmpty(sort)) {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append("," + lastSort);
}
} else {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append(" order by " + lastSort);
}
}
sql.append(" limit "+start+","+rowsPerPage);//分页语句
System.out.println(sql);
}else if(dbType==SQLSERVER){
/*
* SELECT TOP 5 c,c2 FROM tablename
* WHERE id not in(SELECT TOP 10 id FROM tablename WHERE 1=1 ORDER BY id)
*/
sql
.append("select ")
.append("top ")
.append(rowsPerPage)
.append(select)
.append(" from ")
.append(from)
.append(" where ")
.append(sortOrder)
.append(" not in(select top ")
.append(start+" "+sortOrder+" from "+from+" where 1=1");
if (isNotNullOrEmpty(conditions.toString())) {
sql.append(conditions);
}
if(easyCriteria!=null){
sql.append(easyCriteria.getCondition());
}
sql.append(" order by "+sortOrder+" "+sort+")");
}else if(dbType==SQLSERVER_2012){
/*
* SELECT c,c2 FROM tablename
* WHERE 1=1
* ORDER BY col
* OFFSET 10
* ROWS FETCH NEXT 5 ROWS ONLY
*/
sql
.append("select ")
.append(rowsPerPage)
.append(select)
.append(" from ")
.append(from)
.append(" where 1=1 ");
if (isNotNullOrEmpty(conditions.toString())) {
sql.append(conditions);
}
if(easyCriteria!=null){
sql.append(easyCriteria.getCondition());
}
if (isNotNullOrEmpty(sort)) {
sql.append(" order by ").append(sort);
if (isNotNullOrEmpty(sortOrder)) {
sql.append( " " + sortOrder);
}
}
if (isNotNullOrEmpty(sort)) {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append("," + lastSort);
}
} else {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append(" order by " + lastSort);
}
}
sql.append(" offset ")
.append(start)
.append("rows fetch next ")
.append(rowsPerPage)
.append("rows only")
;
}else if(dbType==ORACLE){
/*
* SELECT B.* FROM (
* SELECT A.*,rownum r FROM
* (SELECT col,col2 FROM tableName WHERE 1=1 ORDER BY col,primaryTable.rowid) A
* WHERE rownum<=10
* ) B
* WHERE B.r>5
*
*/
sql
.append("SELECT B.* FROM ( SELECT A.*,rownum r FROM (")
.append("select ")
.append(select)
.append(" from ")
.append(from)
.append(" where 1=1 ");
if (isNotNullOrEmpty(conditions.toString())) {
sql.append(conditions);
}
if(easyCriteria!=null){
sql.append(easyCriteria.getCondition());
}
if (isNotNullOrEmpty(sort)) {
sql.append(" order by ").append(sort);
if (isNotNullOrEmpty(sortOrder)) {
sql.append( " " + sortOrder);
}
}
if (isNotNullOrEmpty(sort)) {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append("," + lastSort);
}
} else {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append(" order by " + lastSort);
}
}
if(isNotNullOrEmpty(primaryTable)){
sql.append(","+primaryTable+".rowid");
}else{
sql.append(".rowid");
};
sql.append(") A where rownum<="+end+" ) B where B.r>"+start);
}else if(dbType==ORACLE_12C){
/*
* SELECT col,col2 FROM tablename WHERE 1=1 ORDER BY col
* OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY;
*/
sql
.append("select ")
.append(select)
.append(" from ")
.append(from)
.append(" where 1=1 ");
if (isNotNullOrEmpty(conditions.toString())) {
sql.append(conditions);
}
if(easyCriteria!=null){
sql.append(easyCriteria.getCondition());
}
if (isNotNullOrEmpty(sort)) {
sql.append(" order by ").append(sort);
if (isNotNullOrEmpty(sortOrder)) {
sql.append( " " + sortOrder);
}
}
if (isNotNullOrEmpty(sort)) {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append("," + lastSort);
}
} else {
if (isNotNullOrEmpty(lastSort) && lastSort.trim().length() != 0) {
sql.append(" order by " + lastSort);
}
}
sql.append("OFFSET "+start+" ROWS FETCH FIRST "+rowsPerPage+" ROWS ONLY");
}
if(easyCriteria!=null){
sqlParameterValues=easyCriteria.getValues();
}
return parameterHandler(sql.toString());
}
/**
* 获得查询数据总条数的SQL语句
* @return 返回根据表信息和条件自动生成当前dbType对应的数据库的查询总条数SQL语句
*/
public String getAutoCountSQL(){
if(isNotNullOrEmpty(countSQL)){
return parameterHandler(countSQL);
}
String countSQL2="select count(*) from "+from+" where 1=1 "; //计算总数量语句
if (isNotNullOrEmpty(conditions.toString())) {
countSQL2+=conditions;
}
if(easyCriteria!=null){
countSQL2+=easyCriteria.getCondition();
}
if(easyCriteria!=null){
sqlParameterValues=easyCriteria.getValues();
}
return parameterHandler(countSQL2);
}
public String getCountSQL() {
return countSQL;
}
public void setCountSQL(String countSQL) {
this.countSQL = countSQL;
}
/**
*
* @param sql
* @return
*/
private String parameterHandler(String sql){
return sql.replaceAll("\\#\\{[\\ ]*(\\S*)[\\ ]*\\}", "\\#\\{sqlParameterValues\\.$1\\}");
}
private boolean isNotNullOrEmpty(String s) {
if (null == s || s.trim().equals("")) {
return false;
}
return true;
}
public String getFrom() {
return from;
}
public void setFrom(String from) {
this.from = from;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
/**
*
*/
public PageBean() {
super();
}
/**
* @return the curPage
*/
public int getPageNo() {
return pageNo;
}
/**
* @param pageNo
* the page number to set
*/
public void setPageNo(int pageNo) {
this.pageNo = pageNo < 1 ? DEFAULT_PAGE_NO : pageNo;
}
/**
* 得到总共有多少页
*
* @return the maxPage
*/
public int getPageTotal() {
return pageTotal = (this.rowsCount - 1) / this.getRowsPerPage() + 1;
}
/**
* @return the maxRow
*/
public int getRowsCount() {
return rowsCount;
}
/**
* 得到每页显示多少条记录
*
* @return the rowsPerPage
*/
public int getRowsPerPage() {
return rowsPerPage <= 0 ? DEFAULT_ROWS_PER_PAGE : rowsPerPage;
}
/**
* @param rowsPerPage
* the rowsPerPage to set
*/
public void setRowsPerPage(int rowsPerPage) {
this.rowsPerPage = rowsPerPage;
}
/**
* @return the data
*/
public List getData() {
return data;
}
/**
* 得到分页前要排除的行数
*
* @return the rowStart
*/
public int getRowStart() {
int ret = (this.pageNo - 1) * this.getRowsPerPage();
return ret < 1 ? 0 : ret;
}
/**
* @param rowStart
* the rowStart to set
*/
private void setRowStart(int rowStart) {
this.rowStart = rowStart;
}
/**
* @return the sort
*/
public String getSort() {
return sort;
}
/**
* @param sort
* the sort to set
*/
public void setSort(String sort) {
this.sort = sort;
}
/**
* 获得查询条件语句
*
* @return 查询条件语句
*/
public String getCondition() {
return conditions.toString();
}
/**
* 设置查询条件
*
* @param condition 条件
* 查询条件语句
*/
public void setCondition(String condition) {
this.conditions = new StringBuffer(condition);
}
/**
* 追加条件
*
* @param condition 条件
*/
public void addCondition(String condition) {
conditions.append(condition);
}
public String getSelect() {
return select;
}
public void setSelect(String select) {
this.select = select;
}
public String getSortOrder() {
return sortOrder;
}
public void setSortOrder(String sortOrder) {
this.sortOrder = sortOrder;
}
public String getLastSort() {
return lastSort;
}
public void setLastSort(String lastSort) {
this.lastSort = lastSort;
}
public EasyCriteria getEasyCriteria() {
return easyCriteria;
}
public void setEasyCriteria(EasyCriteria easyCriteria) {
this.easyCriteria = easyCriteria;
}
public Map getSqlParameterValues() {
return sqlParameterValues;
}
public void setSqlParameterValues(Map sqlParameterValues) {
this.sqlParameterValues = sqlParameterValues;
}
}