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

Go to download
package com.kukababy.plus.dao.db;

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

import com.kukababy.plus.pager.BuildWhere;
import com.kukababy.plus.pager.Pager;
import com.kukababy.plus.pojo.PlusParam;
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, PlusParam plusParam) {
		String pageSql = "";
		switch (plusParam.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);
		}

		if (log.isDebugEnabled()) {
			log.debug(Constant.LogPre + pageSql);
		}
		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(BuildWhere.getSorts(pager.getSqlFilter().getSorts()));
		sqlSb.append(" limit " + first + "," + pager.getPageSize());

		return sqlSb.toString();
	}

	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(BuildWhere.getSorts(pager.getSqlFilter().getSorts()));
		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(BuildWhere.getSorts(pager.getSqlFilter().getSorts()));
		sqlSb.append(" ) a");
		sqlSb.append(" where rownum <" + last);
		sqlSb.append(" where __rn >= " + first);

		return sqlSb.toString();
	}
}