sharp-database 通过SQL多表查询, 并组装返回Bean

业务Bean SchoolBO.java

@Data
public class SchoolBO {

    private Long id;

    private String name;

    private LocalDate buildDate;

    private SchoolLicenseBO sl;

    private List<StudentBO> studentList;

    private List<TeacherBO> teacherList;

    /**
     * 1 <-> 1
     */
    @Data
    public static class SchoolLicenseBO {

        private Long id;

        private String number;

        private String remark;
    }

    /**
     * 1 <-> N
     */
    @Data
    public static class StudentBO {

        private Long id;

        private String name;

        private Integer grade;

        private String sex;

        private Long schoolId;
    }

    /**
     * N <-> N
     */
    @Data
    public static class TeacherBO {

        private Long id;

        private String name;

        private Integer age;

        private Long schoolId;
    }
}

查询测试:

@Test
public void testQueryUtils() {
    String parentSql = "select s.id,\n" +
            "       s.name,\n" +
            "       s.build_date as \"buildDate\",\n" +
            "       sl.id as \"sl.id\",\n" +
            "       sl.number as \"sl.number\",\n" +
            "       sl.remark as \"sl.remark\"\n" +
            "from t_school s\n" +
            "         LEFT JOIN t_school_license sl on sl.id = s.school_license_id\n" +
            "where s.id IN (:id)";

    List<SchoolBO> schoolBOList = sharpService.query(parentSql, Params.builder(1).pv("id", "552173736070144000, 552181593272410112").build(), SchoolBO.class);

    if (CollectionUtils.isEmpty(schoolBOList)) {
        return;
    }

    Collection<?> refValues = schoolBOList.stream().map(SchoolBO::getId).collect(Collectors.toSet());

    // 1 <-> N
    // select id, name, grade, sex from t_school_student where school_id IN (552173736070144000, 552181593272410112);
    Map<?, List<SchoolBO.StudentBO>> schoolStudentMap = QueryUtils.subTableValueMap("id, name, grade, sex, school_id", "t_school_student", "school_id", refValues, SchoolBO.StudentBO.class);

    // N <-> N
    // select t.id, t.name, t.age, school_id from t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id where school_id IN (552173736070144000, 552181593272410112)
    Map<?, List<SchoolBO.TeacherBO>> schoolTeacherMap = QueryUtils.subTableValueMap("t.id, t.name, t.age, school_id", "t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id", "school_id", refValues, SchoolBO.TeacherBO.class);

    schoolBOList.forEach(t -> {
        t.setStudentList(ObjectUtils.defaultIfNull(schoolStudentMap.get(t.getId()), Collections.emptyList()));
        t.setTeacherList(ObjectUtils.defaultIfNull(schoolTeacherMap.get(t.getId()), Collections.emptyList()));
    });

    System.out.println(schoolBOList);
}

参考阅读: https://xhope.top/?p=1577

sharp-database通过注解@Version实现乐观锁

当要更新一条记录的时候，希望这条记录没有被别人更新
乐观锁实现方式：

取出记录时，获取当前 version
更新时，带上这个 version
执行更新时， set version = newVersion where version = oldVersion
如果 version 不对，就更新失败(更新影响的行数为0)

Notice.java 属性添加 @Version

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
@Table(value = "t_notice", comment = "通知公告")
public class Notice extends BaseEntity {

    String name;

    @Version
    Integer version;

}

生成SQL语句

create table `sharp-demo`.t_notice
(
    id          bigint      not null comment '主键'
        primary key,
    name        varchar(32) null,
    version     int         not null comment '版本号',
    create_by   bigint      null,
    create_time datetime    null comment '创建时间',
    update_by   bigint      null,
    update_time datetime    null comment '修改时间',
    is_deleted  bit         null comment '是否逻辑删除'
)
    comment '通知公告' charset = utf8mb3;

测试

新增

    @Order(0)
    @Test
    public void testNoticeInsert() {
        noticeDAO.insert(Notice.builder()
                        .name("hello new version")
                .build());
        // SQL => [INSERT INTO t_notice(name,version,create_by,create_time,update_by,update_time,is_deleted,id) VALUES(?,?,?,?,?,?,?,?)], args:=> [[hello new version, 1, 0, 2023-05-14T03:08:00.944, 0, 2023-05-14T03:08:00.944, false, 688973584810381312]]
    }

