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)
);
生成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