Structured Object Extra Operations

xuejmnet4/19/25About 5 min

This chapter mainly describes how selectAutoInclude filters and controls structured objects to return extra fields and related information.

It's recommended to upgrade eq to 2.8.10+. Using this feature allows you to control any level expression of selectAutoInclude.

We still use the bank, bank card, and user case:

Relationship Diagram

SysBank


@Table("t_bank")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("bank")
public class SysBank implements ProxyEntityAvailable<SysBank, SysBankProxy> {
    @Column(primaryKey = true)
    private String id;
    /**
     * Bank name
     */
    private String name;
    /**
     * Establishment time
     */
    private LocalDateTime createTime;

    /**
     * Bank cards owned
     */
    @Navigate(value = RelationTypeEnum.OneToMany,
            selfProperty = {"id"},
            targetProperty = {"bankId"})
    private List<SysBankCard> bankCards;
}

SysBankCard


@Table("t_bank_card")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("bank_card")
public class SysBankCard implements ProxyEntityAvailable<SysBankCard , SysBankCardProxy> {
    @Column(primaryKey = true)
    private String id;
    private String uid;
    /**
     * Bank card number
     */
    private String code;
    /**
     * Bank card type: debit card, savings card
     */
    private String type;
    /**
     * Belongs to bank
     */
    private String bankId;
    /**
     * User account opening time
     */
    private LocalDateTime openTime;

    /**
     * Belongs to bank
     */
    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {"bankId"}, targetProperty = {"id"}, required=true)
    @ForeignKey//Can be omitted
    private SysBank bank;

    /**
     * Belongs to user
     */
    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {"uid"}, targetProperty = {"id"})
    private SysUser user;
}

SysUser


@Table("t_sys_user")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("user")
public class SysUser implements ProxyEntityAvailable<SysUser , SysUserProxy> {
    @Column(primaryKey = true)
    private String id;
    private String name;
    private String phone;
    private Integer age;
    private LocalDateTime createTime;

    /**
     * Number of bank cards owned by user
     */
    @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {"id"}, targetProperty = {"uid"})
    private List<SysBankCard> bankCards;

    /**
     * Books owned by user
     */
    @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {"id"}, targetProperty = {"uid"})
    private List<SysUserBook> userBooks;
}

SysUserBook


@Table("t_sys_user_book")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("user_book")
public class SysUserBook implements ProxyEntityAvailable<SysUserBook , SysUserBookProxy> {
    private String id;
    private String name;
    private String uid;
    private BigDecimal price;
}

Return data structure is Bank: {Bank Cards: [{User: {How many books does user have, which is the most expensive book}}]}

Create DTO

1. Right-click package name -> CreateStructDTO

2. Select aggregate node

3. Select properties to return

4. Enter DTO name

Don't remove the @link in the file header. The plugin can help users check if DTO has errors through this identifier.


/**
 * this file automatically generated by easy-query struct dto mapping
 * This file is automatically generated by easy-query structured dto mapping
 * {@link com.easy.query.test.mysql8.entity.bank.SysBank }
 *
 * @author xuejiaming
 * @easy-query-dto schema: normal
 */
@Data
public class SysBankDTO {


    private String id;
    /**
     * Bank name
     */
    private String name;
    /**
     * Establishment time
     */
    private LocalDateTime createTime;
    /**
     * Bank cards owned
     */
    @Navigate(value = RelationTypeEnum.OneToMany)
    private List<InternalBankCards> bankCards;


    /**
     * {@link com.easy.query.test.mysql8.entity.bank.SysBankCard }
     */
    @Data
    public static class InternalBankCards {

        private String id;
        private String uid;
        /**
         * Bank card number
         */
        private String code;
        /**
         * Bank card type: debit card, savings card
         */
        private String type;
        /**
         * Belongs to bank
         */
        private String bankId;
        /**
         * User account opening time
         */
        private LocalDateTime openTime;
        /**
         * Belongs to user
         */
        @Navigate(value = RelationTypeEnum.ManyToOne)
        private InternalUser user;


    }


    /**
     * {@link com.easy.query.test.mysql8.entity.bank.SysUser }
     */
    @Data
    public static class InternalUser {

        private String id;
        private String name;
        private String phone;
        private Integer age;
        private LocalDateTime createTime;


    }

}

