SpringBoot-17-Spring-Data-JPA的多数据源配置
在这之前我们以及介绍了
Spring-Data-Jpa的基本使用https://mp.weixin.qq.com/s/YNtcO-DYR8GbkhN7KXQw4w
看过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方法。