package com.walker.jdbc.dao;
|
|
import com.walker.common.SplitPageInfo;
|
import com.walker.db.DatabaseType;
|
import com.walker.db.Sorts;
|
import com.walker.db.page.GenericPager;
|
import com.walker.infrastructure.utils.StringUtils;
|
import com.walker.jdbc.Constants;
|
import com.walker.jdbc.SqlAndParameters;
|
import org.apache.commons.lang3.ArrayUtils;
|
|
import java.util.HashMap;
|
import java.util.Map;
|
|
/**
|
* 分页工具类
|
*
|
* @author 时克英
|
*/
|
public class PaginationHelper {
|
/**
|
* 数据库类型
|
*/
|
private String type;
|
|
/**
|
* 获取数据库类型
|
*
|
* @return 数据库类型
|
* @author 时克英
|
*/
|
public String getType() {
|
return type;
|
}
|
|
/**
|
* 设置数据库类型
|
*
|
* @param type 数据库类型
|
* @author 时克英
|
*/
|
public void setType(String type) {
|
this.type = type;
|
}
|
|
public String getOrderBySql(String sql, Sorts.Sort sort){
|
if(sort == null){
|
return sql;
|
}
|
return new StringBuilder(sql).append(Constants.SQL_ORDER_BY)
|
.append(sort.getField()).append(StringUtils.CHAR_SPACE).append(sort.getValue()).toString();
|
}
|
|
public String getSqlPagingQuery(String sql, Sorts.Sort sort){
|
// if(sort != null){
|
// sql = new StringBuilder(sql).append(Constants.SQL_ORDER_BY)
|
// .append(sort.getField()).append(StringUtils.CHAR_SPACE).append(sort.getValue()).toString();
|
// }
|
sql = this.getOrderBySql(sql, sort);
|
if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
|
return "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
|
} else if(DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
|
|| DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
|
return sql + " limit ?, ?";
|
} else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
|
return sql + " limit ? offset ?";
|
} else {
|
return sql + " limit ?, ?";
|
}
|
}
|
|
/**
|
* 返回带分页信息的SQL信息对象。
|
* <pre>
|
* 1）使用GenericPager重写分页查询。
|
* 2）后续都是使用该方法，废弃:SplitPageInfo
|
* </pre>
|
* @param sql 原始SQL语句
|
* @param parameters 参数集合
|
* @param pager 分页对象，由调用方传递
|
* @return
|
* @auth 时克英
|
* @date 2022-09-18
|
* @date 2023-07-24 oracle分页索引值+1
|
*/
|
public SqlAndParameters<Map<String, Object>> getSqlPagingQuery(String sql
|
, Map<String, Object> parameters, GenericPager<?> pager, Sorts.Sort sort){
|
sql = this.getOrderBySql(sql, sort);
|
Map<String, Object> temp = new HashMap<>(4);
|
temp.putAll(parameters);
|
String pageSql = null;
|
if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
|
if(pager.getPageIndex() == 1){
|
pageSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_";
|
temp.put("endRow_", pager.getEndRowIndexPage()+1);
|
} else {
|
pageSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_) where rownum_ >= :startRow_";
|
temp.put("startRow_", pager.getFirstRowIndexInPage()+1);
|
temp.put("endRow_", pager.getEndRowIndexPage()+1);
|
}
|
} else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
|
|| DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit :pageSize_";
|
temp.put("pageSize_", pager.getPageSize());
|
} else {
|
pageSql = sql + " limit :startRow_, :pageSize_";
|
temp.put("startRow_", pager.getFirstRowIndexInPage());
|
temp.put("pageSize_", pager.getPageSize());
|
}
|
} else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
|
// 2023-10-08，TBase数据库（基于PG）
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit :pageSize_";
|
temp.put("pageSize_", pager.getPageSize());
|
} else {
|
pageSql = sql + " limit :pageSize_ offset :startRow_";
|
temp.put("pageSize_", pager.getPageSize());
|
temp.put("startRow_", pager.getFirstRowIndexInPage());
|
}
|
} else {
|
System.out.println("type = " + type);
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit :pageSize_";
|
temp.put("pageSize_", pager.getPageSize());
|
} else {
|
pageSql = sql + " limit :startRow_, :pageSize_";
|
temp.put("startRow_", pager.getFirstRowIndexInPage());
|
temp.put("pageSize_", pager.getPageSize());
|
}
|
}
|
return new SqlAndParameters<>(pageSql, temp);
|
}
|
|
/**
|
* 获取分页查询参数对象。
|
* @param sql
|
* @param parameters
|
* @param pager
|
* @return
|
* @date 2023-07-24 oracle分页索引值+1
|
*/
|
public SqlAndParameters<Object[]> getSqlPagingQuery(String sql, Object[] parameters, GenericPager<?> pager, Sorts.Sort sort) {
|
sql = this.getOrderBySql(sql, sort);
|
Object[] tempArray = null;
|
String pageSql = null;
|
if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
|
if(pager.getPageIndex() == 1){
|
pageSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?";
|
tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage()+1);
|
} else {
|
pageSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
|
tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage()+1);
|
tempArray = ArrayUtils.add(tempArray, pager.getFirstRowIndexInPage()+1);
|
}
|
} else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
|
|| DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)){
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit ?";
|
// tempArray = ArrayUtils.add(parameters, pager.getEndRowIndexPage());
|
tempArray = ArrayUtils.add(parameters, pager.getPageSize());
|
} else {
|
pageSql = sql + " limit ?, ?";
|
// tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage() - 1);
|
// tempArray = ArrayUtils.add(tempArray, pager.getEndRowIndexPage());
|
tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage());
|
tempArray = ArrayUtils.add(tempArray, pager.getPageSize());
|
}
|
} else if(DatabaseType.NAME_POSTGRES.equalsIgnoreCase(type)){
|
// 2023-10-08，TBase数据库（基于PG）
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit ?";
|
tempArray = ArrayUtils.add(parameters, pager.getPageSize());
|
} else {
|
pageSql = sql + " limit ? offset ?";
|
tempArray = ArrayUtils.add(parameters, pager.getPageSize());
|
tempArray = ArrayUtils.add(tempArray, pager.getFirstRowIndexInPage());
|
}
|
} else {
|
System.out.println("getSqlPagingQuery,不支持的分页类型:" + type);
|
if(pager.getPageIndex() == 1){
|
pageSql = sql + " limit ?";
|
tempArray = ArrayUtils.add(parameters, pager.getPageSize());
|
} else {
|
pageSql = sql + " limit ?,?";
|
tempArray = ArrayUtils.add(parameters, pager.getFirstRowIndexInPage());
|
tempArray = ArrayUtils.add(tempArray, pager.getPageSize());
|
}
|
// throw new PersistenceException("不支持的分页类型:" + type);
|
}
|
return new SqlAndParameters<>(pageSql, tempArray);
|
}
|
|
/**
|
* 组装分页查询语句及查询参数
|
*
|
* @param sql 查询语句
|
* @param parameters 查询参数
|
* @param splitPageInfo 分页对象
|
* @return 查询语句及查询参数
|
*/
|
@Deprecated
|
public SqlAndParameters<Map<String, Object>> getPaginationSql(String sql, Map<String, Object> parameters, SplitPageInfo splitPageInfo) {
|
Map<String, Object> temp = new HashMap<>();
|
temp.putAll(parameters);
|
if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
|
if (splitPageInfo.getCurrentPage() != 1) {
|
// String pSql =
|
// "select *\n" +
|
// " from (select row_.*, rownum rownum_\n" +
|
// " from (" + sql + ") row_\n" +
|
// " where rownum <= :endRow_)\n" +
|
// " where rownum_ >= :startRow_";
|
String pSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_) where rownum_ >= :startRow_";
|
temp.put("startRow_", splitPageInfo.getPageRowBegin());
|
temp.put("endRow_", splitPageInfo.getPageRowEnd());
|
|
return new SqlAndParameters<>(pSql, temp);
|
} else {
|
// String pSql =
|
// "select row_.*, rownum rownum_\n" +
|
// " from (" + sql + ") row_\n" +
|
// " where rownum <= :endRow_";
|
String pSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= :endRow_";
|
temp.put("endRow_", splitPageInfo.getPageRowEnd());
|
|
return new SqlAndParameters<>(pSql, temp);
|
}
|
} else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)
|
|| DatabaseType.NAME_SQLITE.equalsIgnoreCase(type)) {
|
if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
|
String pSql = sql + " limit :startRow_, :pageSize_";
|
temp.put("startRow_", splitPageInfo.getPageRowBegin() - 1);
|
temp.put("pageSize_", splitPageInfo.getPageSize());
|
return new SqlAndParameters<>(pSql, temp);
|
} else {
|
String pSql = sql + " limit :pageSize_";
|
temp.put("pageSize_", splitPageInfo.getPageSize());
|
return new SqlAndParameters<>(pSql, temp);
|
}
|
} else {
|
// throw new PersistenceException("不支持的分页类型！");
|
System.out.println("type = " + type);
|
if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
|
String pSql = sql + " limit :startRow_, :pageSize_";
|
temp.put("startRow_", splitPageInfo.getPageRowBegin() - 1);
|
temp.put("pageSize_", splitPageInfo.getPageSize());
|
return new SqlAndParameters<>(pSql, temp);
|
} else {
|
String pSql = sql + " limit :pageSize_";
|
temp.put("pageSize_", splitPageInfo.getPageSize());
|
return new SqlAndParameters<>(pSql, temp);
|
}
|
}
|
|
}
|
|
/**
|
* 组装分页查询语句及查询参数
|
*
|
* @param sql 查询语句
|
* @param parameters 查询参数
|
* @param splitPageInfo 分页对象
|
* @return 分页查询语句及查询参数
|
* @author 李志慧
|
*/
|
@Deprecated
|
public SqlAndParameters<Object[]> getPaginationSql(String sql, Object[] parameters, SplitPageInfo splitPageInfo) {
|
if (DatabaseType.NAME_ORACLE.equalsIgnoreCase(type) || DatabaseType.NAME_DAMENG.equalsIgnoreCase(type)) {
|
if (splitPageInfo.getCurrentPage() != 1) {
|
// String pSql =
|
// "select *\n" +
|
// " from (select row_.*, rownum rownum_\n" +
|
// " from (" + sql + ") row_\n" +
|
// " where rownum <= ?)\n" +
|
// " where rownum_ >= ?";
|
String pSql = "select * from (select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?) where rownum_ >= ?";
|
Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());
|
tempArray = ArrayUtils.add(tempArray, splitPageInfo.getPageRowBegin());
|
|
return new SqlAndParameters<>(pSql, tempArray);
|
} else {
|
// String pSql =
|
// "select row_.*, rownum rownum_\n" +
|
// " from (" + sql + ") row_\n" +
|
// " where rownum <= ?";
|
String pSql = "select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= ?";
|
Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());
|
|
return new SqlAndParameters<>(pSql, tempArray);
|
}
|
} else if (DatabaseType.NAME_MYSQL.equalsIgnoreCase(type)) {
|
if (splitPageInfo.getCurrentPage() != 1 && splitPageInfo.getCurrentPage() != 0) {
|
String pSql = sql + " limit ?, ?";
|
|
Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowBegin() - 1);
|
tempArray = ArrayUtils.add(tempArray, splitPageInfo.getPageRowEnd());
|
|
return new SqlAndParameters<>(pSql, tempArray);
|
} else {
|
String pSql = sql + " limit ?";
|
Object[] tempArray = ArrayUtils.add(parameters, splitPageInfo.getPageRowEnd());
|
return new SqlAndParameters<>(pSql, tempArray);
|
}
|
} else {
|
throw new PersistenceException("不支持的分页类型！");
|
}
|
}
|
}
|