修改

    @Order(1)
    @Test
    public void testNoticeUpdate() {
        Notice notice = noticeDAO.selectById(688436409735868416L).get();
        notice.setName("new didi");
        noticeDAO.update(notice);
        // SQL => [UPDATE t_notice SET name = ?,version = ?,update_by = ?,update_time = ?,is_deleted = ? WHERE id = ? AND version = ? AND is_deleted = ?], args:=> [[new didi, 2, 0, 2023-05-12T15:33:28, false, 688436409735868416, 1, false]]
    }

IntelliJ 创建Sharp DB模板（Entity/Enum/DAO/Service）

File Header

/**
 * @author Rick.Xu
 * @date ${DATE} ${TIME}
 */

QueryResult

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.rick.db.plugin.dao.annotation.Table;
import lombok.Getter;
import lombok.Setter;
import lombok.AccessLevel;
import lombok.experimental.FieldDefaults;

#parse("File Header.java")
@Getter
@Setter
@FieldDefaults(level = AccessLevel.PRIVATE)
@Table
public class ${NAME} {

}

Entity

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.rick.db.dto.BaseEntity;
import com.rick.db.plugin.dao.annotation.Table;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.AccessLevel;
import lombok.experimental.FieldDefaults;
import lombok.experimental.SuperBuilder;

#parse("File Header.java")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
@Table(value = "table_name", comment = "表描述")
public class ${NAME} extends BaseEntity {

}

EnumCode

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.fasterxml.jackson.annotation.JsonValue;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.HashMap;
import java.util.Map;

#parse("File Header.java")
@AllArgsConstructor
@Getter
public enum ${NAME}Enum {
    NAME1(0, "NAME1"),
    NAME2(1, "NAME2");
    private static final Map<Integer, ${NAME}Enum> codeMap = new HashMap<>();
    static {
        for (${NAME}Enum e : values()) {
            codeMap.put(e.code, e);
        }
    }
    private final int code;
    private final String label;
    @JsonValue
    public int getCode() {
        return this.code;
    }

    /**
     * code枚举 必须重写toString()
     * @return
     */
    @Override
    public String toString() {
        return String.valueOf(code);
    }

    public static ${NAME}Enum valueOfCode(int code) {
        return codeMap.get(code);
    }
}

EnumCode2

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.HashMap;
import java.util.Map;

#parse("File Header.java")
@AllArgsConstructor
@Getter
@JsonFormat(shape = JsonFormat.Shape.OBJECT)
public enum ${NAME}Enum {
    NAME1(0, "NAME1"),
    NAME2(1, "NAME2");
    private static final Map<Integer, ${NAME}Enum> codeMap = new HashMap<>();
    static {
        for (${NAME}Enum e : values()) {
            codeMap.put(e.code, e);
        }
    }
    private final int code;
    private final String label;

    public int getCode() {
        return this.code;
    }

    /**
     * code枚举 必须重写toString()
     * @return
     */
    @Override
    public String toString() {
        return String.valueOf(code);
    }

    public static ${NAME}Enum valueOfCode(int code) {
        return codeMap.get(code);
    }
}

EnumName

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.fasterxml.jackson.annotation.JsonValue;
import lombok.AllArgsConstructor;
import lombok.Getter;

#parse("File Header.java")
@AllArgsConstructor
@Getter
public enum ${NAME}Enum {
    NAME1("NAME1"),
    NAME2("NAME2");

    @JsonValue
    public String getCode() {
        return this.name();
    }
    private final String label;
    public static ${NAME}Enum valueOfCode(String code) {
        return valueOf(code);
    }
}

EnumName2

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Getter;

#parse("File Header.java")
@AllArgsConstructor
@Getter
@JsonFormat(shape = JsonFormat.Shape.OBJECT)
public enum ${NAME}Enum {
    NAME1("NAME1"),
    NAME2("NAME2");

