Spring JdbcTemplate实现自定义动态sql拼接功能

需求描述：

sql 需要能满足支持动态拼接，包含查询字段、查询表、关联表、查询条件、关联表的查询条件、排序、分组、去重等

实现步骤：

1，创建表及导入测试数据

CREATE TABLE YES_DEV.T11 (ID BINARY_BIGINT NOT NULL,NAME VARCHAR(10),XX BINARY_BIGINT,CONSTRAINT _PK_SYS_25_63 PRIMARY KEY (ID)
);CREATE TABLE YES_DEV.T111 (ID BINARY_INTEGER NOT NULL,NAME NUMBER
);INSERT INTO YES_DEV.T11 (ID,NAME,XX) VALUES(11,'123',11),(9,'9',9),(8,'8',8),(7,'7',7),(6,'6',6),(5,'5',5),(4,'4',4),(3,'3',3),(2,'2',2),(1,'1',1);INSERT INTO YES_DEV.T111 (ID,NAME) VALUES(1,123);

2，创建项目并引入 pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>testMybatis</artifactId><version>1.0-SNAPSHOT</version><!-- 父项目信息 --><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.8</version><relativePath/></parent><properties><maven.compiler.source>15</maven.compiler.source><maven.compiler.target>15</maven.compiler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><!-- 高斯DB驱动 --><dependency><groupId>com.huawei.gauss</groupId><artifactId>com.huawei.gauss.jdbc.ZenithDriver</artifactId><version>1.2.1</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency><dependency><groupId>commons-lang</groupId><artifactId>commons-lang</artifactId><version>2.6</version></dependency><dependency><groupId>commons-collections</groupId><artifactId>commons-collections</artifactId><version>3.2.2</version></dependency><dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>30.1-jre</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>

3，编写 application.properties文件

