java JDBC连接池简单封装

java JDBC简单封装（MySQL）

---

此次封装使用maven构建

• 目录结构
  
• product数据库表建表语句

create table `product` (
    `p_id` int(32) not null unique primary key auto_increment,
    `p_name` varchar(50) not null,
    `p_size` double not null,
    `p_price` double not null,
    `p_date` datetime not null default now()
) engine=innodb;

• db.properties文件(位于src/main/resources文件夹下)

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/gdlt?characterEncoding=utf8&useSSL=true
username=root
password=1862
jdbcConnectionInitSize=10

• pom.xml文件

<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>com.unicom</groupId>
  <artifactId>testJDBC</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <dependencies>
  	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>5.1.47</version>
	</dependency>
  </dependencies>
</project>

• ConnectionPools.java文件

/**
 * Project Name:testJDBC
 * File Name:ConnectionPools.java
 * Package Name:com.unicom
 * Date:2018年12月25日下午5:21:28
 * Copyright (c) 2018 Research All Rights Reserved.
 *
 */

package com.unicom;

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.Properties;
import java.util.logging.Logger;

import javax.sql.DataSource;

/**
 * @ClassName ConnectionPools
 * @Description 数据库连接池
 * @date 2018年12月25日 下午5:21:28
 * @author matb
 * @Email
 * @version
 * @since JDK 1.8
 */
public class ConnectionPools implements DataSource {

	private ConnectionPools() {
	}

	private static class SingletonHolder {
		private final static ConnectionPools instance = new ConnectionPools();
	}

	public static ConnectionPools getInstance() {
		return SingletonHolder.instance;
	}

	/**
	 * 使用静态块代码，初始化连接池，创建连接池的中最小连接数量连接， 创建linkedlist集合，将这些连接放入集合中
	 */

	/**
	 * 创建linkedlist集合
	 */
	private static LinkedList<Connection> linkedlist = new LinkedList<Connection>();
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	/**
	 * 最小连接数量
	 */
	private static int jdbcConnectionInitSize;

	/**
	 * 当前最大连接数量=max*jdbcConnectionInitSize
	 */
	private static int max = 1;

