【Java】SpringBoot整合多数据源JdbcTemplate、Mybatis、Jpa

原创

瑞新的博客 2021-12-01 11:19:14 ©著作权

文章标签 vue.js javascript ecmascript spring sql 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者瑞新的博客的原创作品，请联系作者获取转载授权，否则将追究法律责任

SpringBoot 整合 JdbcTemplate 多数据源

pom

<!--        引入阿里的数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>

application.properties

#快捷键alt拉动一列添加one，此时自动配置失效
#数据源one
spring.datasource.one.url=jdbc:mysql://localhost:3306/db_jdbctemplate
spring.datasource.one.username=root
spring.datasource.one.password=rootroot
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
#数据源two
spring.datasource.two.url=jdbc:mysql://localhost:3306/db_jdbctemplate2
spring.datasource.two.username=root
spring.datasource.two.password=rootroot
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource

config

DataSourceConfig.java

package com.bennyrhys.jdbctemplate2.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * 指定配置文件对应的不同数据源
 */
@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    DataSource dsOne(){
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dsTwo(){
        return DruidDataSourceBuilder.create().build();
    }
}

JdbcTemplateConfig.java

package com.bennyrhys.jdbctemplate2.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class JdbcTemplateConfig {
    //@Qualifier("dsOne")指明要注入的bean，后面的参数名无所谓
    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dsOne){
        return new JdbcTemplate(dsOne);
    }
    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dsTwo){
        return new JdbcTemplate(dsTwo);
    }
}

bean

public class User {
    private Integer id;
    private String username;
    private String address;

test

package com.bennyrhys.jdbctemplate2;

import com.bennyrhys.jdbctemplate2.bean.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import java.util.List;

@SpringBootTest
class  Jdbctemplate2ApplicationTests {
    @Autowired //是按照类型查找的，但现在有两个template类型（会报错，需要结合）
    @Qualifier("jdbcTemplateOne")
    JdbcTemplate jdbcTemplateOne;

    @Resource(name = "jdbcTemplateTwo")
    JdbcTemplate jdbcTemplateTwo;

    @Test
    void contextLoads() {
        List<User> list1 = jdbcTemplateOne.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list1);
        List<User> list2 = jdbcTemplateTwo.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list2);

    }

}

控制台输出:

[User{id=2, username=‘bennyrhys’, address=‘jilin’}, User{id=3, username=‘bennyrhys’, address=‘lanzou’}]
2020-01-19 22:39:03.408 INFO 6114 — [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-2} inited
[User{id=1, username=‘zhangsan’, address=‘通化’}]

SpringBoot整合Mybatis多数据源

现在很少用，一般用中间件MyCat整合或者jdbctemplate的同源

pom.xml

<!--修改版本信息-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.16</version>
        </dependency>
<!--        引入阿里数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

application.properties

#数据源1
spring.datasource.one.url=jdbc:mysql://localhost:3306/db_jdbctemplate
spring.datasource.one.username=root
spring.datasource.one.password=rootroot
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
#数据源2
spring.datasource.two.url=jdbc:mysql://localhost:3306/db_jdbctemplate2
spring.datasource.two.username=root
spring.datasource.two.password=rootroot
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource

com.bennyrhys.mybatis2.bean.User

public class User {    private Integer id;    private String username;    private String address;

config

DataSourceConfig.java

package com.bennyrhys.mybatis2.config;import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/** * 数据源的配置 */@Configurationpublic class DataSourceConfig {    @Bean    @ConfigurationProperties(prefix = "spring.datasource.one")    DataSource dsOne(){        return DruidDataSourceBuilder.create().build();    }    @Bean    @ConfigurationProperties(prefix = "spring.datasource.two")    DataSource dsTwo(){        return DruidDataSourceBuilder.create().build();    }}

MyBatisConfigOne.java

package com.bennyrhys.mybatis2.config;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionTemplate;import org.mybatis.spring.annotation.MapperScan;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.annotation.Resource;import javax.sql.DataSource;@Configuration@MapperScan(basePackages = "com.bennyrhys.mybatis2.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1", sqlSessionTemplateRef = "sessionTemplate1")public class MyBatisConfigOne {    @Resource(name = "dsOne")    DataSource dsOne;    @Bean    SqlSessionFactory sqlSessionFactory1(){        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();        try {            bean.setDataSource(dsOne);            return bean.getObject();        } catch (Exception e) {            e.printStackTrace();        }        return null;    }    @Bean    SqlSessionTemplate sessionTemplate1(){        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory1());        return template;    }}

MyBatisConfigTwo.java

package com.bennyrhys.mybatis2.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.bennyrhys.mybatis2.mapper2", sqlSessionFactoryRef = "sqlSessionFactory2", sqlSessionTemplateRef = "sessionTemplate2")
public class MyBatisConfigTwo {
    @Resource(name = "dsTwo")
    DataSource dsTwo;

