一个Sql重写优化器（三）Impl

这里的代码主要基于 一个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;
                        }
                    }
                };
            }
        }

    }
}