上文记录了最基础的JDBC连接数据库的方法,但能看出一个问题,就是要不断的重复去创建connection和关闭connection,如果在对数据库的访问比较频繁的情况下,这种处理方式方式在性能方面是不合适的,下面使用JDBC配合数据库连接池来访问数据库。
除了mysql的驱动,还需引入数据库连接池,本次选用的是dbcp2(类似的还有c3p0、阿里的druid),其实数据库连接池的原理都类似,了解一个,上手其他的就非常容易。
引入依赖:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.5.0</version>
</dependency>
本次要查询的数据:
查询service代码:
package com.test.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.annotation.PostConstruct;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.stereotype.Service;
@Service
public class JdbcPooledService {
Connection connection;
BasicDataSource basicDataSource = new BasicDataSource();
@PostConstruct //用于进行初始化操作,此处初始化连接池
public void initPool() {
System.out.println("PostConstruct init.....");
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/test");
}
public void testJdbc() throws Exception {
System.out.println(basicDataSource.getNumActive());
connection = basicDataSource.getConnection();
Statement statement = connection.createStatement();
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
resultSet.close();
statement.close();
connection.close(); //连接池的连接同样需要调用close方法,但此时的close不是关闭连接,而是将连接还回连接池
}
}
测试类:
package com.test;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.test.database.JdbcPooledService;
public class Main {
public static void main(String[] args) throws BeansException, Exception {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-context.xml");
context.getBean(TestService.class).test();
// context.getBean(JdbcService.class).testJdbc();
for (int i = 0; i < 10; i++) {
System.out.println(i);
context.getBean(JdbcPooledService.class).testJdbc();
}
}
}
打印输出为:
PostConstruct init.....
test service
0
0
tim
1
0
tim
2
0
tim
3
0
tim
4
0
tim
5
0
tim
6
0
tim
7
0
tim
8
0
tim
9
0
tim
若在调用结束时不关闭(将连接还回连接池)connection:
package com.test.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.annotation.PostConstruct;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.stereotype.Service;
@Service
public class JdbcPooledService {
Connection connection;
BasicDataSource basicDataSource = new BasicDataSource();
@PostConstruct //用于进行初始化操作,此处初始化连接池
public void initPool() {
System.out.println("PostConstruct init.....");
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/test");
}
public void testJdbc() throws Exception {
System.out.println(basicDataSource.getNumActive());
connection = basicDataSource.getConnection();
Statement statement = connection.createStatement();
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
resultSet.close();
statement.close();
//connection.close();
}
}
继续调用测试类,结果为:
PostConstruct init.....
test service
0
0
tim
1
1
tim
2
2
tim
3
3
tim
4
4
tim
5
5
tim
6
6
tim
7
7
tim
8
8
现象是建立了8个连接就不再建立了,原因是没有调用connection的close方法,而dbcp2的默认最大连接数是8,所以已经到了最大连接数,注意在使用连接池获取连接时,务必关闭连接。
默认最大连接数:
public static final int DEFAULT_MAX_TOTAL = 8;
连接池获取的连接调用close方法的源码最终调用为:
方法声明:
public synchronized void close() throws SQLException
关键调用:
pool.returnObject(this);
将此连接还回到连接池。