package com.code2roc.fastface.db;
|
|
import com.baomidou.mybatisplus.annotation.TableField;
|
import com.baomidou.mybatisplus.annotation.TableId;
|
import com.baomidou.mybatisplus.annotation.TableName;
|
import com.code2roc.fastface.util.CommonUtil;
|
import com.code2roc.fastface.util.ConvertOp;
|
import com.code2roc.fastface.util.StringUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.context.annotation.Scope;
|
import org.springframework.context.annotation.ScopedProxyMode;
|
import org.springframework.jdbc.datasource.DataSourceUtils;
|
import org.springframework.stereotype.Component;
|
|
import javax.sql.DataSource;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Modifier;
|
import java.math.BigDecimal;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.Timestamp;
|
import java.sql.Types;
|
import java.util.*;
|
|
@Component
|
@Scope(value = "prototype", proxyMode = ScopedProxyMode.TARGET_CLASS)
|
public class CommonDTO {
|
@Autowired
|
private JDBCDTO jdbcDTO;
|
private Map<String, Object> paramMap;
|
private String newGuid;
|
|
public CommonDTO() {
|
paramMap = new HashMap<>();
|
newGuid = UUID.randomUUID().toString();
|
}
|
|
public String getNewGuid() {
|
return newGuid;
|
}
|
|
public void setNewGuid(String newGuid) {
|
this.newGuid = newGuid;
|
}
|
|
public Page selectPage(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
return selectPage(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
|
}
|
|
public Page selectPage(String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
|
Page page = new Page();
|
String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
page.setRows(result);
|
int total = selectCount(tableName, strWhere, paramList);
|
page.setTotal(total);
|
return page;
|
}
|
|
public List<LinkedHashMap<String, Object>> selectPageList(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
return selectPageList(tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
|
}
|
|
public List<LinkedHashMap<String, Object>> selectPageList(String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
|
String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
return result;
|
}
|
|
public <T> List<T> selectPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
return selectPageList(clazz, tableName, selectCols, strWhere, strOrder, pageIndex, pageSize, paramList);
|
}
|
|
public <T> List<T> selectPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, int PageIndex, int PageSize, HashMap<String, Object> paramList) {
|
String sql = getPageSQL(tableName, selectCols, strWhere, strOrder, PageIndex, PageSize);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
List<T> result = new ArrayList<>();
|
for (LinkedHashMap<String, Object> map : findresult) {
|
try {
|
T obj = clazz.newInstance();
|
result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
|
} catch (Exception e) {
|
|
}
|
}
|
return result;
|
}
|
|
public List<LinkedHashMap<String, Object>> selectList(String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
|
String sql = "";
|
sql = " select " + selectCols + " from " + tableName + " where " + strWhere;
|
if (!StringUtil.isEmpty(strOrder)) {
|
sql += " order by " + strOrder;
|
}
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
paramMap.put("sql", sql);
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
return result;
|
}
|
|
public <T> List<T> selectList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strOrder, HashMap<String, Object> paramList) {
|
String sql = "";
|
sql = " select " + selectCols + " from " + tableName + " where " + strWhere;
|
if (!StringUtil.isEmpty(strOrder)) {
|
sql += " order by " + strOrder;
|
}
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
List<T> result = new ArrayList<>();
|
for (LinkedHashMap<String, Object> map : findresult) {
|
try {
|
T obj = clazz.newInstance();
|
result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
return result;
|
}
|
|
public int selectCount(String tableName, String strWhere, HashMap<String, Object> paramList) {
|
String sql = "select count(*) from " + tableName;
|
if (!StringUtil.isEmpty(strWhere)) {
|
sql += " where " + strWhere;
|
}
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
|
reset();
|
return total;
|
}
|
|
public LinkedHashMap<String, Object> selectOne(String tableName, String selectCols, String strWhere, HashMap<String, Object> paramList) {
|
List<LinkedHashMap<String, Object>> result = selectList(tableName, selectCols, strWhere, "", paramList);
|
if (result.size() > 0) {
|
return result.get(0);
|
} else {
|
return null;
|
}
|
}
|
|
public <T> T selectOne(Class<T> clazz, String tableName, String selectCols, String strWhere, HashMap<String, Object> paramList) {
|
List<T> result = selectList(clazz, tableName, selectCols, strWhere, "", paramList);
|
if (result.size() > 0) {
|
return result.get(0);
|
} else {
|
return null;
|
}
|
}
|
|
public <T> T selectOne(Class<T> clazz, String tableName, String selectCols, String filedName, String fieldValue) {
|
String sql = filedName + " = #{" + filedName + "}";
|
paramMap.put(filedName, fieldValue);
|
List<T> result = selectList(clazz, tableName, selectCols, sql, "", null);
|
reset();
|
if (result.size() > 0) {
|
return result.get(0);
|
} else {
|
return null;
|
}
|
}
|
|
public List<LinkedHashMap<String, Object>> selectGroup(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
|
String sql = getGroupSQL(tableName, selectCols, strGroupBy, strWhere, strOrder);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
return result;
|
}
|
|
public List<LinkedHashMap<String, Object>> selectGroupList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
|
String sql = getGroupListSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, "");
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
return jdbcDTO.findListByParam(sql, paramMap);
|
}
|
|
public List<LinkedHashMap<String, Object>> selectGroupList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
|
String sql = getGroupListSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, maxTableIDName);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
return jdbcDTO.findListByParam(sql, paramMap);
|
}
|
|
public List<LinkedHashMap<String, Object>> selectGroupPageList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
int BeginIndex = pageSize * (pageIndex - 1) + 1;
|
String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, "");
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
return result;
|
}
|
|
public List<LinkedHashMap<String, Object>> selectGroupPageList(String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
int BeginIndex = pageSize * (pageIndex - 1) + 1;
|
String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, maxTableIDName);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> result = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
return result;
|
}
|
|
public <T> List<T> selectGroupPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
int BeginIndex = pageSize * (pageIndex - 1) + 1;
|
String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, "");
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
List<T> result = new ArrayList<>();
|
for (LinkedHashMap<String, Object> map : findresult) {
|
try {
|
T obj = clazz.newInstance();
|
result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
return result;
|
}
|
|
public <T> List<T> selectGroupPageList(Class<T> clazz, String tableName, String selectCols, String strWhere, String strGroupBy, String strOrder, HashMap<String, Object> paramList, String maxTableIDName) {
|
int pageSize = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageSize"));
|
int pageIndex = ConvertOp.convert2Int(CommonUtil.getRequestBodyPostParam("pageIndex"));
|
if (pageIndex <= 0)
|
pageIndex = 1;
|
int BeginIndex = pageSize * (pageIndex - 1) + 1;
|
String sql = getGroupPageSQL(tableName, selectCols, strGroupBy, strWhere, strOrder, pageIndex, pageSize, maxTableIDName);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
List<LinkedHashMap<String, Object>> findresult = jdbcDTO.findListByParam(sql, paramMap);
|
reset();
|
List<T> result = new ArrayList<>();
|
for (LinkedHashMap<String, Object> map : findresult) {
|
try {
|
T obj = clazz.newInstance();
|
result.add(ConvertOp.convertLinkHashMapToBean(map, obj));
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
return result;
|
}
|
|
public int selectGroupCount(String tableName, String strWhere, String strGroupBy, HashMap<String, Object> paramList) {
|
String sql = "";
|
sql = getGroupCountSQL(tableName, strWhere, strGroupBy, "");
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
|
reset();
|
return total;
|
}
|
|
public int selectGroupCount(String tableName, String strWhere, String strGroupBy, HashMap<String, Object> paramList, String maxTableIDName) {
|
String sql = "";
|
sql = getGroupCountSQL(tableName, strWhere, strGroupBy, maxTableIDName);
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (String key : paramList.keySet()) {
|
paramMap.put(key, paramList.get(key));
|
}
|
}
|
int total = ConvertOp.convert2Int(jdbcDTO.executeScar(sql,paramMap));
|
reset();
|
return total;
|
}
|
|
public void executeSQL(String sql, HashMap<String, Object> paramList) {
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (Map.Entry<String, Object> entry : paramList.entrySet()) {
|
paramMap.put(entry.getKey(), entry.getValue());
|
}
|
}
|
jdbcDTO.executeSQL(sql,paramMap);
|
reset();
|
}
|
|
public Object executeSQLToQuery(String sql, HashMap<String, Object> paramList) {
|
paramMap.put("sql", sql);
|
if (null != paramList) {
|
for (Map.Entry<String, Object> entry : paramList.entrySet()) {
|
paramMap.put(entry.getKey(), entry.getValue());
|
}
|
}
|
Object result = jdbcDTO.executeScar(sql,paramMap);
|
reset();
|
return result;
|
}
|
|
public void reset() {
|
this.paramMap.clear();
|
}
|
|
///region 分页语句
|
public String getPageSQL(String tableName, String selectCols, String strWhere, String strOrder, int pageIndex, int pageSize) {
|
String sql = " select " + selectCols + " from " + tableName + " where " + strWhere;
|
if (!StringUtil.isEmpty(strOrder)) {
|
sql += " order by " + strOrder;
|
}
|
sql += " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize + ";";
|
|
return sql;
|
}
|
|
public String getGroupPageSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder, int pageIndex, int pageSize, String maxTableIDName) {
|
if (StringUtil.isEmpty(maxTableIDName)) {
|
maxTableIDName = "id";
|
}
|
String sql = "";
|
String groupTableName = "(select * from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")) aGr ";
|
sql = getPageSQL(groupTableName, selectCols, strWhere, strOrder, pageIndex, pageSize);
|
return sql;
|
}
|
|
public String getGroupCountSQL(String tableName, String strWhere, String groupBy, String maxTableIDName) {
|
String sql = "";
|
if (StringUtil.isEmpty(maxTableIDName)) {
|
maxTableIDName = "id";
|
}
|
sql = "select count(1) from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")";
|
return sql;
|
}
|
|
public String getGroupListSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder, String maxTableIDName) {
|
if (StringUtil.isEmpty(maxTableIDName)) {
|
maxTableIDName = "id";
|
}
|
String sql = "";
|
sql = "select " + selectCols + " from " + tableName + " where " + maxTableIDName + " in (select max(" + maxTableIDName + ") from " + tableName + " where " + strWhere + " group by " + groupBy + ")";
|
if (!StringUtil.isEmpty(strOrder)) {
|
sql += " order by " + strOrder;
|
}
|
return sql;
|
}
|
|
public String getGroupSQL(String tableName, String selectCols, String groupBy, String strWhere, String strOrder) {
|
String sql = "select " + selectCols + " from " + tableName + " where " + strWhere + " group by " + groupBy + "";
|
if (!StringUtil.isEmpty(strOrder)) {
|
sql += " order by " + strOrder;
|
}
|
return sql;
|
}
|
///endregion
|
}
|
