Spring Boot 2.x 中使用 H2数据库

环境信息

环境信息： OS：Win10 Jdk：JavaSE 8 Ide：Idea Ultimate Spring Boot：2.1.7.RELEASE

2.创建Web项目

Idea集成了Spring Initializr，新建项目：

• 1.选择Spring Initializr

• 2.填写基本信息
  
• 3.选择需要的工具
  选择Web和Spring Data MongoDB等必要依赖：

• 4.确认完成

忽略我的其他项目：

3.创建数据库&表

3.1 创建数据库

手动或命令创建数据库studentService：

3.2 创建数据表

右键数据库，打开Open Shell执行Js脚本：

• 1.创建表

var names = db.getCollectionNames();
// -- Create Collections --
if (!names.includes('students')) {
    db.createCollection("students", {});
    print("create collection students");
}
if (!names.includes('scores')) {
    db.createCollection("scores", {});
    print("create collection scores");
}

• 2.创建表索引

// -- Create students indexes --
var studentsIndexNames = db.userRoles.getIndexes().map(function (i) {
    return i.name
});
if (!studentsIndexNames.includes('unique_name_address')) {
    db.students.createIndex({
        "name": 1,
        "address": 1
    }, {
        name: "unique_name_address",
        background: true,
        unique: true
    });
    print('create students index unique_name_address');
}
// -- Create scores indexes --
var scoresIndexNames = db.scores.getIndexes().map(function (i) {
    return i.name
});
if (!scoresIndexNames.includes('unique_studentId_subject')) {
    db.scores.createIndex({
        "studentId": 1,
        "subject": 1
    }, {
        name: "unique_studentId_subject",
        background: true,
        unique: true
    });
    print('create scores index unique_studentId_subject');
}

3.3 插入初始化数据

var studentsResult = db.students.findOne({"name": "Even"});
if (studentsResult == null) {
    db.students.insert([  /* 1 */
                           {
                               "name" : "Even",
                               "age" : 9.0,
                               "sex" : "Male",
                               "address" : "Xian",
                               "hobbies" : [
                                   "YuWen",
                                   "English"
                               ]
                           },
                           /* 2 */
                           {
                               "name" : "Weison",
                               "age" : 10.0,
                               "sex" : "Male",
                               "address" : "Henan",
                               "hobbies" : [
                                   "Wuli",
                                   "English"
                               ]
                           },
                           /* 3 */
                           {
                               "name" : "Angule",
                               "age" : 13,
                               "sex" : "Female",
                               "hobbies" : [
                                   "YuWen",
                                   "English"
                               ],
                               "address" : "Chengdu",
                               "_class" : "com.wxx.sbm.domain.Student"
                           }]);
}
var scoresResult = db.scores.findOne({"studentName": "Even"});
if (scoresResult == null) {
    db.scores.insert([/* 1 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f09",
                          "subject" : "YuWen",
                          "studentName" : "Weison",
                          "subjectScore" : 90
                      },
                      /* 2 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f09",
                          "subject" : "English",
                          "studentName" : "Weison",
                          "subjectScore" : 98
                      },
                      /* 3 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f09",
                          "subject" : "ShuXue",
                          "studentName" : "Weison",
                          "subjectScore" : 59
                      },
                      /* 4 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f08",
                          "subject" : "YuWen",
                          "studentName" : "Even",
                          "subjectScore" : 90
                      },
                      /* 5 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f08",
                          "subject" : "English",
                          "studentName" : "Even",
                          "subjectScore" : 98
                      },
                      /* 6 */
                      {
                          "studentId" : "5d48dc820fd614486f1c1f08",
                          "subject" : "ShuXue",
                          "studentName" : "Even",
                          "subjectScore" : 59
                      },
                      /* 7 */
                      {
                          "studentId" : "5d48f09b4cf1451aad930f54",
                          "subject" : "YuWen",
                          "studentName" : "Angule",
                          "subjectScore" : 90
                      },
                      /* 8 */
                      {
                          "studentId" : "5d48f09b4cf1451aad930f54",
                          "subject" : "English",
                          "studentName" : "Angule",
                          "subjectScore" : 98
                      },
                      /* 9 */
                      {
                          "studentId" : "5d48f09b4cf1451aad930f54",
                          "subject" : "ShuXue",
                          "studentName" : "Angule",
                          "subjectScore" : 59
                      }]);
}

