自定义JDBC数据库连接池小例子

import java.util.concurrent.locks.Condition; import java.util.concurrent.locks.ReentrantLock; * Created by wb-zhangkenan on 2017/5/3. * @date 2017/05/03 public class DataSourcePoolNew { * 最大连接数 private static final int COUNT = 10; * 存放数据库 private static final LinkedList Connection connections = new LinkedList (); * 创建锁 private static final ReentrantLock lock = new ReentrantLock(); private static final Condition notEmpty = lock.newCondition(); private static final Condition notFull = lock.newCondition(); * 数据库连接 private static String URL; * 用户名 private static String USER_NAME; * 密码 private static String PASS_WORD; * 驱动类型 private static String DRIVER_CLASS_NAME; * 存放属性信息 private static Properties properties = new Properties(); * 初始化信息 static { InputStream is = DataSourcePoolNew.class.getResourceAsStream("driver.properties"); try { properties.load(is); URL = (String) properties.get("url"); USER_NAME = (String) properties.get("userName"); PASS_WORD = (String) properties.get("passWord"); DRIVER_CLASS_NAME = (String) properties.get("driverClassName"); //加载驱动 Class.forName(DRIVER_CLASS_NAME); Connection connection = null; for (int i = 0; i i++) { connection = DriverManager.getConnection(URL, USER_NAME, PASS_WORD); connections.add(connection); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); * 获取Connection public static Connection getConnection() { final ReentrantLock reentrantLock = lock; reentrantLock.lock(); try { //如果没有连接了，则等待着新放入的连接 if (connections.isEmpty()) { notEmpty.await(); Connection connection = connections.removeFirst(); notFull.signalAll(); return connection; } catch (InterruptedException e) { e.printStackTrace(); } finally { reentrantLock.unlock(); return null; * 释放连接 * @param connection public static void release(Connection connection) { final ReentrantLock reentrantLock = lock; reentrantLock.lock(); try { if (connections.size() == COUNT) { notFull.await(); if (connection == null || connection.isClosed()) { connections.add(DriverManager.getConnection(URL, USER_NAME, PASS_WORD)); notEmpty.signalAll(); return; //恢复默认值 if (connection.getAutoCommit() == false) { connection.setAutoCommit(true); connections.add(connection); notEmpty.signalAll(); } catch (InterruptedException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { reentrantLock.unlock(); }CloseUtils：

package com.zkn.newlearn.jdbc.mysql.second;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

 * Created by wb-zhangkenan on 2017/5/3.

 * @date 2017/05/03

public final class CloseUtils {

 private static Logger log = LoggerFactory.getLogger(CloseUtils.class);

 * 关闭ResultSet

 * @param resultSet

 public static void close(ResultSet resultSet) {

 if (resultSet != null) {

 try {

 log.info("关闭了");

 resultSet.close();

 } catch (SQLException e) {

 log.error("关闭resultSet出现异常!!!");

 * 关闭statement

 * @param statement

 public static void close(Statement statement) {

 if (statement != null) {

 try {

 statement.close();

 } catch (SQLException e) {

 log.error("关闭statement出现异常!!!");

 * 关闭preparedStatement

 * @param preparedStatement

 public static void close(PreparedStatement preparedStatement) {

 if (preparedStatement != null) {

 try {

 preparedStatement.close();

 } catch (SQLException e) {

 log.error("关闭preparedStatement出现异常!!!");

 * 关闭connection

 * @param connection

 public static void close(Connection connection) {

 if (connection != null) {

 try {

 connection.close();

 } catch (SQLException e) {

 log.error("关闭connection出现异常!!!");

DEMO：

package com.zkn.newlearn.jdbc.mysql.third;

import com.zkn.newlearn.jdbc.mysql.second.CloseUtils;

import org.junit.Test;

import java.sql.*;

 * Created by zkn on 2017/5/3.

public class JDBCPoolTest {


 resultSet = statement.executeQuery("SELECT * FROM province_china");

 //取出查询出来的数据

 StringBuilder sb = new StringBuilder();

 while (resultSet.next()) {

 sb.append(resultSet.getLong("id")).append(" ");

 //这里需要注意的是下标是从1开始的，不是从0开始的

 sb.append(resultSet.getString(2)).append(" ");

 sb.append(resultSet.getString("cname")).append(" ");

 System.out.println(sb.toString());

 //清空原来的数据

 sb.delete(0, sb.length());

 } catch (SQLException e) {

 e.printStackTrace();

 } finally {

 CloseUtils.close(resultSet);

 CloseUtils.close(statement);

 DataSourcePoolNew.release(connection);

 * 预编译查询

 @Test

 public void testPreparedStatement() {

 Connection connection = null;

 PreparedStatement pst = null;

 ResultSet resultSet = null;

 try {

 //获取连接

 connection = DataSourcePoolNew.getConnection();

 if(connection == null){

 return;

 String sql = "SELECT * FROM TABLE_NAME WHERE EMPID = ?";

 //获取sql声明

 pst = connection.prepareStatement(sql);

 //pst.setLong(1,2);

 //封装查询条件

 pst.setString(1, "32151");

 //执行sql的操作

 resultSet = pst.executeQuery();

 StringBuilder sb = new StringBuilder();

 while (resultSet.next()) {

 sb.append(resultSet.getLong("id")).append(" ");

 sb.append(resultSet.getString(2));

 System.out.println(sb.toString());

 //清空原来的数据

 sb.delete(0, sb.length());

 } catch (SQLException e) {

 e.printStackTrace();

 } finally {

 closeResource(connection,pst,resultSet);

 * 单条插入

 @Test

 public void testInsert() {

 Connection connection = null;

 PreparedStatement pst = null;

 ResultSet resultSet = null;

 try {

 //获取连接

 connection = DataSourcePoolNew.getConnection();

 if(connection == null){

 return;

 //自动提交为false

 connection.setAutoCommit(false);

 //创建sql声明

 pst = connection.prepareStatement(

 "INSERT INTO TABLE_NAME (NAME,EMPID,ORG_ID,ORG_CODE,IS_ADMIN,GMT_CREATE,GMT_MODIFIED) VALUES (?,"

 + "?,?,?,?,now(),now())",

 Statement.RETURN_GENERATED_KEYS);

 pst.setString(1, "张三");

 pst.setString(2, "784550");

 pst.setLong(3, 2);

 pst.setString(4, "0.1.2");

 pst.setInt(5, 1);

 //执行插入操作

 int count = pst.executeUpdate();

 if (count 0) {

 System.out.println("插入成功!");

 } else {

 System.out.println("插入失败!");

 resultSet = pst.getGeneratedKeys();

 while (resultSet.next()) {

 System.out.println(String.format("主键值为%d", resultSet.getLong(1)));

 //提交操作

 connection.commit();

 } catch (SQLException e) {

 e.printStackTrace();

 //异常回滚

 try {

 connection.rollback();

 } catch (SQLException ee) {

 ee.printStackTrace();

 } finally {

 closeResource(connection,pst,resultSet);

 * 批量插入 需要设置 rewriteBatchedStatements=true

 @Test

 public void testBatchInsert() {

 Connection connection = null;

 PreparedStatement pst = null;

 ResultSet resultSet = null;

 try {

 //获取连接

 connection = DataSourcePoolNew.getConnection();

 if(connection == null){

 return;

 //自动提交为false

 connection.setAutoCommit(false);

 //创建sql声明

 pst = connection.prepareStatement(

 "INSERT INTO TABLE_NAME (NAME,EMPID,ORG_ID,ORG_CODE,IS_ADMIN,GMT_CREATE,GMT_MODIFIED) VALUES (?,"

 + "?,?,?,?,now(),now())",

 Statement.RETURN_GENERATED_KEYS);

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

 pst.setString(1, "张三");

 pst.setString(2, "784550");

 pst.setLong(3, 2);

 pst.setString(4, "0.1.2");

 pst.setInt(5, 1);

 pst.addBatch();

 int[] count = pst.executeBatch();

 if (count != null count.length 0) {

 System.out.println("插入成功!");

 } else {

 System.out.println("插入失败!");

 resultSet = pst.getGeneratedKeys();

 while (resultSet.next()) {

 System.out.println(String.format("主键值为%d", resultSet.getLong(1)));

 //提交操作

 connection.commit();

 } catch (SQLException e) {

 e.printStackTrace();

 //异常回滚

 try {

 connection.rollback();

 } catch (SQLException ee) {

 ee.printStackTrace();

 } finally {

 closeResource(connection,pst,resultSet);

 * 测试更新操作

 @Test

 public void testUpdate() {

 Connection connection = null;

 PreparedStatement pst = null;

 try {

 //获取连接

 connection = DataSourcePoolNew.getConnection();

 if(connection == null){

 return;

 //自动提交为false

 connection.setAutoCommit(false);

 //创建sql声明

 pst = connection.prepareStatement("update TABLE_NAME set name = ? where id =? and id = ? ");

 pst.setString(1, "李思思");

 pst.setLong(2, 1972);

 pst.setLong(3, 1995);

 int count = pst.executeUpdate();

 if (count 0) {

 System.out.println("更新成功!");

 } else {

 System.out.println("更新失败");

 return;

 //提交操作

 connection.commit();

 } catch (SQLException e) {

 e.printStackTrace();

 //异常回滚

 try {

 connection.rollback();

 } catch (SQLException ee) {

 ee.printStackTrace();

 } finally {

 closeResource(connection,pst);

 * 测试删除操作

 @Test

 public void testDelete() {

 Connection connection = null;

 PreparedStatement pst = null;

 try {

 //获取连接

 connection = DataSourcePoolNew.getConnection();

 if(connection == null){

 return;

 //自动提交为false

 connection.setAutoCommit(false);

 //创建sql的声明

 pst = connection.prepareStatement("DELETE FROM TABLE_NAME WHERE ID =? AND ID =? ");

 pst.setLong(1, 1972);

 pst.setLong(2, 1995);

 //执行sql

 pst.executeUpdate();

 //提交

 connection.commit();

 } catch (SQLException e) {

 e.printStackTrace();

 //异常回滚

 try {

 connection.rollback();

 } catch (SQLException ee) {

 ee.printStackTrace();

 } finally {

 closeResource(connection,pst);

 private void closeResource(Connection connection, PreparedStatement statement, ResultSet resultSet) {

 CloseUtils.close(resultSet);

 closeResource(connection, statement);

 private void closeResource(Connection connection, PreparedStatement statement) {

 CloseUtils.close(statement);

 DataSourcePoolNew.release(connection);

对于ORM框架而言，数据源的组织是一个非常重要的一部分，这直接影响到框架的性能问题。本文将通过对MyBatis框架的数据源结构进行详尽的分析，并且深入解析MyBatis的连接池。
jdbc操作根据bean类自动组装sql,天啦，我感觉我实现了hibernate 场景:需要将从ODPS数仓中计算得到的大额可疑交易信息导入到业务系统的mysql中供业务系统审核。最简单的方式是用阿里云的组件自动进行数据同步了。但是本系统是开放是为了产品化，要保证不同环境的可移植性，同时同步的表也就6个表，那么就利用现有的基于jdbc的规则引擎工程来自己实现数据的同步。