    public String getCode() {
        return this.name();
    }
    private final String label;
    public static ${NAME}Enum valueOfCode(String code) {
        return valueOf(code);
    }
}

DAO

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")#set($folderIndex = $PACKAGE_NAME.length() - 4)#end

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import com.rick.db.plugin.dao.core.EntityDAOImpl;
import #if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")${PACKAGE_NAME.substring(0, $folderIndex)}.entity.#end${NAME};
import org.springframework.stereotype.Repository;

#parse("File Header.java")
@Repository
public class ${NAME}DAO extends EntityDAOImpl<${NAME}, Long> {

}

Service

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")#set($folderIndex = $PACKAGE_NAME.length() - 8)#end
#set($length = $NAME.length())

#if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")package ${PACKAGE_NAME};#end

import #if (${PACKAGE_NAME} && ${PACKAGE_NAME} != "")${PACKAGE_NAME.substring(0, $folderIndex)}.dao.#end${NAME}DAO;
import lombok.RequiredArgsConstructor;
import lombok.AccessLevel;
import lombok.experimental.FieldDefaults;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

#parse("File Header.java")
@Service
@FieldDefaults(makeFinal = true, level = AccessLevel.PRIVATE)
@RequiredArgsConstructor
@Validated
public class ${NAME}Service {
    ${NAME}DAO ${NAME.substring(0,1).toLowerCase()}${NAME.substring(1,$length)}DAO;
}

实例：sharp-*完成可配置的增删改查

人员信息表

数据库设计

表名：t_user

字段名 描述 字段类型 java 字段 UI 控件
name 姓名 varchar(32) String input[text]
gender 性别 varchar(32) Enum(by code) radio
age 年龄 int Integer input[number]
birthday 出生时间 datetime LocalDate input[date]
mobile 手机号码 varchar String text
email 邮箱 varchar String input[email]
native_place 籍贯 varchar(32) String select
hobby 兴趣爱好 varchar(32) Enum(by name) checkbox
marriage 婚否 bit String checkbox
introduce 自我介绍 text String textarea
attachment 附件 text List file
school_experience 学习经历 text List table
status 用户状态 varchar(32) Enum(by order) select

常规代码

User.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_user", comment = "人员信息表")
public class User extends BaseEntity {

    @Column(comment = "姓名", nullable = false)
    private String name;

    @Column(comment = "性别", nullable = false)
    private GenderEnum gender;

    @Column(comment = "年龄", nullable = false)
    private Integer age;

    @Column(comment = "出生时间")
    private LocalDate birthday;

    @Column(comment = "手机号码", nullable = false)
    private String mobile;

    @Column(comment = "邮箱")
    private String email;

    @Column(comment = "籍贯", nullable = false)
    private String nativePlace;

    @Column(comment = "兴趣爱好", value = "hobby", nullable = false)
    private List<HobbyEnum> hobbyList;

    @Column(comment = "婚否", nullable = false)
    private Boolean marriage;

    @Column(comment = "自我介绍", columnDefinition = "text", nullable = false)
    private String introduce;

    @Column(comment = "附件", columnDefinition = "text", value = "attachment", nullable = false)
    private List<Map<String, Object>> attachmentList;

    @Column(comment = "学习经历", columnDefinition = "text", value = "school_experience")
    private List<List<String>> schoolExperienceList;

    @Column(comment = "用户状态")
    private StatusEnum status;

    @AllArgsConstructor
    @Getter
    public enum GenderEnum {
        UNKNOWN(0, "Unknown"),
        MALE(1, "Male"),
        FEMALE(2, "Female");
        private static final Map<Integer, GenderEnum> codeMap = new HashMap<>();
        static {
            for (GenderEnum e : values()) {
                codeMap.put(e.code, e);
            }
        }
        private final int code;
        private final String label;
        @JsonValue
        public int getCode() {
            return this.code;
        }
        public static GenderEnum valueOfCode(int code) {
            return codeMap.get(code);
        }
    }

    @Getter
    public enum StatusEnum {
        LOCKED, NORMAL
    }

