spring-boot和JPA多数据源整合

爱被打了一巴掌 2022-01-23 03:03 362阅读 0赞

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

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

只是一套规范

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzc3l5eXk3_size_16_color_FFFFFF_t_70]

然后在依赖里，写一下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/> 
        </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类就可以运行下工程，它会自动在数据库里生成相对应的表

package com.liy.bean;
    
    import javax.persistence.*;
    
    //生成数据库表的关键注解，后面的name是表名
    @Entity(name = "book")
    public class Book { 
        //必须写个id
        @Id
       //也必须设置为主键，以及自增长
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
      
        //给他起别名，  设置约束什么的也是@Column注解里去设置
        @Column(name = "bookname")
        private String username;
    
        private String auther;
    
    
        public Book() {
            super();
        }
    
        public Book(String username, String auther) {
            this.username = username;
            this.auther = auther;
        }
    
    
    
        @Override
        protected Object clone() throws CloneNotSupportedException {
            return super.clone();
        }
    
        @Override
        protected void finalize() throws Throwable {
            super.finalize();
        }
    
        @Override
        public String toString() {
            return "Book{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", auther='" + auther + '\'' +
                    '}';
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getAuther() {
            return auther;
        }
    
        public void setAuther(String auther) {
            this.auther = auther;
        }
    }

然后写个区分两个数据的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包

![在这里插入图片描述][20190602000036180.png]

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());//当前页的数据
    
        }
    }

数据库里  
![在这里插入图片描述][20190602000058203.png]

在两个数据库里用两个表

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzc3l5eXk3_size_16_color_FFFFFF_t_70 1]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzc3l5eXk3_size_16_color_FFFFFF_t_70]: /images/20220123/88c0f5da35dd4eb3ae41b00562a0646c.png
[20190602000036180.png]: /images/20220123/8d1caad992334ed1b8468ff14b4e44ae.png
[20190602000058203.png]: /images/20220123/e23a5521967b40e1baa6eeafb59da4be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzc3l5eXk3_size_16_color_FFFFFF_t_70 1]: /images/20220123/1c90b4f4010e46dc95c8dbd455a76f00.png