文章内容
目前,业界流行的数据操作框架是 Mybatis,那 Druid 是什么呢? Druid 是 Java 的数据库连接池组件。Druid 能够提供强大的监控和扩展功能。比如可以监控 SQL ,在监控业务可以查询慢查询 SQL 列表等。这里举个springboot-mutil-datasource工程案例: user 用户表在主库 master 上,地址表 city 在从库 slave上。
一、 Druid核心
Druid核心主要包括三部分:
- DruidDriver:代理 Driver,能够提供基于 Filter-Chain 模式的插件体系。
- DruidDataSource:高效可管理的数据库连接池 。
- SQLParser:当业务数据量达到了一定程度,DBA 需要合理配置数据库资源。即配置主库的机器高配置,把核心高频的数据放在主库上;把次要的数据放在低配置的从库。开源节流嘛,把数据放在不同的数据库里,就需要通过不同的数据源进行操作数据。
二、数据库
1、主库master
CREATE DATABASE master;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`user_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名称',
`description` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT user VALUES (1 ,'程序员','他有一个小网站');
2、从库slave
CREATE DATABASE slave;
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '城市编号',
`province_id` int(10) UNSIGNED NOT NULL COMMENT '省份编号',
`city_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市名称',
`description` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT city VALUES (1 ,1,'北京市','tom的家在北京。');
三、代码实现
1、项目整体结构
com.springboot.config.ds包下包含了多数据源的配置,同样可以有第三个数据源,按照前几个复制即可resources/mapper下面有两个模块,分别是Mybatis不同数据源需要扫描的mapper.xml目录
2、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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.carry.elk</groupId>
<artifactId>springboot-mutil-datasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springboot-mutil-datasource</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<druid>1.0.31</druid>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- Druid 数据连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</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>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3、application.properties两个数据源配置
## master 数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.jdbc.Driver
## slave 数据源配置
slave.datasource.url=jdbc:mysql://localhost:3306/slave?useUnicode=true&characterEncoding=utf8
slave.datasource.username=root
slave.datasource.password=123456
slave.datasource.driverClassName=com.mysql.jdbc.Driver
#热部署
spring.devtools.restart.enabled=true
4、主数据源MasterDataSourceConfig配置
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 com.alibaba.druid.pool.DruidDataSource;
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
// 精确到 master 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.springboot.dao.master";
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
5、从数据源SlaveDataSourceConfig配置
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 com.alibaba.druid.pool.DruidDataSource;
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
// 精确到 slave 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.springboot.dao.slave";
static final String MAPPER_LOCATION = "classpath:mapper/slave/*.xml";
@Value("${slave.datasource.url}")
private String url;
@Value("${slave.datasource.username}")
private String user;
@Value("${slave.datasource.password}")
private String password;
@Value("${slave.datasource.driverClassName}")
private String driverClass;
@Bean(name = "slaveDataSource")
public DataSource slaveDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "slaveTransactionManager")
public DataSourceTransactionManager slaveTransactionManager() {
return new DataSourceTransactionManager(slaveDataSource());
}
@Bean(name = "slaveSqlSessionFactory")
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(slaveDataSource);
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
6、业务层代码
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.carry.springboot.dao.master.UserDao;
import com.carry.springboot.dao.slave.CityDao;
import com.carry.springboot.domain.City;
import com.carry.springboot.domain.User;
import com.carry.springboot.service.UserService;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao; // 主数据源
@Autowired
private CityDao cityDao; // 从数据源
@Override
public User findByName(String userName) {
User user = userDao.findByName(userName);
City city = cityDao.findByName("北京市");
user.setCity(city);
return user;
}
}
四、测试
写个controller和request方法,这里的是http://localhost:8080/api/user。在浏览器中输入http://localhost:8080/api/user?userName=程序员,得到结果: