資料庫工具類實現

資料庫工具類實現

對資料庫資源進行統一申請和釋放，易於管理與使用，本例中對資料庫連線的獲取使用的c3p0的資料庫連線池，詳情可觀本部落格另一篇介紹

package com.Android.util;

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


public class JdbcUtil {  

    // 定義資料庫的連結  
    private Connection connection;  

    // 定義sql語句的執行物件  
    private PreparedStatement pstmt;  

    // 定義查詢返回的結果集合  
    private ResultSet resultSet;  


    public JdbcUtil() {  
        //從資料庫獲取資料庫連線
        connection = ConnectionManager.getInstance().getConnection();
    }  


    /** 
     * 執行更新操作 
     *  
     * @param sql 
     *            sql語句 
     * @param params 
     *            執行引數 
     * @return 執行結果 
     * @throws SQLException 
     */  
    public boolean updateByPreparedStatement(String sql, List<?> params)  
            throws SQLException {  
        boolean flag = false;  
        int result = -1;// 表示當使用者執行新增刪除和修改的時候所影響資料庫的行數  
        pstmt = connection.prepareStatement(sql);  
        int index = 1;  
        // 填充sql語句中的佔位符  
        if (params != null && !params.isEmpty()) {  
            for (int i = 0; i < params.size(); i++) {  
                pstmt.setObject(index++, params.get(i));  
            }  
        }  
        result = pstmt.executeUpdate();  
        flag = result > 0 ? true : false;  
        return flag;  
    }  

    /** 
     * 執行查詢操作 
     *  
     * @param sql 
     *            sql語句 
     * @param params 
     *            執行引數 
     * @return 
     * @throws SQLException 
     */  
    public List<Map<String, Object>> findResult(String sql, List<?> params)  
            throws SQLException {  
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();  
        int index = 1;  
        pstmt = connection.prepareStatement(sql);  
        if (params != null && !params.isEmpty()) {  
            for (int i = 0; i < params.size(); i++) {  
                pstmt.setObject(index++, params.get(i));  
            }  
        }  
        resultSet = pstmt.executeQuery();  
        ResultSetMetaData metaData = resultSet.getMetaData();  
        int cols_len = metaData.getColumnCount();  
        while (resultSet.next()) {  
            Map<String, Object> map = new HashMap<String, Object>();  
            for (int i = 0; i < cols_len; i++) {  
                String cols_name = metaData.getColumnName(i + 1);  
                Object cols_value = resultSet.getObject(cols_name);  
                if (cols_value == null) {  
                    cols_value = "";  
                }  
                map.put(cols_name, cols_value);  
            }  
            list.add(map);  
        }  
        return list;  
    }  

    /** 
     * 執行查詢操作 
     *  
     * @param sql 
     *            sql語句 
     * @param params 
     *            執行引數 
     * @return 
     * @throws SQLException 
     */  
    public List findResultToBeanList(String sql, List<?> params, Class<?> cls )  
            throws SQLException {  
        List<?> list = null;
        int index = 1;  
        pstmt = connection.prepareStatement(sql);  
        if (params != null && !params.isEmpty()) {  
            for (int i = 0; i < params.size(); i++) {  
                pstmt.setObject(index++, params.get(i));  
            }  
        }  
        resultSet = pstmt.executeQuery();  

        try {
            list =  GetData.resultSetToList(resultSet, cls);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        return list;
    }  

    /** 
     * 執行查詢操作 
     *  
     * @param sql 
     *            sql語句 
     * @param params 
     *            執行引數 
     * @return 
     * @throws SQLException 
     */  
    public int findResultTotalNumber(String sql, List<?> params )  
            throws SQLException {  
        int totalNumber = 0;
        int index = 1;  
        pstmt = connection.prepareStatement(sql);  
        if (params != null && !params.isEmpty()) {  
            for (int i = 0; i < params.size(); i++) {  
                pstmt.setObject(index++, params.get(i));  
            }  
        }  
        resultSet = pstmt.executeQuery();  

        try {
            if(resultSet.next()){
                totalNumber = resultSet.getInt(1);  
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        return totalNumber;
    }

    /** 
     * 釋放資源 
     */  
    public void releaseConn() {  
        if (resultSet != null) {  
            try {  
                resultSet.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if (pstmt != null) {  
            try {  
                pstmt.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if (connection != null) {  
            try {  
                connection.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    public static void main(String[] args) {  
        JdbcUtil jdbcUtil = new JdbcUtil();  

        try {  
            List<Map<String, Object>> result = jdbcUtil.findResult(  
                    "select * from newsTable", null);  
            for (Map<String, Object> m : result) {  
                System.out.println(m);  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            jdbcUtil.releaseConn();  
        }  
    }  
}