DTO Mapping Expert

DTO Mapping Expert

The current chapter mainly describes how selectAutoInclude filters and controls structured objects to return extra field related information

It is recommended to upgrade eq to 3.1.60+. Using this feature allows you to control any level expressions of selectAutoInclude

We still use the bank, bank card, 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;

    /**
     * Owned bank cards
     */
    @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 or 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:{BankCards:[{User:{How many books does the 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 at the file header, the plugin can use this identifier to help users check if the dto has errors

/**
 * 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;
    /**
     * Owned bank cards
     */
    @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 or 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 name and price of most expensive book to InternalUser

@SuppressWarnings("EasyQueryFieldMissMatch") is used to suppress plugin warnings when the current property does not exist in the 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;

    }

6. 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

Where select can also get passed parameters for convenient dynamic dsl for users

    /**
     * {@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')