今天有机会接触到阿里的德鲁伊,德鲁伊的本质其实就是一个数据库连接池,在工作中不需要在自己写一个数据库连接池,直接拿阿里的连接池用它不香吗?
Druid是由阿里巴巴推出的数据库连接池。它结合了C3P0、DBCP、PROXOOL等数据库连接池的优点。之所以从众多数据库连接池中脱颖而出,还有一个重要的原因就是它包含控制台,很方便的帮助我们实现对于sql执行的监控。
先放一段手写连接池
package com.gavin.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
public class ConnectionPool {
final static String DRIVER="com.mysql.cj.jdbc.Driver";
final static String URL = "jdbc:mysql://127.0.0.1:3306/gavin?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&cachePrepStmts=true&reWriteBatchedStatements=true&useServerPrepStmts=true";
final static String USER = "gavin";
final static String PASSWORD = "955945";
private static LinkedList<Connection> pool;
private static int iniSize = 5;
private static int maxSize = 1;
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//初始化链接池
pool = new LinkedList<Connection>();
//创建5个连接对象
for (int i = 0; i < iniSize; i++) {
//初始化
Connection connection = initConnection();
if (null != connection) {
pool.add(connection);
System.out.println("连接初始化"+connection.hashCode()+"放入连接池中");
}
}
}
//初始化连接---私有的
private static Connection initConnection() {
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static Connection getConnection() {
Connection connection = null;
if (pool.size() > 0) {
connection = pool.removeFirst();//移除集合中的一个元素
System.out.println(connection.hashCode()+"连接被取走");
} else {
connection = initConnection();
System.out.println("连接池空,创建新连接"+connection.hashCode()+"放入连接池中");
}
return connection;
}
//归还连接
public static void returnConnection(Connection connection) {
if (null != connection) {
try {
if (!connection.isClosed()) {
try {
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
if (pool.size() < maxSize) {
pool.addLast(connection);
} else {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println( "链接不符合归还要求,归还失败");
}
}
}
看起来能用,但是还是在企业实践之后的 德鲁伊更好,接下来就来初识德鲁伊数据库连接池;
首先新建一个Spring–maven 项目,
在maven仓库中找到德鲁伊连接池
引入spring依赖以及druid依赖,配置好相关文件
接下来测试一下连接池能否链接数据库
开启测试
可以看到德鲁伊对数据库连接的监控能里,其实在德鲁伊里还有很多属性可以装配以实现对数据库连接的更加详细的监控
具体代码如下
maven依赖—
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>SpringDruid</groupId>
<artifactId>DruidJdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.18.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context-support -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>5.2.18.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.18.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.18.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 配置数据源用于连接数据库-->
<!-- alibaba德鲁伊连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="gavin"/>
<property name="username" value="gavin"/>
<property name="password" value="955945"/>
<property name="initialSize" value="1"/>
</bean>
<!--配置jdbc连接模板-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 配置连接的数据源-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--配置注入类-->
<!--<bean id="user" class="注入实现类">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
-->
</beans>
输出一下dataSource以及连接和连接数量
测试代码—>
package com.gavin.test;
import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.sql.SQLException;
public class test {
@Test
public void test() throws SQLException {
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext.xml");
DruidDataSource dataSource = ac.getBean("dataSource", DruidDataSource.class);
System.out.println(dataSource);
System.out.println(dataSource.getConnection());
System.out.println(dataSource.getConnectCount());
}
}
额,报错了,
数据库连接驱动写错了
修改后
运行结果----
但是企业对于一些编程是有要求的,例如,不建议将数据库连接参数直接放在bean配置文件中,而是单独拿出来,因为者是要初始化时候用的;
在resource文件夹中新建一个配置文件
jdbc.properties
额,报错了,
为什么呢?
debug一下,看看是哪里出错了,
查了一下才知道,spring在读取配置文件的时候,由于电脑本地的名为username=Gavin,所以再去读取jdbc配置文件的内容时不会再修改username对应的value,所以发生了错误,
办法很简单,就是修改jdbc配置文件中的内容