SpringBoot进阶教程 | 第三篇：整合Druid连接池以及Druid监控

这篇文篇将介绍，如何通过SpringBoot整合Druid数据库链接池,实时监控数据库链接信息，为优化数据库性能提供更好的指导，同样将通过YML配置文件形式进行配置,方便简洁。

准备工作

环境：

windows
jdk 8
maven 3.0
IDEA

创建数据库表

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROp TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(15) NOT NULL,
  `sname` varchar(50) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `dept` varchar(25) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '李同学', '1', '王同学学习成绩很不错', '2010-07-22', '17');


构建工程


	4.0.0

	
		cn.zhangbox
		spring-boot-study
		1.0-SNAPSHOT
	

	cn.zhangbox
	spring-boot-druid
	0.0.1-SNAPSHOT
	jar

	spring-boot-druid
	this project for Spring Boot



	
		UTF-8
		UTF-8
		1.8
		
		3.4
		1.10
		1.2.0
		1.16.14
		1.2.41
		1.1.2
	

	
		
		
			aliyunmaven
			http://maven.aliyun.com/nexus/content/groups/public/
		
	

	

		
		
			org.mybatis.spring.boot
			mybatis-spring-boot-starter
			${mybatis-spring-boot.version}
		
		

		
		
			org.springframework.boot
			spring-boot-starter-web
		
		

		
		
			mysql
			mysql-connector-java
			runtime
		
		

		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
		

		
		
			org.apache.commons
			commons-lang3
			${commons-lang3.version}
		
		
			commons-codec
			commons-codec
			${commons-codec.version}
		
		

		
		
			com.alibaba
			fastjson
			${fastjson.version}
		
		

		
		
			com.alibaba
			druid-spring-boot-starter
			${druid.version}
		
		

		
		
			org.projectlombok
			lombok
			${lombok.version}
		
		
	

	
		spring-boot-druid
		
		
			org.apache.maven.plugins
			maven-compiler-plugin
			3.5.1
			
				1.8
				1.8
				UTF-8
			
		
		
			org.apache.maven.plugins
			maven-surefire-plugin
			2.19.1
		

		
			org.springframework.boot
			spring-boot-maven-plugin
			
				
					org.springframework
					springloaded
					1.2.4.RELEASE
				
			
			
				cn.zhangbox.admin.SpringBootDruidApplication
				-Dfile.encoding=UTF-8 -Xdebug
					-Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005
				
				true
				true
			
		
			
				org.springframework.boot
				spring-boot-maven-plugin
				
					cn.zhangbox.admin.SpringBootDruidApplication
					-Dfile.encoding=UTF-8 -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005
					true
					true
				
			
		
	



注意：这里引入了lombok插件节省编写实体类时候写get和set方法,这里在idea中进行set和get操作需要下载lombok插件,在设置页面的plugins中搜索lombok插件在中央插件库下载后重启idea即可,更详细的lombok使用教程可以查考:
程序员DD的lombok系列教程:
Lombok：让JAVA代码更优雅 
修改YML配置
#公共配置
server:
    port: 80
    tomcat:
      uri-encoding: UTF-8
spring:
  #激活哪一个环境的配置文件
  profiles:
    active: dev
  #连接池配置
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 配置测试查询语句
      validationQuery: SELECT 1 FROM DUAL
      # 初始化大小，最小，最大
      initialSize: 10
      minIdle: 10
      maxActive: 200
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 180000
      testOnBorrow: false
      testWhileIdle: true
      removeAbandoned: true
      removeAbandonedTimeout: 1800
      logAbandoned: true
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxOpenPreparedStatements: 100
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#mybatis
mybatis:
  # 实体类扫描
  type-aliases-package: cn.zhangbox.springboot.entity
  # 配置映射文件位置
  mapper-locations: classpath:mapper
	private Integer sno;
    
	private String sname;
    
	private String sex;
    
	private String birth;
    
	private String age;
    
	private String dept;

}

