java手写数据库连接词_java设计思想-池化-手写数据库连接池

池：同一类对象集合

连接池的作用

1． 资源重用

由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数量)。

2． 更快的系统响应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

3． 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，使用数据库连接池技术。设置某一应用最大可用数据库连接数，避免某一应用独占所有数据库资源。

4． 统一的连接管理，避免数据库连接泄漏

在较为完备的数据库连接池实现中，可根据预先设定的连接占用超时时间，强制收回被超时占用的连接。从而避免了常规数据库连接操作中可能出现的资源泄漏(当程序存在缺陷时，申请的连接忘记关闭，这时候，就存在连接泄漏了)。

示例

配置文件

jdbcDriver=com.mysql.jdbc.Driver

jdbcurl=jdbc:mysql://localhost:3306/mybatis

userName=root

password=root

initCount=10

stepSize=4

poolMaxSize=150

数据库连接封装

public class PooledConnection {

private boolean isBusy = false;

private Connection connection;

public Connection getConnection() {

return connection;

}

public void setConnection(Connection connection) {

this.connection = connection;

}

public boolean isBusy() {

return isBusy;

}

public void setBusy(boolean isBusy) {

this.isBusy = isBusy;

}

public PooledConnection(Connection connection, boolean isBusy) {

this.connection = connection;

this.isBusy = isBusy;

}

public void close() {

this.isBusy = false;

}

public ResultSet queryBysql(String sql) {

Statement sm = null;

java.sql.ResultSet rs = null;

try {

sm = connection.createStatement();

rs = sm.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

return rs;

}

}

连接池接口

public interface IMyPool {

PooledConnection getConnection();

void createConnection(int count);

}

实现

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

import java.util.Vector;

public class MyPoolImpl implements IMyPool {

private static String jdbcDriver = null;

private static String jdbcUrl = null;

private static String userName = null;

private static String password = null;

private static int initCount;

private static int stepSize;

private static int poolMaxSize;

private static Vector pooledConnections = new Vector<>();

public MyPoolImpl() {

init();

}

private void init() {

InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");

//字节信息 key value 形式化

Properties pro = new Properties();

try {

pro.load(inputStream);

} catch (IOException e) {

e.printStackTrace();

}

jdbcDriver = pro.getProperty("jdbcDriver");

jdbcUrl = pro.getProperty("jdbcurl");

userName = pro.getProperty("userName");

password = pro.getProperty("password");

initCount = Integer.valueOf(pro.getProperty("initCount"));

stepSize = Integer.valueOf(pro.getProperty("stepSize"));

poolMaxSize = Integer.valueOf(pro.getProperty("poolMaxSize"));

try {

Driver mysqlDriver = (Driver) Class.forName(jdbcDriver).newInstance();

DriverManager.registerDriver(mysqlDriver);

createConnection(initCount);

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (InstantiationException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public PooledConnection getConnection() {

if (pooledConnections.size() == 0) {

System.out.println("获取链接管道失败，原因是连接池中没有可用管道");

throw new RuntimeException("创建管道对象失败，原因是即将超过最大上限值");

}

//连接池中的管道是没有超时 没有其他线程占用

PooledConnection connection = getRealConnection();

while (connection == null) {

createConnection(stepSize);

connection = getRealConnection();

try {

Thread.sleep(300);

} catch (InterruptedException e) {

e.printStackTrace();

}

}

return connection;

}

@Override

public void createConnection(int count) {

if (poolMaxSize > 0 && pooledConnections.size() + count > poolMaxSize) {

System.out.println("创建管道对象失败，原因是即将超过最大上限值");

throw new RuntimeException("创建管道对象失败，原因是即将超过最大上限值");

}

for (int i = 0; i < count; i++) {

try {

Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);

PooledConnection pooledConnection = new PooledConnection(conn, false);

pooledConnections.add(pooledConnection);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

private synchronized PooledConnection getRealConnection() {

for (PooledConnection conn : pooledConnections) {

if (!conn.isBusy()) {

Connection connection = conn.getConnection();

try {

//发送一个信息给数据库 2000毫秒内 收到返回信息 认为 这个管道没有超时

if (!connection.isValid(2000)) {

Connection validConn = DriverManager.getConnection(jdbcUrl, userName, password);

conn.setConnection(validConn);

}

} catch (SQLException e) {

e.printStackTrace();

}

conn.setBusy(true);

return conn;

}

}

return null;

}

}

连接池管理

public class PoolManager {

private static class createPool {

private static MyPoolImpl poolImpl = new MyPoolImpl();

}

/**

* 内部类单利模式产生使用对象

*@return

*/

public static MyPoolImpl getInstance() {

return createPool.poolImpl;

}

}

测试类

import java.sql.ResultSet;

import java.sql.SQLException;

public class MypoolTest {

private static MyPoolImpl poolImpl = PoolManager.getInstance();

public synchronized static void selctData() {

PooledConnection connection = poolImpl.getConnection();

ResultSet rs = connection.queryBysql("SELECT * FROM user");

try {

while (rs.next()) {

System.out.println(rs.getString("ID") + "\t\t");

System.out.println(rs.getString("USERNAME") + "\t\t");

System.out.println(rs.getString("PASSWORD") + "\t\t");

System.out.println();

}

rs.close();

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

public static void main(String[] args) {

for (int i = 0; i < 1500; i++) {

new Thread(new Runnable() {

@Override

public void run() {

selctData();

}

}).start();

}

}

}