文章目录

Simple implJoin reducedSelect id firstselect separatesubQuery 这里的代码主要基于 一个Sql重写优化器（一）原理里介绍的原理，提供了对 一个Sql重写优化器（二）DSL里定义接口的多个实现，一个比一个强大(当然，后面一个基于前面一个）。

Simple impl

这种情况仅仅将DSL转换为一个Sql然后执行，使用了NIO的特性，但是对于Sql本身未做优化。

import com.google.common.base.*; import com.google.common.collect.*; import io.r2dbc.spi.Row; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.springframework.core.annotation.AnnotationUtils; import org.springframework.data.r2dbc.core.DatabaseClient; import org.springframework.data.relational.core.mapping.Embedded; import org.springframework.data.util.ReflectionUtils; import reactor.core.publisher.Flux; import java.lang.reflect.Field; import java.util.*; import java.util.stream.Collectors; @Slf4j class SqlBuilderImpl implements SqlBuilder{ private final StringBuilder sql; private final StringBuilder from; protected final StringBuilder where; protected final Map<String,String> with; public SqlBuilderImpl(){ this(new StringBuilder(),new StringBuilder(" from "),new StringBuilder(),Maps.newHashMap()); } private SqlBuilderImpl(StringBuilder sql,StringBuilder from,StringBuilder where,Map<String,String> with){ this.sql=sql; this.from=from; this.where=where; this.with=Maps.newHashMap(with); } @Override public SqlBuilder with(String sql, String alias) { with.put(alias,'('+sql+')'); return this; } protected class FromStatementImpl implements FromStatement{ @Override public JoinStatement leftJoin(String tableName, String alias) { final var joinType="left join"; return getJoinStatement(tableName, alias, joinType); } private JoinStatement getJoinStatement(String tableName, String alias, String joinType) { FromStatement fromStatement=this; from.append(" ").append(joinType).append(" ").append(tableName); if(null!= alias &&!alias.equals(tableName)){ from.append(" as ").append(alias); } return new JoinStatement() { @Override public FromStatement onForeignKey(String leftTableName, String leftTableColumn) { from.append(" on ").append(alias).append(".id=") .append(leftTableName).append(".").append(leftTableColumn).append("\n"); return fromStatement; } @Override public FromStatement onEquals(ForeignKey columnA, ForeignKey columnB) { from.append(" on ").append(columnA.getRelation().getName()) .append('.').append(columnA.getColumnName()).append('=') .append(columnB.getRelation().getName()).append('.') .append(columnB.getColumnName()); return fromStatement; } @Override public FromStatement onAnyForeignKey(ForeignKey... bossColumn) { List<Column> nullColumn= Lists.newArrayList(); for(Column column:bossColumn){ if(nullColumn.isEmpty()){ from.append(" on (").append(alias).append(".id=").append(column.fullName()).append("\n"); }else{ from.append(" or ").append(alias).append(".id=").append(column.fullName()).append("\n"); } nullColumn.add(column); } from.append(")"); return fromStatement; } @Override public Any<FromStatement> onAnyForeignKey(String leftTableName, String leftTableColumn) { from.append(" on ").append(alias).append(".id=") .append(leftTableName).append(".").append(leftTableColumn).append("\n"); return (tableName1, columnName) -> { from.append(" or (").append(leftTableName).append('.').append(leftTableColumn).append(" is null and ") .append(alias).append(".id=").append(tableName1).append(".").append(columnName).append(")"); return fromStatement; }; } }; } @Override public SelectStatement where(Object query,String... raw) { SqlBuilder.where(where,query); for (String sql:raw){ where.append(" and ").append(sql).append("\n"); } return getSelectStatement(); } class SelectStatementImpl implements SelectStatement{ <T> Map<String,Field> buildSelect(StringBuilder sql, Class<T> clazz){ Map<String, Field> columnToFiled= Maps.newHashMap(); var select= com.onlyedu.utils.reflect.ReflectionUtils.getFields(clazz) .filter(f->f.getAnnotation(Raw.class)==null).map(field -> { var columnName= CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE,field.getName()); columnToFiled.put(columnName,field); org.springframework.data.relational.core.mapping.Column column=field.getAnnotation(org.springframework.data.relational.core.mapping.Column.class); if(null!=column){ var rawName=column.value(); if(!StringUtils.contains(rawName,'.')){ log.warn("no table name in "+field); } return rawName+" as "+columnName; }else{ //todo 此处可能触发Spring的bug Embedded.Nullable embedded= AnnotationUtils.findAnnotation(field,Embedded.Nullable.class); Preconditions.checkNotNull(embedded,field.toString()); //此处应使用@Column，为了检查下有没标记错误 Preconditions.checkArgument(field.getType()!=String.class); return Joiner.on(',').join(com.onlyedu.utils.reflect.ReflectionUtils.getFields(field.getType()) .map(field1 -> { var columnName1 = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field1.getName()); return embedded.prefix() + '.' + columnName1 + " as " + columnName + '_' + columnName1; }).collect(Collectors.toList())); } }); sql.append(Joiner.on(",\n").join(select.collect(Collectors.toList()))).append("\n"); return columnToFiled; } @Override public OrderByStatement orderByDesc(String table, String column) { return pageIn -> new LimitStatement() { @Override public <T> DataStatement<T> select(Class<T> viewObject) { final int offset=(pageIn.getPageNumber()-1)*pageIn.getPageSize(); final int limit=pageIn.getPageSize(); StringBuilder sql=with().append("select "); var columnToFiled=buildSelect(sql,viewObject); sql.append(fromStatement(viewObject)).append(where) .append(" order by ").append(table).append('.').append(column).append(" desc ") .append(" offset ").append(offset).append(" limit ").append(limit); return new DataStatement<T>() { @Override public String toString() { return sql.toString(); } @Override public Flux<T> execute(DatabaseClient client) { return SqlUtils.execute(client, toString(), (row, rowMetadata) -> { final T out = com.onlyedu.utils.reflect.ReflectionUtils.newInstance(viewObject); doSet(columnToFiled, row, out); return out; }).all(); } }; } }; } @Override public <T> SumStatement<T> agg(Class<T> clazz) { StringBuilder sql=with().append("select count(1) as count, "); var columnToFiled=buildSelect(sql,clazz); sql.append(fromStatement(clazz)).append(where); return (client, sum) -> SqlUtils.execute(client,sql.toString(), (row, rowMetadata) -> { doSet(columnToFiled, row, sum); return row.get("count",Integer.class); }).first(); } StringBuilder with(){ if(with.isEmpty()){ return new StringBuilder(); }else{ return new StringBuilder("with ").append(Joiner.on(",\n").withKeyValueSeparator(" as ").join(with)).append("\n"); } } @Override public CountStatement count() { StringBuilder sql=with().append("select count(1) as count ").append(fromStatement(null)).append(where); return client -> SqlUtils.execute(client, sql.toString(), (row, rowMetadata) -> row.get("count",Integer.class)).first(); } @Override public <E extends Enum> EnumStatement<SelectStatement,E> and(EnumType<E,?> column) { return new EnumStatement<SelectStatement, E>() { @Override public SelectStatement notEquals(E e) { where.append(" and ").append(column.fullName()).append("!='").append(e).append("'"); return getSelectStatement(); } @Override public SelectStatement in(E... e) { SqlUtils.andIn(where,column.fullName(),Lists.newArrayList(e)); return getSelectStatement(); } }; } @Override public BoolStatement<SelectStatement> and(Bool bool) { return b -> { where.append(" and ").append(bool.fullName()).append("=").append(b); return getSelectStatement(); }; } @Override public SelectStatement andRaw(List<String> raw) { for (String sql:raw){ where.append(" and ").append(sql).append('\n'); } return getSelectStatement(); } } protected SelectStatement getSelectStatement() { return new SelectStatementImpl() ; } @Override public SelectStatement where(Object where1, Object where2) { SqlBuilder.where(where,where1,where2); return getSelectStatement(); } @Override public <T> JoinStatement innerJoin(Class<T> table) { return getJoinStatement(table.getSimpleName(),table.getSimpleName(),"inner join"); } @Override public JoinStatement leftJoin(Alias alias) { return getJoinStatement(alias.getRelation().getName(),alias.getAlias(),"left join"); } @Override public JoinStatement leftJoin(With with) { with(with); return leftJoin(with.alias(), with.alias()); } @Override public <T extends Table<T>> FromStatement andEquals(ForeignKey<T, ?> columnA, ForeignKey<T, ?> columnB) { from.append(" and ").append(columnA.fullName()).append("=").append(columnB.fullName()); return this; } @Override public FromStatement andRaw(String s) { from.append(" and ").append(s).append('\n'); return this; } @Override public JoinStatement leftJoin(Class<? extends Table> table) { return getJoinStatement(table.getSimpleName(),table.getSimpleName(),"left join"); } @Override public BoolStatement<FromStatement> and(Bool column) { FromStatement fromStatement=this; return b -> { from.append(" and ").append(column.fullName()).append('=').append(b); return fromStatement; }; } @Override public <T extends Table<T>> JoinStatement<T> leftJoin(Table<T> br) { return getJoinStatement(br.getName(),br.getName(),"left join"); } } @Override public FromStatement from(String tableName,String alias) { from.append(tableName); if(null!=alias&&!alias.equals(tableName)) { from.append(" as ").append(alias); } return new FromStatementImpl() ; } protected String fromStatement(Class<?> voClass) { return from.toString(); } protected void doSet(Map<String, Field> columnToFiled, Row row, Object out) { columnToFiled.entrySet().parallelStream().forEach(entry->{ var field=entry.getValue(); Object value=null; if(NameVO.class.isAssignableFrom(field.getType())){ FromEnum e=field.getAnnotation(FromEnum.class); if(null==e) { String id = row.get(entry.getKey() + "_id", String.class); if (null != id) { String name = row.get(entry.getKey() + "_name", String.class); value = new BaseVO(id, name); } }else{ var id=row.get(entry.getKey(),String.class); if(null!=id){ var vo=(NameVO) java.lang.Enum.valueOf(e.value(),id); value=new BaseVO(vo.getId(),vo.getName()); } } }else { value = row.get(entry.getKey()); if(value!=null&&value.getClass()!=entry.getValue().getType()){ value = Transformers.to(entry.getValue().getType(), value.toString()); } } if(null==value){ return; } ReflectionUtils.setField(field,out,value); }); } @Override public int hashCode() { return sql.hashCode(); } };

Join reduced

改进的方案减少了join时的表，有时因为where条件中的需要，在Sql中join了一些表，但是用户又没有使用这些查询条件，于是可以去掉这些join。另外对于分页需要的count查询可以减少很多join

public class SqlBuilderImpl2 extends SqlBuilderImpl{ protected String rootTable; private final Map<String,JoinInfo> joins=Maps.newLinkedHashMap(); private final List<Object> wheres= Lists.newArrayList(); @Data private class JoinInfo{ private String alias; private Relation<?> table; private final StringBuilder sql=new StringBuilder(); private final Set<String> left=Sets.newHashSet(); private boolean mark; public void mark() { if(mark){ return; } mark=true; left.stream().map(joins::get).forEach(JoinInfo::mark); } } @Override public FromStatement from(String tableName, String alias) { throw new UnsupportedOperationException(); } @Override public SqlBuilder with(With with) { super.with(with); return this; } @Override protected String fromStatement(Class<?> voClass) { Set<String> marked=Sets.newHashSet(); var mark=wheres.stream().flatMap(w->{ return ReflectionUtils.getFields(w.getClass()) .filter(f->null!= ReflectionUtils.getField(f,w)) .flatMap(this::getTableName) .filter(Objects::nonNull); }); mark.forEach(m->mark(m,marked)); if(null!=voClass){ ReflectionUtils.getFields(voClass) .flatMap(this::getTableName) .filter(Objects::nonNull) .forEach(m->mark(m,marked)); } StringBuilder out=new StringBuilder(" from ").append(rootTable).append('\n'); joins.entrySet().stream().filter(e->marked.contains(e.getKey())||e.getValue().mark) .map(Map.Entry::getValue).map(JoinInfo::getSql) .forEach(s->out.append(s).append('\n')); return out.toString(); } private void mark(String key, Set<String> set) { if(set.contains(key)){ return; } set.add(key); var j=joins.get(key); if(null==j){ //todo return; } j.getLeft().forEach(k->mark(k,set)); } private Stream<String> getTableName(Field field) { Column c=field.getAnnotation(Column.class); if(null!=c){ return getTableName(c); } Embedded.Nullable n=field.getAnnotation(Embedded.Nullable.class); if(null!=n){ return Stream.of(getTableName(n)); } Raw raw=field.getAnnotation(Raw.class); if(null!=raw){ return null; } throw new IllegalArgumentException("can not get table name from "+field); } private String getTableName(Embedded.Nullable nullable) { return nullable.prefix(); } private static Pattern pattern=Pattern.compile("\\b(\\w+)\\."); private static Stream<String> getTableName(Column column) { var m=pattern.matcher(column.value()); List<String> out=Lists.newArrayList(); while (m.find()){ out.add(m.group(1)); } return out.stream(); } protected class FromStatementImpl2 extends FromStatementImpl{ @Override public <T extends Relation<?>> JoinStatement<T> leftJoin(Class<T> tableName, String alias) { throw new UnsupportedOperationException(); } @Override public JoinStatement leftJoin(String tableName, String alias) { throw new UnsupportedOperationException(); } class SelectStatementImpl2 extends SelectStatementImpl{ @Override public <E extends Enum> EnumStatement<SelectStatement, E> and(EnumType<E, ?> column) { return new EnumStatement<SelectStatement, E>() { @Override public SelectStatement notEquals(E e) { final var leftTable=column.getRelation().getName(); if(!rootTable.equals(leftTable)) { joins.get(leftTable).mark(); } SelectStatementImpl2.super.and(column).notEquals(e); return SelectStatementImpl2.this; } @Override public SelectStatement in(E... e) { final var leftTable=column.getRelation().getName(); if(!rootTable.equals(leftTable)) { joins.get(leftTable).mark(); } SelectStatementImpl2.super.and(column).in(e); return SelectStatementImpl2.this; } }; } @Override public BoolStatement<SelectStatement> and(Bool bool) { return new BoolStatement<SelectStatement>() { @Override public SelectStatement isEquals(boolean b) { var leftTable=bool.getRelation().getName(); if(!rootTable.equals(leftTable)){ joins.get(leftTable).mark(); } SelectStatementImpl2.super.and(bool).isEquals(b); return SelectStatementImpl2.this; } }; } @Override public SelectStatement andRaw(List<String> raw) { return super.andRaw(raw); } } @Override protected SelectStatement getSelectStatement() { return new SelectStatementImpl2(); } @Override public SelectStatement where(Object query,String... raw) { wheres.add(query); return super.where(query,raw); } @Override public SelectStatement where(Object where1, Object where2) { wheres.add(where1); wheres.add(where2); return super.where(where1,where2); } @Override public <T> JoinStatement innerJoin(Class<T> table) { throw new UnsupportedOperationException(); } private JoinInfo currentJoin; @Override public JoinStatement leftJoin(Alias alias) { currentJoin=new JoinInfo(); joins.put(alias.getAlias(),currentJoin); currentJoin.setAlias(alias.getAlias()); currentJoin.setTable(alias.getRelation()); currentJoin.getSql().append(" left join ").append(alias.getRelation().getName()).append(" as ").append(alias.getAlias()); return joinStatement(this); } protected JoinStatement joinStatement(FromStatement fromStatement) { return new JoinStatement() { @Override public FromStatement onForeignKey(String leftTableName, String leftTableColumn) { throw new UnsupportedOperationException(); } @Override public Any<FromStatement> onAnyForeignKey(String tableName, String columnName) { throw new UnsupportedOperationException(); } @Override public FromStatement onEquals(ForeignKey columnA, ForeignKey columnB) { return equals("on",columnA,columnB,fromStatement); } public FromStatement equals(String op,ForeignKey columnA, ForeignKey columnB,FromStatement out) { currentJoin.getLeft().add(columnA.getRelation().getName()); currentJoin.getLeft().add(columnB.getRelation().getName()); currentJoin.getSql().append(" ").append(op).append(" ").append(columnA.fullName()).append('=').append(columnB.fullName()); return out; } @Override public FromStatement onAnyForeignKey(ForeignKey[] bossColumn) { currentJoin.getSql().append(" on ("); boolean or=false; for(ForeignKey fk:bossColumn){ currentJoin.getLeft().add(fk.getRelation().getName()); if(or){ currentJoin.getSql().append(" or "); } currentJoin.getSql().append(fk.fullName()).append('=').append(currentJoin.getAlias()).append(".id"); or=true; } currentJoin.getSql().append(")"); return fromStatement; } @Override public FromStatement onForeignKey(ForeignKey column) { final var leftTable=column.getRelation().getName(); currentJoin.getLeft().add(leftTable); currentJoin.getSql().append(" on ").append(column.fullName()).append('=').append(currentJoin.getAlias()).append(".id"); return fromStatement; } @Override public FromStatement onPrimaryKey(Table table) { currentJoin.getLeft().add(table.getName()); currentJoin.getSql().append(" on ").append(table.getName()).append(".id").append('=').append(currentJoin.getAlias()).append(".id"); return fromStatement; } }; } @Override public JoinStatement leftJoin(With with) { with(with); currentJoin=new JoinInfo(); joins.put(with.alias(),currentJoin); currentJoin.setAlias(with.alias()); currentJoin.setTable(with); currentJoin.getSql().append(" left join ").append(with.alias()); return joinStatement(this); } public FromStatement equals(String op,ForeignKey columnA, ForeignKey columnB,FromStatement out) { currentJoin.getLeft().add(columnA.getRelation().getName()); currentJoin.getLeft().add(columnB.getRelation().getName()); currentJoin.getSql().append(" ").append(op).append(" ").append(columnA.fullName()).append('=').append(columnB.fullName()); return out; } @Override public <T extends Table<T>> FromStatement andEquals(ForeignKey<T, ?> columnA, ForeignKey<T, ?> columnB) { return equals("and",columnA,columnB,this); } @Override public FromStatement andRaw(String s) { throw new UnsupportedOperationException(); } @Override public JoinStatement leftJoin(Class<? extends Table> table) { throw new UnsupportedOperationException(); } @Override public BoolStatement<FromStatement> and(Bool column) { FromStatement fromStatement=this; return new BoolStatement<FromStatement>() { @Override public FromStatement isEquals(boolean b) { currentJoin.getSql().append(" and ").append(column.fullName()).append('=').append(b); return fromStatement; } }; } @Override public <T extends Table<T>> JoinStatement<T> leftJoin(Table<T> br) { currentJoin=new JoinInfo(); joins.put(br.getName(),currentJoin); currentJoin.setTable(br); currentJoin.setAlias(br.getName()); currentJoin.getSql().append("left join ").append(currentJoin.getTable().getName()); return joinStatement(this); } } @Override public FromStatementImpl2 from(Class<?> table) { rootTable =table.getSimpleName(); return new FromStatementImpl2(); } }

Select id first

为了去除掉select语句中对from表的束缚，先用with获取id，然后通过id来查出详细数据，在获取id时精简了from的表，并且使用limit从而触发数据库的top方法，但是失败了。

public class SqlBuilderImpl3 implements SqlBuilder{ protected final SqlBuilderImpl2 impl=new SqlBuilderImpl2(); @Override public FromStatement from(String tableName, String alias) { return impl.from(tableName, alias); } @Override public SqlBuilder with(With with) { return impl.with(with); } public String fromStatement(Class<?> voClass) { return impl.fromStatement(voClass); } @Override public SqlBuilder with(String sql, String alias) { return impl.with(sql, alias); } @Override public int hashCode() { return impl.hashCode(); } public <T> RowsFetchSpec<T> execute0(DatabaseClient databaseClient, String sql, BiFunction<Row, RowMetadata, T> mapper) { return SqlUtils.execute(databaseClient, sql, mapper); } @Override public FromStatement from(String tableName) { return impl.from(tableName); } @Override public FromStatement from(Class<?> table) { return new FromStatementImpl3(impl.from(table)); } class FromStatementImpl3 implements FromStatement{ protected final SqlBuilderImpl2.FromStatementImpl2 impl; FromStatementImpl3(SqlBuilderImpl2.FromStatementImpl2 impl) { this.impl = impl; } @Override public <T extends Relation<?>> JoinStatement<T> leftJoin(Class<T> tableName, String alias) { impl.leftJoin(tableName, alias); return impl.joinStatement(this); } @Override public JoinStatement leftJoin(String tableName, String alias) { impl.leftJoin(tableName, alias); return impl.joinStatement(this); } @Override public SelectStatement where(Object query, String... raw) { return new SelectStatementImpl3((SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) impl.where(query, raw)); } @Override public SelectStatement where(Object where1, Object where2) { return new SelectStatementImpl3((SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) impl.where(where1, where2)); } @Override public <T> JoinStatement innerJoin(Class<T> table) { return impl.innerJoin(table); } @Override public JoinStatement leftJoin(With with) { impl.leftJoin(with); return impl.joinStatement(this); } public FromStatement equals(String op, ForeignKey columnA, ForeignKey columnB, FromStatement out) { impl.equals(op, columnA, columnB, out); return this; } @Override public <T extends Table<T>> FromStatement andEquals(ForeignKey<T, ?> columnA, ForeignKey<T, ?> columnB) { impl.andEquals(columnA, columnB); return this; } @Override public FromStatement andRaw(String s) { impl.andRaw(s); return this; } @Override public JoinStatement leftJoin(Class<? extends Table> table) { impl.leftJoin(table); return impl.joinStatement(this); } @Override public BoolStatement<FromStatement> and(Bool column) { return new BoolStatement<FromStatement>() { @Override public FromStatement isEquals(boolean b) { impl.and(column).isEquals(b); return SqlBuilderImpl3.FromStatementImpl3.this; } }; } @Override public <T extends Table<T>> JoinStatement<T> leftJoin(Table<T> br) { impl.leftJoin(br); return impl.joinStatement(this); } @Override @Deprecated public JoinStatement leftJoin(String tableName) { return impl.leftJoin(tableName); } @Override public <T> JoinStatement leftJoin(Alias<T> alias) { impl.leftJoin(alias); return impl.joinStatement(this); } class SelectStatementImpl3 implements SelectStatement{ protected final SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2 impl; SelectStatementImpl3(SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2 impl) { this.impl = impl; } @Override @Deprecated public OrderByStatement orderByDesc(String table, String column) { return impl.orderByDesc(table, column); } @Override public <T> SumStatement<T> agg(Class<T> clazz) { return impl.agg(clazz); } @Override public CountStatement count() { return impl.count(); } @Override public OrderByStatement orderByDesc(Column orderByColumn) { return pageIn -> new LimitStatement() { @Override public <T> DataStatement<T> select(Class<T> viewObject) { final int offset=(pageIn.getPageNumber()-1)*pageIn.getPageSize(); final int limit=pageIn.getPageSize(); final String idColumn=SqlBuilderImpl3.this.impl.rootTable+".id"; with(new StringBuilder().append("select ").append(idColumn).append(fromStatement(null)).append(SqlBuilderImpl3.this.where()) .append(" order by ").append(orderByColumn.fullName()).append(" desc ") .append(" offset ").append(offset).append(" limit ").append(limit).toString(),"id_view"); StringBuilder sql=impl.with().append("select "); var columnToFiled=impl.buildSelect(sql,viewObject); sql.append(fromStatement(viewObject)).append(" where ").append(idColumn).append(" in (select id from id_view)") .append(" order by ").append(orderByColumn.fullName()).append(" desc ");; return new DataStatement<T>() { @Override public String toString() { return sql.toString(); } @Override public Flux<T> execute(DatabaseClient client) { return execute0(client, toString(), (row, rowMetadata) -> { final T out = com.onlyedu.utils.reflect.ReflectionUtils.newInstance(viewObject); SqlBuilderImpl3.this.impl.doSet(columnToFiled, row, out); return out; }).all(); } }; } }; } @Override public <E extends Enum> EnumStatement<SelectStatement, E> and(EnumType<E, ?> column) { var and=impl.and(column); return new EnumStatement<SelectStatement, E>() { @Override public SelectStatement notEquals(E e) { and.notEquals(e); return SqlBuilderImpl3.FromStatementImpl3.SelectStatementImpl3.this; } @Override public SelectStatement in(E... e) { and.in(e); return SqlBuilderImpl3.FromStatementImpl3.SelectStatementImpl3.this; } }; } @Override public BoolStatement<SelectStatement> and(Bool bool) { var and= impl.and(bool); return new BoolStatement<SelectStatement>() { @Override public SelectStatement isEquals(boolean b) { and.isEquals(b); return SqlBuilderImpl3.FromStatementImpl3.SelectStatementImpl3.this; } }; } @Override public SelectStatement andRaw(List<String> raw) { impl.andRaw(raw); return SqlBuilderImpl3.FromStatementImpl3.SelectStatementImpl3.this; } @Override public SelectStatement andRaw(String... raw) { impl.andRaw(raw); return SqlBuilderImpl3.FromStatementImpl3.SelectStatementImpl3.this; } } } protected StringBuilder where(){ return impl.where; } }

select separate

由于上面的失败，只能将select vo通过java分为两次调用，先执行原先with的部分，然后再通过id获取vo数据。可以看到是通过执行了两次execute0来查询数据库的

@Slf4j public class SqlBuilderImpl4 extends SqlBuilderImpl3{ class FromStatement4 extends FromStatementImpl3{ FromStatement4(SqlBuilderImpl2.FromStatementImpl2 impl) { super(impl); } class SelectStatementImpl4 extends SelectStatementImpl3 { SelectStatementImpl4(SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2 impl) { super(impl); } @Override public OrderByStatement orderByDesc(Column column) { return pageIn -> new LimitStatement() { @Override public <T> DataStatement<T> select(Class<T> voClass) { Field field = null; try { field = ReflectionUtils.getFields(voClass, "id"); } catch (Exception e) { log.warn("can not get id field from " + voClass); } if (null == field) { return impl.orderByDesc(column).page(pageIn).select(voClass); } var idField = field; final int offset = (pageIn.getPageNumber() - 1) * pageIn.getPageSize(); final int limit = pageIn.getPageSize(); final String idColumn = SqlBuilderImpl4.this.impl.rootTable + ".id"; StringBuilder idSql = impl.with().append("select ").append(idColumn); idSql.append(fromStatement(null)).append(SqlBuilderImpl4.this.where()) .append(" order by ").append(column.fullName()).append(" desc ") .append(" offset ").append(offset).append(" limit ").append(limit); return new DataStatement<T>() { @Override public String toString() { return idSql.toString(); } @Override public Flux<T> execute(DatabaseClient client) { Map<String, T> result = Maps.newHashMap(); return execute0(client, toString(), (row, rowMetadata) -> { var id = row.get("id", String.class); final T out = com.onlyedu.utils.reflect.ReflectionUtils.newInstance(voClass); ReflectionUtils.setField(idField, out, id); result.put(id, out); return out; }).all().collectList().delayUntil(ids -> { StringBuilder voSql = impl.with().append("select "); var columnToFiled = impl.buildSelect(voSql, voClass); voSql.append(fromStatement(voClass)).append(" where ").append(idColumn).append(" in ('") .append(Joiner.on("','").join(ids.stream().map(i->ReflectionUtils.getField(idField,i)).collect(Collectors.toSet()))) .append("')") .append(" order by ").append(column.fullName()).append(" desc "); return execute0(client, voSql.toString(), (row, rowMetadata) -> { var id = row.get("id", String.class); var out = result.get(id); SqlBuilderImpl4.this.impl.doSet(columnToFiled, row, out); return out; }).all().collectList(); }).flatMapMany(Flux::fromIterable); } }; } }; } } @Override public SelectStatement where(Object query, String... raw) { return new SelectStatementImpl4((SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) super.impl.where(query, raw)); } @Override public SelectStatement where(Object where1, Object where2) { return new SelectStatementImpl4((SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) impl.where(where1, where2)); } } @Override public FromStatement from(Class<?> table) { return new FromStatement4(super.impl.from(table)); } }

subQuery

还有一些一对多关系的补充信息也需要查询出来，上面的方案是在获取vo后（也就是DataStatement.execute之后）再查一次数据库，然后把附加信息补充完整，下面的方案是把这个附加信息的查询提前到获取id之后，也就是能够和vo同时查询，从而提升性能。但是对于附加查询如何执行需要进行不从说明，这就需要ComplexQuery登场了，这就需要给vo的相应field上加上该注解，当然也就需要一个类来实现接口了。

@Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ComplexQuery { interface Query<T>{ Flux<Consumer<T>> flux(DatabaseClient client, Collection<String> ids); Mono<Consumer<List<T>>> mono(DatabaseClient client, Collection<String>ids); } Class<? extends Query<?>> value(); } @Slf4j public class SqlBuilderImpl5 extends SqlBuilderImpl4{ private SqlBuilderImpl2.FromStatementImpl2 impl; @Override public final FromStatement from(Class<?> table) { log.info("---------- begin at "+System.currentTimeMillis()+" ----------"); impl=super.impl.from(table); return fromStatement(); } protected FromStatement fromStatement(){ return new FromStatement5(); } class FromStatement5 extends SqlBuilderImpl4.FromStatement4{ FromStatement5() { super(SqlBuilderImpl5.this.impl); } private SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2 impl; @Override public final SelectStatement where(Object query, String... raw) { impl=(SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) super.impl.where(query, raw); return selectStatement(); } @Override public final SelectStatement where(Object where1, Object where2) { impl=(SqlBuilderImpl2.FromStatementImpl2.SelectStatementImpl2) super.impl.where(where1, where2); return new SelectStatementImpl5(); } protected SelectStatement selectStatement(){ return new SelectStatementImpl5(); } class SelectStatementImpl5 extends SelectStatementImpl4{ SelectStatementImpl5() { super(FromStatement5.this.impl); } class DataStatementImpl5<T> implements DataStatement<T>{ private final Class<T> voClass; private final Column<?> column; private final PageIn pageIn; private String getId(T t){ return ReflectionUtils.getField(idField(),t).toString(); } DataStatementImpl5(Class<T> voClass, Column<?> column, PageIn pageIn) { this.voClass = voClass; this.column = column; this.pageIn = pageIn; } private Field idField(){ Field field = null; try { field = ReflectionUtils.getFields(voClass, idFieldName()); } catch (Exception e) { log.warn("can not get id field from " + voClass); } return field; } @Override public Flux<T> execute(DatabaseClient client) { var sql=idSql(); return getId(client,sql).collectList().delayUntil(ids -> { if(ids.isEmpty()){ log.warn("can not find anything:"+sql); return Flux.empty(); } var data= getVo(client,ids); var id=ids.stream().map(this::getId).collect(Collectors.toList()); var consumers=ReflectionUtils.getFields(voClass) .map(f->f.getAnnotation(ComplexQuery.class)) .filter(Objects::nonNull) .map(ComplexQuery::value) .map(ReflectionUtils::newInstance) .map(s->s.flux(client,id)) .collect(Collectors.toList()); for(var c:consumers){ AtomicBoolean printed=new AtomicBoolean(false); data=data.zipWith(c, (BiFunction<T, Consumer<?>, T>) (t, consumer) -> { if(!printed.getAndSet(true)){ log.info("--- "+consumer+" at "+System.currentTimeMillis()+" ---"); } ((Consumer<T>)consumer).accept(t); return t; }); }; return data; }).flatMapMany(Flux::fromIterable).doOnComplete(() -> log.info("--- complete at "+System.currentTimeMillis()+" ---")); } private Flux<T> getId(DatabaseClient client,String sql) { AtomicBoolean printed=new AtomicBoolean(false); return execute0(client, sql, (row, rowMetadata) -> { if(!printed.getAndSet(true)){ log.info("--- fetch id at "+System.currentTimeMillis()+" ---"); } var id = row.get(idColumnName(), String.class); final T out = com.onlyedu.utils.reflect.ReflectionUtils.newInstance(voClass); ReflectionUtils.setField(idField(), out, id); return out; }).all(); } protected StringBuilder with(){ return impl.with(); } private Map<String,Field> columnToField; protected void doSet(Row row, T out){ SqlBuilderImpl5.super.impl.doSet(columnToField, row, out); } protected void buildSelect(StringBuilder sql){ columnToField= impl.buildSelect(sql,voClass); } protected StringBuilder where(){ return SqlBuilderImpl5.super.where(); } private String idSql() { final int offset = (pageIn.getPageNumber() - 1) * pageIn.getPageSize(); final int limit = pageIn.getPageSize(); StringBuilder idSql = with().append("select ").append(idColumn()); idSql.append(fromStatement(null)).append(where()) .append(" order by ").append(column.fullName()).append(" desc ") .append(" offset ").append(offset).append(" limit ").append(limit); log.info("--- generate idSql at "+System.currentTimeMillis()+" ---"); return idSql.toString(); } private Flux<T> getVo(DatabaseClient client, Collection<T> ids){ StringBuilder voSql = voSql(ids); AtomicBoolean printed=new AtomicBoolean(false); return execute0(client, voSql.toString(), (row, rowMetadata) -> { if(!printed.getAndSet(true)){ log.info("--- fetch vo at "+System.currentTimeMillis()+" ---"); } var id = row.get(idColumnName(), String.class); var out=ids.stream().filter(t->this.getId(t).equals(id)).findAny().get(); // var out = result.get(id); doSet(row,out); return out; }).all(); } private StringBuilder voSql(Collection<T> ids) { StringBuilder voSql = with().append("select "); buildSelect(voSql); voSql.append(fromStatement(voClass)).append(" where ").append(idColumn()).append(" in ('") .append(Joiner.on("','").join(ids.stream().map(this::getId).collect(Collectors.toSet()))) .append("')") .append(" order by ").append(column.fullName()).append(" desc "); log.info("--- generate voSql at "+System.currentTimeMillis()+" ---"); return voSql; } private String idColumnName(){ return CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE,idFieldName()); } protected String idFieldName(){ return "id"; } private String idColumn() { return SqlBuilderImpl5.super.impl.rootTable + "."+idColumnName(); } } @Override public OrderByStatement orderByDesc(Column column) { return pageIn -> new LimitStatement() { @Override public <T> DataStatement<T> select(Class<T> voClass) { DataStatementImpl5<T> out=new DataStatementImpl5<>(voClass,column,pageIn); if(out.idField()==null){ return impl.orderByDesc(column).page(pageIn).select(voClass); }else{ return out; } } }; } } } }