目录
- 一、 srpingboot整合druid
- 1.jar包导入(pom.xml完整配置请查看第三部分)
- 2.druid配置
- 3.Springboot启动类添加注解,手动开启druid数据源@EnableConfigurationProperties(DruidConfig.class)
- 4.新建DynamicDataSource类,用于添加多数据源,继承AbstractRoutingDataSource,此类定义了默认数据源defaultTargetDataSource和数据源Map(targetDataSources)
- 5.新建DruidConfig类,配置多数据源,及druid监控
- 6.新建DynamicDataSourceContextHolder类,在ThreadLocal中保存数据源名称,查询当前数据源
- 7.新建@annotation,TargetDataSource,用于在dao层数据库查询时指定数据源
- 二、整合mybatis
- 三、测试指定数据源是否生效,druid监控是否正常
一、 srpingboot整合druid
1.jar包导入(pom.xml完整配置请查看第三部分)
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.26</version>
</dependency>
2.druid配置
springboot多环境配置,每个环境对应一个配置文件,在主配置文件中设置属性,使用spring.profiles.active=test来指定使用哪个配置文件
#连接池的配置信息
spring.druid.jdbcUrl1=jdbc:mysql://192.168.0.1:3306/wgza
spring.druid.jdbcUrl2=jdbc:mysql://192.168.0.1:3306/wgzb
spring.druid.username=admin
spring.druid.password=admin
spring.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.druid.initialSize=2
spring.druid.minIdle=2
spring.druid.maxActive=2
## 配置获取连接等待超时的时间
spring.druid.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
spring.druid.minEvictableIdleTimeMillis=300000
spring.druid.validationQuery=SELECT 1 FROM DUAL
spring.druid.testWhileIdle=true
spring.druid.testOnBorrow=false
spring.druid.testOnReturn=false
spring.druid.poolPreparedStatements=true
spring.druid.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.druid.filters=stat,wall
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#把该包下的 bean 生成别名
mybatis.typeAliasesPackage=com.spring.fisher.bean
#Mybatis 会把这个路径下的 xml 解析出来建立接口的映射关系
mybatis.mapperLocations=classpath:com/spring/fisher/xml/*.xml
3.Springboot启动类添加注解,手动开启druid数据源@EnableConfigurationProperties(DruidConfig.class)
package com.spring.fisher.starter;
import com.spring.fisher.druidConfig.DruidConfig;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
@SpringBootApplication(scanBasePackages = "com.spring.fisher")
@MapperScan("com.spring.fisher.dao")//该注解会扫描 dao 包下的接口,把接口生成代理对象并加入到 spring 容器中
@EnableConfigurationProperties(DruidConfig.class)//手动开启druid数据源
public class SpringbootDemo {
public static void main(String[] args) {
SpringApplication.run(SpringbootDemo.class, args);
System.out.println("hello world");
}
}
4.新建DynamicDataSource类,用于添加多数据源,继承AbstractRoutingDataSource,此类定义了默认数据源defaultTargetDataSource和数据源Map(targetDataSources)
通过重写钩子方法determineCurrentLookupKey,查看已定义的数据源Map
package com.spring.fisher.dynamicDataSource;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.lang.reflect.Field;
import java.util.Map;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
try {
//通过反射获取父类数据源Map
Field targetDataSources = this.getClass().getSuperclass().getDeclaredField("targetDataSources");
targetDataSources.setAccessible(true);
Map<Object, Object> value = null;
value = (Map<Object, Object>) targetDataSources.get(this);
String ds = DynamicDataSourceContextHolder.getDataSource();
if (ds != null) {
System.out.println("当前数据源:"+ds+",url:"+((DruidDataSource)value.get(ds)).getUrl());
}
return DynamicDataSourceContextHolder.getDataSource();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}catch (IllegalAccessException e) {
e.printStackTrace();
}
return "ds1";
}
}
5.新建DruidConfig类,配置多数据源,及druid监控
动态数据源配置
package com.spring.fisher.druidConfig;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.spring.fisher.dynamicDataSource.DynamicDataSource;
import com.spring.fisher.dynamicDataSource.DynamicDataSourceContextHolder;
import lombok.Data;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
//引入lombok插件,省略get,set方法
@Data
@Configuration
//读取默认的配置文件 application.properties 配置,然后读取 spring.druid 作为前缀的配置属性
@ConfigurationProperties(prefix = "spring.druid",ignoreInvalidFields = true)
@PropertySource("classpath:application.properties")
public class DruidConfig {
private String driverClassName;
private String jdbcUrl1;//ds1数据源
private String jdbcUrl2;//ds2数据源
private String username;
private String password;
private int maxActive;
private int minIdle;
private int initialSize;
private Long timeBetweenEvictionRunsMillis;
private Long minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private Integer maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
//这里需要注意默认是读取的application.properties配置文件。
//如果你的配置文件不在默认文件中
//需要在类中引入配置文件例如:@PropertySource(value = "classpath:druid.properties")
// @Bean(destroyMethod = "close",initMethod = "init"),不使用多数据源时,可以使用@Bean,并注释掉切面与dynamicDataSource方法
public DataSource getDs1(){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUrl(jdbcUrl1);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setInitialSize(initialSize);
druidDataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
druidDataSource.setValidationQuery(validationQuery);
druidDataSource.setTestWhileIdle(testWhileIdle);
druidDataSource.setTestOnBorrow(testOnBorrow);
druidDataSource.setTestOnReturn(testOnReturn);
druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
druidDataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
return druidDataSource;
}
public DataSource getDs2(){
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUrl(jdbcUrl2);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setInitialSize(initialSize);
druidDataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
druidDataSource.setValidationQuery(validationQuery);
druidDataSource.setTestWhileIdle(testWhileIdle);
druidDataSource.setTestOnBorrow(testOnBorrow);
druidDataSource.setTestOnReturn(testOnReturn);
druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
druidDataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
return druidDataSource;
}
@Bean
public DataSource dynamicDataSource(){
Map<Object, Object> targetDataSources = new HashMap<>();
// 创建2个数据源
DataSource ds1 = getDs1();
DataSource ds2 = getDs2();
targetDataSources.put("ds1", ds1);
targetDataSources.put("ds2", ds2);
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 添加到数据源对象中
dynamicDataSource.setTargetDataSources(targetDataSources);
// 默认使用ds1
dynamicDataSource.setDefaultTargetDataSource(ds1);
// 将这个2个数据源添加到自己创建的动态数据源上下文对象中
// 用于判断后续业务方法中使用的数据源是否存在,不存在则使用默认的ds1
DynamicDataSourceContextHolder.dataSourceIds.add("ds1");
DynamicDataSourceContextHolder.dataSourceIds.add("ds2");
return dynamicDataSource;
}
/**
* 配置访问druid监控
*/
@Bean
public ServletRegistrationBean druidStateViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//初始化参数initParams
//添加白名单
servletRegistrationBean.addInitParameter("allow","");
//添加ip黑名单
servletRegistrationBean.addInitParameter("deny","192.168.0.11");
//登录查看信息的账号密码
servletRegistrationBean.addInitParameter("loginUsername","admin");
servletRegistrationBean.addInitParameter("loginPassword","123");
//是否能够重置数据
servletRegistrationBean.addInitParameter("resetEnable","false");
return servletRegistrationBean;
}
/**
* 过滤不需要监控的后缀
*/
@Bean
public FilterRegistrationBean druidStatFilter(){
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
//添加过滤规则
filterRegistrationBean.addUrlPatterns("/*");
//添加不需要忽略的格式信息
filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
6.新建DynamicDataSourceContextHolder类,在ThreadLocal中保存数据源名称,查询当前数据源
package com.spring.fisher.dynamicDataSource;
import java.util.ArrayList;
import java.util.List;
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static List<String> dataSourceIds = new ArrayList<>();
public static ThreadLocal<String> getContextHolder() {
return contextHolder;
}
public static void setDataSource(String dataSource){
contextHolder.set(dataSource);
}
public static String getDataSource(){
return contextHolder.get();
}
public static boolean containsDataSource(String dataSourceId) {
return dataSourceIds.contains(dataSourceId);
}
}
7.新建@annotation,TargetDataSource,用于在dao层数据库查询时指定数据源
package com.spring.fisher.dynamicDataSource;
import java.lang.annotation.*;
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String name();
}
二、整合mybatis
1.引入jar包
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>RELEASE</version>
</dependency>
2.配置mybatis
在idea工程下,可能会出现资源文件找不到的情况,需要在pom.xml中添加相应配置,指定文件后缀及所在目录
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
<include>**/*.txt</include>
<include>**/*.keystore</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
3.添加注解扫描dao,类似与spring中mapperscannerconfigurer的作用
4.创建实体类User
package com.spring.fisher.bean;
import lombok.Data;
@Data
public class Users {
private Integer id;
private String name;
private String sex;
private Integer age;
}
5.创建dao
package com.spring.fisher.dao;
import com.spring.fisher.bean.Users;
import java.util.List;
import java.util.Map;
public interface CommonMapper {
List<Users> queryUser(Map param);
}
6.创建service,指定ds2作为数据源
package com.spring.fisher.service.serviceImpl;
import com.spring.fisher.bean.Users;
import com.spring.fisher.dao.CommonMapper;
import com.spring.fisher.dynamicDataSource.TargetDataSource;
import com.spring.fisher.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class UserServiceImpl implements UserService {
@Autowired
CommonMapper mapper;
@TargetDataSource(name = "ds2")
public List<Users> queryUser(Map param) {
List<Users> usersList = mapper.queryUser(param);
return usersList;
}
}
7.创建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.spring.fisher.dao.CommonMapper">
<resultMap id="UsersResultMap" type="com.spring.fisher.bean.Users">
<result column="id" property="id"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="age" property="age"/>
<result column="sex" property="sex" jdbcType="VARCHAR"/>
</resultMap>
<select id="queryUser" parameterType="java.util.Map" resultMap="UsersResultMap">
select * from users
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="id != null and id != ''">
and id = #{id}
</if>
<if test="name != null and name != ''">
and name = #{name,jdbcType=VARCHAR}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
</trim>
order by id
<if test="beginPage != null and beginPage != ''">
LIMIT #{beginPage}
</if>
<if test="pageSize != null and pageSize != ''">
,#{pageSize}
</if>
</select>
</mapper>
注意命名空间要配置正确
三、测试指定数据源是否生效,druid监控是否正常
1.引入支持aop功能的jar包
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
其他jar包,包括jdbc,mysql等
pom.xml具体配置
<?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 https://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.2.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.spring.fisher</groupId>
<artifactId>springboot-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--web启动器-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--支持aop注解功能-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--解决spring boot configuration annotation processor no found in classpath提示-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!--druid数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.26</version>
</dependency>
<!--jdbc-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<!-- 把mybatis的启动器引入 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>RELEASE</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
<include>**/*.txt</include>
<include>**/*.keystore</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
</project>
2.创建切面类DynamicDataSourceAspect,用于拦截@TargetDataSource(name = “ds2”)注解
package com.spring.fisher.dynamicDataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Aspect
@Order(-1)
@Slf4j
@Component
public class DynamicDataSourceAspect {
@Before("@annotation(dataSource)")//拦截有TargetDataSource注解的方法
public void changeDataSource(JoinPoint joinPoint, TargetDataSource dataSource){
String dsId = dataSource.name();
//如果存在指定的数据源,则放入ThreadLocal中
if (DynamicDataSourceContextHolder.containsDataSource(dsId)){
log.error("正在使用{}数据源,->{}", dsId, joinPoint.getSignature());//需要引入lombok插件
DynamicDataSourceContextHolder.setDataSource(dsId);
}else {
log.info("{}不存在,使用默认数据源,->{}", dsId, joinPoint.getSignature());
}
}
@After(value = "@annotation(dataSource)")//释放数据库链接资源
public void releaseLocal(JoinPoint joinPoint,TargetDataSource dataSource){
if (DynamicDataSourceContextHolder.getDataSource()!=null){
DynamicDataSourceContextHolder.getContextHolder().remove();
log.info("释放ds:" + dataSource.name() + "的ThreadLocal绑定!!!");
}
}
}
3.创建controller,使用queryUser2方法,已指定ds2数据源
package com.spring.fisher.controller;
import com.spring.fisher.bean.Users;
import com.spring.fisher.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
@RestController
public class UserController {
@Autowired
UserService userService;
@RequestMapping(value = "/queryUser2", method = RequestMethod.GET)
public String queryUser2(@RequestParam String param) {
Map<String, Object> map = new HashMap<>();
map.put("age", param);
List<Users> usersList = userService.queryUser(map);
for (Users users : usersList) {
log.info(users.getId() + "," + users.getName() + "," + users.getAge() + "," + users.getSex());
}
return "OK";
}
@RequestMapping(value = "/queryUser",method = RequestMethod.GET)
public String queryUser(@RequestParam String userName){
System.out.println("queryUser");
return userName;
}
}
4.启动项目,调用接口,查看日志
5.登录druid监控页面
http://localhost:8080/druid