5. Modify file to add book count and most expensive book's name and price to `InternalUser`

@SuppressWarnings("EasyQueryFieldMissMatch") is used to suppress plugin warnings. Current property doesn't exist in SysUser object.



    /**
     * {@link com.easy.query.test.mysql8.entity.bank.SysUser }
     */
    @Data
    @FieldNameConstants
    public static class InternalUser {
        private static final ExtraAutoIncludeConfigure EXTRA_AUTO_INCLUDE_CONFIGURE= SysUserProxy.TABLE.EXTRA_AUTO_INCLUDE_CONFIGURE()
                .configure(query->query.subQueryToGroupJoin(u->u.userBooks()))//Configure expression as implicit subquery
                // .configure(query -> query.configure(s->s.getBehavior().addBehavior(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN)))//If there are multiple subqueries and all need to be converted to groupJoin
                .select(u-> Select.of(
                        u.userBooks().count().as(Fields.bookCount),
                        u.userBooks().orderBy(book->book.price().desc()).firstElement().name().as(Fields.bookName),
                        u.userBooks().orderBy(book->book.price().desc()).firstElement().price().as(Fields.bookPrice)
                ));

        private String id;
        private String name;
        private String phone;
        private Integer age;
        private LocalDateTime createTime;

        @SuppressWarnings("EasyQueryFieldMissMatch")
        private Long bookCount;
        @SuppressWarnings("EasyQueryFieldMissMatch")
        private String bookName;
        @SuppressWarnings("EasyQueryFieldMissMatch")
        private BigDecimal bookPrice;

    }

Query


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

t_bank SQL


    SELECT
        t.`id`,
        t.`name`,
        t.`create_time` 
    FROM
        `t_bank` t

t_bank_card SQL


    SELECT
        t.`id`,
        t.`uid`,
        t.`code`,
        t.`type`,
        t.`bank_id`,
        t.`open_time` 
    FROM
        `t_bank_card` t 
    WHERE
        t.`bank_id` IN ('1', '2', '3')

t_sys_user SQL


    SELECT
        t.`id`,
        t.`name`,
        t.`phone`,
        t.`age`,
        t.`create_time`,
        IFNULL(t2.`__count2__`, 0) AS `book_count`,
        t6.`name` AS `book_name`,
        t6.`price` AS `book_price` 
    FROM
        `t_sys_user` t 
    LEFT JOIN
        (SELECT
            t1.`uid` AS `uid`, COUNT(*) AS `__count2__` FROM `t_sys_user_book` t1 
        GROUP BY
            t1.`uid`) t2 
            ON t2.`uid` = t.`id` 
    LEFT JOIN
        (SELECT
            t4.`id` AS `id`, t4.`name` AS `name`, t4.`uid` AS `uid`, t4.`price` AS `price` 
            FROM (SELECT
                t3.`id`, t3.`name`, t3.`uid`, t3.`price`, (ROW_NUMBER() OVER (PARTITION BY t3.`uid` ORDER BY t3.`price` DESC)) AS `__row__` 
                FROM `t_sys_user_book` t3) t4 
        WHERE
            t4.`__row__` = 1) t6 
            ON t6.`uid` = t.`id` 
    WHERE
        t.`id` IN ('u1', 'u2')

[{
	"bankCards": [{
		"bankId": "1",
		"code": "123",
		"id": "bc1",
		"openTime": "2000-01-02 00:00:00",
		"type": "Savings Card",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "User1",
			"phone": "123"
		}
	}, {
		"bankId": "1",
		"code": "1234",
		"id": "bc2",
		"openTime": "2000-01-02 00:00:00",
		"type": "Credit Card",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "User1",
			"phone": "123"
		}
	}, {
		"bankId": "1",
		"code": "1235",
		"id": "bc3",
		"openTime": "2000-01-02 00:00:00",
		"type": "Credit Card",
		"uid": "u2",
		"user": {
			"age": 23,
			"bookCount": 2,
			"bookName": "b3book",
			"bookPrice": 9.90,
			"createTime": "2012-01-01 00:00:00",
			"id": "u2",
			"name": "User2",
			"phone": "1234"
		}
	}],
	"createTime": "2000-01-01 00:00:00",
	"id": "1",
	"name": "ICBC"
}, {
	"bankCards": [{
		"bankId": "2",
		"code": "1236",
		"id": "bc4",
		"openTime": "2001-01-02 00:00:00",
		"type": "Savings Card",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "User1",
			"phone": "123"
		}
	}, {
		"bankId": "2",
		"code": "1237",
		"id": "bc5",
		"openTime": "2001-01-02 00:00:00",
		"type": "Savings Card",
		"uid": "",
		"user": null
	}],
	"createTime": "2001-01-01 00:00:00",
	"id": "2",
	"name": "CCB"
}, {
	"bankCards": [],
	"createTime": "2002-01-01 00:00:00",
	"id": "3",
	"name": "CMB"
}]

Parameter Passing


String arg = "myArg";
List<SysBankDTO> list = easyEntityQuery.queryable(SysBank.class)
        .configure(o -> {
            o.setConfigureArgument(arg);
        })
        .selectAutoInclude(SysBankDTO.class)
        .toList();

Modify InternalUser

Select can also get passed parameters for user dynamic DSL:



    /**
     * {@link com.easy.query.test.mysql8.entity.bank.SysUser }
     */
    @Data
    @FieldNameConstants
    public static class InternalUser {
        private static final ExtraAutoIncludeConfigure EXTRA_AUTO_INCLUDE_CONFIGURE= SysUserProxy.TABLE.EXTRA_AUTO_INCLUDE_CONFIGURE()
                .configure(query->query.subQueryToGroupJoin(u->u.userBooks()))//Configure expression as implicit subquery
                // .configure(query -> query.configure(s->s.getBehavior().addBehavior(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN)))//If there are multiple subqueries and all need to be converted to groupJoin
                .where(o -> {
                    ConfigureArgument configureArgument = o.getEntitySQLContext().getExpressionContext().getConfigureArgument();
                    String arg = configureArgument.getTypeArg();
                    o.name().ne(arg);
                })
                .select(u-> Select.of(
                        u.userBooks().count().as(Fields.bookCount),
                        u.userBooks().orderBy(book->book.price().desc()).firstElement().name().as(Fields.bookName),
                        u.userBooks().orderBy(book->book.price().desc()).firstElement().price().as(Fields.bookPrice)
                ));

        private String id;
        private String name;
        private String phone;
        private Integer age;
        private LocalDateTime createTime;

        @SuppressWarnings("EasyQueryFieldMissMatch")
        private Long bookCount;
        @SuppressWarnings("EasyQueryFieldMissMatch")
        private String bookName;
        @SuppressWarnings("EasyQueryFieldMissMatch")
        private BigDecimal bookPrice;

    }

Final Generated SQL

t_bank SQL


    SELECT
        t.`id`,
        t.`name`,
        t.`create_time` 
    FROM
        `t_bank` t

t_bank_card SQL


    SELECT
        t.`id`,
        t.`uid`,
        t.`code`,
        t.`type`,
        t.`bank_id`,
        t.`open_time` 
    FROM
        `t_bank_card` t 
    WHERE
        t.`bank_id` IN ('1', '2', '3')

t_sys_user SQL


    SELECT
        t.`id`,
        t.`name`,
        t.`phone`,
        t.`age`,
        t.`create_time`,
        IFNULL(t2.`__count2__`, 0) AS `book_count`,
        t6.`name` AS `book_name`,
        t6.`price` AS `book_price` 
    FROM
        `t_sys_user` t 
    LEFT JOIN
        (SELECT
            t1.`uid` AS `uid`, COUNT(*) AS `__count2__` FROM `t_sys_user_book` t1 
        GROUP BY
            t1.`uid`) t2 
            ON t2.`uid` = t.`id` 
    LEFT JOIN
        (SELECT
            t4.`id` AS `id`, t4.`name` AS `name`, t4.`uid` AS `uid`, t4.`price` AS `price` 
            FROM (SELECT
                t3.`id`, t3.`name`, t3.`uid`, t3.`price`, (ROW_NUMBER() OVER (PARTITION BY t3.`uid` ORDER BY t3.`price` DESC)) AS `__row__` 
                FROM `t_sys_user_book` t3) t4 
        WHERE
            t4.`__row__` = 1) t6 
            ON t6.`uid` = t.`id` 
    WHERE
        t.`name` <> 'myArg' 
        AND t.`id` IN ('u1', 'u2')

Contributors

xuejiaming只是我