package com.product.data.center.entity;
|
|
import com.product.common.lang.StringUtils;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.data.center.config.ErrorCode;
|
import com.product.data.center.utils.BatchUtils;
|
import com.product.data.center.utils.CallBackReturnValue;
|
import com.product.datasource.config.DataBaseType;
|
|
import java.util.Arrays;
|
import java.util.HashSet;
|
import java.util.Set;
|
|
/**
|
* 查询实体类
|
*
|
* @author cheng
|
* @date 2022年07月14日10:26:22
|
*/
|
public class QueryDataConfigEntity {
|
/**
|
* 查询表名
|
*/
|
private String queryTable;
|
/**
|
* 需要查询的字段集合
|
*/
|
private Set<String> queryFieldSet;
|
/**
|
* 自动递增主键字段
|
*/
|
private String autoIncrementPrimaryField;
|
/**
|
* 查询条件
|
* 支持占位符
|
*/
|
private String queryFilter;
|
/**
|
* 占位符参数
|
*/
|
private Object[] params;
|
/**
|
* 数据库类型
|
*/
|
private DataBaseType dbt;
|
|
/**
|
* @param queryTable
|
* @param queryFilter
|
* @param params
|
*/
|
public QueryDataConfigEntity(String queryTable, String queryFilter, String autoIncrementPrimaryField, Object[] params) {
|
this.queryTable = queryTable;
|
this.queryFilter = queryFilter;
|
this.params = params;
|
this.autoIncrementPrimaryField = autoIncrementPrimaryField;
|
}
|
|
/**
|
* @param queryTable
|
* @param queryFilter
|
*/
|
public QueryDataConfigEntity(String queryTable, String queryFilter, String autoIncrementPrimaryField) {
|
this.queryTable = queryTable;
|
this.queryFilter = queryFilter;
|
this.autoIncrementPrimaryField = autoIncrementPrimaryField;
|
}
|
|
/**
|
* @param queryTable
|
* @param queryFieldSet
|
* @param queryFilter
|
* @param params
|
*/
|
public QueryDataConfigEntity(String queryTable, Set<String> queryFieldSet, String autoIncrementPrimaryField, String queryFilter, Object[] params) {
|
this.queryTable = queryTable;
|
this.queryFieldSet = queryFieldSet;
|
this.queryFilter = queryFilter;
|
this.params = params;
|
this.autoIncrementPrimaryField = autoIncrementPrimaryField;
|
}
|
|
/**
|
* 添加查询字段
|
*
|
* @param fieldName
|
*/
|
public void addQueryField(String... fieldName) {
|
if (fieldName != null) {
|
if (this.queryFieldSet == null) {
|
this.queryFieldSet = new HashSet<>();
|
}
|
this.queryFieldSet.addAll(Arrays.asList(fieldName));
|
}
|
}
|
|
/**
|
* 删除查询字段
|
*
|
* @param fieldName
|
*/
|
public void removeQueryField(String... fieldName) {
|
this.querySqlSuccess = false;
|
if (fieldName != null && this.queryFieldSet != null && this.queryFieldSet.size() > 0) {
|
for (String field : fieldName) {
|
this.queryFieldSet.remove(field);
|
}
|
}
|
}
|
|
|
public String getQueryTable() {
|
return queryTable;
|
}
|
|
public void setQueryTable(String queryTable) {
|
this.querySqlSuccess = false;
|
this.queryTable = queryTable;
|
}
|
|
public Set<String> getQueryFieldSet() {
|
return queryFieldSet;
|
}
|
|
public void setQueryFieldSet(Set<String> queryFieldSet) {
|
this.querySqlSuccess = false;
|
this.queryFieldSet = queryFieldSet;
|
}
|
|
public String getQueryFilter() {
|
return queryFilter;
|
}
|
|
public void setQueryFilter(String queryFilter) {
|
this.querySqlSuccess = false;
|
this.queryFilter = queryFilter;
|
}
|
|
public Object[] getParams() {
|
return params;
|
}
|
|
public void setParams(Object[] params) {
|
this.params = params;
|
}
|
|
public DataBaseType getDbt() {
|
return dbt;
|
}
|
|
public void setDbt(DataBaseType dbt) {
|
this.querySqlSuccess = false;
|
this.dbt = dbt;
|
}
|
|
public String getAutoIncrementPrimaryField() {
|
return autoIncrementPrimaryField;
|
}
|
|
public void setAutoIncrementPrimaryField(String autoIncrementPrimaryField) {
|
this.autoIncrementPrimaryField = autoIncrementPrimaryField;
|
}
|
|
/**
|
* 查询sql状态 是否初始化模板完成
|
*/
|
private boolean querySqlSuccess = false;
|
|
private String sqlTemplate;
|
|
/**
|
* 获取查询sql
|
*
|
* @param currentPage 当前页数
|
* @param pageSize 每页条数
|
* @return
|
*/
|
public String getQuerySql(int currentPage, int pageSize) throws BaseException {
|
if (!this.querySqlSuccess) {
|
this.initQuerySqlTemplate();
|
}
|
int first = -1;
|
int second = -1;
|
if (DataBaseType.ORACLE.equals(this.dbt)) {
|
first = currentPage * pageSize;
|
second = (currentPage - 1) * pageSize;
|
} else if (DataBaseType.MYSQL.equals(this.dbt)) {
|
if (currentPage == 1) {
|
first = 0;
|
} else {
|
first = (currentPage - 1) * pageSize;
|
}
|
second = pageSize;
|
} else if (DataBaseType.SQLSERVER.equals(this.dbt)) {
|
first = pageSize;
|
second = (currentPage - 1) * pageSize;
|
} else if (DataBaseType.INFORMIX.equals(this.dbt)) {
|
first = (currentPage - 1) * pageSize;
|
second = pageSize;
|
} else if (DataBaseType.PSQL.equals(this.dbt)) {
|
first = pageSize;
|
second = pageSize * (currentPage - 1);
|
} else {
|
throw new BaseException(ErrorCode.NONSUPPORT_DATABASE_TYPE);
|
}
|
|
return sqlTemplate.replace("<<~1~>>", first + "").replace("<<~2~>>", second + "");
|
}
|
|
|
public String getQueryTotalCountSql() {
|
StringBuilder sql = new StringBuilder("SELECT ");
|
sql.append("count(").append(this.autoIncrementPrimaryField).append(") as total_count FROM ");
|
sql.append(this.queryTable);
|
if (!StringUtils.isEmpty(this.queryFilter)) {
|
sql.append(" WHERE ( ").append(this.queryFilter).append(" ) ");
|
}
|
return sql.toString();
|
}
|
|
/**
|
* 初始化查询sql
|
*/
|
private void initQuerySqlTemplate() {
|
if (this.queryFieldSet == null || this.queryFieldSet.size() <= 0) {
|
throw new BaseException(ErrorCode.BATCH_EXECUTE_QUERY_FIELD_CAN_NOT_EMPTY);
|
}
|
StringBuilder queryTemplate = new StringBuilder("SELECT ");
|
if (DataBaseType.INFORMIX.equals(this.dbt)) {
|
queryTemplate.append(" SKIP <<~1~>> FIRST <<~2~>> ");
|
} else if (DataBaseType.SQLSERVER.equals(this.dbt)) {
|
// queryTemplate.append(" TOP <<~1~>> ");
|
// TODO: 2022/7/25 暂未实现sqlserver查询
|
}
|
|
String[] fields = this.queryFieldSet.stream().toArray(String[]::new);
|
for (int i = 0; i < fields.length; i++) {
|
if (i > 0) {
|
queryTemplate.append(" , ");
|
}
|
queryTemplate.append(BatchUtils.getFieldName(fields[i], this.dbt));
|
}
|
if (!this.queryFieldSet.contains(autoIncrementPrimaryField)) {
|
queryTemplate.append(",").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt));
|
}
|
//ORACLE 特殊查询出序号用于分页
|
if (DataBaseType.ORACLE.equals(this.dbt)) {
|
queryTemplate.append("");
|
}
|
queryTemplate.append(" FROM ").append(this.queryTable);
|
//是否拼接了WHERE 关键字
|
boolean isWhere = false;
|
if (!StringUtils.isEmpty(this.queryFilter)) {
|
isWhere = true;
|
queryTemplate.append(" WHERE ").append("(").append(this.queryFilter).append(") ");
|
}
|
if (DataBaseType.ORACLE.equals(this.dbt)) {
|
|
queryTemplate.append(" order by ").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt)).append(" asc ");
|
queryTemplate.insert(0, "SELECT aa.*,ROWNUM as row_num FROM ( ");
|
|
queryTemplate.append(" ) aa ");
|
|
queryTemplate.append(" WHERE ( ").append(" ROWNUM<=<<~1~>> ) ");
|
|
queryTemplate.insert(0, " SELECT * FROM (");
|
queryTemplate.append(" ) A where (row_num> <<~2~>> )");
|
} else {
|
|
queryTemplate.append(" order by ").append(BatchUtils.getFieldName(this.autoIncrementPrimaryField, this.dbt)).append(" asc ");
|
}
|
if (this.dbt.equals(DataBaseType.MYSQL)) {
|
queryTemplate.append(" limit <<~1~>>,<<~2~>>");
|
}
|
this.sqlTemplate = queryTemplate.toString();
|
this.querySqlSuccess = true;
|
}
|
|
public static void main(String[] args) {
|
StringBuilder sql = new StringBuilder();
|
sql.append("SELECT *,ROWNNUM from TABLE where 1=1");
|
sql.append(" order by xxx");
|
sql.insert(0, " SELECT *,ROWNUM as row_num FROM (");
|
sql.append(" ) A ");
|
// sql.append(") a ORDER BY XXX");
|
sql.insert(0, " SELECT * FROM (");
|
sql.append(") A ");
|
System.out.println(sql);
|
}
|
}
|
