springboot自定义配置双数据源,mybatis-plus分页插件
1、配置文件中添加配置实时库和历史库的配置
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
###############################数据库配置################################### ####################实时库配置 start######################### ## 数据库配置 emp.database.realtime.ip=192.169.1.21 emp.database.realtime.port=3306 emp.database.realtime.name=msg_new2 #数据库连接基本配置 spring.datasource.druid.realtime.url=jdbc:mysql://${emp.database.realtime.ip}:${emp.database.realtime.port}/${emp.database.realtime.name}?useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL spring.datasource.druid.realtime.username=root spring.datasource.druid.realtime.driver-class-name=com.mysql.jdbc.Driver #数据库密码加密并注入publicKey spring.datasource.druid.realtime.password=Cj7XmYjdXYN224PhUHYRcQBD5FhzgK7J3ONNO8MwEQ5U4j0u5PetAKd3HBjaSSIPsJ8yBlePsfPow4vpzUcNgg== realtime.public-key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIwNJmigq/vliDQEc82ggGKhSXL3XvWH8MG1Kfk4bsYID5hSWaitxPGY6FagZE2GeRM7vEo2D8tcEWz/jqUDkfUCAwEAAQ== spring.datasource.druid.realtime.connection-properties=config.decrypt=true;config.decrypt.key=${realtime.public-key};password=${spring.datasource.druid.realtime.password} spring.datasource.druid.realtime.filter.config.enabled=true #连接池配置 #初始化连接大小 spring.datasource.druid.realtime.initial-size=5 #最大连接数 spring.datasource.druid.realtime.max-active=100 #最小空闲连接数 spring.datasource.druid.realtime.min-idle=5 #获取连接最大等待时间(毫秒) spring.datasource.druid.realtime.max-wait=60000 #是否开启PSCache,mysql5.5及以上版本支持 spring.datasource.druid.realtime.pool-prepared-statements=true #指定每个连接上PSCache的大小,开启PSCache时,此配置必须大于0 spring.datasource.druid.realtime.max-pool-prepared-statement-per-connection-size=20 #检测连接是否有效的sql spring.datasource.druid.realtime.validation-query=SELECT 1 FROM DUAL #检测连接是否有效超时时间(毫秒) spring.datasource.druid.realtime.validation-query-timeout=60000 #申请连接时执行validationQuery检测连接是否有效 spring.datasource.druid.realtime.test-on-borrow=false #归还连接时执行validationQuery检测连接是否有效 spring.datasource.druid.realtime.test-on-return=false #申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效 spring.datasource.druid.realtime.test-while-idle=true #Destroy线程会检测连接的间隔时间,testWhileIdle的判断依据 spring.datasource.druid.realtime.time-between-eviction-runs-millis=60000 #Destory线程中如果检测到当前连接的最后活跃时间和当前时间的差值大于,minEvictableIdleTimeMillis,则关闭当前连接 spring.datasource.druid.realtime.min-evictable-idle-time-millis=100000 ###监控配置 # WebStatFilter配置 spring.datasource.druid.realtime.web-stat-filter.enabled=true spring.datasource.druid.realtime.web-stat-filter.url-pattern=/* spring.datasource.druid.realtime.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/* # StatViewServlet配置 spring.datasource.druid.realtime.stat-view-servlet.enabled=true spring.datasource.druid.realtime.stat-view-servlet.url-pattern=/druid/* spring.datasource.druid.realtime.stat-view-servlet.reset-enable=false #监控账号信息 spring.datasource.druid.realtime.stat-view-servlet.login-username=admin spring.datasource.druid.realtime.stat-view-servlet.login-password=admin #允许登录监控页面ip白名单,不配置即所有ip均是白名单 spring.datasource.druid.realtime.stat-view-servlet.allow= # 配置StatFilter spring.datasource.druid.realtime.filter.stat.db-type=mysql spring.datasource.druid.realtime.filter.stat.log-slow-sql=true spring.datasource.druid.realtime.filter.stat.slow-sql-millis=5000 # 配置WallFilter spring.datasource.druid.realtime.filter.wall.enabled=true spring.datasource.druid.realtime.filter.wall.db-type=mysql spring.datasource.druid.realtime.filter.wall.config.delete-allow=true spring.datasource.druid.realtime.filter.wall.config.drop-table-allow=false ####################实时库配置 end######################### ####################历史库配置 start######################### ## 数据库配置 emp.database.history.ip=192.169.1.21 emp.database.history.port=3306 emp.database.history.name=msg_new2 #数据库连接基本配置 spring.datasource.druid.history.url=jdbc:mysql://${emp.database.history.ip}:${emp.database.history.port}/${emp.database.history.name}?useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL spring.datasource.druid.history.username=root spring.datasource.druid.history.driver-class-name=com.mysql.jdbc.Driver #数据库密码加密并注入publicKey spring.datasource.druid.history.password=Cj7XmYjdXYN224PhUHYRcQBD5FhzgK7J3ONNO8MwEQ5U4j0u5PetAKd3HBjaSSIPsJ8yBlePsfPow4vpzUcNgg== history.public-key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIwNJmigq/vliDQEc82ggGKhSXL3XvWH8MG1Kfk4bsYID5hSWaitxPGY6FagZE2GeRM7vEo2D8tcEWz/jqUDkfUCAwEAAQ== spring.datasource.druid.history.connection-properties=config.decrypt=true;config.decrypt.key=${history.public-key};password=${spring.datasource.druid.history.password} spring.datasource.druid.history.filter.config.enabled=true #连接池配置 #初始化连接大小 spring.datasource.druid.history.initial-size=5 #最大连接数 spring.datasource.druid.history.max-active=100 #最小空闲连接数 spring.datasource.druid.history.min-idle=5 #获取连接最大等待时间(毫秒) spring.datasource.druid.history.max-wait=60000 #是否开启PSCache,mysql5.5及以上版本支持 spring.datasource.druid.history.pool-prepared-statements=true #指定每个连接上PSCache的大小,开启PSCache时,此配置必须大于0 spring.datasource.druid.history.max-pool-prepared-statement-per-connection-size=20 #检测连接是否有效的sql spring.datasource.druid.history.validation-query=SELECT 1 FROM DUAL #检测连接是否有效超时时间(毫秒) spring.datasource.druid.history.validation-query-timeout=60000 #申请连接时执行validationQuery检测连接是否有效 spring.datasource.druid.history.test-on-borrow=false #归还连接时执行validationQuery检测连接是否有效 spring.datasource.druid.history.test-on-return=false #申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效 spring.datasource.druid.history.test-while-idle=true #Destroy线程会检测连接的间隔时间,testWhileIdle的判断依据 spring.datasource.druid.history.time-between-eviction-runs-millis=60000 #Destory线程中如果检测到当前连接的最后活跃时间和当前时间的差值大于,minEvictableIdleTimeMillis,则关闭当前连接 spring.datasource.druid.history.min-evictable-idle-time-millis=100000 ###监控配置 # WebStatFilter配置 spring.datasource.druid.history.web-stat-filter.enabled=true spring.datasource.druid.history.web-stat-filter.url-pattern=/* spring.datasource.druid.history.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/* # StatViewServlet配置 spring.datasource.druid.history.stat-view-servlet.enabled=true spring.datasource.druid.history.stat-view-servlet.url-pattern=/druid/* spring.datasource.druid.history.stat-view-servlet.reset-enable=false #监控账号信息 spring.datasource.druid.history.stat-view-servlet.login-username=admin spring.datasource.druid.history.stat-view-servlet.login-password=admin #允许登录监控页面ip白名单,不配置即所有ip均是白名单 spring.datasource.druid.history.stat-view-servlet.allow= # 配置StatFilter spring.datasource.druid.history.filter.stat.db-type=mysql spring.datasource.druid.history.filter.stat.log-slow-sql=true spring.datasource.druid.history.filter.stat.slow-sql-millis=5000 # 配置WallFilter spring.datasource.druid.history.filter.wall.enabled=true spring.datasource.druid.history.filter.wall.db-type=mysql spring.datasource.druid.history.filter.wall.config.delete-allow=true spring.datasource.druid.history.filter.wall.config.drop-table-allow=false ####################历史库配置 end######################### mybatis-plus.mapper-locations=classpath:com/montnets/emp/*/mapper/xml/*.xml,classpath:com/montnets/emp/*/*/mapper/xml/*.xml #日志输出,true输出,false不输出 mybatis-plus.sql.show=true
这里实时库连接配置以spring.datasource.druid.realtime开头,历史库连接配置以spring.datasource.druid.history开头
2、mybatis-plus分页插件
package com.harara.config;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.parsers.BlockAttackSqlParser;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import java.util.ArrayList;
import java.util.List;
/**
* @name: MybatisPlusConfig
* @description: MyBatisPlus插件
* @type: JAVA
* @since: 2019/12/03 10:14
* @author: harara
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
List<ISqlParser> sqlParserList = new ArrayList<>();
// 攻击 SQL 阻断解析器、加入解析链,防止全表删除与更新
sqlParserList.add(new BlockAttackSqlParser());
paginationInterceptor.setSqlParserList(sqlParserList);
return paginationInterceptor;
}
}
3、实时库数据源配置类
package com.harara.config.database;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.harara.config.MybatisPlusConfig;
import lombok.extern.slf4j.Slf4j;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @author: chenly
* @date: 2019-11-13 20:50
* @description: 实时库配置类
* @version: 1.0
*/
@Slf4j
@Configuration
@Primary
@MapperScan(basePackages = {"com.harara.*.mapper", "com.harara.*.*.mapper"}, sqlSessionTemplateRef = "realSqlSessionTemplate")
public class RealDataSourceConfiguration {
@Autowired
private MybatisPlusConfig mybatisPlusConfig;
@Value(value = "${mybatis-plus.sql.show}")
private boolean isshowSql;
@Bean(name = "realDataSource")
@Primary //配置默认数据源
@ConfigurationProperties(prefix = "spring.datasource.druid.realtime")
public DataSource dataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
try {
druidDataSource.setPasswordCallbackClassName(DbPasswordCallback.class.getName());
} catch (Exception e) {
log.error("DruidDataSource set PasswordCallbackClassName failed");
}
return druidDataSource;
}
@Bean(name = "realSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("realDataSource") DataSource dataSource) throws Exception {
// SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
//使用MybatisSqlSessionFactoryBean 可以使用mybatis的BaseMapper中的方法
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/real/*.xml"));
List<Resource> resources = new ArrayList<>();
PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
resources.addAll(Arrays.asList(pathMatchingResourcePatternResolver.getResources("classpath:com/harara/*/mapper/xml/*.xml")));
resources.addAll(Arrays.asList(pathMatchingResourcePatternResolver.getResources("classpath:com/harara/*/*/mapper/xml/*.xml")));
bean.setMapperLocations(resources.toArray(new Resource[resources.size()]));
//设置分页插件
bean.setPlugins(new Interceptor[]{mybatisPlusConfig.paginationInterceptor()});
if(isshowSql){
//设置sql语句打印类
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
bean.setConfiguration(mybatisConfiguration);
}
return bean.getObject();
}
@Bean(name = "realTransactionManager")
@Primary
public DataSourceTransactionManager transactionManager(@Qualifier("realDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "realSqlSessionTemplate")
@Primary
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("realSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
标红代码说明:
basePackages :配置实时库mapper扫描范围
setPasswordCallbackClassName : 设置加密类的类名
4、历史库数据源配置类
package com.harara.config.database;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.harara.config.MybatisPlusConfig;
import lombok.extern.slf4j.Slf4j;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;
import java.util.Properties;
/**
* @author: chenly
* @date: 2019-11-14 11:13
* @description: 历史库配置类
* @version: 1.0
*/
@Slf4j
@Configuration
@MapperScan(basePackages = "com.harara.*.his.hismapper", sqlSessionTemplateRef = "historySqlSessionTemplate")
public class HistoryDataSourceConfiguration {
@Value(value = "${mybatis-plus.sql.show}")
private boolean isshowSql;
@Autowired
private MybatisPlusConfig mybatisPlusConfig;
@Bean(name = "historyDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.history")
public DataSource dataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
try {
druidDataSource.setPasswordCallbackClassName(DbPasswordCallback.class.getName());
} catch (Exception e) {
log.error("DruidDataSource set PasswordCallbackClassName failed");
}
return druidDataSource;
}
@Bean(name = "historySqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("historyDataSource") DataSource dataSource) throws Exception {
// SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
//使用MybatisSqlSessionFactoryBean 可以使用mybatis-BaseMapper中的方法
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/harara/*/his/hismapper/xml/*.xml"));
//设置分页插件
bean.setPlugins(new Interceptor[]{mybatisPlusConfig.paginationInterceptor()});
if(isshowSql){
//设置sql语句打印日志类
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
bean.setConfiguration(mybatisConfiguration);
}
return bean.getObject();
}
@Bean(name = "historyTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("historyDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "historySqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("historySqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
5、数据库密码 加密解密类
package com.harara.config.database;
import com.alibaba.druid.filter.config.ConfigTools;
import com.alibaba.druid.util.DruidPasswordCallback;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.util.Properties;
/**
* @author: harara
* @date: 2019-11-14 14:36
* @description: 重新数据库密码加密解密类
* @version: 1.0
*/
@Component
@Slf4j
public class DbPasswordCallback extends DruidPasswordCallback {
/**
* @param properties 属性值列表
* @return
* @description:处理解密
*/
@Override
public void setProperties(Properties properties) {
super.setProperties(properties);
String password = (String) properties.get("password");
String publickey = (String) properties.get("config.decrypt.key");
try {
String dbpassword = ConfigTools.decrypt(publickey, password);
if(dbpassword !=null){
setPassword(dbpassword.toCharArray());
}
} catch (Exception e) {
log.error("Druid ConfigTools.decrypt", e);
}
}
}
标红部分说明:
password : mysql加密后的密码
config.decrypt.key : 密钥
注意在配置文件中配置connection-properties的时候将config.decrypt.key和password添加进来
-- 实时库配置
spring.datasource.druid.realtime.connection-properties=config.decrypt=true;config.decrypt.key=${realtime.public-key};password=${spring.datasource.druid.realtime.password}
-- 历史库配置
spring.datasource.druid.history.connection-properties=config.decrypt=true;config.decrypt.key=${history.public-key};password=${spring.datasource.druid.history.password}
作者:小念
本文版权归作者和博客园共有,欢迎转载,但必须给出原文链接,并保留此段声明,否则保留追究法律责任的权利。