最近在做自己的创业项目的网站,打算使用前后端分离的方式去做,前端使用vue,后端使用springboot,数据库连接池使用druid
第一次使用Java做web项目,梳理一下,做个记录。
1. druid简介:
Druid是阿里巴巴开源的数据库连接池,号称是Java语言中最好的数据库连接池,能够提供强大的监控和扩展功能
2. druid优点:
- 可以监控数据库访问性能,Druid内置提供了一个功能强大的
StatFilter
插件,能够详细统计SQL的执行性能,这对于线上分析数据库访问性能有帮助。 - 替换DBCP和C3P0,Druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
- 数据库密码加密。直接把数据库密码写在配置文件中,这是不好的行为,容易导致安全问题。DruidDriver和DruidDataSource都支持PasswordCallback。
- SQL执行日志,Druid提供了不同的
LogFilter
,能够支持Common-Logging、Log4j和JdkLog,你可以按需要选择相应的LogFilter,监控你应用的数据库访问情况。 - 扩展JDBC,如果你要对JDBC层有编程的需求,可以通过Druid提供的Filter-Chain机制,很方便编写JDBC层的扩展插件。
3. 第一种配置方式(application.properties)
pox.xml配置如下:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<!--<version>2.0.4.RELEASE</version>-->
<version>2.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.tilyp</groupId>
<artifactId>cxcy</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>cxcy</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-joda</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-parameter-names</artifactId>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
# mysql
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
# druid
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.properties配置
server.port=8000
spring.jpa.show-sql = true
logging.level.org.springframework.data=DEBUG
spring.jpa.hibernate.ddl-auto=
spring.application.name=cxcy
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/cxcy?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.druid.filters=stat,wall,slf4j
spring.datasource.druid.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.stat-view-servlet.deny=
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456
logging.level.root = INFO
logging.level.org.hibernate = INFO
logging.level.org.hibernate.type.descriptor.sql.BasicBinder = TRACE
logging.level.org.hibernate.type.descriptor.sql.BasicExtractor = TRACE
logging.level.org.springframework.web.servlet.DispatcherServlet = DEBUG
运行服务后访问http://127.0.0.1:8000/druid/login.html
输入配置中配置的用户名(admin)和密码(123456)后
4. 第二种配置方式(注解)
pom.xml配置同上,但是要修改springboot版本号,否则启动会报错如下错误
2019-12-19 11:04:00.732 ERROR 1480 --- [ main] o.s.b.d.LoggingFailureAnalysisReporter :
***************************
APPLICATION FAILED TO START
***************************
Description:
The bean 'dataSource', defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class], could not be registered. A bean with that name has already been defined in class path resource [com/tilyp/cxcy/webservice/config/DruidDBConfig.class] and overriding is disabled.
Action:
Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
springboot的版本配置如下:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.4.RELEASE</version>
<!--<version>2.2.1.RELEASE</version>-->
<relativePath/> <!-- lookup parent from repository -->
</parent>
创建一个配置类
@Configuration
@EnableAutoConfiguration(exclude = {
org.springframework.boot.autoconfigure.security.servlet.SecurityAutoConfiguration.class
})
public class DruidDBConfig {
private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
@Bean //声明其为Bean实例
@Primary //在同样的DataSource中,首先使用被标注的DataSource
public DataSource dataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl("jdbc:mysql://localhost:3306/cxcy?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&characterEncoding=utf8");
datasource.setUsername("root");
datasource.setPassword("123456");
datasource.setDriverClassName("com.mysql.jdbc.Driver");
//configuration
datasource.setInitialSize(5);
datasource.setMinIdle(5);
datasource.setMaxActive(20);
datasource.setMaxWait(60000);
datasource.setTimeBetweenEvictionRunsMillis(60000);
datasource.setMinEvictableIdleTimeMillis(300000);
datasource.setValidationQuery("SELECT 1 FROM DUAL");
datasource.setTestWhileIdle(true);
datasource.setTestOnBorrow(false);
datasource.setTestOnReturn(false);
datasource.setPoolPreparedStatements(true);
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
datasource.setFilters("stat,wall,slf4j");
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties("druid.stat.mergeSql\\=true;druid.stat.slowSqlMillis\\=5000");
return (DataSource) datasource;
}
}
配置监控拦截器
@WebFilter(filterName = "druidWebStatFilter",urlPatterns = "/*",initParams = {
// 忽略资源
@WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")
})
public class DruidStatFilter extends WebStatFilter {
}
配置监控视图
@WebServlet(urlPatterns = "/druid/*",initParams = {
//IP白名单 没有配置或者为空则允许所有访问
@WebInitParam(name="allow",value = "127.0.0.1"),
// IP黑名单 存在共同时,deny优先于allow
@WebInitParam(name = "deny", value = "192.168.1.10"),
// 用户名
@WebInitParam(name = "loginUsername", value = "admin"),
// 密码
@WebInitParam(name = "loginPassword", value = "123456"),
// 禁用HTML页面上的“Reset All”功能
@WebInitParam(name = "resetEnable", value = "false")
})
public class DruidStatViewServlet extends StatViewServlet {
}
在启动类添加注解 @ServletComponentScan
@SpringBootApplication
//配置druid必须加的注解,如果不加,访问页面打不开,filter和servlet、listener之类的需要单独进行注册才能使用,spring boot里面提供了该注解起到注册作用
@ServletComponentScan
public class CxcyApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(CxcyApplication.class, args);
}
}
配置到此结束有问题请加QQ群 526855734
我是一个不断学习的菜鸟