本文主要实现的是对数据操作进行独立模块得整合,详情请看我的另一篇博文:
【技术杂谈】springcloud微服务之数据操作独立模块化
1、本篇目标
独立部署mysql数据操作模块,整合 Druid + mybatis-plus ,实现SpringBoot项目中依赖数据模块进行数据操作,并进行简单测试。
2、引入依赖
org.springframework.boot
spring-boot-starter-jdbc
1.5.6.RELEASE
mysql
mysql-connector-java
5.1.36
com.baomidou
mybatis-plus
2.1-gamma
com.alibaba
druid
1.0.13
3、配置属性
因为我的项目使用的是springcloud分布式配置
所以配置文件在 ./config-server/config-repo/data-dev.yml,具体配置如下:
# ====================mysql====================
connection:
url: jdbc:mysql://ason-hostname:3306/rms_db?useUnicode=true&characterEncoding=utf8
username: ason
password: ason
# ====================druid====================
druid:
# 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
initialSize: 1
# 最小连接池数量
minIdle: 1
# 最大连接池数量
maxActive: 10
# 配置获取连接等待超时的时间
maxWait: 10000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 验证连接有效与否的SQL,不同的数据配置不同
validationQuery: select 1
# 建议配置为true,不影响性能,并且保证安全性。
# 申请连接的时候检测,如果空闲时间大于
# timeBetweenEvictionRunsMillis,
# 执行validationQuery检测连接是否有效。
testWhileIdle: true
# 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
# 这里建议配置为TRUE,防止取到的连接不可用
testOnBorrow: true
# 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testOnReturn: false
# 是否缓存preparedStatement,也就是PSCache。
# PSCache对支持游标的数据库性能提升巨大,比如说oracle。
# 在mysql5.5以下的版本中没有PSCache功能,建议关闭掉。
# 作者在5.5版本中使用PSCache,通过监控界面发现PSCache有缓存命中率记录,
# 该应该是支持PSCache。
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 属性类型是字符串,通过别名的方式配置扩展插件,
# 常用的插件有:
# 监控统计用的filter:stat
# 日志用的filter:log4j
# 防御sql注入的filter:wall
filters: stat
# 访问的用户名
loginUsername: ason
# 访问的密码
loginPassword: ason
# ====================MybatisPlus====================
mybatisPlus:
globalConfig:
#主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
idType: 0
#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
fieldStrategy: 2
#驼峰下划线转换
dbColumnUnderline: true
#刷新mapper 调试神器
isRefresh: true
#数据库大写下划线转换
isCapitalMode: true
#逻辑删除配置
logicDeleteValue: 0
logicNotDeleteValue: 1
4、DruidConf配置
package com.ason;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Value;
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;
/**
* Created by Ason on 2017/9/26.
*/
@Configuration
public class DruidConf {
private static final Log log = LogFactory.getLog(DruidConf.class);
@Value("${connection.url}")
private String connectionUrl;
@Value("${connection.username}")
private String username;
@Value("${connection.password}")
private String password;
@Value("${druid.initialSize}")
private Integer initialSize;
@Value("${druid.minIdle}")
private Integer minIdle;
@Value("${druid.maxActive}")
private Integer maxActive;
@Value("${druid.maxWait}")
private Integer maxWait;
@Value("${druid.timeBetweenEvictionRunsMillis}")
private Integer timeBetweenEvictionRunsMillis;
@Value("${druid.minEvictableIdleTimeMillis}")
private Integer minEvictableIdleTimeMillis;
@Value("${druid.validationQuery}")
private String validationQuery;
@Value("${druid.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${druid.testOnBorrow}")
private Boolean testOnBorrow;
@Value("${druid.testOnReturn}")
private Boolean testOnReturn;
@Value("${druid.poolPreparedStatements}")
private Boolean poolPreparedStatements;
@Value("${druid.maxPoolPreparedStatementPerConnectionSize}")
private Integer maxPoolPreparedStatementPerConnectionSize;
@Value("${druid.filters}")
private String filters;
@Value("${druid.loginUsername}")
private String loginUsername;
@Value("${druid.loginPassword}")
private String loginPassword;
// 配置数据源
@Bean(name = "basisDataSource", initMethod = "init", destroyMethod = "close")
public DruidDataSource initDataSource() {
log.info("初始化DruidDataSource");
DruidDataSource dds = new DruidDataSource();
dds.setDriverClassName("com.mysql.jdbc.Driver");
dds.setUrl(connectionUrl);
dds.setUsername(username);
dds.setPassword(password);
dds.setInitialSize(initialSize);
dds.setMinIdle(minIdle);
dds.setMaxActive(maxActive);
dds.setMaxWait(maxWait);
dds.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dds.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dds.setValidationQuery(validationQuery);
dds.setTestWhileIdle(testWhileIdle);
dds.setTestOnBorrow(testOnBorrow);
dds.setTestOnReturn(testOnReturn);
dds.setPoolPreparedStatements(poolPreparedStatements);
dds.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
dds.setFilters(filters);
} catch (Exception e) {
e.printStackTrace();
}
return dds;
}
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//设置登录查看信息的账号密码.
servletRegistrationBean.addInitParameter("loginUsername",loginUsername);
servletRegistrationBean.addInitParameter("loginPassword",loginPassword);
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
5、MybatisPlusConf
package com.ason;
import com.alibaba.druid.pool.DruidDataSource;
import com.ason.utils.BlankUtil;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
/**
* Created by Ason on 2017/8/15.
*/
@Configuration
public class MybatisPlusConf {
private static final Log log = LogFactory.getLog(DruidConf.class);
// mybatisPlus全局配置
@Bean(name = "globalConfig")
public GlobalConfiguration globalConfig(
@Value("${mybatisPlus.globalConfig.idType}") Integer idType, //主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
@Value("${mybatisPlus.globalConfig.fieldStrategy}") Integer fieldStrategy, //字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
@Value("${mybatisPlus.globalConfig.dbColumnUnderline}") Boolean dbColumnUnderline, //驼峰下划线转换
@Value("${mybatisPlus.globalConfig.isRefresh}") Boolean isRefresh, //刷新mapper 调试神器
@Value("${mybatisPlus.globalConfig.isCapitalMode}") Boolean isCapitalMode, //数据库大写下划线转换
@Value("${mybatisPlus.globalConfig.logicDeleteValue}") String logicDeleteValue, //逻辑删除配置
@Value("${mybatisPlus.globalConfig.logicNotDeleteValue}") String logicNotDeleteValue //逻辑删除配置
) {
log.info("初始化GlobalConfiguration");
GlobalConfiguration globalConfig = new GlobalConfiguration();
if ( !BlankUtil.isBlank(idType)) {
globalConfig.setIdType(idType); //主键类型
}
if ( !BlankUtil.isBlank(fieldStrategy)) {
// globalConfig.setFieldStrategy(fieldStrategy); //字段策略
}
if ( !BlankUtil.isBlank(dbColumnUnderline)) {
globalConfig.setDbColumnUnderline(dbColumnUnderline); //驼峰下划线转换
}
if ( !BlankUtil.isBlank(isRefresh)) {
// globalConfig.setRefresh(isRefresh); //刷新mapper 调试神器
}
if ( !BlankUtil.isBlank(isCapitalMode)) {
globalConfig.setCapitalMode(isCapitalMode); //数据库大写下划线转换
}
if ( !BlankUtil.isBlank(logicDeleteValue)) {
// globalConfig.setLogicDeleteValue(logicDeleteValue); //逻辑删除配置
}
if ( !BlankUtil.isBlank(logicNotDeleteValue)) {
// globalConfig.setLogicNotDeleteValue(logicNotDeleteValue); //逻辑删除配置
}
return globalConfig;
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier(value = "globalConfig")GlobalConfiguration globalConfig,
@Qualifier(value = "basisDataSource")DruidDataSource dataSource) throws Exception {
log.info("初始化SqlSessionFactory");
String mapperLocations = "classpath:db-ason/sql/**/*.xml";
String configLocation = "classpath:db-ason/mybatis/mybatis-sqlconfig.xml";
String typeAliasesPackage = "com.ason.entity.**";
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource); //数据源
sqlSessionFactory.setGlobalConfig(globalConfig); //全局配置
Interceptor[] interceptor = {new PaginationInterceptor()};
sqlSessionFactory.setPlugins(interceptor); //分页插件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
//自动扫描Mapping.xml文件
sqlSessionFactory.setMapperLocations(resolver.getResources(mapperLocations));
sqlSessionFactory.setConfigLocation(resolver.getResource(configLocation));
sqlSessionFactory.setTypeAliasesPackage(typeAliasesPackage);
return sqlSessionFactory.getObject();
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
// MyBatis 动态扫描
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
log.info("初始化MapperScannerConfigurer");
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
String basePackage = "com.ason.db.mapper";
mapperScannerConfigurer.setBasePackage(basePackage);
return mapperScannerConfigurer;
}
// 配置事务管理
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier(value = "basisDataSource")DruidDataSource dataSource) {
log.info("初始化DataSourceTransactionManager");
return new DataSourceTransactionManager(dataSource);
}
}
6、mybatis-sqlconfig.xml
7、具体使用
因为是将数据库的配置单独作为一个模块的,所以我的rms-service微服务想进行数据操作,需依赖db-mysql模块:
com.ason
db-mysql
0.0.1-SNAPSHOT
同时,还需要读取数据配置的属性,上面我的配置是放在 ./config-server/config-repo/data-dev.yml 下,所以在rms-service微服务下的 bootstrap.yml 配置文件中,需要指定配置中心服务的地址以及配置文件的name和profile:
spring:
# 配置中心服务的地址
cloud:
config:
name: data
profile: ${spring.profiles.active} # 要读取的配置文件profile属性
# uri: http://127.0.0.1:7001
#label: ${spring.profiles.active}
discovery:
enabled: true # 默认false,设为true表示使用注册中心中的configserver配置而不自己配置configserver的uri
serviceId: config-server
profiles:
active: dev
在 rms-service 的 RmsUserController 下,添加:
/**
* 查询单个用户
*/
@GetMapping(value = "/{id}", produces = "application/json;charset=UTF-8")
public String findUserById(@PathVariable("id") Integer id) throws Exception {
return ResultBody.success(rmsUserService.selectUserById(id));
}
访问druid后台:http://localhost:8888/druid,显示登录界面,输入配置文件中设置的账号密码:
至此,已完成Druid + mybatis-plus独立数据操作模块的整合
详情请看github地址:https://github.com/5-Ason/aso...