	static {
		/**
		 * 通过反射机制获取访问db.properties文件
		 */
		InputStream is = ConnectionPools.class
				.getResourceAsStream("/db.properties");
		Properties prop = new Properties();
		try {
			/**
			 * 加载db.properties文件
			 */
			prop.load(is);
			/**
			 * 获取db.properties文件中的数据库连接信息
			 */
			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			username = prop.getProperty("username");
			password = prop.getProperty("password");
			jdbcConnectionInitSize = Integer
					.parseInt(prop.getProperty("jdbcConnectionInitSize"));

			Class.forName(driver);

			/**
			 * 创建最小连接数个数据库连接对象以备使用
			 */
			for (int i = 0; i < jdbcConnectionInitSize; i++) {
				Connection conn = DriverManager.getConnection(url, username,
						password);
				/**
				 * 将创建好的数据库连接对象添加到Linkedlist集合中
				 */

				linkedlist.add(conn);
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.CommonDataSource#getLogWriter()
	 */
	@Override
	public PrintWriter getLogWriter() throws SQLException {
		return null;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.CommonDataSource#setLogWriter(java.io.PrintWriter)
	 */
	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.CommonDataSource#setLoginTimeout(int)
	 */
	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.CommonDataSource#getLoginTimeout()
	 */
	@Override
	public int getLoginTimeout() throws SQLException {
		return 0;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.CommonDataSource#getParentLogger()
	 */
	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		return null;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see java.sql.Wrapper#unwrap(java.lang.Class)
	 */
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return null;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see java.sql.Wrapper#isWrapperFor(java.lang.Class)
	 */
	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return false;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.DataSource#getConnection()
	 */
	@Override
	public Connection getConnection() throws SQLException {
		/**
		 * 如果集合中没有数据库连接对象了，且创建的数据库连接对象没有达到最大连接数量，可以再创建一组数据库连接对象以备使用
		 * 
		 */
		if (linkedlist.size() == 0 && max <= 5) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			for (int i = 0; i < jdbcConnectionInitSize; i++) {
				Connection conn = DriverManager.getConnection(url, username,
						password);
				/**
				 * 将创建好的数据库连接对象添加到Linkedlist集合中
				 * 
				 */
				linkedlist.add(conn);
			}
			max++;
		}
		if (linkedlist.size() > 0) {
			/**
			 * 从linkedlist集合中取出一个数据库链接对象Connection使用
			 * 
			 */
			final Connection conn1 = linkedlist.removeFirst();
			/**
			 * 返回一个Connection对象，并且设置Connection对象方法调用的限制， 当调用connection类对象的close()方法时会将Connection对象重新收集放入linkedlist集合中。
			 */
			return (Connection) Proxy.newProxyInstance(
					/**
					 * 这里换成JdbcConnectionsPool.class.getClassLoader();也可以
					 */
					conn1.getClass().getClassLoader(),
					conn1.getClass().getInterfaces(), new InvocationHandler() {

						@Override
						public Object invoke(Object proxy, Method method,
								Object[] args) throws Throwable {
							if (!method.getName().equalsIgnoreCase("close")) {
								return method.invoke(conn1, args);
							} else {
								linkedlist.add(conn1);
								return null;
							}
						}
					});
		}
		return null;
	}

	/**
	 * TODO 简单描述该方法的实现功能（可选）.
	 * 
	 * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
	 */
	@Override
	public Connection getConnection(String username, String password)
			throws SQLException {
		return null;
	}

	public void release(Connection conn, Statement st, ResultSet rs) {
		if (rs != null) {
			try {
				/**
				 * 关闭存储查询结果的ResultSet对象
				 * 
				 */
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if (st != null) {
			try {
				/**
				 * 关闭负责执行SQL命令的Statement对象
				 * 
				 */
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				/**
				 * 关闭Connection数据库连接对象
				 * 
				 */
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}

• DBUtil.java文件

package com.unicom;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: 9.18JDBC
 * @description:
 * @author: matengbing
 * @create: 2018-09-18 14:18
 **/
public class DBUtil {
	private ConnectionPools connectionPools = ConnectionPools.getInstance();

	public DBUtil() {
	}

	/**
	 * 执行sql语句，增删改
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public boolean execute(List<String> sqls) {
		Connection conn = null;
		Statement st = null;
		boolean res = true;
		try {
			conn = connectionPools.getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			for (String sql : sqls) {
				st.execute(sql);
			}

			conn.commit();
			return res;
		} catch (Exception e) {
			e.printStackTrace();
			res = false;
			return res;
		} finally {
			connectionPools.release(conn, st, null);
		}
	}

	/**
	 * 查询数据
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> query(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			conn = connectionPools.getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			/**
			 * 获得结果集结构信息,元数据
			 */
			ResultSetMetaData md = rs.getMetaData();
			/**
			 * 获得列数
			 */
			int columnCount = md.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);

			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			connectionPools.release(conn, ps, rs);
		}
	}
}

• 测试文件(ProductTest.java)

/**
 * Project Name:testJDBC
 * File Name:ProductTest.java
 * Package Name:test
 * Date:2018年12月25日上午11:26:48
 * Copyright (c) 2018,  Research All Rights Reserved.
 *
 */

package test;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.unicom.DBUtil;

/**
 * @ClassName ProductTest
 * @Description 产品数据库操作测试
 * @date 2018年12月25日 上午11:26:48
 *
 * @author matb
 * @Email
 * @version
 * @since JDK 1.8
 */
public class ProductTest {
	public static void main(String[] args) {
		ProductTest productTest = new ProductTest();
		productTest.testQuery();
		// productTest.insertTest();
	}

	public void testQuery() {
		String querySql = "select p_id,p_name,p_price,p_size,p_date from product";
		DBUtil dbUtil = new DBUtil();
		List<Map<String, Object>> list = dbUtil.query(querySql, null);
		Iterator<Map<String, Object>> iterator = list.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = iterator.next();
			System.out.println("id:" + map.get("p_id"));
			System.out.println("name:" + map.get("p_name"));
			System.out.println("price:" + map.get("p_price"));
			System.out.println("size:" + map.get("p_size"));
			System.out.println("date:" + map.get("p_date"));

			System.out.println("-----------------");
		}
	}

	public void insertTest() {
		DBUtil dbUtil = new DBUtil();
		String insertSql = "insert into product(p_name,p_price,p_size,p_date) values('"
				+ "手机" + "','" + 2324 + "','" + 12 + "','"
				+ "2018-12-25 11:55:00" + "')";
		List<String> sqls = new ArrayList<>();
		/**
		 * 一次插入两条数据，测试事务
		 */
		sqls.add(insertSql);
		sqls.add(insertSql);
		boolean res = dbUtil.execute(sqls);
		System.out.println("插入数据" + res);
	}
}

• 输出

---

id:1
name:电脑
price:2333.0
size:12.0
date:2018-12-25 11:32:03.0
------------
id:2
name:手机
price:2344.0
size:12.0
date:2018-12-25 11:55:00.0