1. 配置双数据源
首先，确保你的项目中有两个数据库，分别配置两套 thxi-take 和 new_pipayShop，以下是一个典型的双数据库配置。
server:
port: 9111
spring:
application:
name: CONNECTION
datasource:
master:
driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
jdbc-url: jdbc:mysql://localhost:3306/thxi-take?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username: pyq
password: 123456
type: com.zaxxer.hikari.HikariDataSource
type-aliases-package: com.atguigu.spzx.model.entity
mapper-locations: classpath:mapper/pishop/*.xml
slave:
driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
jdbc-url: jdbc:mysql://111.230.16.231:3306/new_pipayShop?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username: new_pipayShop
password: TEZfswirDJzdc8NJ
type: com.zaxxer.hikari.HikariDataSource
type-aliases-package: com.atguigu.spzx.model.entity
mapper-locations: classpath:mapper/user/*.xml
然后是文件结构
然后重写两个数据库的配置文件
db1
package com.atguigu.spzx.manager.config;/*
*classNameLove HURTS
*Desciption
*@Author yq
*＠Create 2023/11/30
*@Version 1.0
*/
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.atguigu.spzx.manager.mapper.user", sqlSessionTemplateRef = "db1SqlSessionTemplate")
//此处的basePackages指向的是你存放数据库db1的mapper的包
public class DataSource1Config {
@Bean(name = "db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")//指向yml配置文件中的数据库配置
@Primary //主库加这个注解，修改优先权，表示发现相同类型bean，优先使用该方法。
public DataSource dbDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db1SqlSessionFactory")
@Primary
public SqlSessionFactory dbSqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//这个的getResources指向的是你的mapper.xml文件，相当于在yml中配置的mapper-locations，此处配置了yml中就不用配置，或者说不会读取yml中的该配置。
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*.xml"));
// 指定驼峰转换
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
//分页插件
Interceptor[] plugins = new Interceptor[]{new PageInterceptor()};
bean.setPlugins(plugins);
return bean.getObject();
}
@Bean(name = "db1TransactionManager")
@Primary
public DataSourceTransactionManager dbTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "db1SqlSessionTemplate")
@Primary
public SqlSessionTemplate dbSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
db2
`package com.atguigu.spzx.manager.config;/*
*classNameLove HURTS
*Desciption
*@Author yq
*＠Create 2023/11/30
*@Version 1.0
*/
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.atguigu.spzx.manager.mapper.pishop", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DataSource2Config {
@Bean(name = "db2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource dbDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db2SqlSessionFactory")
public SqlSessionFactory dbSqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*.xml"));
// 指定驼峰转换
bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
Interceptor[] plugins = new Interceptor[]{new PageInterceptor()};
bean.setPlugins(plugins);
return bean.getObject();
}
@Bean(name = "db2TransactionManager")
public DataSourceTransactionManager dbTransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "db2SqlSessionTemplate")
public SqlSessionTemplate dbSqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
# 通过指向不同的数据库和mapper做到只需要写两个不同的mapper访问两个数据库的数据
要注意的是这样配置的缺陷就是有可能导致各种基于mybatis的插件失效需要手动配置，这里这个分页插件配置还是有点问题，做不到分页插件，它会查询所有的数据。所以需要手写分页
4. 总结
- 配置双数据库时，需要分别定义两个数据源配置类
PrimaryDataSourceConfig和SecondaryDataSourceConfig，并分别设置EntityManagerFactory和TransactionManager。 - 要打印 SQL 语句，可以设置
spring.jpa.show-sql=true，并且格式化输出 SQL 语句。 - 如果需要启用驼峰命名规则，可以根据使用的技术选择对应的配置方式（MyBatis 或 JPA）。