预览数据：

4.集成Spring Data MongoDB

4.1 配置MongoDB链接信息

application-dev.yml：

spring:
  data:
    mongodb:
      database: studentService
      host: localhost
      port: 27017

4.2 创建Domain

Student:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Document(collection = "students")
public class Student {
    @Id
    @JsonSerialize(using = ObjectIdSerializer.class)
    private ObjectId id;
    private String name;
    private Integer age;
    private String sex;
    private List<String> hobbies;
    private String address;
}

Score:

@Data
@Builder
@Document(collection = "scores")
public class Score {
    @Id
    @JsonSerialize(using = ObjectIdSerializer.class)
    private ObjectId id;
    //学生编号
    @JsonSerialize(using = ObjectIdSerializer.class)
    private ObjectId studentId;
    private String studentName;
    //科目
    private String subject;
    //科目成绩
    private Integer subjectScore;
}

StudentScore:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class StudentScore {
    private String name;
    private Integer age;
    private String sex;
    private Score score;
}

StudentScores:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class StudentScores {
    private String name;
    private Integer age;
    private String sex;
    private List<Score> scoreList;
}

4.3 创建StudentRepository

StudentRepository 继承自 CrudRepository，可以通过方法名映射来定义方法：

public interface StudentRepository extends CrudRepository<Student, ObjectId> {
    public Student findStudentByName(String name);
    public Student findStudentByNameAndSex(String name, String sex);
    public Student findStudentByNameIn(List<String> name);
    public Integer deleteStudentByName(String name);
}

方法名映射，参考：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

4.4 创建CustomizedStudentRepository

当方法名映射无法满足需求的时候，可以自定义Repository，通过调用MongoTemplate的API来实现复杂查询、多表关联查询等：

@Repository
public class CustomizedStudentRepositoryImpl implements CustomizedStudentRepository {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Override
    public StudentScore findStudentScoreByName(String name) {
        return null;
    }
    /**
     * 查找大于"10"岁 性别是"男" 并且 爱好"语文"和"英语"的 童鞋们
     *
     * @return
     */
    public List<Student> findStudentListByAgeAndSexAndHobbies() {
        Query query = query(where("age").gte(10)
          .and("sex").is("Male")
          .orOperator(where("hobbies").is("YuWen"),
            where("hobbies").is("English")));
        List<Student> students = mongoTemplate.find(query, Student.class, "students");
        return students;
    }
    /**
     * 查询所有童鞋和他们的各科成绩
     *
     * @return
     */
    public List<StudentScores> findStudentScoreList() {
        Aggregation aggregation = Aggregation.newAggregation(
          newLookup()
            .from("scores")
            .localField("name")
            .foreignField("studentName")
            .as("scores"),
          project()
            .andExpression("name").as("name")
            .andExpression("age").as("age")
            .andExpression("sex").as("sex")
            .andExpression("scores").as("scoreList"));
        AggregationResults<StudentScores> aggregationResults = mongoTemplate.aggregate(aggregation, "students", StudentScores.class);
        List<StudentScores> studentScores = aggregationResults.getMappedResults();
        return studentScores;
    }
    /**
     * 查询大于"10岁" 喜欢"语文"或"英语" 的"男孩" 的 "英语"成绩
     *
     * @return
     */
    public List<StudentScore> findStudentScores() {
        Aggregation aggregation = Aggregation.newAggregation(
          newLookup()
            .from("scores")
            .localField("name")
            .foreignField("studentName")
            .as("scores"),
          match(where("age").gte(10)
            .and("sex").is("Male")
            //.and("scores.subjectScore").gt(60) 这里不生效
            .orOperator(where("hobbies").is("YuWen"), where("hobbies").is("English"))),
          project()
            .andExpression("name").as("name")
            .andExpression("age").as("age")
            .andExpression("sex").as("sex")
            .andExpression("scores").as("score"),
          unwind("score"),
          match(where("score.subject").is("English")));
        AggregationResults<StudentScore> aggregationResults = mongoTemplate.aggregate(aggregation, "students", StudentScore.class);
        List<StudentScore> studentScores = aggregationResults.getMappedResults();
        return studentScores;
    }
}