    @AllArgsConstructor
    @Getter
    public enum HobbyEnum {
        BASKETBALL("篮球"),
        FOOTBALL("羽毛球"),
        BADMINTON("羽毛球"),
        VOLLEYBALL("排球");

        @JsonValue
        public String getCode() {
            return this.name();
        }
        private final String label;
        public static HobbyEnum valueOfCode(String code) {
            return valueOf(code);
        }
    }
}

执行 tableGenerator.createTable(User.class) 生成表。

create table t_user
(
    id bigint not null comment '主键'
        primary key,
    name varchar(32) not null comment '姓名',
    gender varchar(16) not null comment '性别',
    age int not null comment '年龄',
    birthday date null comment '出生时间',
    mobile varchar(32) not null comment '手机号码',
    email varchar(32) null comment '邮箱',
    native_place varchar(32) not null comment '籍贯',
    hobby json not null comment '兴趣爱好',
    marriage bit not null comment '婚否',
    introduce text null,
    attachment text null,
    school_experience text null,
    status varchar(16) null comment '用户状态'
)
comment '人员信息表';

DAO

UserDAO.java

@Repository
public class UserDAO extends EntityDAOImpl<User, Long> {

}

UserFormAdvice

@Component("userFormAdvice")
public class UserFormAdvice implements FormAdvice {

    @Override
    public void beforeInstanceHandle(FormBO form, Long instanceId, Map<String, Object> values) {
        // checkbox List => boolean
        if (CollectionUtils.isEmpty((Collection<?>) values.get("marriage"))) {
            values.put("marriage", false);
        } else {
            values.put("marriage", true);
        }
    }

    @Override
    public void afterInstanceHandle(FormBO form, Long instanceId, Map<String, Object> values) {

    }
}

创建UI控件

@Test
  public void testAddConfigurerToForm() {
      // 设计控件
      List<CpnConfigurer> cpnConfigurerList = createCpnConfigurerList();
      cpnConfigurerService.saveOrUpdate(cpnConfigurerList);

      // 创建表
      Form form = formService.save(Form.builder()
              .code("t_user")
              .tableName("t_user")
              .name("人员信息表")
              .repositoryName("userDAO")
              .formAdviceName("userFormAdvice")
              .storageStrategy(Form.StorageStrategyEnum.CREATE_TABLE)
              .build());

      // 关联关系
      formCpnService.saveOrUpdateByConfigIds(form.getId(), cpnConfigurerList.stream().map(CpnConfigurer::getId).collect(Collectors.toList()));
  }

  public List<CpnConfigurer> createCpnConfigurerList() {
      // 文本校验器
      Set<Validator> textValidatorList = Sets.newHashSetWithExpectedSize(2);
      textValidatorList.add(new Required(true));
      textValidatorList.add(new Length(16));

      Set<Validator> requiredValidatorList = Sets.newHashSetWithExpectedSize(1);
      requiredValidatorList.add(new Required(true));

      CpnConfigurer nameCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.TEXT)
              .name("name")
              .label("姓名")
              .placeholder("请输入姓名")
              .defaultValue("Rick.Xu")
              .validatorList(textValidatorList)
              .build();

      CpnConfigurer genderCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.RADIO)
              .name("gender")
              .label("性别")
              .options(Arrays.asList(new CpnConfigurer.CpnOption("0", "未知"), new CpnConfigurer.CpnOption("1", "男"), new CpnConfigurer.CpnOption("2", "女")))
              .defaultValue("1")
              .validatorList(requiredValidatorList)
              .build();

      Set<Validator> ageValidatorList = Sets.newHashSetWithExpectedSize(1);
      ageValidatorList.add(new Size(18, 100));
      ageValidatorList.add(new Required(true));
      CpnConfigurer ageCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.NUMBER_TEXT)
              .name("age")
              .label("年龄")
              .placeholder("请输入年龄")
              .defaultValue("28")
              .validatorList(ageValidatorList)
              .build();

      CpnConfigurer birthdayCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.DATE)
              .name("birthday")
              .label("出生时间")
              .placeholder("请输入出生时间")
              .defaultValue("1989-12-11")
              .build();

      CpnConfigurer mobileCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.MOBILE)
              .name("mobile")
              .label("手机号码")
              .placeholder("请输入手机号码")
              .defaultValue("18898765543")
              .validatorList(requiredValidatorList)
              .build();

      CpnConfigurer emailCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.EMAIL)
              .name("email")
              .label("邮箱")
              .placeholder("请输入邮箱")
              .defaultValue("jkxyx@163.com")
              .build();

      CpnConfigurer nativePlaceCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.SELECT)
              .name("native_place")
              .label("籍贯")
              .options(Arrays.asList(new CpnConfigurer.CpnOption("陕西"), new CpnConfigurer.CpnOption("江苏"), new CpnConfigurer.CpnOption("四川"), new CpnConfigurer.CpnOption("上海")))
              .defaultValue("江苏")
              .validatorList(requiredValidatorList)
              .build();

      CpnConfigurer hobbyCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.CHECKBOX)
              .name("hobby")
              .label("兴趣爱好")
              .options(Arrays.asList(new CpnConfigurer.CpnOption("BASKETBALL", "篮球"), new CpnConfigurer.CpnOption("FOOTBALL", "足球"), new CpnConfigurer.CpnOption("BADMINTON", "羽毛球"), new CpnConfigurer.CpnOption("VOLLEYBALL", "排球"))                 )
              .defaultValue("[\"BASKETBALL\", \"FOOTBALL\"]")
              .validatorList(requiredValidatorList)
              .build();

      CpnConfigurer marriageCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.CHECKBOX)
              .name("marriage")
              .label("婚否")
