Select Advanced

API

Method	Supports Subsequent Chaining	Description
select(o->proxy)	✅	Users can customize implementation to return results (Return result must be a Proxy class, simply put, DTO needs to add annotation @EntityProxy)
selectColumn(o->o.column())	❌	Used for users to return a single field, can also directly use select(o->o.id()) requires eq 2.0.0^
select(Class<TR>)	❌	Automatically maps the relationship between table and DTO (the relationship is that the DTO mapped columnName is consistent with the entity's columnName), for example, if both properties are name, but the entity adds @Column(value="my_name"), then if the DTO's name property doesn't add the corresponding annotation, automatic mapping will not be possible and manual as is needed for querying
select(Class<TR>,expression)	❌	Users can automatically or manually map any DTO object's class, for example select(DTO.class,o->Select.of(o.FETCHER.allFields(),o.name().as("myName")))
selectAutoInclude	❌	Supports users to return database object relationship associated data of any column, such as nested structure: {name:.. , age:... ,list:[{...}, {...}]}
selectAutoInclude expression	❌	Supports users to return database object relationship associated data of any column, and can also additionally customize join to return other data, such as nested structure: {name:.. , age:... ,address:...,list:[{...}, {...}]}, where address is additional assignment of user address information

Preview

1. Return VOProxy

List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .leftJoin(DocUser.class, (bank_card, user) -> bank_card.uid().eq(user.id()))
        .leftJoin(DocBank.class, (bank_card, user, bank) -> bank_card.bankId().eq(bank.id()))
        .where((bank_card, user, bank) -> {
            user.name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select((bank_card, user, bank) -> {
            BankCardVOProxy r = new BankCardVOProxy();
            r.selectAll(bank_card);//Equivalent to querying all bankCard fields
            r.userName().set(user.name());
            r.bankName().set(bank.name());
            return r;
        }).toList();

2. Return Partial Columns

List<DocBankCard> list = easyEntityQuery.queryable(DocBankCard.class)
        .where(bank_card -> bank_card.type().eq("储蓄卡"))
        .select(bank_card -> bank_card.FETCHER.id().code())
        .toList();

3. Implicit Mapping

List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .leftJoin(DocUser.class, (bank_card, user) -> bank_card.uid().eq(user.id()))
        .leftJoin(DocBank.class, (bank_card, user, bank) -> bank_card.bankId().eq(bank.id()))
        .where((bank_card, user, bank) -> {
            user.name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select((bank_card, user, bank) -> new ClassProxy<>(BankCardVO.class)
            //Automatically map all properties of bank_card, equals select t.* but based on the result
            .selectAll(bank_card)
            //Can use string: "userName" or lombok's @FieldNameConstant annotation
            .field("userName").set(user.name())
            .field("bankName").set(bank.name())
        ).toList();

4. Fully Automatic Mapping

List<SysBankDTO> list = easyEntityQuery.queryable(SysBank.class)
        .selectAutoInclude(SysBankDTO.class)
        .toList();

select(o->proxy)

This API returns a custom proxy object. For example, we create the following return result:

CardVO

@Data
@FieldNameConstants
@EntityProxy
@SuppressWarnings("EasyQueryFieldMissMatch")
public class BankCardVO {

    private String id;
    private String uid;
    /**
     * Bank card number
     */
    private String code;
    /**
     * Bank card type: debit card, savings card
     */
    private String type;

    private String userName;
    private String bankName;
}

UserVO

@Data
@EntityProxy
@SuppressWarnings("EasyQueryFieldMissMatch")
@FieldNameConstants
public class DocUserVO {


    private String id;
    private String name;
    private String phone;
    private Integer age;
    private Long cardCount;

}

Because we added the @EntityProxy annotation, the APT class BankCardVOProxy will be generated by default. Our userName and bankName are not data from the BankCard table,
so we need to assign them additionally.

Get Partial Columns

List<DocBankCard> list = easyEntityQuery.queryable(DocBankCard.class)
        .where(bank_card -> bank_card.type().eq("储蓄卡"))
        .select(bank_card -> bank_card.FETCHER.id().code())
        .toList();

Custom VO Query

//Manual join
List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .leftJoin(DocUser.class, (bank_card, user) -> bank_card.uid().eq(user.id()))
        .leftJoin(DocBank.class, (bank_card, user, bank) -> bank_card.bankId().eq(bank.id()))
        .where((bank_card, user, bank) -> {
            user.name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select((bank_card, user, bank) -> {
            BankCardVOProxy r = new BankCardVOProxy();
            r.selectAll(bank_card);//Equivalent to querying all bankCard fields
            r.userName().set(user.name());
            r.bankName().set(bank.name());
            return r;
        }).toList();

==> Preparing: SELECT t.id,t.uid,t.code,t.type,t1.name AS user_name,t2.name AS bank_name FROM doc_bank_card t LEFT JOIN doc_user t1 ON t.uid = t1.id LEFT JOIN doc_bank t2 ON t.bank_id = t2.id WHERE t1.name LIKE ? AND t.type = ?
==> Parameters: %小明%(String),储蓄卡(String)

//The above and below produce the same result
//Implicit join
List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .where(bank_card -> {
            bank_card.user().name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select(bank_card -> {
            BankCardVOProxy r = new BankCardVOProxy();
            r.selectAll(bank_card);//Equivalent to querying all bankCard fields
            r.userName().set(bank_card.user().name());
            r.bankName().set(bank_card.bank().name());
            return r;
        }).toList();
==> Preparing: SELECT t.id,t.uid,t.code,t.type,t1.name AS user_name,t2.name AS bank_name FROM doc_bank_card t LEFT JOIN doc_user t1 ON t1.id = t.uid LEFT JOIN doc_bank t2 ON t2.id = t.bank_id WHERE t1.name LIKE ? AND t.type = ?
==> Parameters: %小明%(String),储蓄卡(String)



//Implicit select subquery
List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(user -> {
            DocUserVOProxy r = new DocUserVOProxy();
            r.selectAll(user);//Equivalent to querying all bankCard fields
            r.cardCount().set(user.bankCards().count());
            return r;
                }).toList();

==> Preparing: SELECT t.id,t.name,t.phone,t.age,(SELECT COUNT(*) FROM doc_bank_card t1 WHERE t1.uid = t.id) AS card_count FROM doc_user t WHERE t.name LIKE ?
==> Parameters: %小明%(String)

//Manual select subquery
List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(user -> {
            DocUserVOProxy r = new DocUserVOProxy();
            r.selectAll(user);//Equivalent to querying all bankCard fields
            Query<Long> longQuery = easyEntityQuery.queryable(DocBankCard.class)
                    .where(bank_card -> {
                        bank_card.uid().eq(user.id());
                    }).selectCount();
            r.cardCount().setSubQuery(longQuery);
            return r;
        }).toList();

==> Preparing: SELECT t.id,t.name,t.phone,t.age,(SELECT COUNT(*) FROM doc_bank_card t1 WHERE t1.uid = t.id) AS card_count FROM doc_user t WHERE t.name LIKE ?
==> Parameters: %小明%(String)

//Manual SQL fragment
List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(user -> {
            DocUserVOProxy r = new DocUserVOProxy();
            r.selectAll(user);//Equivalent to querying all bankCard fields
            r.cardCount().setSQL("IFNULL({0},1)",c -> c.expression(user.age()));
            return r;
        }).toList();

==> Preparing: SELECT t.id,t.name,t.phone,t.age,IFNULL(t.age,1) AS card_count FROM doc_user t WHERE t.name LIKE ?
==> Parameters: %小明%(String)

Proxy Object Assignment

Method	Description
set	r.set(value) assigns constant or database function value to proxy object, types need to match
setNull	Assign to column
setSubQuery	Assign a subquery result
setSQL	Assign custom string SQL fragment
setExpression	Same as set, no need for type consistency (weak type)
selectAll	Same as t.*, automatically maps and matches columns from a table to the result object

select(Class<TR>,expression)

This API returns a custom result object. For example, we created the following return result:

CardVO

@Data
@FieldNameConstants
@SuppressWarnings("EasyQueryFieldMissMatch")
public class BankCardVO {

    private String id;
    private String uid;
    /**
     * Bank card number
     */
    private String code;
    /**
     * Bank card type: debit card, savings card
     */
    private String type;

    private String userName;
    private String bankName;
}

UserVO

@Data
@SuppressWarnings("EasyQueryFieldMissMatch")
@FieldNameConstants
public class DocUserVO {


    private String id;
    private String name;
    private String phone;
    private Integer age;
    private Long cardCount;

}

Custom VO Query

Unlike select(o->proxy), we use the class result instead of the proxy object to receive the result

List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .leftJoin(DocUser.class, (bank_card, user) -> bank_card.uid().eq(user.id()))
        .leftJoin(DocBank.class, (bank_card, user, bank) -> bank_card.bankId().eq(bank.id()))
        .where((bank_card, user, bank) -> {
            user.name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select(BankCardVO.class,(bank_card, user, bank) -> Select.of(
                //Automatically map all properties of bank_card, equals select t.* but based on the result
                bank_card.FETCHER.allFields(),
                //Add FieldNameConstants, can also use method reference BankCardVO::getUserName if the property conforms to Java bean specification
                user.name().as(BankCardVO.Fields.userName),
                bank.name().as(BankCardVO.Fields.bankName)
        )).toList();




List<BankCardVO> list = easyEntityQuery.queryable(DocBankCard.class)
        .where(bank_card -> {
            bank_card.user().name().like("小明");
            bank_card.type().eq("储蓄卡");
        })
        .select(BankCardVO.class, bank_card -> Select.of(
                bank_card.FETCHER.allFields(),
                bank_card.user().name().as(BankCardVO.Fields.userName),
                bank_card.bank().name().as(BankCardVO.Fields.bankName)
        )).toList();




List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(DocUserVO.class,user -> Select.of(
                user.FETCHER.allFields(),
                user.bankCards().count().as(DocUserVO.Fields.cardCount)
        )).toList();



List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(DocUserVO.class,user -> Select.of(
                user.FETCHER.allFields(),
                user.expression().subQuery(()->{
                    return easyEntityQuery.queryable(DocBankCard.class)
                            .where(bank_card -> {
                                bank_card.uid().eq(user.id());
                            }).selectCount();
                }).as(DocUserVO.Fields.cardCount)
        )).toList();



List<DocUserVO> list = easyEntityQuery.queryable(DocUser.class)
        .where(user -> {
            user.name().like("小明");
        })
        .select(DocUserVO.class,user -> Select.of(
                user.FETCHER.allFields(),
                user.expression().sqlSegment("IFNULL({0},1)", c -> c.expression(user.age()),Integer.class).as(DocUserVO.Fields.cardCount)
        )).toList();

selectAutoInclude(Class<TR>,expression)

Because selectAutoInclude has too much content, it's recommended you jump to the link Structured Result Query Return