com.kukababy.plus.dao.db.DbHelper Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of jdbc-plus Show documentation
JdbcTemplate Plus
The newest version!
package com.kukababy.plus.dao.db;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kukababy.plus.pager.CustPager;
import com.kukababy.plus.pager.Pager;
import com.kukababy.plus.pager.SqlWhere;
import com.kukababy.plus.pojo.SqlCfg;
import com.kukababy.plus.utils.Constant;

/**
 * 
 * 
 * 
 *  * 描述:
 * 
 * 
 * 
 * 
 * @author [email protected]
 * @date 2019年3月5日 下午10:47:51
 */
public class DbHelper {

	private static final Logger log = LoggerFactory.getLogger(DbHelper.class);

	public static String getPageSql(String sql, Pager pager, String sqlWhere, SqlCfg sqlParam) {
		String pageSql = "";
		switch (sqlParam.getDbType()) {
		case Constant.Mysql:
			pageSql = getMysqlPage(sql, pager, sqlWhere);
			break;
		case Constant.SqlServer:
			pageSql = getSqlServerPage(sql, pager, sqlWhere);
			break;
		default:
			pageSql = getMysqlPage(sql, pager, sqlWhere);
		}

		return pageSql;

	}

	public static String getCustPageSql(String sql, CustPager pager, SqlCfg sqlParam) {
		String pageSql = "";
		switch (sqlParam.getDbType()) {
		case Constant.Mysql:
			pageSql = getCustMysqlPage(sql, pager);
			break;
		default:
			pageSql = getCustMysqlPage(sql, pager);
		}

		return pageSql;

	}

	private static String getMysqlPage(String sql, Pager pager, String sqlWhere) {
		int currPage = pager.getCurrPage() - 1;// 前端第一页为1，需要减1
		if (currPage < 0) {
			currPage = 0;
		}
		int first = currPage * pager.getPageSize();

		StringBuilder sqlSb = new StringBuilder();

		sqlSb.append(sql);
		sqlSb.append(sqlWhere);
		sqlSb.append(SqlWhere.getOrderby(pager.getSqlFilter().getOrderbys()));
		sqlSb.append(" limit " + first + "," + pager.getPageSize());

		return sqlSb.toString();
	}

	private static String getCustMysqlPage(String sql, CustPager pager) {
		
		int offset = getOffset(pager);

		StringBuilder sqlSb = new StringBuilder();

		sqlSb.append(sql);
		sqlSb.append(" limit " + offset + "," + pager.getPageSize());

		return sqlSb.toString();
	}
	
	public static int getOffset(CustPager pager){
		int currPage = pager.getCurrPage() - 1;// 前端第一页为1，需要减1
		if (currPage < 0) {
			currPage = 0;
		}
		int offset = currPage * pager.getPageSize();
		return offset;
	}

	private static String getSqlServerPage(String sql, Pager pager, String sqlWhere) {

		// select * from table order by id offset 4 rows fetch next 5 rows only
		// --order by id offset 页数 rows fetch next 条数 rows only ----
		int currPage = pager.getCurrPage() - 1;// 前端第一页为1，需要减1
		if (currPage < 0) {
			currPage = 0;
		}
		int first = currPage * pager.getPageSize();

		StringBuilder sqlSb = new StringBuilder();

		sqlSb.append(sql);
		sqlSb.append(sqlWhere);
		sqlSb.append(SqlWhere.getOrderby(pager.getSqlFilter().getOrderbys()));
		sqlSb.append(" offset " + first + " rows fetch next " + pager.getPageSize() + " rows only");

		return sqlSb.toString();
	}

	private static String getSqlOraclePage(String sql, Pager pager, String sqlWhere) {

		// select * from
		// (
		// select a.*, rownum rn
		// from (select * from table_name) a
		// where rownum < 40
		// )
		// where rn >= 21
		int currPage = pager.getCurrPage() - 1;// 前端第一页为1，需要减1
		if (currPage < 0) {
			currPage = 0;
		}
		int first = currPage * pager.getPageSize();
		int last = first + pager.getPageSize();

		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("select * from (");
		sqlSb.append(" select a.*, rownum __rn");
		sqlSb.append(" from (");
		sqlSb.append(sql);
		sqlSb.append(sqlWhere);
		sqlSb.append(SqlWhere.getOrderby(pager.getSqlFilter().getOrderbys()));
		sqlSb.append(" ) a");
		sqlSb.append(" where rownum <" + last);
		sqlSb.append(" ) where __rn >= " + first);

		return sqlSb.toString();
	}
}