定制化Repository，参考：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behavior

4.5 创建Service

@Service
public class StudentService {
    @Autowired
    private StudentRepository studentRepository;
    @Autowired
    private CustomizedStudentRepository customizedStudentRepository;
    public Student addStudent(Student student) {
        return studentRepository.save(student);
    }
    public Integer deleteStudentByName(String name) {
        return studentRepository.deleteStudentByName(name);
    }
    public Student updateStudent(Student student) {
        Student studentByName = studentRepository.findStudentByName(student.getName());
        student.setId(studentByName.getId());
        Student saveStudent = studentRepository.save(student);
        return saveStudent;
    }
    public Student findStudentById(ObjectId id) {
        Optional<Student> studentOptional = studentRepository.findById(id);
        return studentOptional.orElseGet(Student::new);
    }
    public Student findStudentByNameAndSex(String name, String sex) {
        Student student = studentRepository.findStudentByNameAndSex(name, sex);
        return student;
    }
    public List<Student> findStudents() {
        List<Student> students = (List) studentRepository.findAll();
        return students;
    }
    public List<Student> findStudentsById(List<ObjectId> ids) {
        List<Student> students = (List) studentRepository.findAllById(ids);
        return students;
    }
    public List<Student> findStudentListByAgeAndSexAndHobbies() {
        List<Student> studentList = customizedStudentRepository.findStudentListByAgeAndSexAndHobbies();
        return studentList;
    }
    public List<StudentScores> findStudentScoreList() {
        List<StudentScores> studentScoreList = customizedStudentRepository.findStudentScoreList();
        return studentScoreList;
    }
    public List<StudentScore> findStudentScores() {
        List<StudentScore> studentScoreList = customizedStudentRepository.findStudentScores();
        return studentScoreList;
    }
}

4.6 创建Controller

@RestController
public class StudentController {
    @Autowired
    private StudentService studentService;
    @GetMapping("/students")
    public List<Student> getStudentList() {
        List<Student> students = studentService.findStudents();
        return students;
    }
    @PostMapping("/students")
    public Student saveStudent(@RequestBody Student student) {
        Student addStudent = studentService.addStudent(student);
        return addStudent;
    }
    @DeleteMapping("/students/{name}")
    public Integer deleteStudentByName(@PathVariable String name) {
        Integer deleteNum = studentService.deleteStudentByName(name);
        return deleteNum;
    }
    @PutMapping("/students")
    public Student updateStudent(@RequestBody Student student) {
        Student updateStudent = studentService.updateStudent(student);
        return updateStudent;
    }
    @GetMapping("/students/list")
    public List<Student> getStudentListByAgeAndSexAndHobbies() {
        List<Student> studentList = studentService.findStudentListByAgeAndSexAndHobbies();
        return studentList;
    }
    @GetMapping("/students/scores")
    public List<StudentScores> getStudentScoresList() {
        List<StudentScores> studentList = studentService.findStudentScoreList();
        return studentList;
    }
    @GetMapping("/students/score")
    public List<StudentScore> getStudentScoreList() {
        List<StudentScore> studentScores = studentService.findStudentScores();
        return studentScores;
    }
}

两个比较复杂的接口返回：
/students/scores：

/students/score：

代码 —> https://github.com/WeisonWei/springboot-aggregation/tree/master/springboot-mvc-mongodb