//                .options(Arrays.asList(new CpnConfigurer.CpnOption("true", "")) )
              .options(Arrays.asList(new CpnConfigurer.CpnOption("1", "")) )
              .defaultValue("true")
              .build();

      CpnConfigurer introduceCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.TEXTAREA)
              .name("introduce")
              .label("自我介绍")
              .placeholder("请输入自我介绍")
              .defaultValue("我叫Rick...")
              .validatorList(textValidatorList)
              .build();

      CpnConfigurer attachmentCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.FILE)
              .name("attachment")
              .label("附件")
              .validatorList(requiredValidatorList)
              .build();

      CpnConfigurer yearCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.TEXT)
              .name("school_year")
              .label("年份")
              .defaultValue("Rick.Xu")
              .validatorList(textValidatorList)
              .build();

      CpnConfigurer schoolNameCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.TEXT)
              .name("school_name")
              .label("学校名称")
              .defaultValue("Rick.Xu")
              .validatorList(textValidatorList)
              .build();

      CpnConfigurer schoolExperienceCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.TABLE)
              .name("school_experience")
              .label("学习经历")
              .additionalInfo(Params.builder()
                        .pv("columns", Arrays.asList(yearCpn, schoolNameCpn))
                      .build())
              .defaultValue("[[\"2021-2022\", \"四川大学\"], [\"2012-2015\", \"永安高中\"]]")
              .build();

      CpnConfigurer statusCpn = CpnConfigurer.builder()
              .cpnType(CpnTypeEnum.SELECT)
              .name("status")
              .label("用户状态")
              .options(Arrays.asList(new CpnConfigurer.CpnOption("LOCKED", "锁定"), new CpnConfigurer.CpnOption("NORMAL", "正常")))
              .defaultValue("LOCKED")
              .build();

      List<CpnConfigurer> cpnConfigurerList = Lists.newArrayList(nameCpn, genderCpn, ageCpn, birthdayCpn,
              mobileCpn, emailCpn, nativePlaceCpn, hobbyCpn, marriageCpn, introduceCpn, attachmentCpn, schoolExperienceCpn, statusCpn);
      return cpnConfigurerList;
  }

访问表单

新增 POST /{form_id}
修改 POST /{form_id}/{instance_id}
查找 GET /{form_id}/{instance_id}

http://xhope.top/wp-content/uploads/2023/03/from.png

创建报表