    @Bean
    SqlSessionFactory sqlSessionFactory2(){
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        try {
            bean.setDataSource(dsTwo);
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    @Bean
    SqlSessionTemplate sessionTemplate2(){
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory2());
        return template;
    }

}

mapper1

UserMapper1.java

package com.bennyrhys.mybatis2.mapper1;

import com.bennyrhys.mybatis2.bean.User;

import java.util.List;

public interface UserMapper1 {
    List<User> getAllUsers();
}

UserMapper1.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="com.bennyrhys.mybatis2.mapper1.UserMapper1">    <select id="getAllUsers" resultType="com.bennyrhys.mybatis2.bean.User">        select * from user;    </select></mapper>

mapper2

UserMapper2

package com.bennyrhys.mybatis2.mapper2;

import com.bennyrhys.mybatis2.bean.User;

import java.util.List;

public interface UserMapper2 {
    List<User> getAllUser();
}

UserMapper2.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bennyrhys.mybatis2.mapper2.UserMapper2">
    <select id="getAllUser" resultType="com.bennyrhys.mybatis2.bean.User">
        select * from user;
    </select>
</mapper>

Pom.xml 修改访问路径

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes><include>**/*.xml</include></includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
        </resource>
    </resources>

test

package com.bennyrhys.mybatis2;

import com.bennyrhys.mybatis2.bean.User;
import com.bennyrhys.mybatis2.mapper1.UserMapper1;
import com.bennyrhys.mybatis2.mapper2.UserMapper2;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatis2ApplicationTests {
    @Autowired
    UserMapper1 userMapper1;
    @Autowired
    UserMapper2 userMapper2;

//记得启动之前，要声明mapper.xml位置，pom中定义

    /**
     * [User{id=2, username='bennyrhys', address='jilin'}, User{id=3, username='bennyrhys', address='lanzou'}]
     * 2020-01-20 13:30:35.558  INFO 1927 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} inited
     * [User{id=1, username='zhangsan', address='通化'}]
      */
    @Test
    void contextLoads() {
        List<User> list1 = userMapper1.getAllUsers();
        System.out.println(list1);

        List<User> list2 = userMapper2.getAllUser();
        System.out.println(list2);
    }

}

SpringBoot整合Jpa多数据源

模板选择web、mysql driver、jpa

pom.xml

<!--修改mysql版本-->        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <scope>runtime</scope>            <version>8.0.16</version>        </dependency><!--        添加阿里数据源-->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid-spring-boot-starter</artifactId>            <version>1.1.10</version>        </dependency>

application.properties

#配置数据源spring.datasource.one.url=jdbc:mysql://localhost:3306/db_jdbctemplatespring.datasource.one.username=rootspring.datasource.one.password=rootrootspring.datasource.one.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.two.url=jdbc:mysql://localhost:3306/db_jdbctemplate2spring.datasource.two.username=rootspring.datasource.two.password=rootrootspring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource#配置jpa,前面配置定义了propertiesspring.jpa.properties.show-sql=truespring.jpa.properties.hibernate.ddl-auto=updatespring.jpa.properties.database=mysqlspring.jpa.properties.database-platform=mysqlspring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

Book

@Entity(name = "t_book")public class Book {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Integer id;    private String name;    private String author;

config

DataSourceConfig

package com.bennyrhys.jpa2.config;import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.Primary;import javax.sql.DataSource;@Configurationpublic class DataSourceConfig {    @Bean    @Primary //优先使用，特色    @ConfigurationProperties(prefix = "spring.datasource.one")    DataSource dsOne(){        return  DruidDataSourceBuilder.create().build();    }    @Bean    @ConfigurationProperties(prefix = "spring.datasource.two")    DataSource dsTwo(){        return  DruidDataSourceBuilder.create().build();    }}

JpaConfig1

package com.bennyrhys.jpa2.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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 javax.sql.DataSource;

@Configuration
//JPA相关扫描dao
@EnableJpaRepositories(basePackages = "com.bennyrhys.jpa2.dao1",
        entityManagerFactoryRef = "localContainerEntityManagerFactoryBean1",
        transactionManagerRef = "platformTransactionManager1")
public class JpaConfig1 {
    @Autowired
    @Qualifier("dsOne")
    DataSource dsOne;

    @Autowired  //加载properties的jpa
    JpaProperties jpaProperties;

    @Bean
    @Primary //多实例，优先使用
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean1(EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsOne)
                .properties(jpaProperties.getProperties())
                .persistenceUnit("pu1")
                .packages("com.bennyrhys.jpa2.bean")
                .build();
    }
//    事务
    @Bean
    PlatformTransactionManager platformTransactionManager1(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean1(builder).getObject());
    }
}

JpaConfig2

package com.bennyrhys.jpa2.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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 javax.sql.DataSource;

@Configuration
//JPA相关扫描dao
@EnableJpaRepositories(basePackages = "com.bennyrhys.jpa2.dao2",
        entityManagerFactoryRef = "localContainerEntityManagerFactoryBean2",
        transactionManagerRef = "platformTransactionManager2")
public class JpaConfig2 {
    @Autowired
    @Qualifier("dsTwo")
    DataSource dsTwo;

    @Autowired  //加载properties的jpa
    JpaProperties jpaProperties;

    @Bean
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean2(EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsTwo)
                .properties(jpaProperties.getProperties())
                .persistenceUnit("pu2")
                .packages("com.bennyrhys.jpa2.bean")
                .build();
    }
//    事务
    @Bean
    PlatformTransactionManager platformTransactionManager2(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean2(builder).getObject());
    }
}

dao1

BookDao1

package com.bennyrhys.jpa2.dao1;

import com.bennyrhys.jpa2.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;

public interface BookDao1 extends JpaRepository<Book,Integer> {
}

dao2

BookDao2

package com.bennyrhys.jpa2.dao2;

import com.bennyrhys.jpa2.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;

public interface BookDao2 extends JpaRepository<Book,Integer> {
}

Test

package com.bennyrhys.jpa2;

import com.bennyrhys.jpa2.bean.Book;
import com.bennyrhys.jpa2.dao1.BookDao1;
import com.bennyrhys.jpa2.dao2.BookDao2;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Jpa2ApplicationTests {
    @Autowired
    BookDao1 dao1;
    @Autowired
    BookDao2 dao2;

    @Test
    void contextLoads() {
        List<Book> all = dao1.findAll();
        System.out.println(all);
        List<Book> all1 = dao2.findAll();
        System.out.println(all1);
    }

}