JDBC使用工具类

连接池使用DBCP，代码如下：

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import java.sql.SQLException;
import java.sql.Connection;
import java.util.Properties;

public class ConnectionSource {

    private static BasicDataSource dataSource = null;

    private final static String connUrl1 = "jdbc:mysql://localhost:3306/directory?useUnicode=true&characterEncoding=UTF-8";

    private final static String connUrl2 = "";

    private static String connUrl = "";

    private static boolean connBool = false;

    private final static String userName = "root";

    private final static String password = "root";

    public static void init(String connUrl) {

        if (dataSource != null) {
            try {
                dataSource.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            dataSource = null;
        }
        try {
            Properties p = new Properties();
            p.setProperty("driverClassName", "com.mysql.jdbc.Driver");
            p.setProperty("url", connUrl);
            p.setProperty("username", userName);
            p.setProperty("password", password);
            // 初始化连接
            p.setProperty("initialSize", "20");
            // 最大连接数量
            p.setProperty("maxActive", "200");
            // 最大空闲连接
            p.setProperty("maxIdle", "20");
            // maxWait的时间不要设得太长
            p.setProperty("maxWait", "1000");
            // 是否自动回收超时连接
            p.setProperty("removeAbandoned", "true");
            // 超时时间(以秒数为单位)
            p.setProperty("removeAbandonedTimeout", "120");
            p.setProperty("testOnBorrow", "true");
            // 是否在自动回收超时连接的时候打印连接的超时错误
            p.setProperty("logAbandoned", "true");
            dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static synchronized Connection getConn() throws SQLException {
        if (dataSource == null) {
            init(connUrl);
        }
        Connection conn = null;
        if (dataSource != null) {
            conn = dataSource.getConnection();
        }
        return conn;
    }

    public static synchronized Connection getConnection() {
        if (!connBool) {
            connUrl = connUrl1;
        } else {
            connUrl = connUrl2;
        }
        Connection conn = null;
        try {
            conn = getConn();
        } catch (SQLException e) {
        }
        if (conn == null) {
            connBool = true;
            dataSource = null;
            connUrl = connUrl2;
            try {
                conn = getConn();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    
    public static void main(String[] args) {
        ConnectionSource.getConnection();
    }
}

工具类JDBCUtils.java

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 数据库操作工具类
 * 
 * @author ZZJ
 */
public class JDBCUtils {

    /**
     * 执行查询
     * 
     * @param sql
     * @param pams like aa,bb,cc
     * @return
     */
    public static List<Map<String, Object>> getObjects(String sql, String pams) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(0);
        Connection conn = null;
        String[] ps = pams.split(",");
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ConnectionSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<String, Object>(0);
                for (int i = 0; i < ps.length; i++) {
                    if (rs.getObject(ps[i]) instanceof Clob) {
                        map.put(ps[i], rs.getString(ps[i]));
                    } else {
                        map.put(ps[i], rs.getObject(ps[i]));
                    }
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * 查询数量
     * 
     * @param sql like select count(id) as c from table
     * @return
     */
    public static int getCount(String sql) {
        List<Map<String, Object>> list = getObjects(sql, "c");
        if (list.size() > 0) {
            Map<String, Object> m = list.get(0);
            return ((Long) m.get("c")).intValue();
        }
        return 0;
    }

    /**
     * 查询数量
     * 
     * @param table
     * @return
     */
    public static int getCountByTable(String table) {
        String sql = "select count(id) as c from " + table;
        List<Map<String, Object>> list = getObjects(sql, "c");
        if (list.size() > 0) {
            Map<String, Object> m = list.get(0);
            return ((Long) m.get("c")).intValue();
        }
        return 0;
    }

    /**
     * 
     * @param table
     * @param id
     * @return
     */
    public synchronized static boolean excuteDelete(String table, Integer id) {
        String sql = "delete from " + table + " where id=" + id;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ConnectionSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    /**
     * 执行更新语句
     * 
     * @param sql
     * @return
     */
    public synchronized static boolean excuteUpdate(String sql) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ConnectionSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    /**
     * 执行更新语句
     * 
     * @param table 表名
     * @param map 数据对
     * @param id 数据ID
     * @return
     */
    public synchronized static boolean excuteUpdate(String table, Map<String, Object> map, Integer id) {
        Set<Map.Entry<String, Object>> sets = map.entrySet();
        String fields = "";
        Iterator<Map.Entry<String, Object>> iterator = sets.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> entry = iterator.next();
            fields += entry.getKey() + "=?,";
        }
        if (fields.endsWith(",")) {
            fields = fields.substring(0, fields.length() - 1);
        }
        String sql = "update " + table + " set " + fields + " where id=" + id;
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = ConnectionSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            iterator = sets.iterator();
            int index = 1;
            while (iterator.hasNext()) {
                Map.Entry<String, Object> entry = iterator.next();
                pstmt.setObject(index, entry.getValue());
                index++;
            }
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    /**
     * 执行插入数据操作，返回该条的数据的ID
     * 
     * @param table
     * @param seq
     * @param map
     * @return
     * @throws SQLException
     */
    public static int excuteInsert(String table, Map<String, Object> map) throws SQLException {
        Set<Map.Entry<String, Object>> sets = map.entrySet();
        String fields = "";
        String values = "";
        Iterator<Map.Entry<String, Object>> iterator = sets.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> entry = iterator.next();
            fields += entry.getKey() + ",";
            values += "?,";
        }
        if (fields.endsWith(",")) {
            fields = fields.substring(0, fields.length() - 1);
            values = values.substring(0, values.length() - 1);
        }
        StringBuffer sb = new StringBuffer(0);
        sb.append("insert into " + table + "(");
        sb.append(fields + ")");
        sb.append(" values(");
        sb.append(values);
        sb.append(")");

        int id = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = ConnectionSource.getConnection();
            pstmt = conn.prepareStatement(sb.toString(), new String[] { "id" });
            iterator = sets.iterator();
            int index = 1;
            while (iterator.hasNext()) {
                Map.Entry<String, Object> entry = iterator.next();
                pstmt.setObject(index, entry.getValue());
                index++;
            }
            pstmt.execute();
            rs = pstmt.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (null != conn) {
                conn.rollback();
            }
        } finally {
            try {
                rs.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return id;
    }
}