布吉网站设计,wordpress怎么加目录,布吉商城网站建设基本流程,保定哪家做网站公司好需求描述#xff1a; sql 需要能满足支持动态拼接#xff0c;包含 查询字段、查询表、关联表、查询条件、关联表的查询条件、排序、分组、去重等
实现步骤#xff1a; 1#xff0c;创建表及导入测试数据
CREATE TABLE YES_DEV.T11 (ID BINARY_BIGINT NOT NULL,NAME VARCH…需求描述 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 version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdorg.example/groupIdartifactIdtestMybatis/artifactIdversion1.0-SNAPSHOT/version!-- 父项目信息 --parentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.5.8/versionrelativePath//parentpropertiesmaven.compiler.source15/maven.compiler.sourcemaven.compiler.target15/maven.compiler.targetproject.build.sourceEncodingUTF-8/project.build.sourceEncoding/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion2.1.4/version/dependency!-- 高斯DB驱动 --dependencygroupIdcom.huawei.gauss/groupIdartifactIdcom.huawei.gauss.jdbc.ZenithDriver/artifactIdversion1.2.1/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependencydependencygroupIdjunit/groupIdartifactIdjunit/artifactIdscopetest/scope/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.4.2/version/dependencydependencygroupIdcommons-lang/groupIdartifactIdcommons-lang/artifactIdversion2.6/version/dependencydependencygroupIdcommons-collections/groupIdartifactIdcommons-collections/artifactIdversion3.2.2/version/dependencydependencygroupIdcom.google.guava/groupIdartifactIdguava/artifactIdversion30.1-jre/version/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactIdconfigurationexcludesexcludegroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/exclude/excludes/configuration/plugin/plugins/build/project 3编写 application.properties文件
spring.datasource.urljdbc:zenith:xxxx:xxx
spring.datasource.usernamexxx
spring.datasource.passwordxxxx
spring.datasource.driver-class-namecom.huawei.gauss.jdbc.inner.GaussDriver
mybatis.mapper-locationsclasspath: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 ListString tableAliases new ArrayList();private Column(String name, ListString 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 MapString, List? parameterTempData;private final String name;private final String alias;private final String physicalName;private final ListCondition joinConditions new ArrayList();private final ListPredicateCondition? joinPredicateConditions new ArrayList();private final ListCondition filterConditions new ArrayList();private final ListPredicateCondition? filterPredicateConditions new ArrayList();private final ListPropertyCondition 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, MapString, List? parameterTempData) {return of(name, null, parameterTempData);}public static Table of(String name, String physicalName, MapString, 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 ListCondition getJoinConditions() {return joinConditions;}public ListPredicateCondition? getJoinPredicateConditions() {return joinPredicateConditions;}public ListCondition getFilterConditions() {return filterConditions;}public ListPredicateCondition? getFilterPredicateConditions() {return filterPredicateConditions;}public ListPropertyCondition 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(PredicateConditionT predicateCondition) {joinPredicateConditions.add(predicateCondition);return this;}public T Table on(PredicateT predicate, String condition) {return filter(PredicateCondition.of(predicate, condition));}public T Table on(PredicateT predicate, FunctionT, String conditionSupplier) {return filter(PredicateCondition.of(predicate, conditionSupplier));}SafeVarargspublic final T Table on(PredicateT predicate, PredicateConditionT... predicateConditions) {if (ArrayUtils.isEmpty(predicateConditions)) {return this;}for (PredicateConditionT 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(PredicateConditionT predicateCondition) {filterPredicateConditions.add(predicateCondition);return this;}public T Table filter(SFunctionT, Object fieldGetter, String columnName) {return filter(fieldGetter, columnName, true);}public T Table filter(SFunctionT, Object fieldGetter, String columnName, boolean optimizable) {filterPropertyConditions.add(PropertyCondition.of(fieldGetter, columnName, optimizable));return this;}public T Table filter(PredicateT predicate, String condition) {return filter(PredicateCondition.of(predicate, condition));}public T Table filter(PredicateT predicate, FunctionT, String conditionSupplier) {return filter(PredicateCondition.of(predicate, conditionSupplier));}SafeVarargspublic final T Table filter(PredicateT predicate, PredicateConditionT... predicateConditions) {if (ArrayUtils.isEmpty(predicateConditions)) {return this;}for (PredicateConditionT 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 MapString, List? getParameterTempData() {return parameterTempData;}public void setParameterTempData(MapString, 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 ListString associationTableAliases;public static Condition of(String sql) {return new Condition(sql, null);}public static Condition of(String sql, ListString associationTableAliases) {return new Condition(sql, associationTableAliases);}private Condition(String sql, ListString associationTableAliases) {this.sql sql;this.associationTableAliases associationTableAliases null ? Collections.emptyList() : associationTableAliases;}public String getSql() {return sql;}public ListString getAssociationTableAliases() {return associationTableAliases;}} 7核心类之 PredicateCondition
package com.example.dao.sql;import java.util.function.Function;
import java.util.function.Predicate;public class PredicateConditionT {private PredicateT predicate;private Condition condition;private FunctionT, Condition conditionSupplier;public static T PredicateConditionT of(PredicateT predicate, String condition) {return new PredicateCondition(predicate, Condition.of(condition));}public static T PredicateConditionT of(PredicateT predicate, FunctionT, String conditionSupplier) {return new PredicateCondition(predicate, parameter - Condition.of(conditionSupplier.apply(parameter)));}private PredicateCondition(PredicateT predicate, Condition condition) {this.predicate predicate;this.condition condition;}private PredicateCondition(PredicateT predicate, FunctionT, Condition conditionSupplier) {this.predicate predicate;this.conditionSupplier conditionSupplier;}public PredicateT getPredicate() {return predicate;}public void setPredicate(PredicateT predicate) {this.predicate predicate;}public Condition getCondition() {return condition;}public void setCondition(Condition condition) {this.condition condition;}public FunctionT, Condition getConditionSupplier() {return conditionSupplier;}public void setConditionSupplier(FunctionT, 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 ListTable fromTables new ArrayList();Getterprivate final ListTable queryTables new ArrayList();Getterprivate final ListColumn selectColumns new ArrayList();private final ListCondition filterConditions new ArrayList();private final ListString orderByConditions new ArrayList();private final ListString appendSqlSnippets new ArrayList();private final MapTable, TableQueryCondition tableQueryConditions new HashMap();//private final MapTable, ListTable associationTables new HashMap();/* private final ListPredicateCondition? 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 MapString, List? supplementPropertyFilters(Table table, Object queryCriteria) {MapString, 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) {MapString, 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) {SetString referencedTableAliases filterConditions.stream().flatMap(record - record.getAssociationTableAliases().stream()).collect(Collectors.toSet());referencedTableAliases.addAll(selectColumns.stream().flatMap(column - column.getTableAliases().stream()).collect(Collectors.toSet()));MapString, Table tableAliasMap new HashMap();ListTable 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, MapString, Table tableAliasMap) {ListCondition effectiveConditions findEffectiveConditions(referencedTable.getJoinConditions(), referencedTable.getJoinPredicateConditions(),queryCriteria);effectiveConditions.addAll(findEffectiveConditions(referencedTable.getFilterConditions(), referencedTable.getFilterPredicateConditions(), queryCriteria));ListString associationTableAliases effectiveConditions.stream().flatMap(condition - condition.getAssociationTableAliases().stream()).collect(Collectors.toList());for (String associationTableAlias : associationTableAliases) {Table associationTable tableAliasMap.get(associationTableAlias);Assert.notNull(associationTable, Cant 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 ListCondition findEffectiveConditions(ListCondition conditions, ListPredicateCondition? predicateConditions, Object queryCriteria) {ListCondition 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(ListCondition conditions, ListPredicateCondition? predicateConditions, Object queryCriteria) {StringBuilder conditionSql new StringBuilder();ListCondition 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) {ListTable 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) {ListTable 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(ListColumn 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 MapString, 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 MapString, List? getParameterTempData() {return parameterTempData;}public void setParameterTempData(MapString, 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 ListString name;private ListLong xx;private ListInteger 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));//拼接sqlListColumn 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());ListMapString, Object maps jdbcTemplate.queryForList(executionSql.getSql());maps.stream().forEach(v-{v.entrySet().stream().forEach(w-{log.info(key:{},value:{},w.getKey(),w.getValue());});});}}13项目结构类图