创建Controller
在工程java代码目录下创建controller的目录在下面创建StudentConteroller类加入以下代码:
@Controller
@RequestMapping("/student")
public class StudentConteroller {
    private static final Logger LOGGER = LoggerFactory.getLogger(StudentConteroller.class);

    @Autowired
    protected StudentService studentService;

    
    @ResponseBody
    @GetMapping("/list")
    public String list(String sname, Integer age, ModelMap modelMap) {
 String json = null;
 try {
     List studentList = studentService.getStudentList(sname, age);
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询成功");
     modelMap.put("studentList", studentList);
     json = JSON.toJSONString(modelMap);
 } catch (Exception e) {
     e.printStackTrace();
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询失败===>" + e);
     LOGGER.error("查询失败===>" + e);
     json = JSON.toJSONString(modelMap);
 }
 return json;
    }
}

创建Service
在工程java代码目录下面创建service目录在下面创建StudentService类加入以下代码:
public interface StudentService {

    
    List getStudentList(String sname, Integer age);
}

创建ServiceImpl
在工程java代码目录下的service的目录下面创建impl目录在下面创建StudentServiceImpl类加入以下代码:
@Service("StudentService")
@Transactional(readonly = true, rollbackFor = Exception.class)
public class StudentServiceImpl implements StudentService {

	@Autowired
	StudentDao studentDao;

	@Override
	public List getStudentList(String sname, Integer age) {
		return studentDao.getStudentList(sname,age);
	}
}

创建Dao
在工程java代码目录下创建dao的目录在下面创建StudentDao类加入以下代码:
public interface StudentDao {

	List getStudentList(@Param("sname")String sname, @Param("age")Integer age);

}

创建Mapper映射文件
在工程resource目录下创建mapper的目录在下面创建StudentMapper.xml映射文件加入以下代码:



    
    
 SELECT
     s.sno,
     s.sname,
     s.sex,
     s.dept,
     s.birth,
     s.age
 FROM
     student s
 WHERe
 1 = 1
 
     and s.sname = #{sname}
 
 
     and s.age = #{age}
 
    


创建启动类
@SpringBootApplication
@MapperScan("cn.zhangbox.springboot.dao")//配置mybatis接口包扫描
public class SpringBootDruidApplication extends SpringBootServletInitializer {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootDruidApplication.class, args);
	}
}

启动项目进行测试：
控制台打印
    .   ____   _     __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |___, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot :: (v1.5.3.RELEASE)

2018-07-07 10:54:52.404  INFO 24084 --- [    main] c.z.s.SpringBootDruidApplication  : Starting SpringBootDruidApplication on HP with PID 24084 (C:UsersAdministratorDesktopspring-boot-studyspring-boot-druidtargetclasses started by Administrator in C:UsersAdministratorDesktopspring-boot-study)
2018-07-07 10:54:52.445  INFO 24084 --- [    main] c.z.s.SpringBootDruidApplication  : The following profiles are active: dev
2018-07-07 10:54:52.570  INFO 24084 --- [    main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@53142455: startup date [Sat Jul 07 10:54:52 CST 2018]; root of context hierarchy
2018-07-07 10:54:52.997  INFO 24084 --- [kground-preinit] o.h.validator.internal.util.Version      : HV000001: Hibernate Validator 5.3.5.Final
2018-07-07 10:54:53.736  INFO 24084 --- [    main] o.s.b.f.s.DefaultListableBeanFactory     : Overriding bean definition for bean 'filterRegistrationBean' with a different definition: replacing [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=druidDBConfig; factoryMethodName=filterRegistrationBean; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [cn/zhangbox/springboot/config/DruidDBConfig.class]] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=com.alibaba.druid.spring.boot.autoconfigure.stat.DruidWebStatFilterConfiguration; factoryMethodName=filterRegistrationBean; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/stat/DruidWebStatFilterConfiguration.class]]
2018-07-07 10:54:54.887  INFO 24084 --- [    main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-07-07 10:54:54.903  INFO 24084 --- [    main] o.apache.catalina.core.StandardService   : Starting service Tomcat
2018-07-07 10:54:54.905  INFO 24084 --- [    main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.14
2018-07-07 10:54:55.123  INFO 24084 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]: Initializing Spring embedded WebApplicationContext
2018-07-07 10:54:55.124  INFO 24084 --- [ost-startStop-1] o.s.web.context.ContextLoader     : Root WebApplicationContext: initialization completed in 2554 ms
2018-07-07 10:54:55.522  INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'statViewServlet' to [/druidfavicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-07-07 10:54:58.066  INFO 24084 --- [    main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2018-07-07 10:54:58.068  INFO 24084 --- [    main] o.s.j.e.a.AnnotationMBeanExporter : Bean with name 'statFilter' has been autodetected for JMX exposure
2018-07-07 10:54:58.069  INFO 24084 --- [    main] o.s.j.e.a.AnnotationMBeanExporter : Bean with name 'dataSource' has been autodetected for JMX exposure
2018-07-07 10:54:58.077  INFO 24084 --- [    main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'dataSource': registering with JMX server as MBean [com.alibaba.druid.spring.boot.autoconfigure:name=dataSource,type=DruidDataSourceWrapper]
2018-07-07 10:54:58.081  INFO 24084 --- [    main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'statFilter': registering with JMX server as MBean [com.alibaba.druid.filter.stat:name=statFilter,type=StatFilter]
2018-07-07 10:54:58.101  INFO 24084 --- [    main] o.a.coyote.http11.Http11NioProtocol      : Initializing ProtocolHandler ["http-nio-8080"]
2018-07-07 10:54:58.118  INFO 24084 --- [    main] o.a.coyote.http11.Http11NioProtocol      : Starting ProtocolHandler ["http-nio-8080"]
2018-07-07 10:54:58.145  INFO 24084 --- [    main] o.a.tomcat.util.net.NioSelectorPool      : Using a shared selector for servlet write/read
2018-07-07 10:54:58.172  INFO 24084 --- [    main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-07-07 10:54:58.179  INFO 24084 --- [    main] c.z.s.SpringBootDruidApplication  : Started SpringBootDruidApplication in 7.666 seconds (JVM running for 10.386)
2018-07-07 11:00:00.245  INFO 24084 --- [nio-8080-exec-5] o.a.c.c.C.[Tomcat].[localhost].[/]: Initializing Spring frameworkServlet 'dispatcherServlet'
2018-07-07 11:00:00.246  INFO 24084 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : frameworkServlet 'dispatcherServlet': initialization started
2018-07-07 11:00:00.302  INFO 24084 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : frameworkServlet 'dispatcherServlet': initialization completed in 56 ms
2018-07-07 11:00:01.606  INFO 24084 --- [nio-8080-exec-5] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited

浏览器请求测试

本地日志打印效果


2018-07-07 10:54:52.439 [main] DEBUG cn.zhangbox.springboot.SpringBootDruidApplication - Running with Spring Boot v1.5.3.RELEASE, Spring v4.3.8.RELEASE
2018-07-07 11:00:01.715 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - ==>  Preparing: SELECT s.sno, s.sname, s.sex, s.dept, s.birth, s.age FROM student s WHERe 1 = 1 
2018-07-07 11:00:01.881 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - ==> Parameters: 
2018-07-07 11:00:01.931 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - <==      Total: 2

这里使用logback配置中将不同级别的日志设置了在不同文件中打印，这样很大程度上方便项目出问题查找问题。
Druid管控台




源码地址
Spring Boot整合Druid连接池以及Druid监控源码

SpringBoot进阶教程 | 第三篇：整合Druid连接池以及Druid监控

SpringBoot相关栏目本月热门文章