【机房报修管理系统】后端篇（三） 配置阿里巴巴Druid数据源

一、前情回顾

---

    在上一篇文章，我们初步搭建了一个SpringBoot，并且配置了一些如服务器端口号，tomcat编码格式的基本配置，这一次我们来配置MySQL数据源。

二、任务详情

---

这一次我们有以下的任务需要完成。

• 通过Maven导入阿里巴巴研发的数据源Druid
• 将相关的MySQL信息配置在Druid上
• 设置Druid的可视化监控界面
  
  
  

三、相关介绍

---

1.什么是Druid？

    Druid其实是阿里巴巴公司自主研发的一款数据池。说到这里，有的同学可能会问到：“数据池又是什么？”其实在我们在使用最基本的JDBC连接数据库，每一次需要查询数据库时，写下列这些模板代码

	try {
			Class.forName(driverClass);
			connection = DriverManager.getConnection(url, user, password);
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			rowSet = new CachedRowSetImpl();
			rowSet.populate(rs);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				statement.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}

每次都会新建一个Connection、Statement等的对象，用完还要一个个把它们关闭掉，这样做费时费力，由于仙剑对象会占用内存的，所以一旦在同一时间大量发送请求，那内存肯定是不够的。
    而数据池的出现，让这些问题能够迎刃而解，在《百度百科——数据池》的描述是这样的

    数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

    也就是说，有了数据池的介入，我们就不必每次查询数据库的时候手动创建和关闭连接对象，数据池帮助我们创建和关闭，从而提高了性能。
    而这个Druid数据池是由阿里巴巴开发的，总所周知，淘宝是属于阿里巴巴的，每次双十一的时候，由于大量的消费者在零点抢购折扣商品，导致大量同时并发，而阿里巴巴把Druid应用在了自家的淘宝后台上，抗住了大量的并发，经过了几年的双十一的实践，毫不夸张地说，Druid可能是现在唯一能够抗住这么大量并发的数据池。

2.Druid的WIKI

阿里巴巴Druid的WIKI：点我跳转

四、实践操作

---

    上面吹了那么久的牛，现在开始干正事，把上面设定的任务完成。

1.将Druid通过Maven导入

注意：由于我们在一开始搭建项目的时候已经导入了MySQL的JDBC驱动，如果没有按照上一篇教程做的同学在导入Druid之前先导入JDBC驱动

		 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

在pom.xml加入以下代码，导入Druid

  		<!-- 阿里开源数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <!--MyBatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

由于在这里我使用了Druid的SpringBoot版，需要提前导入MyBatis，不然启动项目时会发生以下错误：
java.lang.ClassNotFoundException: org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'dataSource': Unsatisfied dependency expressed through field 'basicProperties'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'spring.datasource-org.springframework.boot.autoconfigure.jdbc.DataSourceProperties': Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.boot.autoconfigure.jdbc.DataSourceProperties]: Constructor threw exception; nested exception is java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/embedded/EmbeddedDatabaseType
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:374) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1378) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:575) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:846) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:863) ~[spring-context-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546) ~[spring-context-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) [spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at com.repairsystem.RepairsystemApplication.main(RepairsystemApplication.java:10) [classes/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'spring.datasource-org.springframework.boot.autoconfigure.jdbc.DataSourceProperties': Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.boot.autoconfigure.jdbc.DataSourceProperties]: Constructor threw exception; nested exception is java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/embedded/EmbeddedDatabaseType
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:1270) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1164) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:538) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:273) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1237) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1164) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	... 19 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.boot.autoconfigure.jdbc.DataSourceProperties]: Constructor threw exception; nested exception is java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/embedded/EmbeddedDatabaseType
	at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:184) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:87) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:1262) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	... 30 common frames omitted
Caused by: java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/embedded/EmbeddedDatabaseType
	at org.springframework.boot.jdbc.EmbeddedDatabaseConnection.<clinit>(EmbeddedDatabaseConnection.java:50) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at org.springframework.boot.autoconfigure.jdbc.DataSourceProperties.<init>(DataSourceProperties.java:152) ~[spring-boot-autoconfigure-2.1.1.RELEASE.jar:2.1.1.RELEASE]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_172]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_172]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_172]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_172]
	at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:172) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
	... 32 common frames omitted
Caused by: java.lang.ClassNotFoundException: org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[na:1.8.0_172]
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[na:1.8.0_172]
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349) ~[na:1.8.0_172]
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[na:1.8.0_172]
	... 39 common frames omitted

2.在aplication.properties配置Druid

############################################################
#
# 配置数据源相关 使用阿里巴巴的 druid 数据源
#
############################################################

# 数据库地址
spring.datasource.url=jdbc:mysql://localhost:3306/machineroomsystem?useUnicode=true&characterEncoding=UTF-8&useSSL=false
# 数据库用户
spring.datasource.username=root
# 数据库密码
spring.datasource.password=123456
# 数据库驱动
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 初始化连接数
spring.datasource.druid.initial-size=1
# 最小连接池数量
spring.datasource.druid.min-idle=1
# 最大连接池数量
spring.datasource.druid.max-active=20
# 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
spring.datasource.druid.test-on-borrow=true
# 使用可视化统计界面
spring.datasource.druid.stat-view-servlet.allow=true

# WebStatFilter配置
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.css,/druid/*

# StatViewServlet配置
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=CheungChingYin
spring.datasource.druid.stat-view-servlet.login-password=test123456

3.启动项目，Druid是否生效

启动项目后，访问：http://127.0.0.1:8081/druid
会发现需要登录账户和密码

在第2步的时候我们在aplication.properties配置Druid，里面有一行是这样写的

spring.datasource.druid.stat-view-servlet.login-username=CheungChingYin
spring.datasource.druid.stat-view-servlet.login-password=test123456

所以账户和密码是

• 账户：CheungChingYin
• 密码：test123456

登录进去看见了Druid的可视化管理页面

看见数据源这里有刚才填入的信息即为成功

    到这里，Druid的配置已经完成了，如果您对次篇文章有疑问，可以在文章下方留言，谢谢您的阅读。如对【机房报修管理系统】系列文章有兴趣，可以关注或收藏我的文章，您的支持是我最大的动力，我会尽快推出下一期内容，敬请期待。