SpringBoot-17-Spring-Data-JPA的多数据源配置
在这之前我们以及介绍了
看过Jbdc多数据源配置的配置的都知道,既然我们现在介绍了Spring-Data-Jpa的操作了,那么现在我们就要介绍Spring-Data-Jpa的多数据操作了。
创建两个数据源的实体表
Student表的创建
create table `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生姓名', `sex` enum ('男', '女') DEFAULT '男' COMMENT '性别默认男', `age` tinyint unsigned default 1 comment '年龄', `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '注册手机号', `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '注册邮箱', `create_date` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0), `update_date` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0), `is_enabled` int(2) NULL DEFAULT 1 COMMENT '帐户是否可用(1 可用,0 删除用户)', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`) USING BTREE, UNIQUE INDEX `mobile`(`mobile`) USING BTREE, UNIQUE INDEX `email`(`email`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;
Teacher表的创建
create table `teacher` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '教师姓名', `sex` enum ('男', '女') DEFAULT '男' COMMENT '性别默认男', `age` tinyint unsigned default 1 comment '年龄', `course` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '上课科目', `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '手机号', `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '邮箱', `create_date` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0), `update_date` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0), `is_enabled` int(2) NULL DEFAULT 1 COMMENT '帐户是否可用(1 可用,0 删除用户)', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`) USING BTREE, UNIQUE INDEX `mobile`(`mobile`) USING BTREE, UNIQUE INDEX `email`(`email`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '教师表' ROW_FORMAT = Dynamic;
创建实体类
创建Student对应的实体类
@Table(name="student") @Entity @Data public class Student { @Id @GeneratedValue(strategy= GenerationType.IDENTITY) private Long id; private String name; private String sex; private int age; private String email; private String mobile; private int isEnabled; private Date createDate; private Date updateDate; }
创建teacher表对应的实体类
@Table(name="teacher") @Entity @Data public class Teacher { @Id @GeneratedValue(strategy= GenerationType.IDENTITY) private Long id; private String name; private String sex; private String course; private int age; private String email; private String mobile; private int isEnabled; private Date createDate; private Date updateDate; }
配置application.yml为多数据源配置
配置2个数据源,student数据源:mybatis数据库,teacher数据源:mysql数据库
server: port: 8899 spring: datasource: student: jdbc-url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSL=false username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver teacher: jdbc-url: jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSL=false username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver jpa: database-platform: org.hibernate.dialect.MySQL5InnoDBDialect show-sql: true database: mysql
持久化接口
创建Student表对应的接口层StudentService
public interface StudentService extends PagingAndSortingRepository<Student, Long> { /** * 获取所有在校学生信息 * @return */ @Query("from Student s where s.isEnabled=1") Slice<Student> getAllSutdents(Pageable pageable); }
创建Teacher表对应的接口层TeacherService
public interface TeacherService extends PagingAndSortingRepository<Teacher, Long> { /** * 获取所有在校老师信息 * @return */ @Query("from Teacher s where s.isEnabled=1") Slice<Teacher> getAllTeachers(Pageable pageable); }
JPA数据源配置
配置JPA的数据源,需要配置:
- DataSource数据源
- EntityManager 实体管理器
- EntityManagerFactoryBean 实体管理器工厂
- PlatformTransactionManager 事务管理器
student数据源配置如下:
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties; import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.annotation.Resource; import javax.persistence.EntityManager; import javax.sql.DataSource; import java.util.Map; @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef="entityManagerFactoryStudent", transactionManagerRef="transactionManagerStudent", basePackages= {"com.learn.springboot.entity.student"}) //换成你自己的Repository所在位置 public class JPAStudentConfig { @Resource private JpaProperties jpaProperties; @Resource private HibernateProperties hibernateProperties; /** * 主数据源默认使用Student * @return */ @Primary @Bean(name = "studentDataSource") @ConfigurationProperties(prefix="spring.datasource.student") //使用application.yml的primary数据源配置 public DataSource studentDataSource() { return DataSourceBuilder.create().build(); } /** * 数据管理器 * @param builder * @return */ @Primary @Bean(name = "entityManagerStudent") //primary实体管理器 public EntityManager entityManagerStudent(EntityManagerFactoryBuilder builder) { return entityManagerFactoryStudent(builder).getObject().createEntityManager(); } /** * 实体惯例工厂 * @param builder * @return */ @Primary @Bean(name = "entityManagerFactoryStudent") //primary实体工厂 public LocalContainerEntityManagerFactoryBean entityManagerFactoryStudent (EntityManagerFactoryBuilder builder) { Map<String,Object> properties = hibernateProperties.determineHibernateProperties( jpaProperties.getProperties(), new HibernateSettings()); return builder.dataSource(studentDataSource()) .properties(properties) //换成数据表对应实体类所在包 .packages("com.learn.springboot.entity.student") .persistenceUnit("primaryPersistenceUnit") .build(); } /** * 事务管理器 * @param builder * @return */ @Primary @Bean(name = "transactionManagerStudent") //primary事务管理器 public PlatformTransactionManager transactionManagerStudent(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactoryStudent(builder).getObject()); } }
teacher-数据源配置如下:
HibernateProperties hibernateProperties; @Bean(name = "teacherDataSource") //使用application.yml的teacher数据源配置 @ConfigurationProperties(prefix="spring.datasource.teacher") public DataSource teacherDataSource() { return DataSourceBuilder.create().build(); } //teacher实体管理器 @Bean(name = "entityManagerTeacher") public EntityManager entityManagerTeacher(EntityManagerFactoryBuilder builder) { return entityManagerFactoryTeacher(builder).getObject().createEntityManager(); } @Bean(name = "entityManagerFactoryTeacher") public LocalContainerEntityManagerFactoryBean entityManagerFactoryTeacher (EntityManagerFactoryBuilder builder) { Map<String,Object> properties = hibernateProperties.determineHibernateProperties( jpaProperties.getProperties(), new HibernateSettings()); return builder .dataSource(teacherDataSource()) .properties(properties) //换成数据表对应实体类所在包 .packages("com.learn.springboot.entity.teacher") .persistenceUnit("secondaryPersistenceUnit") .build(); } @Bean(name = "transactionManagerTeacher") PlatformTransactionManager transactionManagerTeacher(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactoryTeacher(builder).getObject()); } }
控制层的实现
Student控制层的实现
@Slf4j @RestController @RequestMapping("/student") public class StudentController { @Autowired private StudentService studentService; @GetMapping("getallstudents") public Slice<Student> getAllSutdents(PageNumber pageNumber){ if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } //分页查询 Slice<Student> createDate = studentService.getAllSutdents(PageRequest.of(pageNumber.getNumber(), pageNumber.getSize(), Sort.by("createDate"))); return createDate; } /** * 保存数据 * @param student * @return */ @PostMapping("create") public Student saveStudent(@RequestBody Student student) { //保存一个对象到数据库,insert studentService.save(student); return student; } @GetMapping("/{id}") public Student getSutdentInfo(@PathVariable("id") Long id) { Optional<Student> optional = studentService.findById(id); return optional.orElseGet(Student::new); } @GetMapping("/delete/{id}") public void deleteSutdent(@PathVariable("id") Long id) { //根据id删除1条数据库记录 studentService.deleteById(id); } @PostMapping("update") public @ResponseBody Student updatSutdent(@RequestBody Student student) { Optional<Student> optional = studentService.findById(student.getId()); Student stu = optional.orElseGet(Student::new); stu.setEmail(student.getEmail()); stu.setMobile(student.getEmail()); stu.setAge(student.getAge()); stu.setSex(student.getSex()); stu.setName(student.getName()); stu.setUpdateDate(new Date()); //保存一个对象到数据库,insert Student save = studentService.save(student); return save; } @GetMapping("getall") public Page<Student> getAll(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<Student> studentList =studentService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询student表的所有数据 return studentList; } @GetMapping("getAllSecond") public Page<Student> getAllSecond(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<Student> studentList =studentService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询student表的所有数据 return studentList; } }@Slf4j @RestController @RequestMapping("/student") public class StudentController { @Autowired private StudentPrimayService studentService; @Autowired private StudentSecondaryService studentSecondaryService; @GetMapping("getallstudents") public Slice<StudentPrimay> getAllSutdents(PageNumber pageNumber){ if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } //分页查询 Slice<StudentPrimay> createDate = studentService.getAllSutdents(PageRequest.of(pageNumber.getNumber(), pageNumber.getSize(), Sort.by("createDate"))); return createDate; } /** * 保存数据 * @param student * @return */ @PostMapping("create") public StudentPrimay saveStudent(@RequestBody StudentPrimay student) { //保存一个对象到数据库,insert studentService.save(student); return student; } @GetMapping("/{id}") public StudentPrimay getSutdentInfo(@PathVariable("id") Long id) { Optional<StudentPrimay> optional = studentService.findById(id); return optional.orElseGet(StudentPrimay::new); } @GetMapping("/delete/{id}") public void deleteSutdent(@PathVariable("id") Long id) { //根据id删除1条数据库记录 studentService.deleteById(id); } @PostMapping("update") public @ResponseBody StudentPrimay updatSutdent(@RequestBody StudentPrimay student) { Optional<StudentPrimay> optional = studentService.findById(student.getId()); StudentPrimay stu = optional.orElseGet(StudentPrimay::new); stu.setEmail(student.getEmail()); stu.setMobile(student.getEmail()); stu.setAge(student.getAge()); stu.setSex(student.getSex()); stu.setName(student.getName()); stu.setUpdateDate(new Date()); //保存一个对象到数据库,insert StudentPrimay save = studentService.save(student); return save; } @GetMapping("getall") public Page<StudentPrimay> getAll(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<StudentPrimay> studentList =studentService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询article表的所有数据 return studentList; } @GetMapping("getAllSecond") public Page<StudentSecondary> getAllSecond(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<StudentSecondary> studentList =studentSecondaryService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询article表的所有数据 return studentList; } }
Teacher控制层的实现
@Slf4j @RestController @RequestMapping("/teacher") public class TeacherController { @Autowired private TeacherService teacherService; @GetMapping("allteachers") public Slice<Teacher> getAllTeachers(PageNumber pageNumber){ if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } //分页查询 Slice<Teacher> createDate = teacherService.getAllTeachers(PageRequest.of(pageNumber.getNumber(), pageNumber.getSize(), Sort.by("createDate"))); return createDate; } /** * 保存数据 * @param teacher * @return */ @PostMapping("create") public Teacher saveTeacher(@RequestBody Teacher teacher) { //保存一个对象到数据库,insert teacherService.save(teacher); return teacher; } @GetMapping("/{id}") public Teacher getTeacherInfo(@PathVariable("id") Long id) { Optional<Teacher> optional = teacherService.findById(id); return optional.orElseGet(Teacher::new); } @GetMapping("/delete/{id}") public void deleteTeacher(@PathVariable("id") Long id) { //根据id删除1条数据库记录 teacherService.deleteById(id); } @PostMapping("update") public @ResponseBody Teacher updatTeacher(@RequestBody Teacher teacher) { Optional<Teacher> optional = teacherService.findById(teacher.getId()); Teacher tea = optional.orElseGet(Teacher::new); tea.setEmail(teacher.getEmail()); tea.setCourse(teacher.getCourse()); tea.setMobile(teacher.getEmail()); tea.setAge(teacher.getAge()); tea.setSex(teacher.getSex()); tea.setName(teacher.getName()); tea.setUpdateDate(new Date()); //保存一个对象到数据库,insert Teacher save = teacherService.save(tea); return save; } @GetMapping("getall") public Page<Teacher> getAll(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<Teacher> teachers =teacherService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询teacher表的所有数据 return teachers; } @GetMapping("getAllTeacher") public Page<Teacher> getAllTeacher(PageNumber pageNumber) { if(pageNumber.getNumber()==0){ pageNumber =new PageNumber(); pageNumber.setNumber(0); pageNumber.setSize(10); } Page<Teacher> teachers =teacherService.findAll(PageRequest.of(pageNumber.getNumber(),pageNumber.getSize())); //查询teacher表的所有数据 return teachers; } }
测试
使用postman分别测试
http://localhost:8899/teacher/create Post方法
http://localhost:8899/teacher/update Post方法
http://localhost:8899/student/getallstudents Get方法
分别测试了student和teacher的方法以及Get和Post方法。