sharp-database实例详解

POJO

实体

总共4个实体类：School.java SchoolLicense.java Student.java Teacher.java

  • 「学校」和「证书」1对1外键
  • 「学校」和「学生」1对多
  • 「学校」和「老师」多对多

School.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_school", comment = "学校")
public class School extends BasePureEntity {

    @Column(comment = "学校名称")
    private String name;

    @Column(comment = "建校日期")
    private LocalDate buildDate;

    @Column(comment = "学校性质 PRIVATE：私立；PUBLIC：公立")
    private TypeEnum type;

    @Column(comment = "每年经费预算")
    private BigDecimal budget;

    @Column(comment = "专业数")
    private Integer score;

    @Column(comment = "学校地址")
    private Address address;

    @Column(comment = "其他信息")
    private Map<String, Object> additionalInfo;

    @Column(comment = "历届领导信息")
    private List<Map<String, Object>> leadershipInformationList;

    @Column(comment = "历届获奖信息")
    private Set<Map<String, Object>> awardsSet;

    @Column(comment = "历届学校评分")
    private List<Float> scoreList;

    @Column(comment = "学校评价")
    private Evaluate evaluate;

    /**
     * 「学校」和「证书」1对1外键
     *  1 <==> 1
     */
    @ManyToOne(value = "school_license_id", parentTable = "t_school_license", comment = "证书信息")
    private SchoolLicense schoolLicense;

    /**
     * 「学校」和「学生」1对多
     *  1 <==> N
     */
    @OneToMany(subTable = "t_school_student", joinValue = "school_id")
    private List<Student> studentList;

    /**
     * 「学校」和「老师」多对多
     *  N <==> N
     */
    @ManyToMany(thirdPartyTable = "t_school_teacher_related", columnDefinition = "school_id", referenceTable = "t_school_teacher", referenceColumnName = "teacher_id")
    private List<Teacher> teacherList;

    @AllArgsConstructor
    @Getter
    public enum TypeEnum {
        PRIVATE("私立"),
        PUBLIC("公立");

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

SchoolLicense.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_school_license", comment = "学校证书")
public class SchoolLicense extends BasePureEntity {

    @Column(comment = "证书编号")
    private String number;

    @Column(comment = "备注")
    private String remark;

    @OneToMany(subTable = "t_school", joinValue = "school_license_id", oneToOne = true)
    private School school;

}

Student.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_school_student", comment = "学生")
public class Student extends BasePureEntity {

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

    @Column(comment = "年级")
    private Integer grade;

    @Column(comment = "性别")
    private SexEnum sex;

    @ManyToOne(value = "school_id", parentTable = "t_school")
    private School school;

    @AllArgsConstructor
    @Getter
    public enum SexEnum {
        MALE(0, "男"),
        FEMALE(1, "女");

        private static final Map<Integer, SexEnum> codeMap = new HashMap<>();

        static {
            for (SexEnum e : values()) {
                codeMap.put(e.code, e);
            }
        }

        private final int code;
        private final String label;

        @JsonValue
        public int getCode() {
            return code;
        }

        public static SexEnum valueOfCode(int code) {
            return codeMap.get(code);
        }
    }
}

Teacher.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_school_teacher", comment = "教师")
public class Teacher extends BasePureEntity {

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

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

    @ManyToMany(thirdPartyTable = "t_school_teacher_related", columnDefinition = "teacher_id", referenceTable = "t_school", referenceColumnName = "school_id")
    private List<School> schoolList;

}

存储Model

Evaluate.java

@AllArgsConstructor
@Getter
public class Evaluate {

    /**
     * 评价等级
     */
    private Integer grade;

    /**
     * 评价内容
     */
    private String description;

    @Override
    public String toString() {
        return "Evaluate{" +
                "grade=" + grade +
                ", description='" + description + '\'' +
                '}';
    }
}

Evaluate.java 没有实现接口 JsonStringToObjectConverterFactory.JsonValue，所以必须自定义converter反序列号字符串。
EvaluateConverterFactory.java

@Component
public class EvaluateConverterFactory implements ConverterFactory<String, Evaluate> {

