SpringBoot__整合多数据源JPA

我们来看看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));
    }

}