spring.datasource.url=jdbc:zenith:@xxxx:xxx
spring.datasource.username=xxx
spring.datasource.password=xxxx
spring.datasource.driver-class-name=com.huawei.gauss.jdbc.inner.GaussDriver
mybatis.mapper-locations=classpath:mapper/*.xmlorg.apache.springframework.jdbc.core.JdbcTemplate = debug

4，核心类之 Column

package com.example.dao.sql;import com.example.utils.SqlUtils;
import lombok.Getter;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;@Getter
public class Column {private final String name;private final List<String> tableAliases = new ArrayList<>();private Column(String name, List<String> tableAliases) {this.name = name;if (CollectionUtils.isNotEmpty(tableAliases)) {this.tableAliases.addAll(tableAliases);}}public static Column of(String name, String... tableAliases) {if (ArrayUtils.isNotEmpty(tableAliases)) {tableAliases = Arrays.stream(tableAliases).filter(Objects::nonNull).toArray(String[]::new);}if (ArrayUtils.isEmpty(tableAliases)) {String tableAlias = StringUtils.substringBefore(name, ".").trim();if (SqlUtils.isValidAlias(tableAlias)) {tableAliases = new String[] {tableAlias};}}return new Column(name, ArrayUtils.isEmpty(tableAliases) ? null : Arrays.asList(tableAliases));}public String getTableAlias() {return tableAliases.isEmpty() ? null : tableAliases.get(0);}}

5，核心类之 Table

package com.example.dao.sql;import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.example.utils.SqlUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;
import java.util.stream.Stream;public class Table {private String joinType;private boolean weak;private boolean optimizable = true;private Map<String, List<?>> parameterTempData;private final String name;private final String alias;private final String physicalName;private final List<Condition> joinConditions = new ArrayList<>();private final List<PredicateCondition<?>> joinPredicateConditions = new ArrayList<>();private final List<Condition> filterConditions = new ArrayList<>();private final List<PredicateCondition<?>> filterPredicateConditions = new ArrayList<>();private final List<PropertyCondition> filterPropertyConditions = new ArrayList<>();private Table(String name, String alias, String physicalName) {this.name = name;this.alias = alias;this.physicalName = physicalName;}public static Table of(String name, String physicalName) {String separator = " ";String normalName = name.trim();String alias = StringUtils.substringAfterLast(normalName, separator).trim();SqlUtils.assertValidAlias(alias);if (StringUtils.isEmpty(physicalName)) {physicalName = StringUtils.substringBefore(normalName, separator).trim();}return new Table(name, alias, physicalName);}public static Table of(String name) {return of(name, (String) null);}public static Table of(String name, Map<String, List<?>> parameterTempData) {return of(name, null, parameterTempData);}public static Table of(String name, String physicalName, Map<String, List<?>> parameterTempData) {Table table = Table.of(name, physicalName);table.setParameterTempData(parameterTempData);return table;}public String getJoinType() {return joinType;}public void setJoinType(String joinType) {this.joinType = joinType;}public String getName() {return name;}public String getAlias() {return alias;}public String getPhysicalName() {return physicalName;}public List<Condition> getJoinConditions() {return joinConditions;}public List<PredicateCondition<?>> getJoinPredicateConditions() {return joinPredicateConditions;}public List<Condition> getFilterConditions() {return filterConditions;}public List<PredicateCondition<?>> getFilterPredicateConditions() {return filterPredicateConditions;}public List<PropertyCondition> getFilterPropertyConditions() {return filterPropertyConditions;}public Table on(String condition, String... associationTableAliases) {joinConditions.add(Condition.of(condition, associationTableAliases == null ? null : Arrays.asList(associationTableAliases)));return this;}public <T> Table on(PredicateCondition<T> predicateCondition) {joinPredicateConditions.add(predicateCondition);return this;}public <T> Table on(Predicate<T> predicate, String condition) {return filter(PredicateCondition.of(predicate, condition));}public <T> Table on(Predicate<T> predicate, Function<T, String> conditionSupplier) {return filter(PredicateCondition.of(predicate, conditionSupplier));}@SafeVarargspublic final <T> Table on(Predicate<T> predicate, PredicateCondition<T>... predicateConditions) {if (ArrayUtils.isEmpty(predicateConditions)) {return this;}for (PredicateCondition<T> predicateCondition : predicateConditions) {predicateCondition.setPredicate(predicate.and(predicateCondition.getPredicate()));joinPredicateConditions.add(predicateCondition);}return this;}public Table filter(String... conditions) {if (ArrayUtils.isNotEmpty(conditions)) {filterConditions.addAll(Stream.of(conditions).map(Condition::of).collect(Collectors.toList()));}return this;}public <T> Table filter(PredicateCondition<T> predicateCondition) {filterPredicateConditions.add(predicateCondition);return this;}public <T> Table filter(SFunction<T, Object> fieldGetter, String columnName) {return filter(fieldGetter, columnName, true);}public <T> Table filter(SFunction<T, Object> fieldGetter, String columnName, boolean optimizable) {filterPropertyConditions.add(PropertyCondition.of(fieldGetter, columnName, optimizable));return this;}public <T> Table filter(Predicate<T> predicate, String condition) {return filter(PredicateCondition.of(predicate, condition));}public <T> Table filter(Predicate<T> predicate, Function<T, String> conditionSupplier) {return filter(PredicateCondition.of(predicate, conditionSupplier));}@SafeVarargspublic final <T> Table filter(Predicate<T> predicate, PredicateCondition<T>... predicateConditions) {if (ArrayUtils.isEmpty(predicateConditions)) {return this;}for (PredicateCondition<T> predicateCondition : predicateConditions) {predicateCondition.setPredicate(predicate.and(predicateCondition.getPredicate()));filter(predicateCondition);}return this;}public Table weak() {return weak(true);}public Table weak(boolean weak) {this.weak = weak;return this;}public boolean isWeak() {return weak;}public Table optimize(boolean optimizable) {this.optimizable = optimizable;return this;}public boolean isOptimizable() {return optimizable;}public Map<String, List<?>> getParameterTempData() {return parameterTempData;}public void setParameterTempData(Map<String, List<?>> parameterTempData) {this.parameterTempData = parameterTempData;}}

6，核心类之 Condition

package com.example.dao.sql;import java.util.Collections;
import java.util.List;class Condition {private final String sql;private final List<String> associationTableAliases;public static Condition of(String sql) {return new Condition(sql, null);}public static Condition of(String sql, List<String> associationTableAliases) {return new Condition(sql, associationTableAliases);}private Condition(String sql, List<String> associationTableAliases) {this.sql = sql;this.associationTableAliases = associationTableAliases == null ? Collections.emptyList() : associationTableAliases;}public String getSql() {return sql;}public List<String> getAssociationTableAliases() {return associationTableAliases;}}

7，核心类之 PredicateCondition

package com.example.dao.sql;import java.util.function.Function;
import java.util.function.Predicate;public class PredicateCondition<T> {private Predicate<T> predicate;private Condition condition;private Function<T, Condition> conditionSupplier;public static <T> PredicateCondition<T> of(Predicate<T> predicate, String condition) {return new PredicateCondition<>(predicate, Condition.of(condition));}public static <T> PredicateCondition<T> of(Predicate<T> predicate, Function<T, String> conditionSupplier) {return new PredicateCondition<>(predicate, parameter -> Condition.of(conditionSupplier.apply(parameter)));}private PredicateCondition(Predicate<T> predicate, Condition condition) {this.predicate = predicate;this.condition = condition;}private PredicateCondition(Predicate<T> predicate, Function<T, Condition> conditionSupplier) {this.predicate = predicate;this.conditionSupplier = conditionSupplier;}public Predicate<T> getPredicate() {return predicate;}public void setPredicate(Predicate<T> predicate) {this.predicate = predicate;}public Condition getCondition() {return condition;}public void setCondition(Condition condition) {this.condition = condition;}public Function<T, Condition> getConditionSupplier() {return conditionSupplier;}public void setConditionSupplier(Function<T, Condition> conditionSupplier) {this.conditionSupplier = conditionSupplier;}}

8，核心类之 PropertyCondition

package com.example.dao.sql;import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import lombok.Data;@Data
public class PropertyCondition {private SFunction<?, Object> fieldGetter;private String columnName;private boolean optimizable;private PropertyCondition(SFunction<?, Object> fieldGetter, String columnName, boolean optimizable) {this.fieldGetter = fieldGetter;this.columnName = columnName;this.optimizable = optimizable;}public static PropertyCondition of(SFunction<?, Object> fieldGetter, String columnName, boolean optimizable) {return new PropertyCondition(fieldGetter, columnName, optimizable);}}

9，核心类之 SqlBuilder

package com.example.dao.sql;import com.baomidou.mybatisplus.core.toolkit.LambdaUtils;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.example.utils.ObjectUtils;
import com.example.utils.SqlUtils;
import com.google.common.base.CaseFormat;
import lombok.Getter;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.reflection.property.PropertyNamer;import java.util.*;
import java.util.stream.Collectors;//TODO 优化join排序，无依赖的inner join排在前面，有依赖的排在依赖表后面
public class SqlBuilder {private static final int PARAM_VALUE_NUM = 1000;private static final String NA = "NA";private static final String FROM = "FROM";private static final String INNER = "INNER";private static final String LEFT = "LEFT";private static final String RIGHT = "RIGHT";private static final String FULL = "FULL";@Getterprivate final List<Table> fromTables = new ArrayList<>();@Getterprivate final List<Table> queryTables = new ArrayList<>();@Getterprivate final List<Column> selectColumns = new ArrayList<>();private final List<Condition> filterConditions = new ArrayList<>();private final List<String> orderByConditions = new ArrayList<>();private final List<String> appendSqlSnippets = new ArrayList<>();private final Map<Table, TableQueryCondition> tableQueryConditions = new HashMap<>();//private final Map<Table, List<Table>> associationTables = new HashMap<>();/*   private final List<PredicateCondition<?>> predicateConditions = new ArrayList<>();*/private boolean enablePlaceholder = true;private boolean enableOrderBy = true;/*** 增加字段去重*/private boolean enableDistinct;public boolean isEnablePlaceholder() {return enablePlaceholder;}public SqlBuilder setEnablePlaceholder(boolean enablePlaceholder) {this.enablePlaceholder = enablePlaceholder;return this;}public SqlBuilder enableOrderBy() {this.enableOrderBy = true;return this;}public SqlBuilder disableOrderBy() {this.enableOrderBy = false;return this;}public boolean isEnableDistinct() {return enableDistinct;}public void setEnableDistinct(boolean enableDistinct) {this.enableDistinct = enableDistinct;}private static class TableQueryCondition {private final Table table;private String joinCondition;private String filterCondition;private boolean referenced;public TableQueryCondition(Table table) {this.table = table;}public Table getTable() {return table;}public String getJoinCondition() {return joinCondition;}public void setJoinCondition(String joinCondition) {this.joinCondition = joinCondition;}public String getFilterCondition() {return filterCondition;}public void setFilterCondition(String filterCondition) {this.filterCondition = filterCondition;}public boolean isReferenced() {return referenced;}public void setReferenced(boolean referenced) {this.referenced = referenced;}}private Map<String, List<?>> supplementPropertyFilters(Table table, Object queryCriteria) {Map<String, List<?>> parameterTempData = new HashMap<>();if(queryCriteria == null){return parameterTempData;}for (PropertyCondition propertyCondition : table.getFilterPropertyConditions()) {SFunction<?, Object> fieldGetter = propertyCondition.getFieldGetter();String fieldName = PropertyNamer.methodToProperty(LambdaUtils.resolve(fieldGetter).getImplMethodName());Object fieldValue = fieldGetter.apply(ObjectUtils.cast(queryCriteria));if (!(fieldValue instanceof List) || ((List<?>) fieldValue).isEmpty()) {continue;}String columnName = propertyCondition.getColumnName();if (!propertyCondition.isOptimizable()) {table.optimize(false);}List<?> collectionValue = ((List<?>) fieldValue);if (collectionValue.size() <= PARAM_VALUE_NUM) {StringBuilder condition = new StringBuilder(columnName).append(SqlUtils.buildColumnInCondition(fieldName, collectionValue, enablePlaceholder));if (collectionValue.contains(NA) || collectionValue.contains(null)) {condition.append(" OR ").append(columnName).append(" is null");table.optimize(false);}table.filter(condition.toString());} else {// 当参数值过多采用临时表关联时，将表设置为强关联// todo referencedtable.weak(false);String tempTableAlias = "_" + CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName);String tempDataType = tempTableAlias.toUpperCase();StringBuilder joinCondition = new StringBuilder();joinCondition.append(tempTableAlias).append(".value").append(" = ");if (collectionValue.contains(NA)) {joinCondition.append("nvl(").append(columnName).append(", '").append(NA).append("')");} else {joinCondition.append(columnName);}Object sampleValue = collectionValue.get(0);String queryField = sampleValue instanceof Number ? "to_number(string_value)" : "string_value";Table tempTable = Table.of("(SELECT " + queryField + " value FROM t_comm_data_temp WHERE data_type = '" + tempDataType + "') " + tempTableAlias).on(joinCondition.toString(), table.getAlias());tempTable.setJoinType(INNER);associationTable(tempTable, table);parameterTempData.put(tempDataType, collectionValue);}}return parameterTempData;}public ExecutionSql builder(Object queryCriteria) {ExecutionSql executionSql = new ExecutionSql();analyzeTables(queryCriteria, executionSql);StringBuilder sql = new StringBuilder();appendSelectColumns(sql);appendFromTables(sql, executionSql);appendJoinTables(sql, executionSql);appendWhereConditions(sql);appendOrderByConditions(sql);appendSqlSnippets(sql);executionSql.setSql(sql.toString());return executionSql;}private void analyzeTables(Object queryCriteria, ExecutionSql executionSql) {supplementTablePropertyFilters(queryCriteria, executionSql);analyzeTableDependences(queryCriteria);}private void supplementTablePropertyFilters(Object queryCriteria, ExecutionSql executionSql) {Map<String, List<?>> parameterTempData = new HashMap<>();// Query Table列表需进行拷贝，便于逻辑中动态增加临时表for (Table table : new ArrayList<>(queryTables)) {parameterTempData.putAll(supplementPropertyFilters(table, queryCriteria));}executionSql.setParameterTempData(parameterTempData);}private void analyzeTableDependences(Object queryCriteria) {Set<String> referencedTableAliases = filterConditions.stream().flatMap(record -> record.getAssociationTableAliases().stream()).collect(Collectors.toSet());referencedTableAliases.addAll(selectColumns.stream().flatMap(column -> column.getTableAliases().stream()).collect(Collectors.toSet()));Map<String, Table> tableAliasMap = new HashMap<>();List<Table> referencedTables = new ArrayList<>();for (Table table : queryTables) {TableQueryCondition tableAnalysisResult = new TableQueryCondition(table);tableAnalysisResult.setJoinCondition(buildConditionSql(table.getJoinConditions(), table.getJoinPredicateConditions(), queryCriteria));tableAnalysisResult.setFilterCondition(buildConditionSql(table.getFilterConditions(), table.getFilterPredicateConditions(), queryCriteria));// 是否自带过滤条件或被其他其他地方使用if (StringUtils.isNotEmpty(tableAnalysisResult.getFilterCondition()) || referencedTableAliases.contains(table.getAlias())) {tableAnalysisResult.setReferenced(true);referencedTables.add(table);}// 是否开启优化, 有过滤条件的情况下将Left/Right Join优化成Inner Join，提升查询速度if (table.isOptimizable() && Arrays.asList(LEFT, RIGHT).contains(table.getJoinType()) && StringUtils.isNotEmpty(tableAnalysisResult.getFilterCondition())) {table.setJoinType(INNER);}tableQueryConditions.put(table, tableAnalysisResult);tableAliasMap.put(StringUtils.defaultString(table.getAlias(), table.getName()), table);}//TODO tale and table 引用依赖/*for (Table table : referencedTables) {markReferenceTable(table, queryCriteria, tableAliasMap);}*/}/*private void markReferenceTable(Table referencedTable, Object queryCriteria, Map<String, Table> tableAliasMap) {List<Condition> effectiveConditions = findEffectiveConditions(referencedTable.getJoinConditions(), referencedTable.getJoinPredicateConditions(),queryCriteria);effectiveConditions.addAll(findEffectiveConditions(referencedTable.getFilterConditions(), referencedTable.getFilterPredicateConditions(), queryCriteria));List<String> associationTableAliases = effectiveConditions.stream().flatMap(condition -> condition.getAssociationTableAliases().stream()).collect(Collectors.toList());for (String associationTableAlias : associationTableAliases) {Table associationTable = tableAliasMap.get(associationTableAlias);Assert.notNull(associationTable, "Can't find association table for alias [" + associationTableAlias + "]");TableQueryCondition tableQueryCondition = tableQueryConditions.get(associationTable);if (!tableQueryCondition.isReferenced()) {//todo table.setReferenced(true);tableQueryCondition.setReferenced(true);markReferenceTable(associationTable, queryCriteria, tableAliasMap);}}}*/private List<Condition> findEffectiveConditions(List<Condition> conditions, List<PredicateCondition<?>> predicateConditions, Object queryCriteria) {List<Condition> effectiveConditions = new ArrayList<>();conditions.stream().filter(record -> StringUtils.isNotEmpty(record.getSql())).forEach(effectiveConditions::add);if (predicateConditions != null) {for (PredicateCondition<?> predicateCondition : predicateConditions) {if (predicateCondition.getPredicate().test(ObjectUtils.cast(queryCriteria))) {Condition condition = predicateCondition.getCondition();if (condition != null && StringUtils.isNotEmpty(condition.getSql())) {effectiveConditions.add(condition);}if (predicateCondition.getConditionSupplier() != null) {condition = predicateCondition.getConditionSupplier().apply(ObjectUtils.cast(queryCriteria));if (condition != null && StringUtils.isNotEmpty(condition.getSql())) {effectiveConditions.add(condition);}}}}}return effectiveConditions;}private String buildConditionSql(List<Condition> conditions, List<PredicateCondition<?>> predicateConditions, Object queryCriteria) {StringBuilder conditionSql = new StringBuilder();List<Condition> effectiveConditions = findEffectiveConditions(conditions, predicateConditions, queryCriteria);for (Condition condition : effectiveConditions) {appendChildCondition(conditionSql, condition.getSql(), true);}return conditionSql.toString();}private void appendSelectColumns(StringBuilder sql) {sql.append("SELECT ");if(enableDistinct){sql.append("DISTINCT ");}for (int i = 0; i < selectColumns.size(); i++) {Column currentColumn = selectColumns.get(i);sql.append(currentColumn.getName());if (i < selectColumns.size() - 1) {sql.append(", ");}}}private void appendFromTables(StringBuilder sql, ExecutionSql executionSql) {List<Table> fromTables = queryTables.stream().filter(table -> FROM.equals(table.getJoinType())).collect(Collectors.toList());sql.append(" FROM ");for (int i = 0; i < fromTables.size(); i++) {Table table = fromTables.get(i);sql.append(table.getName());if (i < fromTables.size() - 1) {sql.append(", ");}if (table.getParameterTempData() != null) {executionSql.getParameterTempData().putAll(table.getParameterTempData());}}}private void appendJoinTables(StringBuilder sql, ExecutionSql executionSql) {List<Table> joinTables = queryTables.stream().filter(table -> !FROM.equals(table.getJoinType())).collect(Collectors.toList());for (Table table : joinTables) {if (!judgeJoinTable(table)) {continue;}sql.append(" ").append(table.getJoinType()).append(" JOIN ").append(table.getName());sql.append(" ON ").append(tableQueryConditions.get(table).getJoinCondition());if (table.getParameterTempData() != null) {executionSql.getParameterTempData().putAll(table.getParameterTempData());}}}private boolean judgeJoinTable(Table table) {// 非弱表(即强表)则必须Joinif (!table.isWeak()) {return true;}// 是否有被引用return tableQueryConditions.get(table).isReferenced();}private void appendWhereConditions(StringBuilder sql) {StringBuilder whereConditionSql = new StringBuilder();for (Table table : queryTables) {String conditionSql = tableQueryConditions.get(table).getFilterCondition();appendChildCondition(whereConditionSql, conditionSql);}String conditionSql = buildConditionSql(filterConditions, null, null);appendChildCondition(whereConditionSql, conditionSql);if (whereConditionSql.length() > 0) {sql.append(" WHERE ").append(whereConditionSql);}}private void appendChildCondition(StringBuilder parentSql, String condition) {appendChildCondition(parentSql, condition, false);}private void appendChildCondition(StringBuilder parentSql, String condition, boolean bracketWrap) {if (StringUtils.isNotEmpty(condition)) {if (parentSql.length() > 0) {parentSql.append(" AND ");}if (bracketWrap) {condition = "(" + condition + ")";}parentSql.append(condition);}}private void appendOrderByConditions(StringBuilder sql) {if (enableOrderBy && CollectionUtils.isNotEmpty(orderByConditions)) {sql.append(" ORDER BY ").append(String.join(", ", orderByConditions));}}private void appendSqlSnippets(StringBuilder sql) {if (CollectionUtils.isNotEmpty(appendSqlSnippets)) {sql.append(" ").append(String.join(" ", appendSqlSnippets));}}public SqlBuilder select(String... columnNames) {if (ArrayUtils.isNotEmpty(columnNames)) {for (String columnName : columnNames) {select(columnName, null);}}return this;}public SqlBuilder select(String columnName, String tableAlias) {selectColumns.add(Column.of(columnName, tableAlias));return this;}public SqlBuilder select(Column... columns) {if (ArrayUtils.isEmpty(columns)) {return this;}return select(Arrays.asList(columns));}public SqlBuilder select(List<Column> columns) {selectColumns.addAll(columns);return this;}public SqlBuilder fromTable(Table table) {table.setJoinType(FROM);fromTables.add(table);associationTable(table, null);return this;}public SqlBuilder innerJoin(Table table) {table.setJoinType(INNER);associationTable(table, null);return this;}public SqlBuilder leftJoin(Table table) {table.weak().setJoinType(LEFT);associationTable(table, null);return this;}public SqlBuilder rightJoin(Table table) {table.weak().setJoinType(RIGHT);associationTable(table, null);return this;}public SqlBuilder fullJoin(Table table) {table.setJoinType(FULL);associationTable(table, null);return this;}private void associationTable(Table table, Table previousTable) {int index = previousTable == null ? queryTables.size() : queryTables.indexOf(previousTable) + 1;queryTables.add(index, table);}public SqlBuilder filter(String condition, String... associationTableAliases) {filterConditions.add(Condition.of(condition, ArrayUtils.isEmpty(associationTableAliases) ? Collections.emptyList() : Arrays.asList(associationTableAliases)));return this;}public SqlBuilder orderBy(String condition) {orderByConditions.add(condition);return this;}public SqlBuilder append(String sqlSnippet) {appendSqlSnippets.add(sqlSnippet);return this;}}

10，核心类之 ExecutionSql

package com.example.dao.sql;import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;public class ExecutionSql {private String id;private String sql;private Map<String, List<?>> parameterTempData = new HashMap<>();public ExecutionSql() {id = UUID.randomUUID().toString().replace("-", "");id = id.substring(id.length() - 10);}public String getId() {return id;}public String getSql() {return sql;}public void setSql(String sql) {this.sql = sql;}public Map<String, List<?>> getParameterTempData() {return parameterTempData;}public void setParameterTempData(Map<String, List<?>> parameterTempData) {this.parameterTempData = parameterTempData;}}

11，核心类之 BasicConditionQueryCriteria（入参）

package com.example.dao.sql;import lombok.Data;import java.util.List;
import java.util.Set;@Data
public class BasicConditionQueryCriteria implements Cloneable {private List<String> name;private List<Long> xx;private List<Integer> newName;
}

12，编写测试类

import com.example.DemoApplication;
import com.example.dao.sql.*;
import com.example.entity.DataTemp;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
public class SqlBuilderTest {@Autowiredprivate JdbcTemplate jdbcTemplate;@Testpublic void test1(){BasicConditionQueryCriteria queryCriteria = new BasicConditionQueryCriteria();queryCriteria.setName(Arrays.asList("1","2"));//拼接sqlList<Column> columns = Arrays.asList(Column.of("t11.id","t11"),Column.of("t11.name","t11"),Column.of("t11.xx","t11"),Column.of("T1111.name as newName","T1111"));SqlBuilder sqlBuilder = new SqlBuilder();sqlBuilder.select(columns);sqlBuilder.setEnablePlaceholder(false);sqlBuilder.fromTable(Table.of("t11 t11").filter(BasicConditionQueryCriteria::getName,"t11.name")).leftJoin(Table.of("T1111 T1111").on("t11.name = T1111.name"));ExecutionSql executionSql = sqlBuilder.builder(queryCriteria);executionSql.setSql(executionSql.getSql() + " order by t11.id");log.info("sql:{}",executionSql.getSql());List<Map<String, Object>> maps = jdbcTemplate.queryForList(executionSql.getSql());maps.stream().forEach(v->{v.entrySet().stream().forEach(w->{log.info("key:{},value:{}",w.getKey(),w.getValue());});});}}

13，项目结构类图