一、前情回顾
在上一篇文章,我们初步搭建了一个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的配置已经完成了,如果您对次篇文章有疑问,可以在文章下方留言,谢谢您的阅读。如对【机房报修管理系统】系列文章有兴趣,可以关注或收藏我的文章,您的支持是我最大的动力,我会尽快推出下一期内容,敬请期待。