我们知道每执行一次sql语句都需要创建与数据库的链接,这样太消耗性能了,于是下一步就是使用池化技术了。
这里说一下数据库连接池的基本实现和原理。连接池本质就是存放已经创建好的固定数量的链接实例,方便后期执行sql语句操作时获取。
步骤如下:1.实现java.sql.DataSource接口,然后使用LinkedList存储链接实例即可。
为了简单,这里省略了DataSource接口的其他需要实现的方法。只保留主要实现。
1.首先封装一个创建链接的类(前提是引入mysql-connector-java.jar包)。
/**
* 创建数据库连接
*
* @author Quifar
* @version V1.0
**/
public class ConnectionFactory {
private Connection connection;
private static String url;
private static String user;
private static String password;
private static String driver;
// 初始化获取config.properties属性
static {
//类路径下文件
String configPath = "config.properties";
InputStream is = ConnectionFactory.class.getClassLoader().getResourceAsStream(configPath);
Properties info = new Properties();
try {
info.load(is);
url = info.getProperty("url");
user = info.getProperty("username");
password = info.getProperty("password");
driver = info.getProperty("driver");
} catch (IOException e) {
e.printStackTrace();
}
}
public ConnectionFactory() {
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() {
return this.connection;
}
}
2.实现java.sql.DataSource接口
/**
* 自定义连接池
* <p>
* 实现自定义连接池步骤
* 1.实现javax.sql.DataSource接口
* 2.主要实现getConnection方法
*
* @author Quifar
* @version V1.0
**/
public class MyDatasource implements DataSource {
// 用来存储数据库连接的
final LinkedList<Connection> connectionPool = new LinkedList<>();
public MyDatasource() {
initPool();
}
private void initPool() {
for (int i = 0; i < 5; i++) {
connectionPool.add(new ConnectionFactory().getConnection());
}
}
@Override
public Connection getConnection() throws SQLException {
Connection con;
synchronized (connectionPool) {
if (connectionPool.size() == 0) {
//System.err.println("connection pool is busy now,create 5 connections again.");
//initPool();
throw new SQLException("connection pool is busy now,please wait amount.");
}
con = connectionPool.remove();
}
return con;
}
// 回收使用后的连接
public void backConnection(Connection con) {
connectionPool.add(con);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return getConnection();
}
public int poolSize() {
return connectionPool.size();
}
public LinkedList<Connection> getConnectionPool() {
return this.connectionPool;
}
// 其他方法省略
}
3.测试以上方法
@Test
public void test2() throws SQLException {
MyDatasource datasource = new MyDatasource();
System.err.println("连接池的有效链接:" + datasource.poolSize());
datasource.getConnectionPool().forEach(System.err::println);
Connection con = datasource.getConnection();
System.err.println("本次获取的链接实例是:" + con);
Statement stm = null;
ResultSet rs = null;
try {
stm = con.createStatement();
String sql = "select * from pn_user";
rs = stm.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
System.err.println("id:" + id + ",name:" + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
rs.close();
stm.close();
// 连接池回收资源
datasource.backConnection(con);
}
}
输出:
连接池的有效链接:5
com.mysql.jdbc.JDBC4Connection@61009542
com.mysql.jdbc.JDBC4Connection@77e9807f
com.mysql.jdbc.JDBC4Connection@448ff1a8
com.mysql.jdbc.JDBC4Connection@1a38c59b
com.mysql.jdbc.JDBC4Connection@7f77e91b
本次获取的链接实例是:com.mysql.jdbc.JDBC4Connection@61009542
id:10001,name:admin
id:10002,name:quifar
id:10003,name:1
id:10004,name:1111
id:10005,name:quifar