@Test
public void testAddReport() {
    reportService.saveOrUpdate(Report.builder()
            .id(668074769748373504L)
            .name("人员信息表")
            .querySql("SELECT t_user.name              AS \"name\",\n" +
                    "#        t_user.gender            AS \"gender\",\n" +
                    "       sd.label AS \"gender\",\n" +
                    "       t_user.age               AS \"age\",\n" +
                    "       t_user.birthday          AS \"birthday\",\n" +
                    "       t_user.mobile            AS \"mobile\",\n" +
                    "       t_user.email             AS \"email\",\n" +
                    "       t_user.native_place      AS \"nativePlace\",\n" +
                    "       t_user.hobby             AS \"hobbyList\",\n" +
                    "       IF(t_user.marriage, '是', '否')         AS \"marriage\",\n" +
                    "       t_user.introduce         AS \"introduce\",\n" +
                    "       t_user.attachment        AS \"attachmentList\",\n" +
                    "       t_user.school_experience AS \"schoolExperienceList\",\n" +
                    "       t_user.status            AS \"status\",\n" +
                    "       t_user.created_by        AS \"createdBy\",\n" +
                    "       t_user.created_at        AS \"createdAt\",\n" +
                    "       t_user.updated_by        AS \"updatedBy\",\n" +
                    "       t_user.updated_at        AS \"updatedAt\",\n" +
                    "       t_user.is_deleted        AS \"deleted\",\n" +
                    "       t_user.id                AS \"id\"\n" +
                    "FROM t_user left JOIN sys_dict sd ON t_user.gender = sd.name" +
                    " WHERE is_deleted = 0 AND t_user.name like :name AND t_user.gender = :gender AND t_user.mobile = :mobile " +
                    "AND t_user.native_place IN (:nativePlace) AND t_user.status = :status AND t_user.hobby like :hobbyList" +
                            " AND t_user.birthday >= :birthday0 AND t_user.birthday <= :birthday1 AND marriage = :marriage")
            .reportColumnList(Arrays.asList(
                    new ReportColumn("name", "姓名", true),
                    new ReportColumn("gender", "性别"),
                    new ReportColumn("age", "年龄"),
                    new ReportColumn("birthday", "出生日期",false, null, Arrays.asList("localDateConverter")),
                    new ReportColumn("mobile", "手机号码"),
                    new ReportColumn("email", "邮箱"),
                    new ReportColumn("nativePlace", "籍贯"),
                    new ReportColumn("hobbyList", "兴趣爱好", false, "hobbyList", Arrays.asList("arrayDictConverter")),
                    new ReportColumn("marriage", "婚否"),
                    new ReportColumn("introduce", "自我介绍"),
//                        new ReportColumn("attachmentList", "附件"),
//                        new ReportColumn("schoolExperienceList", "学习经历"),
                    new ReportColumn("status", "状态", false, "status", Arrays.asList("dictConverter"))
            ))
            .queryFieldList(Arrays.asList(
                    new QueryField("name", "姓名"),
                    new QueryField("mobile", "手机号码"),
                    // sys_dic#gender
                    new QueryField("gender", "性别", QueryField.Type.SELECT, "gender"),
                    new QueryField("birthday", "出生日期", QueryField.Type.DATE_RANGE),
                    // 配置文件
                    new QueryField("nativePlace", "籍贯", QueryField.Type.MULTIPLE_SELECT, "nativePlace"),
                    // 接口实现
                    new QueryField("hobbyList", "兴趣爱好", QueryField.Type.SELECT, "hobbyList"),
                    // 配置文件
                    new QueryField("status", "状态", QueryField.Type.SELECT, "status"),
                    new QueryField("marriage", "婚否", QueryField.Type.SELECT, "bol")
            ))
            .pageable(true)
//                .summary(true)
            .summaryColumnNames("age, mobile")
            .sidx("name")
            .sord(SordEnum.ASC)
            .build());
}

http://xhope.top/wp-content/uploads/2023/03/list.png
参数