    @Override
    public <T extends Evaluate> Converter<String, T> getConverter(Class<T> aClass) {
        return value -> {
            // value => Evaluate{grade=1, description='GREAT!'}

            String substring = value.substring(value.indexOf("{") + 1, value.length() - 1);
            String[] values = substring.split(",\\s+");

            return (T) new Evaluate(Integer.parseInt(values[0].split("=")[1]), values[1].split("=")[1]);
        };
    }
}

Address.java

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Address implements JsonStringToObjectConverterFactory.JsonValue {

    private String code;

    private String detail;
}

生成SQL

运行 TableGenerator 生成SQL，参考sharp-database中的TableGenerator根据实体对象生成表

tableGenerator.createTable(School.class);
tableGenerator.createTable(Teacher.class);
tableGenerator.createTable(Student.class);
tableGenerator.createTable(SchoolLicense.class);

生成的SQL DDL

create table t_school_license
(
    id bigint not null comment '主键'
        primary key,
    number varchar(32) null comment '证书编号',
    remark varchar(32) null comment '备注',
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null
)
comment '学校证书';

create table t_school
(
    id bigint not null comment '主键'
        primary key,
    name varchar(32) null comment '学校名称',
    build_date date null comment '建校日期',
    type varchar(16) null comment '学校性质 PRIVATE：私立；PUBLIC：公立',
    budget decimal(10,4) null comment '每年经费预算',
    score int null comment '专业数',
    address json null comment '学校地址',
    additional_info json null comment '其他信息',
    leadership_information_list json null comment '历届领导信息',
    awards_set varchar(32) null comment '历届获奖信息',
    score_list json null comment '历届学校评分',
    evaluate varchar(128) null comment '学校评价',
    school_license_id bigint null comment '证书信息',
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null,
    constraint t_school_t_school_license_id_fk
        foreign key (school_license_id) references t_school_license (id)
)
comment '学校';

create table t_school_student
(
    id bigint not null comment '主键'
        primary key,
    name varchar(32) null comment '姓名',
    grade int null comment '年级',
    sex varchar(16) null comment '性别',
    school_id bigint null,
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null,
    constraint t_school_student_t_school_id_fk
        foreign key (school_id) references t_school (id)
)
comment '学生';

create table t_school_teacher
(
    id bigint not null comment '主键'
        primary key,
    name varchar(32) null comment '姓名',
    age int null comment '年龄',
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null
)
comment '教师';

create table t_school_teacher_related
(
    school_id bigint not null,
    teacher_id bigint not null,
    is_deleted bit default b'0' not null,
    constraint t_school_teacher_related_pk
        unique (school_id, teacher_id),
    constraint t_school_teacher_related_t_school_id_fk
        foreign key (school_id) references t_school (id),
    constraint t_school_teacher_related_t_school_teacher_id_fk
        foreign key (teacher_id) references t_school_teacher (id)
);

https://xhope.top/wp-content/uploads/2022/05/ii.png

生成DAO

SchoolDAO

@Repository
public class SchoolDAO extends BaseDAOImpl<School> {

}

SchoolLicenseDAO

@Repository
public class SchoolLicenseDAO extends BaseDAOImpl<SchoolLicense> {

}

StudentDAO

@Repository
public class StudentDAO extends BaseDAOImpl<Student> {

}

TeacherDAO

@Repository
public class TeacherDAO extends BaseDAOImpl<Teacher> {

}

测试

Junit5

@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class SchoolTest {

    @Autowired
    private SchoolDAO schoolDAO;

    @Autowired
    private SchoolLicenseDAO schoolLicenseDAO;

    @Autowired
    private StudentDAO studentDAO;

    @Autowired
    private TeacherDAO teacherDAO;

    @Order(0)
    @Test
    public void testSave() {
        SchoolLicense schoolLicense = createSchoolLicense();
        schoolLicenseDAO.insert(schoolLicense);

        School school = createSchool(schoolLicense);
        schoolDAO.insert(school);

        Student student = createStudent(school);
        studentDAO.insert(student);

        Teacher teacher = createTeacher(Arrays.asList(school));
        teacherDAO.insert(teacher);
    }

    @Order(1)
    @Test
    public void testSchoolFindById() {
        Optional<School> option = schoolDAO.selectById(552173736070144000L);
        School school = option.get();
        checkSchool(school);
        checkSchoolLicense(school.getSchoolLicense());
        checkStudent(school.getStudentList().get(0));
        checkTeacher(school.getTeacherList().get(0));
    }

    @Order(1)
    @Test
    public void testStudentFindById() {
        Student student = studentDAO.selectById(552173736246304768L).get();
        checkStudent(student);
        checkSchool(student.getSchool());
    }

    @Order(2)
    @Test
    public void testTeacherFindById() {
        Teacher teacher = teacherDAO.selectById(552173736518934528L).get();
        checkTeacher(teacher);
        checkSchool(teacher.getSchoolList().get(0));
    }

    @Order(3)
    @Test
    public void testSchoolLicenseFindById() {
        SchoolLicense schoolLicense = schoolLicenseDAO.selectById(552173735432609792L).get();
        checkSchoolLicense(schoolLicense);
        checkSchool(schoolLicense.getSchool());
    }

    private School createSchool(SchoolLicense schoolLicense) {
        return School.builder()
                .name("清华大学")
                .buildDate(LocalDate.now())
                .type(School.TypeEnum.PUBLIC)
                .score(100)
                .budget(new BigDecimal("111323.23"))
                .address(Address.builder().code("100084").detail("中华人民共和国北京市海淀区清华园").build())
                .additionalInfo(Params.builder(1).pv("Teacher Count", 3641).build())
                .evaluate(new Evaluate(1, "GREAT!"))
                .leadershipInformationList(Arrays.asList(Params.builder(1).pv("Principal", "王希勤").build()))
                .awardsSet(Sets.newHashSet(Params.builder(1).pv("c", "C9联盟").build()))
                .scoreList(Arrays.asList(99.9f, 98f, 100f))
                .schoolLicense(schoolLicense)
                .build();
    }

    private Student createStudent(School school) {
        return Student.builder()
                .name("Rick.Xu")
                .sex(Student.SexEnum.MALE)
                .grade(1)
                .school(school)
                .build();
    }

    private Teacher createTeacher(List<School> schoolList) {
        return Teacher.builder()
                .name("姚期智")
                .age(50)
                .schoolList(schoolList)
                .build();
    }

    private SchoolLicense createSchoolLicense() {
        return SchoolLicense.builder()
                .number("124535354C34X")
                .remark("自强不息 厚德载物")
                .build();
    }

    private void checkSchool(School school) {
        assertThat(school.getName(),  equalTo("清华大学"));
        assertThat(school.getBuildDate(), notNullValue());
        assertThat(school.getType(), equalTo(School.TypeEnum.PUBLIC));
        assertThat(school.getScore(), equalTo(100));
        assertThat(school.getBudget(), equalTo(new BigDecimal("111323.2300")));
        assertThat(school.getAddress(), anyOf(
                hasProperty("code", equalTo("100084")),
                hasProperty("address", equalTo("中华人民共和国北京市海淀区清华园"))
        ));
        assertThat(school.getAdditionalInfo(), hasKey("Teacher Count"));
        assertThat(school.getEvaluate(), anyOf(
                hasProperty("grade", equalTo(1)),
                hasProperty("description", equalTo("GREAT!"))
        ));
        assertThat(school.getLeadershipInformationList().get(0).get("Principal"), equalTo("王希勤"));
        assertThat(school.getAwardsSet().iterator().next().get("c"), equalTo("C9联盟"));
        assertThat(school.getScoreList(), hasItem(99.9f));
    }

    private void checkStudent(Student student) {
        assertThat(student.getName(),  equalTo("Rick.Xu"));
        assertThat(student.getGrade(), equalTo(1));
        assertThat(student.getSex(), equalTo(Student.SexEnum.MALE));
    }

    private void checkTeacher(Teacher teacher) {
        assertThat(teacher.getName(),  equalTo("姚期智"));
        assertThat(teacher.getAge(), equalTo(50));
    }

    private void checkSchoolLicense(SchoolLicense schoolLicense) {
        assertThat(schoolLicense.getNumber(),  equalTo("124535354C34X"));
        assertThat(schoolLicense.getRemark(), equalTo("自强不息 厚德载物"));
    }
}

Postman

SchoolController.java

@RestController
@RequestMapping("schools")
@RequiredArgsConstructor
public class SchoolController {

    private final SchoolDAO schoolDAO;

    @PostMapping
    public School save(@RequestBody School school) {
        schoolDAO.insert(school);
        return schoolDAO.selectById(school.getId()).get();
    }
}


curl -X POST \
  http://localhost:8080/schools \
  -H 'Content-Type: application/json' \
  -H 'Postman-Token: 468b3efe-3014-4f6c-bb28-8cd5905e309e' \
  -H 'cache-control: no-cache' \
  -d '{
    "name": "北京大学",
    "buildDate": [2022, 5, 1],
    "type": "PUBLIC",
    "budget": 111323.2300,
    "score": 100,
    "address": {
        "code": "100084",
        "detail": "中华人民共和国北京市海淀区清华园"
    },
    "additionalInfo": {
        "Teacher Count": 3641
    },
    "leadershipInformationList": [{
        "Principal": "王希勤"
    }],
    "awardsSet": [{
        "c": "C9联盟"
    }],
    "scoreList": [99.9, 98.0, 100.0],
    "evaluate": {
        "grade": 1,
        "description": "'\''GREAT!'\''"
    }
}'

测试仓库

https://github.com/jkxyx205/sharp-project/tree/master/sharp-demo