com.kukababy.plus.dao.SelectImpl Maven / Gradle / Ivy
package com.kukababy.plus.dao;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.script.ScriptEngine;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import com.alibaba.fastjson.JSON;
import com.kukababy.plus.dao.db.DbHelper;
import com.kukababy.plus.exception.PlusRuntimeException;
import com.kukababy.plus.pager.CustPager;
import com.kukababy.plus.pager.CustSqlInfo;
import com.kukababy.plus.pager.Page;
import com.kukababy.plus.pager.Pager;
import com.kukababy.plus.pager.SqlFilter;
import com.kukababy.plus.pager.SqlInfo;
import com.kukababy.plus.pager.SqlWhere;
import com.kukababy.plus.pojo.P;
import com.kukababy.plus.pojo.SqlCfg;
import com.kukababy.plus.pojo.SqlPo;
import com.kukababy.plus.utils.Constant;
import com.kukababy.plus.utils.HoldUtil;
import com.kukababy.plus.utils.JsUtil;
import com.kukababy.plus.utils.SqlUtil;
import com.kukababy.plus.utils.StringUtil;
/**
*
* 描述:
*
*
* @author [email protected]
* @date 2019年3月5日 下午10:52:48
*/
public class SelectImpl extends BasePlus implements SelectFace {
private static final Logger log = LoggerFactory.getLogger(SelectImpl.class);
/**
* @param injectParam
*/
public SelectImpl(SqlCfg sqlCfg, ScriptEngine engine) {
super(sqlCfg, engine);
}
@Override
public T get(Class entityClass, Serializable id) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
String sql = "select * from " + sqlPo.getTableName() + " where " + sqlPo.getKey() + " = ?";
try {
T plusPO = entityClass.newInstance();
Map map = sqlCfg.getJdbcTemplate().queryForMap(sql, id);
for (Map.Entry entry : map.entrySet()) {
String colName = entry.getKey();
String varName = sqlPo.getCol2VarMap().get(colName);
if (varName != null) {
SqlUtil.setValue(plusPO, varName, entry.getValue());
}
}
return plusPO;
} catch (EmptyResultDataAccessException e) {
return null;
} catch (InstantiationException | IllegalAccessException e) {
throw new PlusRuntimeException(e);
}
}
@Override
public List getAll(Class entityClass) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
String sql = "select * from " + sqlPo.getTableName();
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + sql);
}
List> list = sqlCfg.getJdbcTemplate().queryForList(sql);
List resList = new ArrayList();
for (Map rowMap : list) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
resList.add(plusPO);
}
return resList;
}
@Override
public T selectOne(Class entityClass, P... params) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
// 可以支持列的名字，写成变量的名字，此处需要转换为列名
SqlUtil.varConvertCol(sqlPo.getCol2VarMap(), params);
SqlFilter sqlFilter = SqlUtil.param2SqlFilter(params);
String entitySql = "select * from " + sqlPo.getTableName();
return this.selectOne(entitySql, entityClass, sqlFilter);
}
@Override
public T selectOne(Class entityClass, SqlFilter sqlFilter) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
String entitySql = "select * from " + sqlPo.getTableName();
return selectOne(entitySql, entityClass, sqlFilter);
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#selectOne(java.lang.Class,
* java.lang.String, com.kukababy.plus.pojo.P[])
*/
@Override
public T selectOne(Class entityClass, String functionName, P... params) {
Map rowMap = selectOne(functionName, params);
if (rowMap != null) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
return plusPO;
}
return null;
}
@Override
public Map selectOne(String functionName, P... keyVals) {
List params = new ArrayList();
for (P p : keyVals) {
params.add(p);
}
List> rows = this.custSelectList(functionName, params);
if (rows.isEmpty()) {
return null;
} else {
return rows.get(0);
}
}
/**
*
* 描述:查询表的一条记录
*
*
* @param entitySql
* @param entityClass
* @param sqlFilter
* @return
*/
private T selectOne(String entitySql, Class entityClass, SqlFilter sqlFilter) {
Pager pager = new Pager();
pager.setTotal(1);// 不统计总数
pager.setPageSize(1);// 查一条
pager.setSqlFilter(sqlFilter);
Page page = this.select(entitySql, entityClass, pager);
if (page.getRows().isEmpty()) {
return null;
} else {
return page.getRows().get(0);
}
}
@Override
public int getTotal(Class entityClass, P... whereColVals) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
// 可以支持列的名字，写成变量的名字，此处需要转换为列名
SqlUtil.varConvertCol(sqlPo.getCol2VarMap(), whereColVals);
SqlFilter sqlFilter = SqlUtil.param2SqlFilter(whereColVals);
return this.getTotal(sqlPo, entityClass, sqlFilter);
}
@Override
public int getTotal(Class entityClass, SqlFilter sqlFilter) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
return this.getTotal(sqlPo, entityClass, sqlFilter);
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#getTotal(java.lang.String,
* com.kukababy.plus.pojo.P[])
*/
@Override
public int getTotal(String functionName, P... keyVals) {
List params = new ArrayList();
for (P p : keyVals) {
params.add(p);
}
return this.getCustTotal(functionName, params);
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#getTotal(java.lang.String,
* java.util.Map)
*/
@Override
public int getTotal(String functionName, Map keyVals) {
List params = new ArrayList();
for (Entry entry : keyVals.entrySet()) {
params.add(new P(entry.getKey(), entry.getValue()));
}
return this.getCustTotal(functionName, params);
}
/**
*
* 描述:按条件查表的记录数
*
*
* @param sqlPo
* @param entityClass
* @param sqlFilter
* @return
*/
private int getTotal(SqlPo sqlPo, Class entityClass, SqlFilter sqlFilter) {
SqlInfo sqlRes = SqlWhere.getWheres(sqlFilter);
String sqlWhere = sqlRes.getWhereSql();
Object sqlVals[] = sqlRes.getWhereVals().toArray();
String countSql = "select count(1) from " + sqlPo.getTableName() + sqlWhere;
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + countSql);
}
int total = 0;
if (sqlVals.length == 0) {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class);
} else {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class, sqlVals);
}
return total;
}
@Override
public List selectList(Class entityClass, P... whereColVals) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
// 可以支持列的名字，写成变量的名字，此处需要转换为列名
SqlUtil.varConvertCol(sqlPo.getCol2VarMap(), whereColVals);
SqlFilter sqlFilter = SqlUtil.param2SqlFilter(whereColVals);
String entitySql = "select * from " + sqlPo.getTableName();
return this.selectList(entitySql, entityClass, sqlFilter);
}
@Override
public List selectList(Class entityClass, SqlFilter sqlFilter) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
String entitySql = "select * from " + sqlPo.getTableName();
return this.selectList(entitySql, entityClass, sqlFilter);
}
@Override
public List> selectList(String functionName, P... keyVals) {
List params = new ArrayList();
for (P p : keyVals) {
params.add(p);
}
return this.custSelectList(functionName, params);
}
@Override
public List selectList(Class entityClass, String functionName, P... keyVals) {
List params = new ArrayList();
for (P p : keyVals) {
params.add(p);
}
return selectList(entityClass, functionName, params);
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#select(java.lang.Class,
* java.lang.String, java.util.Map)
*/
@Override
public List selectList(Class entityClass, String functionName, Map keyVals) {
List params = new ArrayList();
for (Entry entry : keyVals.entrySet()) {
params.add(new P(entry.getKey(), entry.getValue()));
}
return selectList(entityClass, functionName, params);
}
private List selectList(Class entityClass, String functionName, List keyVals) {
List> rows = this.custSelectList(functionName, keyVals);
List resList = new ArrayList();
for (Map rowMap : rows) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
resList.add(plusPO);
}
return resList;
}
@Override
public Page selectPage(Class entityClass, Pager pager) {
SqlPo sqlPo = this.getSqlPo(entityClass, sqlCfg.getCamel());
String entitySql = "select * from " + sqlPo.getTableName();
return this.select(entitySql, entityClass, pager);
}
/**
*
* 描述:按条件查单表的多条记录
*
*
* @param entitySql
* @param entityClass
* @param sqlFilter
* @return
*/
private List selectList(String entitySql, Class entityClass, SqlFilter sqlFilter) {
List> rows = select(entitySql, sqlFilter);
List resList = new ArrayList();
for (Map rowMap : rows) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
resList.add(plusPO);
}
return resList;
}
private Page select(String entitySql, Class entityClass, Pager pager) {
Page resPage = new Page();
Page> page = select(entitySql, pager);
for (Map rowMap : page.getRows()) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
resPage.getRows().add(plusPO);
}
resPage.setCurrPage(page.getCurrPage());
resPage.setPageSize(page.getPageSize());
resPage.setTotal(page.getTotal());
return resPage;
}
/**
*
* 描述:针对单表分页
*
*
* @param sql
* @param pager
* @return
*/
private Page> select(String entitySql, Pager pager) {
SqlInfo sqlRes = SqlWhere.getWheres(pager.getSqlFilter());
String sqlWhere = sqlRes.getWhereSql();
Object sqlVals[] = sqlRes.getWhereVals().toArray();
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + sqlRes.getWhereVals());
}
int total = pager.getTotal();
if (total == 0) {// 需要计算总记录数
String countSql = StringUtil.getCountSql(entitySql) + sqlWhere;
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + countSql);
}
if (sqlVals.length == 0) {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class);
} else {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class, sqlVals);
}
}
String pageSql = DbHelper.getPageSql(entitySql, pager, sqlWhere, sqlCfg);
Page> page = new Page>();
List> rows;
if (sqlVals.length == 0) {
rows = sqlCfg.getJdbcTemplate().queryForList(pageSql);
} else {
rows = sqlCfg.getJdbcTemplate().queryForList(pageSql, sqlVals);
}
page.setRows(rows);
page.setCurrPage(pager.getCurrPage());
page.setPageSize(pager.getPageSize());
page.setTotal(total);
return page;
}
/**
*
* 描述:查单表记录
*
*
* @param entitySql
* @param sqlFilter
* @return
*/
private List> select(String entitySql, SqlFilter sqlFilter) {
SqlInfo sqlInfo = HoldUtil.getSqlAndVals(entitySql, sqlFilter);
Object sqlVals[] = sqlInfo.getExecVals().toArray();
String execSql = sqlInfo.getExecSql();
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + sqlInfo.getExecVals());
}
String joinSql = execSql + SqlWhere.getOrderby(sqlFilter.getOrderbys());
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + joinSql);
}
List> rows;
if (sqlVals.length == 0) {
rows = sqlCfg.getJdbcTemplate().queryForList(joinSql);
} else {
rows = sqlCfg.getJdbcTemplate().queryForList(joinSql, sqlVals);
}
return rows;
}
private List> custSelect(String functionName, Map keyVals) {
List params = new ArrayList();
for (Entry entry : keyVals.entrySet()) {
P p = new P(entry.getKey(), entry.getValue());
params.add(p);
}
return custSelectList(functionName, params);
}
private List> custSelectList(String functionName, List keyVals) {
CustSqlInfo sqlInfo = JsUtil.getCustSqlInfo(functionName, this.engine, keyVals);
Object sqlVals[] = sqlInfo.getExecVals().toArray();
String resSql = sqlInfo.getExecSql();
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + JSON.toJSONString(sqlInfo, true));
}
List> rows;
if (sqlVals.length == 0) {
rows = sqlCfg.getJdbcTemplate().queryForList(resSql);
} else {
rows = sqlCfg.getJdbcTemplate().queryForList(resSql, sqlVals);
}
return rows;
}
private int getCustTotal(String functionName, List keyVals) {
CustSqlInfo sqlInfo = JsUtil.getCustSqlInfo(functionName, this.engine, keyVals);
Object sqlVals[] = sqlInfo.getExecVals().toArray();
String resSql = sqlInfo.getExecSql();
if (log.isDebugEnabled()) {
log.debug(Constant.LogPre + JSON.toJSONString(sqlInfo, true));
}
int total = 0;
if (sqlVals.length == 0) {
total = sqlCfg.getJdbcTemplate().queryForObject(resSql, Integer.class);
} else {
total = sqlCfg.getJdbcTemplate().queryForObject(resSql, Integer.class, sqlVals);
}
return total;
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#select(java.lang.String,
* java.util.Map)
*/
@Override
public List> selectList(String functionName, Map keyVals) {
List params = new ArrayList();
for (Entry entry : keyVals.entrySet()) {
params.add(new P(entry.getKey(), entry.getValue()));
}
return custSelectList(functionName, params);
}
/*
* (non-Javadoc)
*
* @see com.kukababy.plus.dao.SelectFace#select(java.lang.String,
* com.kukababy.plus.pager.CustPager)
*/
@Override
public Page selectPage(Class entityClass, String functionName, CustPager pager) {
Page resPage = new Page();
Page> page = selectPage(functionName, pager);
for (Map rowMap : page.getRows()) {
T plusPO = SqlUtil.map2PlusPO(rowMap, entityClass, sqlCfg.getCamel());
resPage.getRows().add(plusPO);
}
resPage.setCurrPage(pager.getCurrPage());
resPage.setPageSize(pager.getPageSize());
resPage.setTotal(page.getTotal());
return resPage;
}
@Override
public Page> selectPage(String functionName, CustPager pager) {
List params = new ArrayList();
// 加入分页参数
pager.getKeyVals().put("offset", DbHelper.getOffset(pager));
pager.getKeyVals().put("pageSize", pager.getPageSize());
for (Entry entry : pager.getKeyVals().entrySet()) {
params.add(new P(entry.getKey(), entry.getValue()));
}
// 自定义sql的语句和值
CustSqlInfo sqlInfo = JsUtil.getCustSqlInfo(functionName, this.engine, params);
Object execVals[] = sqlInfo.getExecVals().toArray();
String execSql = sqlInfo.getExecSql();
int total = 0;
Page> page = null;
if (pager.isHasCount()) {// 有单独统计总数的函数function
CustSqlInfo countCustSqlInfo = JsUtil.getCustSqlInfo(functionName + "Count", this.engine, params);
Object countExecVals[] = countCustSqlInfo.getExecVals().toArray();
String countExecSql = countCustSqlInfo.getExecSql();
this.debugLog(countExecSql, countExecVals);
total = getCustPageCount(countExecVals, countExecSql, pager.getTotal());
} else {
// 没有单独写统计总数的sql，程序进行组装
String countSql = "select count(1) from (" + execSql + ") a";
this.debugLog(countSql, execVals);
total = getCustPageCount(execVals, countSql, pager.getTotal());
// 组装分页
execSql = DbHelper.getCustPageSql(execSql, pager, sqlCfg);
}
this.debugLog(execSql, execVals);
page = getCustPage(execVals, execSql, pager);
page.setTotal(total);
return page;
}
private int getCustPageCount(Object sqlVals[], String countSql, int total) {
if (total == 0) {// 需要计算总记录数
if (sqlVals.length == 0) {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class);
} else {
total = sqlCfg.getJdbcTemplate().queryForObject(countSql, Integer.class, sqlVals);
}
}
return total;
}
private Page getCustPage(Object sqlVals[], String pageSql, CustPager pager) {
Page> page = new Page>();
List> rows;
if (sqlVals.length == 0) {
rows = sqlCfg.getJdbcTemplate().queryForList(pageSql);
} else {
rows = sqlCfg.getJdbcTemplate().queryForList(pageSql, sqlVals);
}
page.setRows(rows);
page.setCurrPage(pager.getCurrPage());
page.setPageSize(pager.getPageSize());
return page;
}
}