我们来看看SpringBoot如何整合多数据源的JPA。
话不多说,直接放代码。
项目结构
依赖
这值得一提的是这里使用数据库连接池,使用的是Druid,这里是Druid是使用的是com.alibaba.druid-spring-boot-starter,该starter是由第三方提供的,MyBatis和Druid依赖的命名和其他库的命名不太一样,但是它所支持的mysql是mysql8,这由于数据库版本的问题,所以我们需要锁定数据库版本。
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
</dependencies>
application.properties中配置多数据源基本信息和JPA的基本设置
#数据源1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.url=jdbc:mysql:///db
spring.datasource.one.username=root
spring.datasource.one.password=rootzsl
spring.datasource.one.driver-class-name=com.mysql.jdbc.Driver
#数据源2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.url=jdbc:mysql:///demo
spring.datasource.two.username=root
spring.datasource.two.password=rootzsl
spring.datasource.two.driver-class-name=com.mysql.jdbc.Driver
#Jpa配置
spring.jpa.properties.database=mysql
#数据库平台
spring.jpa.properties.database-platform=mysql
#在控制台打印SQL
spring.jpa.properties.show-sql=true
#每次启动项目时,数据库初始化策略
spring.jpa.properties.hibernate.ddl-auto=update
#指定默认的存储引擎为InnoDB
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
注意
在mysql中,默认情况下,自动创建表的时候会使用MyISAM做表的引擎,所以需要配置数据库方言为MySQL57Dialect,则使用InnoDB做表的引擎。也就是上面的最后一行
#指定默认的存储引擎为InnoDB
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.````
多数据源下配置的JPA的配置比单个数据源配置的JPA,Key中多了properties,
单个数据源下配置的JPA
# JPA配置
spring.jpa.database=mysql
# 在控制台打印SQL
spring.jpa.show-sql=true
# 数据库平台
spring.jpa.database-platform=mysql
# 每次启动项目时,数据库初始化策略
spring.jpa.hibernate.ddl-auto=update
# 指定默认的存储引擎为InnoDB
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
而多个数据源下的JPA
#Jpa配置
spring.jpa.properties.database=mysql
#数据库平台
spring.jpa.properties.database-platform=mysql
#在控制台打印SQL
spring.jpa.properties.show-sql=true
#每次启动项目时,数据库初始化策略
spring.jpa.properties.hibernate.ddl-auto=update
#指定默认的存储引擎为InnoDB
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
因为有多个数据源,所以我们要配置DataSource的配置类,把数据源1和数据源2在DataSource配置类中进行注册。
/**
* @author ZSL
* @ClassName DataSourceConfig
* @description
* @date 2019/8/10
*/
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.one")
@Primary//一定不能少,否则在项目启动时会出错,@Primary表示当某一个类存在多个实例时,优先使用哪个实例
DataSource dsOne() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.two")
DataSource dsTwo() {
return DruidDataSourceBuilder.create().build();
}
}
@Primary//一定不能少,否则在项目启动时会出错,@Primary表示当某一个类存在多个实例时,优先使用哪个实例
这的
@ConfigurationProperties(prefix = “spring.datasource.two”)
和application.properties中配置的数据源对应。
Jpa配置类,因为有两个数据源,所以有两个JPA配置类。
JpaConfigOne
/**
* @author ZSL
* @ClassName JpaConfigOne
* @description
* @date 2019/8/10
*
*
*@EnableJpaRepositories 需要指定这个JPA配置类对应的DAO包的所在。
*entityManagerFactoryRef:需要注册的bean的名称,LocalContainerEntityManagerFactoryBean
*transactionManagerRef:需要注册的bean的名称,platformTransactionManagerOne
*
*/
@Configuration
@EnableJpaRepositories(basePackages = "com.zsl.jpa.dao1",entityManagerFactoryRef ="localContainerEntityManagerFactoryBeanOne", transactionManagerRef ="platformTransactionManagerOne")
public class JpaConfigOne {
@Autowired
@Qualifier(value = "dsOne")//相当于@Resource,这里需要通过变量名来获取bean
DataSource dsOne;
/**
* 系统提供的一个实例,里边的数据就是我们在application.properties中配置的jpa相关的配置
*/
@Autowired
JpaProperties jpaProperties;
@Bean
@Primary
LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder) {
return builder.dataSource(dsOne)
.packages("com.zsl.jpa.pojo")//一定要指定包,这个数据源对应的实体类所在的位置
.properties(jpaProperties.getProperties())
.persistenceUnit("pu1")
.build();
}
@Bean
PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean factoryBeanOne = localContainerEntityManagerFactoryBeanOne(builder);
return new JpaTransactionManager(factoryBeanOne.getObject());
}
}
JpaConfigTwo
/**
* @author ZSL
* @ClassName JpaConfigOne
* @description
* @date 2019/8/10
*
* @EnableJpaRepositories 需要指定这个JPA配置类对应的DAO包的所在。
* entityManagerFactoryRef:需要注册的bean的名称,localContainerEntityManagerFactoryBeanTwo
* transactionManagerRef:需要注册的bean的名称,platformTransactionManagerTwo
*/
@Configuration
@EnableJpaRepositories(basePackages = "com.zsl.jpa.dao2",entityManagerFactoryRef="localContainerEntityManagerFactoryBeanTwo", transactionManagerRef ="platformTransactionManagerTwo")
public class JpaConfigTwo {
@Autowired
@Qualifier(value = "dsTwo")
DataSource dsTwo;
/**
* 系统提供的一个实例,里边的数据就是我们在application.properties中配置的jpa相关的配置
*/
@Autowired
JpaProperties jpaProperties;
@Bean
LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanTwo(EntityManagerFactoryBuilder builder) {
return builder.dataSource(dsTwo)
.packages("com.zsl.jpa.pojo")//一定要指定包,这个数据源对应的实体类所在的位置
.properties(jpaProperties.getProperties())
.persistenceUnit("pu2")
.build();
}
@Bean
PlatformTransactionManager platformTransactionManagerTwo(EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean factoryBeanTwo = localContainerEntityManagerFactoryBeanTwo(builder);
return new JpaTransactionManager(factoryBeanTwo.getObject());
}
}
Pojo
/**
* @author ZSL
* @ClassName Dept
* @description
* @date 2019/8/10
*/
@Entity(name = "dept")
public class Dept {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer deptno;
@Column(name = "dname")
private String dname;
@Column(name = "loc")
private String loc;
public Dept(Integer deptno, String dname, String loc) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public Dept() {
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
'}';
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
/**
* @author ZSL
* @ClassName User
* @description
* @date 2019/8/10
*/
@Entity(name = "t_user")//这是一个实体类,name指定表名
public class User {
@Id//主键id
/**
* 主键自增长,IDENTITY用于Mysql的主键自增长,SEQUENCE用于Oralc,序列自增长
*/
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
/**
* 默认都会根据属性名在表中生成相应的字段,字段名和属性名相同
* 也可以根据业务逻辑来配置字段的名称,长度,是否为空等
*/
@Column(name = "name")//指定字段名
private String name;
@Column(name = "age")//指定字段名
private Integer age;
public User(String name, Integer age) {
this.name = name;
this.age = age;
}
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
IUserDao1
/**
* @author ZSL
* @ClassName IUserDao
* @description
* @date 2019/8/10
*/
@Repository
public interface IUserDao1 extends JpaRepository<User,Integer> {
@Query(value = "SELECT * FROM t_user WHERE id > ?1 and name LIKE ?2",nativeQuery = true)
List<User> selUserLike(Integer id, String str);
@Query(value = "SELECT u FROM t_user u WHERE name = :name")
List<User> selUserByName(@Param("name") String name);
@Modifying
@Query("UPDATE t_user SET name = :name WHERE id = :id")
int updUser1(@Param("name") String name,@Param("id") Integer id);
}
IUserDao2
/**
* @author ZSL
* @ClassName IUserDao
* @description
* @date 2019/8/10
*/
@Repository
public interface IUserDao2 extends JpaRepository<Dept,Integer> {
@Query(value = "SELECT * FROM dept WHERE deptno > ?1 and dname LIKE ?2",nativeQuery = true)
List<Dept> selDeptLike(Integer deptno, String dname);
@Query(value = "SELECT d FROM dept d WHERE dname = :dname")
List<Dept> selDeptByName(@Param("dname") String dname);
@Modifying
@Query("UPDATE dept SET name = :dname WHERE deptno = :deptno")
int updDept2(@Param("dname") String dname, @Param("deptno") Integer deptno);
}
注意
如果要使用sql进行数据的更新等DML操作时,需要加上@Modifying,这里需要注意的是,要在Service层中调用该方法的方法上必须使用事务。
DeptService
/**
* @author ZSL
* @ClassName UserService
* @description
* @date 2019/8/10
*/
@Service
public class DeptService {
@Autowired
IUserDao2 userDao;
public List<Dept> selDeptLike(Integer id, String str){
return userDao.selDeptLike(id,str);
}
public List<Dept> selDeptByName(String name){
return userDao.selDeptByName(name);
}
@Transactional( propagation = Propagation.REQUIRES_NEW)
public int updDept2(String name,Integer id){
return userDao.updDept2(name,id);
}
}
UserService
/**
* @author ZSL
* @ClassName UserService
* @description
* @date 2019/8/10
*/
@Service
public class UserService {
@Autowired
IUserDao1 userDao;
public User updUser(User user){
return userDao.saveAndFlush(user);
}
public void delUser(User user){
userDao.delete(user);
}
public Integer delUserById(Integer id){
boolean flag = userDao.existsById(id);
if (flag == true){
userDao.deleteById(id);
return 1;
}else {
return 0;
}
}
public List<User> selUserLike(Integer id, String str){
return userDao.selUserLike(id,str);
}
public List<User> selUserByName(String name){
return userDao.selUserByName(name);
}
@Transactional( propagation = Propagation.REQUIRES_NEW)
public int updUser1(String name,Integer id){
return userDao.updUser1(name,id);
}
}
Controller
/**
* @author ZSL
* @ClassName UserController
* @description
* @date 2019/8/10
*/
@RestController
public class UserController {
@Autowired
private UserService userService;
@Autowired
private DeptService deptService;
@GetMapping("/uu")
public void updUser(User user){
user.setName("zsl");
user.setAge(111);
User user1 = userService.updUser(user);
System.out.println(user1);
}
@GetMapping("/du")
public void delUser(User user){
user.setId(1);
user.setName("zsl");
user.setAge(111);
userService.delUser(user);
}
@GetMapping("/dub")
public void delUserById(Integer id){
Integer id1 = userService.delUserById(id);
System.out.println(id1);
}
@GetMapping("/sul")
public void selUserLike(Integer id, String str){
System.out.println(userService.selUserLike(id,str));
}
@GetMapping("/subu")
public void selUserByName(String name){
System.out.println(userService.selUserByName(name));
}
@GetMapping("/updu")
public void updUser1(String name,Integer id){
System.out.println(userService.updUser1(name,id));
}
@GetMapping("/sdl")
public void selDeptLike(Integer deptno, String str){
System.out.println(deptService.selDeptLike(deptno,str));
}
@GetMapping("/sdb")
public void selDeptByName(String dname){
System.out.println(deptService.selDeptByName(dname));
}
@GetMapping("/ud")
public void updDept2(String dname, Integer deptno){
System.out.println(deptService.updDept2(dname,deptno));
}
}