1。maven添加依赖
<?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.hanhuide</groupId>
<artifactId>driver</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>driver</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>2.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatisplus-spring-boot-starter</artifactId>
<version>1.0.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.8.0</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.2.2.RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2. 修改application.yml
server:
port: 9000
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.p6spy.engine.spy.P6SpyDriver
druid:
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: admin
login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
master: #数据源1
url: jdbc:p6spy:mysql://localhost:3306/springcloud?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
username: root
password: root
cluster: #数据源2
url: jdbc:p6spy:mysql://localhost:3306/occs?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
username: root
password: root
mybatis-plus:
mapper-locations: classpath:/mapper/*.xml
type-aliases-package: com.hanhuide.driver.mapper
configuration:
map-underscore-to-camel-case: true
password: root
3.添加p6spy日志输出配置(spy.properties)与yal同级
module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
# 使用日志系统记录sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
## 配置记录Log例外
excludecategories=info,debug,result,batc,resultset
# 设置使用p6spy driver来做代理
deregisterdrivers=true
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动
driverlist=com.mysql.cj.jdbc.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 秒
outagedetectioninterval=2
customLogMessageFormat=%(currentTime) | SQL耗时: %(executionTime) ms | 连接信息: %(category)-%(connectionId) | 执行语句: %(sql)
4. 定义数据源名称
package com.hanhuide.driver.dataSource;
/**
* 数据源名称
* @author 韩惠德
*/
public interface DataSourceNames {
String MASTER = "MASTER";
String CLUSTER = "CLUSTER";
}
5.配置多数据源 ,将数据源添加到系统中
package com.hanhuide.driver.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.hanhuide.driver.dataSource.DataSourceNames;
import com.hanhuide.driver.dataSource.DynamicDataSource;
import lombok.extern.slf4j.Slf4j;
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;
import java.util.HashMap;
import java.util.Map;
/**
* 配置多数据源 ,将数据源添加到系统中
*
* @author 韩惠德
* @date 2018-05-14
*/
@Slf4j
@Configuration
public class DynamicDataSourceConfig {
/**
* 创建 DataSource Bean
*/
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource oneDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
@Bean
@ConfigurationProperties("spring.datasource.cluster")
public DataSource twoDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
/**
* 如果还有数据源,在这继续添加 DataSource Bean
*/
@Bean
@Primary
public DynamicDataSource dataSource(DataSource oneDataSource, DataSource twoDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceNames.MASTER, oneDataSource);
targetDataSources.put(DataSourceNames.CLUSTER, twoDataSource);
// 还有数据源,在targetDataSources中继续添加
log.info("DataSources:" + targetDataSources);
return new DynamicDataSource(oneDataSource, targetDataSources);
}
}
6. 动态数据源
package com.hanhuide.driver.dataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源
* @author 韩惠德
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 配置DataSource, defaultTargetDataSource为主数据库
*/
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
public static String getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
contextHolder.remove();
}
}
7. 配置p6spy输出日志
package com.hanhuide.driver.logger;
import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import java.time.LocalDateTime;
/**
* @program: maven
* @description:配置p6spy输出日志
* @author: 韩惠德
* @create: 2019-12-24 15:03
* @version: 1.0
**/
public class P6SpyLogger implements MessageFormattingStrategy {
@Override
public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String s4) {
return !"".equals(sql.trim()) ? "[ " + LocalDateTime.now() + " ] --- | took "
+ elapsed + "ms | " + category + " | connection " + connectionId + "\n "
+ sql + ";" : "";
}
}
8. 利用注解自由切换数据库,自定义注解
package com.hanhuide.driver.annotation;
import com.hanhuide.driver.dataSource.DataSourceNames;
import java.lang.annotation.*;
/**
* 多数据源注解
*
* @author 韩惠德
* @date 2018-05-14
*/
@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
String value() default DataSourceNames.MASTER;
}
9.利用切面添加注解
package com.hanhuide.driver.aspect;
import com.hanhuide.driver.annotation.DataSource;
import com.hanhuide.driver.dataSource.DataSourceNames;
import com.hanhuide.driver.dataSource.DynamicDataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* 数据源AOP切面处理
*
* @author geYang
* @date 2018-05-14
*/
@Slf4j
@Aspect
@Component
public class DataSourceAspect implements Ordered {
/**
* 切点: 所有配置 DataSource 注解的方法
*/
@Pointcut("@annotation(com.hanhuide.driver.annotation.DataSource)")
public void dataSourcePointCut() {
}
@Around("dataSourcePointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource ds = method.getAnnotation(DataSource.class);
if (ds == null) {
DynamicDataSource.setDataSource(DataSourceNames.MASTER);
log.debug("set datasource is " + DataSourceNames.MASTER);
} else {
DynamicDataSource.setDataSource(ds.value());
log.debug("set datasource is " + ds.value());
}
try {
return point.proceed();
} finally {
DynamicDataSource.clearDataSource();
log.debug("clean datasource");
}
}
@Override
public int getOrder() {
return 1;
}
}
8.配置启动类
package com.hanhuide.driver;
import com.hanhuide.driver.config.DynamicDataSourceConfig;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Import;
/**
* 动态数据源配置,需要将自有的配置依赖(DynamicDataSourceConfig),将原有的依赖去除(DataSourceAutoConfiguration)
* @author 韩惠德
* @date 2018-05-15
*/
@Import({DynamicDataSourceConfig.class})
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan(basePackages = "com.hanhuide.driver.Service")//这是我测试数据的扫描地址
public class DriverApplication {
public static void main(String[] args) {
SpringApplication.run(DriverApplication.class, args);
}
}
--------------------------------------------------------------------------------------到此springboot配置druid多数据源 ,注解切换的代码就完事了
接下来我们编辑测试类
1.随便一个实体类
package com.hanhuide.driver.Service;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* <p>
*
* </p>
*
* @author 韩惠德
* @since 2019-11-29
*/
@TableName("sys_user")
@Data
public class SysUser implements Serializable{
private static final long serialVersionUID = 1L;
/**
* 用户ID
*/
@TableId(value = "USER_ID", type = IdType.AUTO)
private Long userId;
/**
* 用户名
*/
@TableField("USERNAME")
private String username;
/**
* 密码
*/
@TableField("PASSWORD")
private String password;
/**
* 部门ID
*/
@TableField("DEPT_ID")
private Long deptId;
/**
* 邮箱
*/
@TableField("EMAIL")
private String email;
/**
* 联系电话
*/
@TableField("MOBILE")
private String mobile;
/**
* 状态 0锁定 1有效
*/
@TableField("STATUS")
private String status;
/**
* 创建时间
*/
@TableField("CREATE_TIME")
private Date createTime;
/**
* 修改时间
*/
@TableField("MODIFY_TIME")
private Date modifyTime;
/**
* 最近访问时间
*/
@TableField("LAST_LOGIN_TIME")
private Date lastLoginTime;
/**
* 性别 0男 1女 2保密
*/
@TableField("SSEX")
private String ssex;
/**
* 描述
*/
@TableField("DESCRIPTION")
private String description;
/**
* 用户头像
*/
@TableField("AVATAR")
private String avatar;
public void setAvatar(String avatar) {
this.avatar = avatar;
}
public static final String USER_ID = "USER_ID";
public static final String USERNAME = "USERNAME";
public static final String PASSWORD = "PASSWORD";
public static final String DEPT_ID = "DEPT_ID";
public static final String EMAIL = "EMAIL";
public static final String MOBILE = "MOBILE";
public static final String STATUS = "STATUS";
public static final String CREATE_TIME = "CREATE_TIME";
public static final String MODIFY_TIME = "MODIFY_TIME";
public static final String LAST_LOGIN_TIME = "LAST_LOGIN_TIME";
public static final String SSEX = "SSEX";
public static final String DESCRIPTION = "DESCRIPTION";
public static final String AVATAR = "AVATAR";
@Override
public String toString() {
return "SysUser{" +
"userId=" + userId +
", username=" + username +
", password=" + password +
", deptId=" + deptId +
", email=" + email +
", mobile=" + mobile +
", status=" + status +
", createTime=" + createTime +
", modifyTime=" + modifyTime +
", lastLoginTime=" + lastLoginTime +
", ssex=" + ssex +
", description=" + description +
", avatar=" + avatar +
"}";
}
}
2,编写mapper
package com.hanhuide.driver.Service;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.hanhuide.driver.annotation.DataSource;
import com.hanhuide.driver.dataSource.DataSourceNames;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @program: maven
* @description:
* @author: 韩惠德
* @create: 2019-12-24 16:39
* @version: 1.0
**/
@Service
public interface CeshiMapper extends BaseMapper<SysUser> {
@Select("select * from sys_user")
List<SysUser> findAll();
@Select("select * from sys_user")
@DataSource(DataSourceNames.CLUSTER)//注解切换数据源
List<SysUser> findAll2();
}
再写个contrller
package com.hanhuide.driver.Service;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @program: maven
* @description:
* @author: 韩惠德
* @create: 2019-12-24 16:41
* @version: 1.0
**/
@RestController
@Slf4j
public class Contrller11 {
@Resource
private CeshiMapper ceshiMapper;
@GetMapping("ceshi")
public List<SysUser> ceshi() {
return ceshiMapper.findAll();
}
@GetMapping("ceshi2")
public List<SysUser> ceshi2() {
return ceshiMapper.findAll2();
}
}
===============到此测试类类及方法就完成了,启动运行
两个数据源已经连接
项目已启动
连接主数据库成功
p6spy 搭建成功 但是乱码没有解决
说明数据库切换成功
打开druid的监控页面查看当前数据源的相关信息,登录用户名密码在配置文件共配置了
查看两个数据源