需求描述：
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/*.xml
org.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));
}
@SafeVarargs
public 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));
}
@SafeVarargs
public 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";
@Getter
private final List<Table> fromTables = new ArrayList<>();
@Getter
private final List<Table> queryTables = new ArrayList<>();
@Getter
private 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 referenced
table.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) {
// 非弱表(即强表)则必须Join
if (!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 {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
public void test1(){
BasicConditionQueryCriteria queryCriteria = new BasicConditionQueryCriteria();
queryCriteria.setName(Arrays.asList("1","2"));
//拼接sql
List<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，项目结构类图
