spring-boot和JPA多数据源整合

今天弄一下spring-boot和jpa的多数据源整合

Jpa（Java Persistence API）Java持久化API，它是一套ORM规范

只是一套规范

Spring Boot中使用的Jpa实际上是Spring Data Jpa，Spring Data是Spring家族的一个子项目，用于简化SQL和NoSQL的访问，在Spring Data中，只要你的方法名称符合规范，它就知道你想干嘛，不需要自己再去写SQL。实际还是hibernate。

接下来我们简单来弄下，直接

在idea里创建spring-boot工程，勾选web里的web和sql里的JPA和mysql依赖

然后在依赖里，写一下mysql的版本，一般五点几就行了，再添加Druid依赖

依赖如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.liy</groupId>
    <artifactId>jpa</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jpa</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <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>
            <scope>runtime</scope>
            <version>5.1.27</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

然后写配置信息

application.properties

spring.datasource.one.username=root
spring.datasource.one.password=root
spring.datasource.one.url=jdbc:mysql:///again
spring.datasource.one.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.two.username=root
spring.datasource.two.password=root
spring.datasource.two.url=jdbc:mysql:///demo
spring.datasource.two.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource




#数据库平台
spring.jpa.properties.database-platform=mysql
#如果再次运行，检查表是否要更新
spring.jpa.properties.hibernate.ddl-auto=update
#sql信息打印
spring.jpa.properties.show-sql=true
#定义数据库的方言
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

spring.jpa.properties.database=mysql

写个bean类。写好bean类就可以运行下工程，它会自动在数据库里生成相对应的表

然后写个区分两个数据的DataSource配置类

DataSourceConfig

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    @Primary
    DataSource dsOne(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dsTwo(){
        return DruidDataSourceBuilder.create().build();
    }
}

然后写两个jpa的配置类，分别对应两个数据源

JpaConfig

@Configuration
@EnableJpaRepositories(basePackages = "com.liy.dao1",
        entityManagerFactoryRef = "localContainerEntityManagerFactoryBeanOne",
        transactionManagerRef = "platformTransactionManagerOne")
public class JpaConfig {

    @Autowired
    @Qualifier(value = "dsOne")
    DataSource dsOne;

    @Autowired
    JpaProperties jr;

    @Bean
    @Primary
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsOne)
                .properties(jr.getProperties())
                .packages("com.liy.bean")
                .persistenceUnit("pu1")
                .build();

    }

    @Bean
    @Primary
    PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = localContainerEntityManagerFactoryBeanOne(builder);
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean.getObject());
    }
}

JpaConfig1 ,其中代码基本一样，要在一个配置类中的两个方法都加上@Primary注解   ，我都加在上面那个了

@Configuration
@EnableJpaRepositories(basePackages = "com.liy.dao2",
        entityManagerFactoryRef = "localContainerEntityManagerFactoryBeanTwo",
        transactionManagerRef = "platformTransactionManagerTwo")
public class JpaConfig1 {

    @Autowired
    @Qualifier(value = "dsTwo")
    DataSource dsTwo;

    @Autowired
    JpaProperties jr;

    @Bean
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanTwo(EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsTwo)
                .properties(jr.getProperties())
                .packages("com.liy.bean")
                .persistenceUnit("pu2")
                .build();

    }

    @Bean
    PlatformTransactionManager platformTransactionManagerTwo(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = localContainerEntityManagerFactoryBeanTwo(builder);
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean.getObject());
    }
}

然后根据上面JpaConfig的扫描负责的包来写两个dao包

BookDao1

package com.liy.dao1;

import com.liy.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface BookDao1 extends JpaRepository<Book,Integer> {

    //方法名可以靠拼接，相当于自定义sql
    public List<Book> findBookByIdGreaterThanAndAndUsernameContains(Integer id,String usernames);

    @Query(value = "select count(*) from book",nativeQuery = true)
    public long TotalCount();

    //如果sql语句不是查询，那么必须加@Modifying注解
    //另外在对数据库进行DML(update,delete,insert)操作时，必须加上事物，也就是@Transactional
    //@Transactional记得不要导错包
    //import org.springframework.transaction.annotation.Transactional;
    @Query(value = "update book set auther=:auther  where id=:id",nativeQuery = true)
    @Modifying
    @Transactional
    public int update(@Param("id") int id,@Param("auther") String auther);

    @Query(value = "update book set bookname=:bookname  where id=:id",nativeQuery = true)
    @Modifying
    public int update1(@Param("id") int id ,@Param("bookname") String bookname);


}

BookDao2

package com.liy.dao2;

import com.liy.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

public interface BookDao2 extends JpaRepository<Book,Integer> {


    @Query(value = "update book set auther=:auther  where id=:id",nativeQuery = true)
    @Modifying
    @Transactional
    public int update1(@Param("id") int id,@Param("auther") String auther);



}

然后在spring-boot自带的测试类里进行测试

package com.liy;

import com.liy.bean.Book;
import com.liy.dao1.BookDao1;
import com.liy.dao2.BookDao2;
import com.liy.service.BookService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class JpaApplicationTests {

    @Autowired
    BookDao1 db1;

    @Autowired
    BookDao2 db2;


    @Test
    public void updateMany(){
        db1.update(3,"liy1");
        db2.update1(3,"liy1");
    }

    @Test
    public void TestFind() {

        List<Book> a = db1.findBookByIdGreaterThanAndAndUsernameContains(1, "a");
        System.out.println(a);
        long l = db1.TotalCount();
        System.out.println(l);
        db1.update(3,"aaaa");
    }


    @Test
    public void contextLoads() {
        Book book = new Book("老夫子", "afaf");
        db1.save(book);
    }

    @Test
    public void update(){
        Book book = new Book("老夫子", "老夫");
        book.setId(1);
        db1.saveAndFlush(book);
    }

    @Test
    public void find(){

        List<Book> all = db1.findAll(Sort.by(new Sort.Order(Sort.Direction.DESC, "id")));
        System.out.println(all);
    }

    @Test
    public void findPage(){
        PageRequest data = PageRequest.of(0, 2);
        System.out.println(data);
        Page<Book> page = db1.findAll(data);

        System.out.println(page.getNumberOfElements());//当前记录数
        System.out.println(page.getNumber());//
        System.out.println(page.getSize());//每页应该查到的记录数
        System.out.println(page.isLast());//是否是最后一页
        System.out.println(page.isFirst());//是否是第一页
        System.out.println(page.getTotalPages());//总页数
        System.out.println(page.getTotalElements());//总记录数
        System.out.println(page.getContent());//当前页的数据

    }
}

数据库里

在两个数据库里用两个表

第一个表是在spring-boot工程了写了个bean类，自动生成的

我为了方便，直接把上面的复制了，表结构，数据都复制，然后下面的修改下就可以了

显示运行了两条sql语句，然后再看下数据库里数据是否改变即可

这个打印sql语句的在application.properties里配置   我写了注释的