SELECT t_user.name                   AS "name",
       sd.label                      AS "gender",
       t_user.age                    AS "age",
       t_user.birthday               AS "birthday",
       t_user.mobile                 AS "mobile",
       t_user.email                  AS "email",
       t_user.native_place           AS "nativePlace",
       t_user.hobby                  AS "hobbyList",
       IF(t_user.marriage, '是', '否') AS "marriage",
       t_user.introduce              AS "introduce",
       t_user.attachment             AS "attachmentList",
       t_user.school_experience      AS "schoolExperienceList",
       t_user.status                 AS "status",
       t_user.created_by             AS "createdBy",
       t_user.created_at             AS "createdAt",
       t_user.updated_by             AS "updatedBy",
       t_user.updated_at             AS "updatedAt",
       t_user.is_deleted             AS "deleted",
       t_user.id                     AS "id"
FROM t_user
         LEFT JOIN sys_dict sd ON t_user.gender = sd.name
WHERE is_deleted = 0
  AND UPPER(t_user.name) LIKE CONCAT('%', UPPER(:name), '%') ESCAPE
      '\\' AND t_user.gender = :gender AND t_user.mobile = :mobile AND t_user.native_place IN (:nativePlace0) AND
      t_user.status = :status AND t_user.birthday >= :birthday0 AND t_user.birthday <= :birthday1 AND
      marriage = :marriage

[{gender=2, marriage=1, name=凡夫俗子, mobile=17798765543, nativePlace0=上海, birthday1=1994-01-17, birthday0=1984-01-17, status=NORMAL}]

sharp-database中的BaseCodeDAOImpl通过对实体字段code的支持

@BaseCodeDAOImpl 作用是对实体字段 code 的支持。一张表可能会有一个外部可见的唯一字段，可以做主键使用。比如序列号、注册的手机号都是属于业务主键。

示例如下：

实体 SerialNumber.java 继承 BaseCodeEntity ，表生成字段 code

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "mm_serial_number", comment = "序列号")
public class SerialNumber extends BaseCodeEntity {

    private SerialNumber.SerialNumberEnum status;

    @AllArgsConstructor
    @Getter
    public enum SerialNumberEnum {
        CREATED("创建"),
        AVAILABLE("在库"),
        SOLD("离库");

        @JsonValue
        public String getCode() {
            return this.name();
        }

        private final String label;

        public static SerialNumberEnum valueOfCode(String code) {
            return valueOf(code);
        }
    }

}

SerialNumberDAO.java 继承 BaseCodeDAOImpl

@Repository
public class SerialNumberDAO extends BaseCodeDAOImpl<SerialNumber, Long> {
}

测试

    @Autowired
    private SerialNumberDAO serialNumberDAO;

    @Autowired
    private BaseCodeEntityIdFillService idFillService;

    @Test
    public void testSave() {
        assertThrows(BizException.class, ()-> {
            serialNumberDAO.insert(SerialNumber.builder()
                    .code("11")
                    .status(SerialNumber.SerialNumberEnum.AVAILABLE)
                    .build());
        });
    }

    @Test
    public void testSaveOrUpdate() {
        // 可以根据code insertOrUpdate，也可以通过id insertOrUpdate
        serialNumberDAO.insertOrUpdate(SerialNumber.builder()
                .code("11")
                .status(SerialNumber.SerialNumberEnum.SOLD)
                .build());
    }

    @Test
    public void testFindByCode() {
        SerialNumber serialNumber = serialNumberDAO.selectByCode("11").get();
        assertThat(serialNumber.getStatus()).isEqualTo(SerialNumber.SerialNumberEnum.SOLD);
    }

    @Test
    public void testFillId1() {
        SerialNumber serialNumber = SerialNumber.builder().code("11").build();
        idFillService.fill(Arrays.asList(serialNumber));
        assertThat(serialNumber.getId()).isEqualTo(664984730659258368L);
    }

    @Test
    public void testFillId2() {
        SerialNumber serialNumber = SerialNumber.builder().code("11").build();
        idFillService.fill(serialNumber);
        assertThat(serialNumber.getId()).isEqualTo(664984730659258368L);
    }

    @Test
    public void testFillId3() {
        List<SerialNumber> list = idFillService.fill(SerialNumber.class, Arrays.asList("11"));
        assertThat(list.get(0).getId()).isEqualTo(664984730659258368L);
    }