在以前的项目中用springMVC加原生的mybatis框架使用过多数据源的配置,是用xml配置的。在这次的新项目里面使用到了tk的通用mapper,刚好项目结束,利用空闲时间写了个全注解的多数据源配置小demo。
现在互联网项目中流行使用Mybatis框架和Druid数据库连接池进行数据库的操作。本来直接是用的spring boot整合mybatis做的多数据源,因为刚结束的项目中使用到了通用mapper这个免去sql编写,提高开发效率的组件,所以顺带的把这个组件也集成进来了。
因为主要讲的是多数据源的实现,对其他的框架组件就不花费太多的笔墨,以后会另外的花时间去记录。
首先看下项目的整体结构:
datasource包下面放的是数据源一和数据源二的配置
mapper包下面放的对应的mapper文件,注意包要细分到不同的来源,比喻mapper.source1和mapper.source2,不同来源库的mapper文件要放到不同的source包下面。
项目是用maven工具构建的,肯定少不了在pom.xml文件里面添加相应的jar依赖:
<?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.lms</groupId> <artifactId>multiple-datasource</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>multiple-datasource</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.2.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> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- druid连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.8</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!--tk通用mapper--> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>1.2.4</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
添加完依赖之后就要开始配置数据库了,在application.properties文件里面配置数据库的地址和druid相关的一些配置:
mybatis.type-aliases-package=com.lms.multipledatasource.entity spring.datasource.source1.driver-class-name = com.mysql.jdbc.Driver spring.datasource.source1.url = jdbc:mysql://192.168.2.115:3306/data_source_test1?useUnicode=true&characterEncoding=utf-8 spring.datasource.source1.username = root spring.datasource.source1.password = 8Nj1jo+I8f1qNQ #使用Druid数据源 spring.datasource.source1.initialSize=5 # 初始化大小,最小,最大 spring.datasource.source1.minIdle=5 spring.datasource.source1.maxActive= 20 # 配置获取连接等待超时的时间 spring.datasource.source1.maxWait= 60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 spring.datasource.source1.timeBetweenEvictionRunsMillis= 60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 spring.datasource.source1.minEvictableIdleTimeMillis= 300000 spring.datasource.source1.validationQuery= select 'x' spring.datasource.source1.testWhileIdle= true spring.datasource.source1.testOnBorrow= false spring.datasource.source1.testOnReturn= false # 打开PSCache,并且指定每个连接上PSCache的大小 spring.datasource.source1.poolPreparedStatements= true spring.datasource.source1.maxPoolPreparedStatementPerConnectionSize= 20 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 spring.datasource.source1.filters= stat,wall,slf4j # 通过connectProperties属性来打开mergeSql功能;慢SQL记录 spring.datasource.source1.connectionProperties= druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 spring.datasource.source2.driver-class-name = com.mysql.jdbc.Driver spring.datasource.source2.url = jdbc:mysql://192.168.2.115:3306/data_source_test2?useUnicode=true&characterEncoding=utf-8 spring.datasource.source2.username = root spring.datasource.source2.password = 8Nj1jo+I8f1qNQ #使用Druid数据源 spring.datasource.source2.initialSize=5 # 初始化大小,最小,最大 spring.datasource.source2.minIdle=5 spring.datasource.source2.maxActive= 20 # 配置获取连接等待超时的时间 spring.datasource.source2.maxWait= 60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 spring.datasource.source2.timeBetweenEvictionRunsMillis= 60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 spring.datasource.source2.minEvictableIdleTimeMillis= 300000 spring.datasource.source2.validationQuery= select 'x' spring.datasource.source2.testWhileIdle= true spring.datasource.source2.testOnBorrow= false spring.datasource.source2.testOnReturn= false # 打开PSCache,并且指定每个连接上PSCache的大小 spring.datasource.source2.poolPreparedStatements= true spring.datasource.source2.maxPoolPreparedStatementPerConnectionSize= 20 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 spring.datasource.source2.filters= stat,wall,slf4j # 通过connectProperties属性来打开mergeSql功能;慢SQL记录 spring.datasource.source2.connectionProperties= druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
mybatis.type-aliases-package配置可以自动的扫描mapper文件,但是需要在Mapper接口上标注@Mapper注解,下面配置了两个数据源source1和source2,如果还有更多的数据源,只需要依次的添加就可以了。
接下来创建两个数据源配置类DataSourceOneConfig和DataSourceTwoConfig
在配置数据源的时候,必要要配置一个主数据源,在这里把DataSourceOneConfig作为主数据源
@Configuration //扫描Mapper basePackages要精确到source1目录便于进行不同数据源的区分 @MapperScan(basePackages = "com.lms.multipledatasource.mapper.source1", sqlSessionTemplateRef = "sqlSessionTemplateOne") public class DataSourceOneConfig {<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"dataSourceOne"</span>) <span style="color:#bbb529;">@ConfigurationProperties</span>(<span style="color:#d0d0ff;">prefix </span>= <span style="color:#6a8759;">"spring.datasource.source1"</span>) <span style="color:#bbb529;">@Primary </span><span style="color:#808080;">//设置主数据源
public DataSource DataSourceOne(){
DruidDataSource dataSource = new DruidDataSource();
return dataSource;
}
<span style="color:#bbb529;">@Bean
public ServletRegistrationBean druidStatViewServlet() {
ServletRegistrationBean registrationBean = new ServletRegistrationBean(new StatViewServlet(), “/druid/*”);
registrationBean.addInitParameter(“allow”, “127.0.0.1”); // IP白名单 (没有配置或者为空,则允许所有访问)
registrationBean.addInitParameter(“deny”, “”); // IP黑名单 (存在共同时,deny优先于allow)
registrationBean.addInitParameter(“loginUsername”, “admin”);
registrationBean.addInitParameter(“loginPassword”, “admin”);
registrationBean.addInitParameter(“resetEnable”, “false”);
return registrationBean;
}
<span style="color:#bbb529;">@Bean
public FilterRegistrationBean druidWebStatViewFilter() {
FilterRegistrationBean registrationBean = new FilterRegistrationBean(new WebStatFilter());
registrationBean.addInitParameter(“urlPatterns”, “/");
registrationBean.addInitParameter(“exclusions”, ".js,.gif,.jpg,.bmp,.png,.css,.ico,/druid/*”);
return registrationBean;
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"sqlSessionFactoryOne"</span>)
<span style="color:#bbb529;">@Primary
public SqlSessionFactory sqlSessionFactoryOne(@Qualifier(“dataSourceOne”) DataSource dataSource)throws Exception{
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"dataSourceTransactionManagerOne"</span>)
<span style="color:#bbb529;">@Primary
public DataSourceTransactionManager dataSourceTransactionManagerOne(@Qualifier(“dataSourceOne”) DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"sqlSessionTemplateOne"</span>)
<span style="color:#bbb529;">@Primary
public SqlSessionTemplate sqlSessionTemplateOne(@Qualifier(“sqlSessionFactoryOne”) SqlSessionFactory sqlSessionFactory)throws Exception{
return new SqlSessionTemplate(sqlSessionFactory);
}
}
@Configuration 说明这是一个配置类
@MapperScan 扫描mapper接口和进行容器管理,注意的是basePackages要精确到source1目录便于和不同数据源的区分
@Primary 标志主数据源,一定要有且只能有一个主数据源,不然会报错的。
@ConfigurationProperties 读取properties配置文件里面的以spring.datasource.source1开头的数据库配置
在配置datasource的时候一定要用DruidDataSource类,不然的话druid数据库连接池是不会起作用的
public DataSource DataSourceOne(){
DruidDataSource dataSource = new DruidDataSource();
return dataSource;
}
druidStatViewServlet()和druidWebStatViewFilter()是配置durid的登陆地址和登陆账户的。
同理DataSourceTwoConfig类中的配置和DataSourceOneConfig里面的是差不多的 ,把source1改为source2即可,并且不需要@Primary注解
@Configuration
@MapperScan(basePackages = “com.lms.multipledatasource.mapper.source2”, sqlSessionTemplateRef = “sqlSessionTemplateTwo”)
public class DataSourceTwoConfig {<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"dataSourceTwo"</span>) <span style="color:#bbb529;">@ConfigurationProperties</span>(<span style="color:#d0d0ff;">prefix </span>= <span style="color:#6a8759;">"spring.datasource.source2"</span>) <span style="color:#cc7832;">public </span>DataSource <span style="color:#ffc66d;">DataSourceOne</span>(){ DruidDataSource dataSource = <span style="color:#cc7832;">new </span>DruidDataSource()<span style="color:#cc7832;">;
return dataSource;
}
<span style="color:#bbb529;">@Bean
public ServletRegistrationBean druidStatViewServlet() {
ServletRegistrationBean registrationBean = new ServletRegistrationBean(new StatViewServlet(), “/druid/*”);
registrationBean.addInitParameter(“allow”, “127.0.0.1”); // IP白名单 (没有配置或者为空,则允许所有访问)
registrationBean.addInitParameter(“deny”, “”); // IP黑名单 (存在共同时,deny优先于allow)
registrationBean.addInitParameter(“loginUsername”, “admin”);
registrationBean.addInitParameter(“loginPassword”, “admin”);
registrationBean.addInitParameter(“resetEnable”, “false”);
return registrationBean;
}
<span style="color:#bbb529;">@Bean
public FilterRegistrationBean druidWebStatViewFilter() {
FilterRegistrationBean registrationBean = new FilterRegistrationBean(new WebStatFilter());
registrationBean.addInitParameter(“urlPatterns”, “/");
registrationBean.addInitParameter(“exclusions”, ".js,.gif,.jpg,.bmp,.png,.css,.ico,/druid/*”);
return registrationBean;
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"sqlSessionFactoryTwo"</span>)
<span style="color:#cc7832;">public </span>SqlSessionFactory <span style="color:#ffc66d;">sqlSessionFactoryOne</span>(<span style="color:#bbb529;">@Qualifier</span>(<span style="color:#6a8759;">"dataSourceTwo"</span>) DataSource dataSource)<span style="color:#cc7832;">throws </span>Exception{
SqlSessionFactoryBean bean = <span style="color:#cc7832;">new </span>SqlSessionFactoryBean()<span style="color:#cc7832;">;
bean.setDataSource(dataSource);
return bean.getObject();
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"dataSourceTransactionManagerTwo"</span>)
<span style="color:#cc7832;">public </span>DataSourceTransactionManager <span style="color:#ffc66d;">dataSourceTransactionManagerOne</span>(<span style="color:#bbb529;">@Qualifier</span>(<span style="color:#6a8759;">"dataSourceTwo"</span>) DataSource dataSource){
<span style="color:#cc7832;">return new </span>DataSourceTransactionManager(dataSource)<span style="color:#cc7832;">;
}
<span style="color:#bbb529;">@Bean</span>(<span style="color:#d0d0ff;">name </span>= <span style="color:#6a8759;">"sqlSessionTemplateTwo"</span>)
<span style="color:#cc7832;">public </span>SqlSessionTemplate <span style="color:#ffc66d;">sqlSessionTemplateOne</span>(<span style="color:#bbb529;">@Qualifier</span>(<span style="color:#6a8759;">"sqlSessionFactoryTwo"</span>) SqlSessionFactory sqlSessionFactory)<span style="color:#cc7832;">throws </span>Exception{
<span style="color:#cc7832;">return new </span>SqlSessionTemplate(sqlSessionFactory)<span style="color:#cc7832;">;
}
到这里,基本的数据源配置就已经差不多结束了,接下来的service,mapper,conttroller就和平常写项目是一样的,没有什么太大的区别。
因为使用的是tk通用mapper,所以我们在自己定义一个接口去是继承tk的两个接口Mapper<T>, MySqlMapper<T> ,自己定义的接口名称可以随自己定,和普通的接口一样,没有特殊的要求。在这里取名为CommnMapper<T>,注意的是一定要带上泛型。这个接口上面不需要任何的注解
public interface CommnMapper<T> extends Mapper<T>, MySqlMapper<T> {
}
然后新建一个UserInfoOneMapper接口继承刚刚创建的CommnMapper接口,接口上面要添加@Component和@Mapper来说明这个接口是一个组件和一个mapper文件
@Component
@Mapper
public interface UserInfoOneMapper extends CommnMapper<UserInfoEntity>{
}这个mapper连接的是userinfo表,所以泛型里面用的是UserInfoEntity对象。mapper连接的是哪个表,泛型里面对应的就是哪个表对应的实体类对象。
@Component
@Table(name = “userinfo”)
public class UserInfoEntity {<span style="color:#bbb529;">@Id
private Integer id;
private String userName;
private String departName;
private Date created;
@Table里面name的值是数据库里面对应的表名,一定不要填错了。
然后我们就可以在service里面调用mapper的增删查改方法来对表里面的数据进行对应的操作了。因为这只是一个demo,所以就没有创建service层,直接在controller实例化了mapper调用了对应的方法
@RestController
public class UserInfoController {
@Autowired
private UserInfoOneMapper userInfoOneMapper;
@Autowired
private UserInfoTwoMapper userInfoTwoMapper;
@RequestMapping("/getOneUserById")
public UserInfoEntity getOneUserById(Integer id){
UserInfoEntity entity = new UserInfoEntity();
entity.setId(id);
UserInfoEntity entity1 = userInfoOneMapper.selectOne(entity);
return entity1;
}<span style="color:#bbb529;">@RequestMapping</span>(<span style="color:#6a8759;">"/getAllUserForOne"</span>) <span style="color:#cc7832;">public </span>List<UserInfoEntity> <span style="color:#ffc66d;">getAllUserForOne</span>(){ List<UserInfoEntity> allUser = <span style="color:#9876aa;">userInfoOneMapper</span>.selectAll()<span style="color:#cc7832;">;
return allUser;
}
<span style="color:#bbb529;">@RequestMapping</span>(<span style="color:#6a8759;">"/getTwoUserById"</span>)
<span style="color:#cc7832;">public </span>UserInfoEntity <span style="color:#ffc66d;">getTwoUserById</span>(Integer id){
UserInfoEntity entity = <span style="color:#cc7832;">new </span>UserInfoEntity()<span style="color:#cc7832;">;
entity.setId(id);
UserInfoEntity entity1 = userInfoTwoMapper.selectOne(entity);
return entity1;
}
<span style="color:#bbb529;">@RequestMapping</span>(<span style="color:#6a8759;">"/getAllUserForTwo"</span>)
<span style="color:#cc7832;">public </span>List<UserInfoEntity> <span style="color:#ffc66d;">getAllUserForTwo</span>(){
List<UserInfoEntity> allUser = <span style="color:#9876aa;">userInfoTwoMapper</span>.selectAll()<span style="color:#cc7832;">;
return allUser;
}
}
启动项目,在postman里面调用不同的接口,可以获取到不同的表里面的数据。
调用getOneUserById,传id=1获得返回数据:
调用getTwoUserById,传id=1获得返回数据: