package com.lunhan.xxx.common.orm2;
|
|
import com.lunhan.xxx.common.ConstantFactory;
|
import com.lunhan.xxx.common.orm2.enums.ColumnBasic;
|
import com.lunhan.xxx.common.orm2.enums.EnumBuildType;
|
import com.lunhan.xxx.common.orm2.enums.EnumSqlType;
|
import com.lunhan.xxx.common.orm2.models.SqlCondition;
|
import com.lunhan.xxx.common.orm2.models.SqlParameter;
|
import com.lunhan.xxx.common.util.ListUtil;
|
import com.lunhan.xxx.common.util.NumericUtil;
|
import com.lunhan.xxx.common.util.StringUtil;
|
import org.apache.commons.lang3.BooleanUtils;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
/**
|
* Sql生成器
|
*
|
* @author yufq
|
* @date 2021-4-9
|
*/
|
public class SqlBuilder {
|
private EnumBuildType buildType;
|
private List<String> selectColumnList;
|
private String fromTableName;
|
private List<String> joinTableList;
|
private List<SqlCondition> setList;
|
private List<SqlCondition> whereList;
|
private List<SqlCondition> condList;
|
private List<String> orderByList;
|
private Integer skipIndex;
|
private Integer takeNum;
|
|
public Integer getPageSize() {
|
return NumericUtil.tryParseInt(takeNum, 0);
|
}
|
public Integer getPageIndex() {
|
return NumericUtil.tryParseInt(skipIndex, 0) / NumericUtil.tryParseInt(takeNum, 1) + 1;
|
}
|
|
public SqlBuilder() {
|
this.buildType = EnumBuildType.SELECT;
|
this.selectColumnList = new ArrayList<>();
|
this.joinTableList = new ArrayList<>();
|
this.setList = new ArrayList<>();
|
this.whereList = new ArrayList<>();
|
this.condList = new ArrayList<>();
|
this.orderByList = new ArrayList<>();
|
}
|
|
public SqlBuilder select(ColumnBasic... columns) {
|
String[] columnNames = new String[columns.length];
|
for (int i = 0; i < columns.length; i++) {
|
columnNames[i] = columns[i].getColumnName();
|
}
|
this.select(columnNames);
|
return this;
|
}
|
|
public SqlBuilder select(String... columns) {
|
buildType = EnumBuildType.SELECT;
|
selectColumnList.addAll(Arrays.asList(columns));
|
return this;
|
}
|
|
public SqlBuilder update(String tableName) {
|
buildType = EnumBuildType.UPDATE;
|
this.fromTableName = tableName;
|
return this;
|
}
|
|
public SqlBuilder delete(String tableName) {
|
buildType = EnumBuildType.DELETE;
|
this.fromTableName = tableName;
|
return this;
|
}
|
|
public SqlBuilder insert(String tableName) {
|
buildType = EnumBuildType.INSERT;
|
this.fromTableName = tableName;
|
return this;
|
}
|
|
public SqlBuilder from(String tableName) {
|
this.fromTableName = tableName;
|
return this;
|
}
|
|
public SqlBuilder join(String tableName, ColumnBasic columnA, ColumnBasic columnB) {
|
joinTableList.add(String.format(" JOIN %s ON %s = %s", tableName, columnA.getColumnName(), columnB.getColumnName()));
|
return this;
|
}
|
|
public SqlBuilder join(String tableName, String columnA, String columnB) {
|
joinTableList.add(String.format(" JOIN %s ON %s = %s", tableName, columnA, columnB));
|
return this;
|
}
|
|
public SqlBuilder leftJoin(String tableName, ColumnBasic columnA, ColumnBasic columnB) {
|
joinTableList.add(String.format(" LEFT JOIN %s ON %s = %s", tableName, columnA.getColumnName(), columnB.getColumnName()));
|
return this;
|
}
|
|
public SqlBuilder leftJoin(String tableName, String columnA, String columnB) {
|
joinTableList.add(String.format(" LEFT JOIN %s ON %s = %s", tableName, columnA, columnB));
|
return this;
|
}
|
|
public SqlBuilder set(ColumnBasic column, Object value) {
|
this.setList.add(column.equal(value));
|
return this;
|
}
|
|
public SqlBuilder set(SqlCondition param) {
|
this.setList.add(param);
|
return this;
|
}
|
|
public SqlBuilder where(SqlCondition param) {
|
this.whereList.add(param);
|
return this;
|
}
|
|
public SqlBuilder and(SqlCondition cond) {
|
String sql = cond.getSql();
|
if (this.isAddCond()) {
|
sql = ConstantFactory.SQL_AND + " " + sql;
|
}
|
this.condList.add(new SqlCondition(sql, cond.getParamList()));
|
return this;
|
}
|
|
public SqlBuilder and(SqlBuilder builder) {
|
SqlCondition buildResult = builder.whereBuild();
|
String sql = "(" + buildResult.getSql() + ")";
|
if (this.isAddCond()) {
|
sql = ConstantFactory.SQL_AND + " " + sql;
|
}
|
this.condList.add(new SqlCondition(sql, buildResult.getParamList()));
|
return this;
|
}
|
|
public SqlBuilder andIf(Boolean condition, SqlCondition cond) {
|
if (BooleanUtils.isTrue(condition)) {
|
this.and(cond);
|
}
|
return this;
|
}
|
|
public SqlBuilder andIf(Boolean condition, SqlBuilder builder) {
|
if (BooleanUtils.isTrue(condition)) {
|
this.and(builder);
|
}
|
return this;
|
}
|
|
public SqlBuilder or(SqlCondition cond) {
|
String sql = cond.getSql();
|
if (this.isAddCond()) {
|
sql = ConstantFactory.SQL_OR + " " + cond.getSql();
|
}
|
this.condList.add(new SqlCondition(sql, cond.getParamList()));
|
return this;
|
}
|
|
public SqlBuilder or(SqlBuilder builder) {
|
SqlCondition buildResult = builder.whereBuild();
|
String sql = "(" + buildResult.getSql() + ")";
|
if (this.isAddCond()) {
|
sql = ConstantFactory.SQL_OR + " " + sql;
|
}
|
this.condList.add(new SqlCondition(sql, buildResult.getParamList()));
|
return this;
|
}
|
|
public SqlBuilder orIf(Boolean condition, SqlCondition cond) {
|
if (BooleanUtils.isTrue(condition)) {
|
this.or(cond);
|
}
|
return this;
|
}
|
|
public SqlBuilder orIf(Boolean condition, SqlBuilder builder) {
|
if (BooleanUtils.isTrue(condition)) {
|
this.or(builder);
|
}
|
return this;
|
}
|
|
public SqlBuilder orderBy(String... columns) {
|
this.orderByList.addAll(Arrays.asList(columns));
|
return this;
|
}
|
|
public SqlBuilder skip(Integer skipIndex) {
|
this.skipIndex = skipIndex;
|
return this;
|
}
|
|
public SqlBuilder take(Integer takeNum) {
|
this.takeNum = takeNum;
|
return this;
|
}
|
|
public SqlBuilder page(Integer pageIndex, Integer pageSize) {
|
this.skipIndex = pageSize * (pageIndex - 1);
|
this.takeNum = pageSize;
|
return this;
|
}
|
|
public SqlCondition count() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
sql.append("SELECT COUNT(*)");
|
if (StringUtil.isNotNullOrEmpty(this.fromTableName)) {
|
sql.append(" FROM ");
|
sql.append(this.fromTableName);
|
}
|
if (ListUtil.isNotNullOrEmpty(this.joinTableList)) {
|
for (String joinTable : this.joinTableList) {
|
sql.append(joinTable);
|
}
|
}
|
SqlCondition whereCond = this.whereBuild();
|
if (this.whereList.isEmpty() && BooleanUtils.isFalse(condList.isEmpty())) {
|
sql.append(" WHERE ");
|
}
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
public SqlCondition build() {
|
return this.build(EnumSqlType.DEFAULT);
|
}
|
|
public SqlCondition build(EnumSqlType sqlType) {
|
//参数处理
|
this.paramSort();
|
|
SqlCondition sqlCondition;
|
switch (this.buildType) {
|
case SELECT:
|
sqlCondition = this.selectBuild();
|
break;
|
case UPDATE:
|
sqlCondition = this.updateBuild();
|
break;
|
case DELETE:
|
sqlCondition = this.deleteBuild();
|
break;
|
case INSERT:
|
sqlCondition = this.insertBuild();
|
break;
|
default:
|
return null;
|
}
|
|
if (EnumSqlType.MYBATIS.equals(sqlType)) {
|
sqlCondition = this.getSqlMyBatis(sqlCondition);
|
}
|
return sqlCondition;
|
}
|
|
public String log() {
|
return this.log(EnumSqlType.DEFAULT);
|
}
|
|
public String log(EnumSqlType sqlType) {
|
SqlCondition buildResult = this.build(sqlType);
|
String sql = buildResult.getSql();
|
List<String> paramList = new ArrayList<>();
|
for (SqlParameter param : buildResult.getParamList()) {
|
paramList.add(param.getName() + "=" + param.getValue());
|
}
|
return sql + " [" + StringUtil.join(paramList, ",") + "]";
|
}
|
|
//***************************************************************************//
|
|
private boolean isAddCond() {
|
if (ListUtil.isNotNullOrEmpty(this.whereList) || ListUtil.isNotNullOrEmpty(this.condList)) {
|
return true;
|
}
|
return false;
|
}
|
|
private SqlCondition selectBuild() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
if (ListUtil.isNotNullOrEmpty(this.selectColumnList)) {
|
sql.append("SELECT ");
|
sql.append(StringUtil.join(this.selectColumnList, ConstantFactory.SQL_STR_COMMA));
|
}
|
if (StringUtil.isNotNullOrEmpty(this.fromTableName)) {
|
sql.append(" FROM ");
|
sql.append(this.fromTableName);
|
}
|
if (ListUtil.isNotNullOrEmpty(this.joinTableList)) {
|
for (String joinTable : this.joinTableList) {
|
sql.append(joinTable);
|
}
|
}
|
SqlCondition whereCond = this.whereBuild();
|
if (this.whereList.isEmpty() && BooleanUtils.isFalse(condList.isEmpty())) {
|
sql.append(" WHERE ");
|
}
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
if (ListUtil.isNotNullOrEmpty(this.orderByList)) {
|
sql.append(" ORDER BY ");
|
sql.append(StringUtil.join(this.orderByList, ConstantFactory.SQL_STR_COMMA));
|
}
|
if (skipIndex != null || takeNum != null) {
|
sql.append(String.format(" LIMIT %s,%s ", NumericUtil.tryParseInt(skipIndex, 0), NumericUtil.tryParseInt(takeNum, 0)));
|
}
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
private SqlCondition updateBuild() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
sql.append("UPDATE ");
|
sql.append(fromTableName);
|
if (ListUtil.isNotNullOrEmpty(this.setList)) {
|
sql.append(" SET ");
|
for (int i = 0; i < this.setList.size(); i++) {
|
SqlCondition setCond = this.setList.get(i);
|
if (i > 0) {
|
sql.append(ConstantFactory.SQL_STR_COMMA);
|
}
|
sql.append(setCond.getSql());
|
paramList.addAll(setCond.getParamList());
|
}
|
}
|
SqlCondition whereCond = this.whereBuild();
|
if (this.whereList.isEmpty() && BooleanUtils.isFalse(condList.isEmpty())) {
|
sql.append(" WHERE ");
|
}
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
private SqlCondition deleteBuild() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
sql.append("DELETE FROM ");
|
sql.append(fromTableName);
|
SqlCondition whereCond = this.whereBuild();
|
if (this.whereList.isEmpty() && BooleanUtils.isFalse(condList.isEmpty())) {
|
sql.append(" WHERE ");
|
}
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
private SqlCondition insertBuild() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
sql.append("INSERT INTO ");
|
sql.append(fromTableName);
|
if (ListUtil.isNotNullOrEmpty(this.setList)) {
|
sql.append(" (");
|
for (int i = 0; i < this.setList.size(); i++) {
|
SqlCondition setCond = this.setList.get(i);
|
if (i > 0) {
|
sql.append(ConstantFactory.SQL_STR_COMMA);
|
}
|
String name = setCond.getParamList().get(0).getName();
|
sql.append(name.substring(0, name.indexOf(".")));
|
paramList.addAll(setCond.getParamList());
|
}
|
sql.append(") VALUES(");
|
List<String> listWen = this.setList.stream()
|
.map(c -> "?")
|
.collect(Collectors.toList());
|
sql.append(StringUtil.join(listWen));
|
sql.append(")");
|
}
|
sql.append(";");
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
private SqlCondition whereBuild() {
|
StringBuilder sql = new StringBuilder();
|
List<SqlParameter> paramList = new ArrayList<>();
|
if (ListUtil.isNotNullOrEmpty(this.whereList)) {
|
sql.append(" WHERE ");
|
for (SqlCondition whereCond : this.whereList) {
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
}
|
}
|
if (ListUtil.isNotNullOrEmpty(this.condList)) {
|
for (SqlCondition whereCond : this.condList) {
|
sql.append(whereCond.getSql());
|
paramList.addAll(whereCond.getParamList());
|
}
|
}
|
return new SqlCondition(sql.toString(), paramList);
|
}
|
|
private SqlCondition getSqlMyBatis(SqlCondition sqlCondition) {
|
List<SqlParameter> paramList = new ArrayList<>();
|
sqlCondition.getParamList().stream().forEach(a -> {
|
paramList.add(new SqlParameter(String.format("#{%s}", a.getName()), a.getValue(), a.getType()));
|
});
|
|
String sql = sqlCondition.getSql();
|
sql = sql.replace("?", "%s");
|
sql = String.format(sql, paramList.stream().map(SqlParameter::getName).toArray());
|
return new SqlCondition(sql, paramList);
|
}
|
|
private void paramSort() {
|
List<SqlParameter> paramList = new ArrayList<>();
|
this.setList.stream().forEach(item -> paramList.addAll(item.getParamList()));
|
this.whereList.stream().forEach(item -> paramList.addAll(item.getParamList()));
|
this.condList.stream().forEach(item -> paramList.addAll(item.getParamList()));
|
paramList.stream().forEach(param -> {
|
String name = param.getName() + "." + paramList.indexOf(param);
|
param.setName(name);
|
});
|
}
|
}
|
