前言
前一篇介绍了SpringBoot整合MyBatis的三种方式,整合了MyBatis之后,可以通过Druid来对数据库操作进行监控。
正题
一、整合MyBatis
先通过IDEA新建web项目,勾选好如下几个模块,如下图:
mapper接口
import com.sbwithmybatisweb.model.User;
import java.util.List;
public interface UserMapper {
List<User> getUser();
}
mapper.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.sbwithmybatisweb.mapper.UserMapper">
<select id="getUser" resultType="com.sbwithmybatisweb.model.User">
select * from tb_user
</select>
</mapper>
application.yml
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.sbwithmybatis2.model
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #配置mybatis输出日志
引入log4j配置文件,输出MyBatis语句
# log4j配置文件
#控制日志级别,在哪里输出Output pattern : date [thread] priority category - message FATAL 0 ERROR 3 WARN 4 INFO 6 DEBUG 7
log4j.rootLogger=info,ServerDailyRollingFile,stdout
log4j.appender.ServerDailyRollingFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.ServerDailyRollingFile.DatePattern='.'yyyy-MM-dd
# linux下目录为/
log4j.appender.ServerDailyRollingFile.File=D://logs/log.log
log4j.appender.ServerDailyRollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.ServerDailyRollingFile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p [%c] - %m%n
log4j.appender.ServerDailyRollingFile.Append=true
#控制台 显示的的方式为控制台普通方式
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#日志输出的格式
log4j.appender.stdout.layout.ConversionPattern=%d %-5p [%c{5}] - %m%n
#打印sql部分
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Connection = DEBUG
log4j.logger.java.sql.Statement = DEBUG
log4j.logger.java.sql.PreparedStatement = DEBUG
log4j.logger.java.sql.ResultSet = DEBUG
log4j
#配置logger扫描的包路径 这样才会打印sql
log4j.logger.com.sbwithmybatis2.mapper=DEBUG
引入Druid配置,在application.yml中添加如下配置
spring:
datasource:
username: root
password: bruis
url: jdbc:mysql://localhost:3306/jdy?characterEncoding=UTF-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
### 数据源的其他配置
initial-size: 5
min-idle: 5
max-active: 20
tomcat:
max-wait: 60000
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
pool-prepared-statements: true
### 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat, wall, log4j
max-pool-prepared-statement-per-connection-size: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
需要配置druid的监控页面,就需要在Druid配置文件中添加druidStatViewServlet()、druidWebStatFilter()两个方法。
DruidConfig配置文件
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DruidConfig {
//将所有前缀为spring.datasource下的配置项都加载到DataSource中
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource() {
return new DruidDataSource();
}
@Bean
public static PropertySourcesPlaceholderConfigurer propertyConfigure(){
return new PropertySourcesPlaceholderConfigurer();
}
@Bean
public PlatformTransactionManager transactionManager() throws Exception {
DataSourceTransactionManager txManager = new DataSourceTransactionManager();
txManager.setDataSource(dataSource());
return txManager;
}
@Bean
public ServletRegistrationBean druidStatViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
Map<String, String> initParams = new HashMap<>();
// 可配的属性都在 StatViewServlet 和其父类下
initParams.put("loginUsername", "admin-druid");
initParams.put("loginPassword", "111111");
servletRegistrationBean.setInitParameters(initParams);
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean druidWebStatFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
Map<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
filterRegistrationBean.setInitParameters(initParams);
filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
return filterRegistrationBean;
}
}
MyBatisDataSourceConfig.java
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
public class MyBatisDataSourceConfig {
@Autowired
private DataSource dataSource;
@Bean(name="sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean() {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// 添加XML目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
SqlSessionFactory sqlSessionFactory = bean.getObject();
sqlSessionFactory.getConfiguration().setCacheEnabled(Boolean.TRUE);
return sqlSessionFactory;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
MyBatisMapperScannerConfig.java
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@AutoConfigureAfter(MyBatisDataSourceConfig.class)
public class MyBatisMapperScannerConfig {
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
mapperScannerConfigurer.setBasePackage("com.sbwithmybatisweb.mapper");
return mapperScannerConfigurer;
}
}
以上就完成了SpringBoot整合MyBatis的代码。
二、引入Swagger
通过Swagger来调用项目的接口。
2.1 添加Swagger2依赖
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.6.1</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.6.1</version>
</dependency>
2.2 添加Swagger2配置类
在XXXApplication.java同级目录创建类Swagger2,basePackage指定将生成文档的接口路径。
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
@Configuration
@EnableSwagger2
public class Swagger2 {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage("com.sbwithmybatisweb"))
.paths(PathSelectors.any())
.build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
.title("sbwithmybatisweb")
.description("服务名:sbwithmybatisweb")
.version("1.0")
.build();
}
}
3 接口使用
@ApiOperation(value = "invoke", notes = "invoke测试")
@ApiImplicitParam(name = "param", value = "invoke入参", required = true, dataType = "String", paramType = "query")
@GetMapping(value = "/invoke")
public String invoke(String param) {
System.out.println("This springCloudConsumerService 1:" + param);
String result = param + "_consumer_" + System.currentTimeMillis();
return result;
}
4 访问swagger管理页面
访问 http://localhost:port/swagger-ui.html,看到如下界面即表示成功。
5 在代码的controller中添加swagger注解
UserService.java
import com.sbwithmybatisweb.mapper.UserMapper;
import com.sbwithmybatisweb.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public List<User> getUsers() {
return userMapper.getUser();
}
}
UserController.java
import com.sbwithmybatisweb.model.User;
import com.sbwithmybatisweb.service.UserService;
import io.swagger.annotations.ApiOperation;
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.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@Slf4j
@RestController
public class UserController {
@Autowired
UserService userService;
@ApiOperation(value = "获取用户信息", notes = "获取用户信息")
@GetMapping("getUsers")
public String getUsers(@RequestParam String operator) {
log.info("===================="+operator);
StringBuilder res = new StringBuilder();
for (User user : userService.getUsers()) {
res.append(user.getName());
}
return res.toString();
}
}
输入网址:http://localhost:8080/swagger-ui.html#/
进入Swagger页面,如图:
这里调用了两次接口,结果都被Druid进行了监控。先进入Druid监控页面
输入:
http://localhost:8080/druid/login.html
登录进入Druid监控首页
SQL语句调用的监控结果:
URI调用的监控结果,注意,Swagger的URI也被监控了:
至此,SpringBoot整合MyBatis并通过Druid进行数据库访问监控的实现完成。