结构化对象额外操作

xuejmnet2025/4/19大约 5 分钟

当前章节主要描述selectAutoInclude是如何对结构对象进行筛选控制返回额外字段相关信息

建议eq升级到2.8.10+ 使用该功能可以让selectAutoInclude的任意层级表达式都由你进行掌控

我们依然使用银行银行卡用户这个案例

关系图

SysBank


@Table("t_bank")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("bank")
public class SysBank implements ProxyEntityAvailable<SysBank, SysBankProxy> {
    @Column(primaryKey = true)
    private String id;
    /**
     * 银行名称
     */
    private String name;
    /**
     * 成立时间
     */
    private LocalDateTime createTime;

    /**
     * 拥有的银行卡
     */
    @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;
    /**
     * 银行卡号
     */
    private String code;
    /**
     * 银行卡类型借记卡 储蓄卡
     */
    private String type;
    /**
     * 所属银行
     */
    private String bankId;
    /**
     * 用户开户时间
     */
    private LocalDateTime openTime;

    /**
     * 所属银行
     */
    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {"bankId"}, targetProperty = {"id"})
    @ForeignKey//可以不加 加了就是InnerJoin处理更多细节查看注解篇章
    private SysBank bank;

    /**
     * 所属用户
     */
    @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;

    /**
     * 用户拥有的银行卡数
     */
    @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {"id"}, targetProperty = {"uid"})
    private List<SysBankCard> bankCards;

    /**
     * 用户拥有的书本
     */
    @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;
}

返回数据结构为银行:{银行卡:[{用户:{用户有多少书本，用户最贵的那一本是哪一本}}]}

创建dto

1.右键包名->CreateStructDTO

2.选中聚合节点

3.选中要返回的属性

4.输入dto名称

其中文件头部的@link别去掉,插件可以通过这个标识来帮助用户进行检查dto是否有错误


/**
 * this file automatically generated by easy-query struct dto mapping
 * 当前文件是easy-query自动生成的 结构化dto 映射
 * {@link com.easy.query.test.mysql8.entity.bank.SysBank }
 *
 * @author xuejiaming
 * @easy-query-dto schema: normal
 */
@Data
public class SysBankDTO {


    private String id;
    /**
     * 银行名称
     */
    private String name;
    /**
     * 成立时间
     */
    private LocalDateTime createTime;
    /**
     * 拥有的银行卡
     */
    @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;
        /**
         * 银行卡号
         */
        private String code;
        /**
         * 银行卡类型借记卡 储蓄卡
         */
        private String type;
        /**
         * 所属银行
         */
        private String bankId;
        /**
         * 用户开户时间
         */
        private LocalDateTime openTime;
        /**
         * 所属用户
         */
        @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.修改文件对`InternalUser`添加书籍数量和最贵书籍的名称和价格

@SuppressWarnings("EasyQueryFieldMissMatch")用来抑制插件警告，当前属性不存在SysUser对象中



    /**
     * {@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()))//配置表达式为隐式子查询
                .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.查询


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": "储蓄卡",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "用户1",
			"phone": "123"
		}
	}, {
		"bankId": "1",
		"code": "1234",
		"id": "bc2",
		"openTime": "2000-01-02 00:00:00",
		"type": "信用卡",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "用户1",
			"phone": "123"
		}
	}, {
		"bankId": "1",
		"code": "1235",
		"id": "bc3",
		"openTime": "2000-01-02 00:00:00",
		"type": "信用卡",
		"uid": "u2",
		"user": {
			"age": 23,
			"bookCount": 2,
			"bookName": "b3book",
			"bookPrice": 9.90,
			"createTime": "2012-01-01 00:00:00",
			"id": "u2",
			"name": "用户2",
			"phone": "1234"
		}
	}],
	"createTime": "2000-01-01 00:00:00",
	"id": "1",
	"name": "工商银行"
}, {
	"bankCards": [{
		"bankId": "2",
		"code": "1236",
		"id": "bc4",
		"openTime": "2001-01-02 00:00:00",
		"type": "储蓄卡",
		"uid": "u1",
		"user": {
			"age": 22,
			"bookCount": 4,
			"bookName": "b2book",
			"bookPrice": 11.00,
			"createTime": "2012-01-01 00:00:00",
			"id": "u1",
			"name": "用户1",
			"phone": "123"
		}
	}, {
		"bankId": "2",
		"code": "1237",
		"id": "bc5",
		"openTime": "2001-01-02 00:00:00",
		"type": "储蓄卡",
		"uid": "",
		"user": null
	}],
	"createTime": "2001-01-01 00:00:00",
	"id": "2",
	"name": "建设银行"
}, {
	"bankCards": [],
	"createTime": "2002-01-01 00:00:00",
	"id": "3",
	"name": "招商银行"
}]

参数传递


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

修改InternalUser

其中select也可以获取传递的参数方便用户动态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()))//配置表达式为隐式子查询
                .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;

    }

最终生成的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')

贡献者

xuejiaming