SpringBoot-16-Spring-Data-JPA的多数据源配置

在这之前我们以及介绍了

使用JdbcTemplate链接Mysql数据库

JdbcTemplate多数据源配置

Spring-Data-Jpa的基本使用

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方法。