数据库阿里连接池 druid 配置详解
什么是 druid
Druid 是阿里巴巴开源的一个数据库连接池、监控组件。 druid中维护了一个数据库连接池,并提供了一些监控统计和扩展功能。
Druid提供的功能:
- 数据库连接池
- 监控统计
- 扩展JDBC
- SQL注入检查:wall filter
- 大数据量低延时:PSCache
- 干扰SQL执行: stat filter
- 统计SQL性能
如何配置druid连接池
在maven中引入以下依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
在application.properties
中添加以下配置:
# 数据库主从配置
spring.datasource.url = jdbc:mysql://ip:port/dbName?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
spring.datasource.username = username
spring.datasource.password = password
# druid连接池配置
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.filters = stat, wall, logback
spring.datasource.max-active = 20
spring.datasource.initial-size = 1
spring.datasource.min-idle = 3
spring.datasource.max-wait = 60000
spring.datasource.validation-query = SELECT 1
spring.datasource.test-while-idle = true
spring.datasource.test-on-borrow = false
spring.datasource.test-on-return = false
spring.datasource.time-between-eviction-runs-millis = 60000
spring.datasource.min-evictable-idle-time-millis = 300000
spring.datasource.log-abandoned = true
spring.datasource.remove-abandoned = true
spring.datasource.remove-abandoned-timeout-millis = 1800000
spring.datasource.pool-prepared-statements = true
spring.datasource.max-pool-prepared-statement-per-connection-size = 20
参数解释
spring.datasource.url
: 数据库连接urlspring.datasource.username
: 数据库连接用户名spring.datasource.password
: 数据库连接密码spring.datasource.type
: 连接池类型为druidspring.datasource.driver-class-name
: 数据库驱动类名spring.datasource.filters
: 连接池所用多种过滤器spring.datasource.max-active
: 连接池最大连接数spring.datasource.initial-size
: 连接池初始连接数spring.datasource.min-idle
: 连接池最小闲置连接数spring.datasource.max-wait
: 连接池最大等待时长spring.datasource.validation-query
: 连接验证的sql语句spring.datasource.test-while-idle
: 是否开启空闲检查spring.datasource.test-on-borrow
: 是否开启连接获取时检查spring.datasource.test-on-return
: 是否开启连接返回时检查spring.datasource.time-between-eviction-runs-millis
: 空闲检测周期spring.datasource.min-evictable-idle-time-millis
: 最小空闲时间spring.datasource.log-abandoned
: 是否打印连接泄露日志spring.datasource.remove-abandoned
: 是否自动回收超过最大闲置时间的连接spring.datasource.remove-abandoned-timeout-millis
: 超时时间由检测自动回收功能spring.datasource.pool-prepared-statements
: 是否缓存prepared statementsspring.datasource.max-pool-prepared-statement-per-connection-size
: 单个连接最大缓存prepare statement数量
示例
第一条示例
@Service
public class UserService {
@Autowired
private UserDao userDao;
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private DataSource dataSource;
private static final Logger logger = LoggerFactory.getLogger(UserService.class);
/**
* 添加用户
*/
public void addUser(String name, Integer age) {
userDao.addUser(name, age);
}
/**
* JdbcTemplate操作数据库
*/
public void addUserByJdbcTemplate(String name, Integer age) {
String sql = "INSERT INTO user(name, age) VALUES(?,?)";
try {
jdbcTemplate.update(sql, name, age);
} catch (DataAccessException e) {
logger.error("addUserByJdbcTemplate异常:", e);
}
}
/**
* DruidDataSource获取连接
*/
public void addUserByDataSource(String name, Integer age) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
String sql = "INSERT INTO user(name, age) VALUES(?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.executeUpdate();
} catch (SQLException e) {
logger.error("addUserByDataSource异常:", e);
} finally {
try {
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
logger.error("addUserByDataSource关闭连接异常:", e);
}
}
}
}
上述代码中使用了三种方式操作数据库,分别是:
- 使用
UserDao
中封装好的增加用户的方法 - 使用
JdbcTemplate
对象操作数据库 - 使用
DataSource
对象获取连接,再通过PrepareStatement
执行增加操作
第二条示例
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
/**
* 数据库配置例子
* <p>
* Created by jim on 2017/6/24.
*/
public class ApplicationTest {
private static final String DB_URL = "jdbc:mysql://localhost/test";
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";
@Autowired
private DataSource dataSource;
@Autowired
private JdbcTemplate jdbcTemplate;
public void test() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUrl(DB_URL);
druidDataSource.setUsername(USERNAME);
druidDataSource.setPassword(PASSWORD);
druidDataSource.setMaxActive(20);
druidDataSource.setInitialSize(1);
druidDataSource.setMinIdle(3);
druidDataSource.setMaxWait(60000);
druidDataSource.setPoolPreparedStatements(true);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
dataSource = druidDataSource;
String sql = "INSERT INTO user(name,age) VALUES (?,?)";
jdbcTemplate.update(sql, "name", 18);
}
}
上述代码中,构建了一个DruidDataSource
对象,设置连接url、用户名、密码等连接池参数,之后通过set
方法注入到dataSource
属性。
通过注入的JdbcTemplate
对象即可完成数据库增加操作。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:数据库阿里连接池 druid配置详解 - Python技术站