目录
多数据源一般是由mycat等中间件实现比较好,拥有读写分离,分库分表,备份等功能,但是在spring 中也可以配置多数据源
数据库准备
创建两个数据库 ,其中都有book表
JDBCTemplate 多数据源配置
JDBCTemplate 多数据源配置比较简单,分别配置两个DataSource,再由两个DataSource分别配置JDBCTemplate 即可
因为自动配置中有 @ConditionalOnMissingBean
注解 ,在我们手动提供了 JDBCTemplate 自动配置将不再注入了
项目依赖和 JDBCTemplate 相同
org.springframework.boot
spring-boot-starter-jdbc
org.springframework.boot
spring-boot-starter-web
com.alibaba
druid-spring-boot-starter
1.1.10
mysql
mysql-connector-java
runtime
编写配置文件
提供两个datasource
@Configuration
public class DataSourceConfig {
@Bean("datasource1")
@ConfigurationProperties("spring.datasource.one")
DataSource dataSourceOne(){
return DruidDataSourceBuilder.create().build();
}
@Bean("datasource2")
@ConfigurationProperties("spring.datasource.two")
DataSource dataSourceTwo(){
return DruidDataSourceBuilder.create().build();
}
}
@Configuration
public class JDBCTemplateConfig {
@Bean
JdbcTemplate jdbcTemplateOne(@Qualifier("datasource1") DataSource one){
return new JdbcTemplate(one);
}
@Bean
JdbcTemplate jdbcTemplateTwo(@Qualifier("datasource2") DataSource two){
return new JdbcTemplate(two);
}
}
public class Book {
public Integer id;
public String name;
public String author;
}
在application.properties 文件中提供配置
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.username=root
spring.datasource.one.password=root
spring.datasource.one.url=jdbc:mysql://localhost:3306/ch1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.username=root
spring.datasource.two.password=root
spring.datasource.two.url=jdbc:mysql://localhost:3306/ch2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
测试
@Controller
@ResponseBody
public class JDBCController {
@Resource(name = "jdbcTemplateOne")
JdbcTemplate jdbcTemplate1;
@Resource(name = "jdbcTemplateTwo")
JdbcTemplate jdbcTemplate2;
@RequestMapping("/test")
public Object test(){
List list1 = jdbcTemplate1.query("select * from book ", new BeanPropertyRowMapper<>(Book.class));
for (Book book : list1) {
System.out.println(book.getName());
}
List list2 = jdbcTemplate2.query("select * from book ", new BeanPropertyRowMapper<>(Book.class));
for (Book book : list2) {
System.out.println(book.getName());
}
list1.addAll(list2);
return list1;
}
}
mybatis多数据源
mybatis 多数据源 主要是提供多个DataSource ,sqlsessionfactory,sqlsessiontemplate 实例
编写配置文件
提供两个datasource ,与JDBCTemplate 相同
提供sqlsessionfactory,sqlsessiontemplate
@Configuration
@MapperScan(value = "org.gushiyu.jdbctemplate.mapper1",sqlSessionFactoryRef = "sqlSessionFactory1")
//MapperScan 表示对于mapper1 包下的mapper接口 使用 sqlSessionFactory1 实例
public class MyBatisConfigOne {
@Autowired
@Qualifier("datasource1")
DataSource dataSource1;
@Bean("sqlSessionFactory1")
SqlSessionFactory sqlSessionFactory1() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
factoryBean.setDataSource(dataSource1);
factoryBean.setMapperLocations(resourceResolver.getResources("classpath:mapping1/*Mapper.xml"));
return factoryBean.getObject();
}
@Bean("sqlSessionTemplate1")
SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlsf) throws Exception {
return new SqlSessionTemplate(sqlsf);
}
}
第二个
@Configuration
@MapperScan(value = "org.gushiyu.jdbctemplate.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2")
//MapperScan 表示对于mapper2 包下的mapper接口 使用 sqlSessionFactory2 实例
public class MyBatisConfigTwo {
@Autowired
@Qualifier("datasource2")
DataSource dataSource2;
@Bean("sqlSessionFactory2")
SqlSessionFactory sqlSessionFactory2() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
factoryBean.setDataSource(dataSource2);
factoryBean.setMapperLocations(resourceResolver.getResources("classpath:mapping2/*Mapper.xml"));
return factoryBean.getObject();
}
@Bean("sqlSessionTemplate2")
SqlSessionTemplate sqlSessionTemplate2(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlsf) throws Exception {
return new SqlSessionTemplate(sqlsf);
}
}
其他配置参考 mybatisAutoConfiguration
类的配置方式
根据配置建立 mapper1,mapper2 包 和 mapping1,mapping2 文件夹
使用
分别创建mapper 接口
public interface BookMapper1 {
List getAllBooks();
}
public interface BookMapper2 {
List getAllBooks();
}
分别创建xml映射文件
<?xml version="1.0" encoding="UTF-8" ?>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from book
<?xml version="1.0" encoding="UTF-8" ?>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from book
测试
@RestController
public class MyBatisController {
@Autowired
BookMapper1 bookMapper1;
@Autowired
BookMapper2 bookMapper2;
@RequestMapping("/test")
public Object test(){
List list1 = bookMapper1.getAllBooks();
List list2 = bookMapper2.getAllBooks();
list1.addAll(list2);
return list1;
}
}
Spring Data JPA 多数据源
Spring Data JPA 实现多数据源需要提供不同的 LocalContainerEntityManagerFactoryBean
和 PlatformTransactionManager
编写配置文件
依赖
org.springframework.boot
spring-boot-starter-data-jpa
org.springframework.boot
spring-boot-starter-web
com.alibaba
druid-spring-boot-starter
1.1.10
mysql
mysql-connector-java
runtime
application.properties
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.username=root
spring.datasource.one.password=root
spring.datasource.one.url=jdbc:mysql://localhost:3306/ch1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.username=root
spring.datasource.two.password=root
spring.datasource.two.url=jdbc:mysql://localhost:3306/ch2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
# 此后配置的时候要从 spring.jpa.properties 中获取响应的配置,所以此处的配置以spring.jpa.properties 为前缀
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.database=mysql
# 在项目启动时根据实体类更新数据库表(create,create-drop,validate,no)
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.show-sql=true
编写实体类
@Entity(name = "ch_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private String gender;
private Integer age;
//gettet and setter....
编写datasource 配置类
@Configuration
public class DataSourceConfig {
@Bean("datasource1")
@ConfigurationProperties("spring.datasource.one")
DataSource dataSourceOne(){
return DruidDataSourceBuilder.create().build();
}
@Bean("datasource2")
@ConfigurationProperties("spring.datasource.two")
DataSource dataSourceTwo(){
return DruidDataSourceBuilder.create().build();
}
}
编写jpa配置
JpaConfigOne
@Configuration
@EnableTransactionManagement //开启事务
@EnableJpaRepositories(basePackages = "org.gushiyu.jpa.dao1", //dao 位置
entityManagerFactoryRef = "entityManagerFactoryBean1", //使用哪个entityManagerFactory(实体类管理工厂)
transactionManagerRef = "platformTransactionManager1") //使用哪个transactionManager(事务管理器)
public class JpaConfigOne {
@Resource(name = "datasource1")
DataSource dataSource1;
@Autowired
JpaProperties jpaProperties;
@Bean("entityManagerFactoryBean1")
@Primary
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean1(
EntityManagerFactoryBuilder builder
) {
return builder.dataSource(dataSource1)
.properties(jpaProperties.getProperties())
.packages("org.gushiyu.jpa.model") //实体类位置
.persistenceUnit("pu1") //持久化单元名
.build();
}
@Bean("platformTransactionManager1")
@Primary
PlatformTransactionManager platformTransactionManager1(
EntityManagerFactoryBuilder builder
) {
LocalContainerEntityManagerFactoryBean factoryBean = entityManagerFactoryBean1(builder);
return new JpaTransactionManager(Objects.requireNonNull(factoryBean.getObject()));
}
}
JpaConfigTwo
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "org.gushiyu.jpa.dao2", //dao 位置
entityManagerFactoryRef = "entityManagerFactoryBean2", //使用哪个entityManagerFactory(实体类管理工厂)
transactionManagerRef = "platformTransactionManager2") //使用哪个transactionManager(事务管理器)
public class JpaConfigTwo {
@Resource(name = "datasource2")
DataSource dataSource2;
@Autowired
JpaProperties jpaProperties;
@Bean("entityManagerFactoryBean2")
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean2(
EntityManagerFactoryBuilder builder
){
return builder.dataSource(dataSource2)
.properties(jpaProperties.getProperties())
.packages("org.gushiyu.jpa.model") //实体类位置
.persistenceUnit("pu2") //持久化单元名
.build();
}
@Bean("platformTransactionManager2")
PlatformTransactionManager platformTransactionManager2(
EntityManagerFactoryBuilder builder
){
LocalContainerEntityManagerFactoryBean factoryBean = entityManagerFactoryBean2(builder);
return new JpaTransactionManager(Objects.requireNonNull(factoryBean.getObject()));
}
}
使用
创建dao(Repository)
public interface UserDao1 extends JpaRepository {
}
public interface UserDao2 extends JpaRepository {
}
测试
@RestController
public class JpaController {
@Autowired
UserDao1 userDao1;
@Autowired
UserDao2 userDao2;
@RequestMapping("/test")
public Object test(){
User user1 = new User();
user1.setName("张1");
user1.setGender("男");
user1.setAge(10);
userDao1.save(user1);
User user2 = new User();
user2.setName("张2");
user2.setGender("女");
user2.setAge(20);
userDao2.save(user2);
List list1 = userDao1.findAll();
List list2 = userDao2.findAll();
list1.addAll(list2);
return list1;
}
}