JDBC通用工具类

由于公司的框架体系太成熟，所以工作一段时间后导致最底层的东西几乎忘掉，今天趁闲暇时间又把jdbc基础捡了一下并写了一个较为通用的工具类，其中包括事务模板，简单查询，修改，有不足的地方忘大牛们多提建议，拍转，工具类主要包括DaoSupport、DaoSupportTemplate、DaoSupportAction。

1、DaoSupport 工具类，封装了自己实现的连接池和jdbc简单的查询和修改。

/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

import com.jinfeng.practice.util.impl.DaoSupportTemplateImpl;

/**
* dao支持类
*
* @author feng.jin
* @version $Id: DaoSupport.java, v 0.1 2013-8-7 上午10:58:28 feng.jin Exp $
*/
public class DaoSupport {

    /** 数据连接池 */
    private static final List<Connection> connectionPool    = new LinkedList<Connection>();

    /** 驱动 */
    private static String                 driver;

    /** url*/
    private static String                 url;

    /** username */
    private static String                 username;

    /** password */
    private static String                 password;

    /** properties */
    private static final Properties       properties        = new Properties();

    /** 最大连接数 */
    private static final int              MAX               = 10;

    /** 活跃连接数 */
    private static int                    activeNum         = 10;

    /** 已用连接数 */
    private static int                    usedNum           = 0;

    /** 连接 */
    private Connection                    conn              = null;

    /** 语句平台 */
    private PreparedStatement             preparedStatement = null;

    /** 模板 */
    private final DaoSupportTemplate      daoSupportTemplate;

    /**
     * constructor
     */
    public DaoSupport() {

        daoSupportTemplate = new DaoSupportTemplateImpl();
        daoSupportTemplate.setDaoSupport(this);
    }

    static {
        try {

            //1.【初始化数据连接需要的数据】
            properties.load(DaoSupport.class.getResourceAsStream("/config/datasource.properties"));

            driver = properties.getProperty("jdbc.driver");
            url = properties.getProperty("jdbc.url");
            username = properties.getProperty("jdbc.username");
            password = properties.getProperty("jdbc.password");
            Class.forName(driver);

            //2.【初始化连接池】
            for (int i = 0; i < 10; i++) {

                Connection conn = DriverManager.getConnection(url, username, password);
                connectionPool.add(conn);

            }

        } catch (IOException e) {
            System.out.println(e);
        } catch (ClassNotFoundException e) {
            System.out.println(e);
        } catch (SQLException e) {
            System.out.println(e);
        }
    }

    /**
     * 获取连接
     *
     * @return
     */
    public synchronized Connection getConnection() {
        if (conn == null) {

            if (connectionPool != null && connectionPool.size() >= 0) {

                conn = connectionPool.get(0);
                connectionPool.remove(0);
                activeNum--;
                usedNum++;

            } else {
                throw new RuntimeException("当前使用的连接数已经超过" + MAX + ", 连接池没有足够的数据库连接!");
            }

        } else {
            return conn;
        }

        return conn;

    }

    /**
     * 获取语句执行器
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public PreparedStatement getPreparedStatement(Connection conn, String sql, List<Object> params) {

        try {

            if (preparedStatement == null) {
                preparedStatement = conn.prepareStatement(sql);
            }

            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    preparedStatement.setObject(i + 1, param);
                }

            }

        } catch (SQLException e) {
            System.out.println(e);
        }

        return preparedStatement;

    }

    public int excuteUpdate(String sql, List<Object> params) {

        conn = getConnection();
        int i = 0;

        PreparedStatement preparedStatement = getPreparedStatement(conn, sql, params);

        try {
            i = preparedStatement.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e);
        }

        return i;

    }

    /**
     *  释放连接
     */
    public synchronized void close() {

        if (conn != null) {

            connectionPool.add(conn);
            conn = null;
            activeNum++;
            usedNum--;
        }

    }

    /**
     *  释放指定连接
     */
    public synchronized void close(Connection conn) {

        if (conn != null) {

            connectionPool.add(conn);
            conn = null;
            activeNum++;
            usedNum--;
        }

    }

    /** *
     * 执行查询
     *
     * @param sql
     * @param params
     * @return
     */
    public ResultSet excuteQuery(String sql, List<Object> params) {

        Connection conn = getConnection();
        PreparedStatement preparedStatement = getPreparedStatement(conn, sql, params);



        ResultSet resultSet = null;
        try {
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
        }

        return resultSet;

    }

    /**
     * Getter method for property <tt>daoSupportTemplate</tt>.
     *
     * @return property value of daoSupportTemplate
     */
    public DaoSupportTemplate getDaoSupportTemplate() {
        return daoSupportTemplate;
    }

}

2、DaoSupportTemplate 模板，用于处理事务相关的操作，中间可用用户自行实现

/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;

import java.sql.Connection;

/**
* 模板
*
* @author feng.jin
* @version $Id: DaoSupportTemplate.java, v 0.1 2013-8-7 下午03:51:59 feng.jin Exp $
*/
public interface DaoSupportTemplate {

    /**
     * 执行
     *
     * @param conn
     * @param daoSupportCallBack
     */
    public void excute(Connection conn, DaoSupportCallBack daoSupportCallBack);

    /**
     * 获取
     *
     * @return
     */
    public DaoSupport getDaoSupport();

    /**
     * 设置
     *
     * @param daoSupport
     */
    public void setDaoSupport(DaoSupport daoSupport);
}

3、DaoSupportTemplateImpl 模板实现
/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util.impl;

import java.sql.Connection;

import com.jinfeng.practice.util.DaoSupport;
import com.jinfeng.practice.util.DaoSupportCallBack;
import com.jinfeng.practice.util.DaoSupportTemplate;

/**
* 模板
*
* @author feng.jin
* @version $Id: DaoSupportTemplateImpl.java, v 0.1 2013-8-7 下午03:54:00 feng.jin Exp $
*/
public class DaoSupportTemplateImpl implements DaoSupportTemplate {

    private DaoSupport daoSupport;

    /**
     * @see com.jinfeng.practice.util.DaoSupportTemplate#excute(java.sql.Connection)
     */
    @Override
    public void excute(Connection conn, DaoSupportCallBack daoSupportCallBack) {

        try {

            conn.setAutoCommit(false);
            daoSupportCallBack.process();
            conn.commit();

        } catch (Exception e) {
            System.out.println(e);
            try {
                conn.rollback();
            } catch (Exception e1) {
                System.out.println("回滚失败" + e);
            }
        } finally {
            daoSupport.close(conn);
        }

    }

    /**
     * Getter method for property <tt>daoSupport</tt>.
     *
     * @return property value of daoSupport
     */
    public DaoSupport getDaoSupport() {
        return daoSupport;
    }

    /**
     * Setter method for property <tt>daoSupport</tt>.
     *
     * @param daoSupport value to be assigned to property daoSupport
     */
    public void setDaoSupport(DaoSupport daoSupport) {
        this.daoSupport = daoSupport;
    }

}

4、DaoSupportCallBack 回调

/**
* Alipay.com Inc.
* Copyright (c) 2004-2013 All Rights Reserved.
*/
package com.jinfeng.practice.util;

/**
* 回调
*
* @author feng.jin
* @version $Id: DaoSupportCallBack.java, v 0.1 2013-8-7 下午03:59:18 feng.jin Exp $
*/
public interface DaoSupportCallBack {

    public void process();
}