java_method_DataBaseManage操作数据库的方法

/**
* @Title: DataBaseManage.java
* @Package cn.com.qmhd.tools
* @Description: TODO
* @author eric
* @date 2013-11-11下午06:15:31
* @version V1.0
*/
package cn.com.qmhd.tools;

import java.io.File;
import java.io.FileInputStream;
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;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataBaseManage {

    private static ComboPooledDataSource dataSource = null;
    private static DataBaseManage dataBaseManage = null;
    
    public static DataBaseManage getInstance(){
        if (dataBaseManage==null) {
            dataBaseManage = new DataBaseManage();
        }
        return dataBaseManage;
    }
    
    /**
     * Get C3P0 data source.
     */
    private DataBaseManage() {
        Properties properties = new Properties();
        String strFilePath = DataBaseManage.class.getClassLoader().getResource(
                "").getFile().toString()
                + "config/DataBaseConfig.properties";
//        strFilePath = "F:/qmhd/qmhd/客户个性化产品/12368最高法院/应用源码/qmhd_supcourt/src/config/DataBaseConfig.properties";

        File file = new File(strFilePath);
        try {
            FileInputStream input = new FileInputStream(file);
            properties.load(input);
            dataSource = new C3p0DataSource(properties).getDataSource();
            Log.getInstance().printInfo("C3p0DataSource", dataSource.toString());
        } catch (Exception e) {
            Log.getInstance().printError(DataBaseManage.class.getName(),
                    e.toString());
        }
    }
    
    /**
     * Get SQL Connection.
     * @return Connection :    Correct.
     *            null       : SQL Exception or other errors.
     */
    public Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            return connection;
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"Get SQL Connection failed");
            return null;
        }
    }
    
    /**
     * Release SQL Connection.
     * 
     */
    public void releaseConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
                connection = null;
            }
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"Release SQL Connection failed");
        }
    }
    
    /**
     * Close SQL Statement.
     * 
     */
    public void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
                statement = null;
            }
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"Close SQL Statement failed");
        }
    }
    
    /**
     * Close SQL ResultSet.
     * 
     */
    public void closeResultSet(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"Close SQL ResultSet failed");
        }
    }
    
    /**
     * execute delete.create.drop
     * @param sSql : delete SQL.create SQL.drop SQL.
     * @param list : Batch processing Data,list is count,String[] is ?
     * @return n>0 : n is Handle Count
     *            0：  Processed,No results
     *            -2: sql error
     *         -1: connection is error
     */
    public int execute(String sSql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            int bRet = 0;
            connection = this.getConnection();
            if (connection == null) {
                return -1;
            }
            preparedStatement = connection.prepareStatement(sSql);
            bRet = preparedStatement.executeUpdate();
            return bRet;
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"SQL :"+sSql+" execute failed");
            return -2;
        }finally{
            closeStatement(preparedStatement);
            releaseConnection(connection);
        }
    }
    
    /**
     * execute insert,update,delete.
     * @param sSql : insert Batch processing SQL,update Batch processing SQL,delete Batch processing SQL.
     * @param list : Batch processing Data,list is count,String[] is ?
     * @return n>0 : n is Handle Count
     *            0：  Processed,No results
     *            -2: sql error
     *         -1: connection is error
     */
    public int execute(String sSql,List<String[]> list) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            int bRet = 0;
            connection = this.getConnection();
            if (connection == null) {
                return -1;
            }
            int nArg = 0;
            int columnArg = 0;
            String[] strs = null;
            preparedStatement = connection.prepareStatement(sSql);
            nArg = list.size();
            connection.setAutoCommit(false);
            for (int i = 0; i < nArg; i++) {
                strs = list.get(i);
                columnArg = strs .length;
                for (int j = 0; j < columnArg; j++) {
                    //如果是英文双引号替换成中文半角双引号
                    if (strs[j]!=null&&!"".equals(strs[j])) {
                        strs[j]=strs[j].replace("\'", "’");
                        strs[j]=strs[j].replace("\"", "”");
                    }
                    preparedStatement.setString((j+1), strs[j]);
                }
                columnArg=0;
                strs=null;
                preparedStatement.addBatch();
            }
            nArg = 0;
            list = null;
            int[] batchCount = preparedStatement.executeBatch();
            connection.commit();
            nArg = batchCount.length;
            for (int i = 0; i < nArg; i++) {
                bRet +=batchCount[i];
            }
            return bRet;
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"SQL :"+sSql+" execute failed");
            return -2;
        }finally{
            closeStatement(preparedStatement);
            releaseConnection(connection);
        }
    }
    
    /**
     * executeQuery 
     * @param sSql : query Batch processing SQL.
     * @param count : Back data Number of rows.
     * @param list : Batch processing Data,where is data,if not have where,list size is 0;
     * @return ArrayList<String[]> : HandleCorrect,list is row,String[] is line.
     * @return null : HandleError.
     */
    public List<String[]> executeQuery(String sSql,int count,List<String> list) {
        List<String[]> arrList = new ArrayList<String[]>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rSet = null;
        String[] sRets = null;
        
        String str="";
        try {
            connection = this.getConnection();
            if (connection == null) {
                return null;
            }
            preparedStatement = connection.prepareStatement(sSql);
            if (list!=null && list.size()!=0) {
                int nArg = list.size();
                for (int i = 0; i < nArg; i++) {
                    //如果是英文双引号替换成中文半角双引号
                    str = list.get(i);
                    if (str!=null&&!"".equals(str)) {
                        str = str.replace("\'", "’");
                        str = str.replace("\"", "”");
                    }
                    preparedStatement.setString((i+1), list.get(i));
                    str = "";
                }
                preparedStatement.addBatch();
                nArg = 0;
            }
            rSet = preparedStatement.executeQuery();
            while(rSet.next()) {
                sRets = new String[count];
                for (int i = 0; i < count; i++) {
                    sRets[i] = rSet.getString(i+1);
                }
                arrList.add(sRets);
                sRets = null;
            }
            str = null;
            return arrList;
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"SQL :"+sSql+" execute failed");
            return null;
        } finally {
            closeResultSet(rSet);
            closeStatement(preparedStatement);
            releaseConnection(connection);
        }
        
    }
    
    public List<String> executeInsertBackID(String sSql,List<String[]> list) {
        List<String> arrList = new ArrayList<String>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rSet = null;
        int nRets = 0;
        try {
            connection = this.getConnection();
            if (connection == null) {
                return null;
            }
            int nArg = 0;
            int columnArg = 0;
            String[] strs = null;
            preparedStatement = connection.prepareStatement(sSql,Statement.RETURN_GENERATED_KEYS);
            nArg = list.size();
            connection.setAutoCommit(false);
            for (int i = 0; i < nArg; i++) {
                strs = list.get(i);
                columnArg = strs .length;
                for (int j = 0; j < columnArg; j++) {
                    //如果是英文双引号替换成中文半角双引号
                    if (strs[j]!=null&&!"".equals(strs[j])) {
                        strs[j]=strs[j].replace("\'", "’");
                        strs[j]=strs[j].replace("\"", "”");
                    }
                    preparedStatement.setString((j+1), strs[j]);
                }
                columnArg=0;
                strs=null;
                preparedStatement.addBatch();
            }
            nArg = 0;
            list = null;
            preparedStatement.executeBatch();
            rSet = preparedStatement.getGeneratedKeys();
            while(rSet.next()) {
                nRets = rSet.getInt(1);
                arrList.add(nRets+"");
                nRets = 0;
            }
            connection.commit();
            return arrList;
        } catch (SQLException e) {
            ExceptionHeading.getException(this.getClass().getName(), e,"SQL :"+sSql+" executeInsert failed");
            return null;
        }finally{
            closeResultSet(rSet);
            closeStatement(preparedStatement);
            releaseConnection(connection);
        }
    }
    /**
     * executeQuery
     * @param sSql : query Batch processing SQL.
     * @param count : Back data Number of rows.
     * @param list : Batch processing Data,where is data,if not have where,list size is 0;
     * @return ArrayList< HashMap< String,String > > : HandleCorrect,list is row,HashMap is line,
     *         String(key) is line's name,String(value) is line's value.
     * @return null : HandleError.
     */
    public List<Map<String,String>> executeQuery(String sSql,List<String> list) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rSet = null;
        ResultSetMetaData rsmd = null;
        List<Map<String, String>> arrList = null;
        HashMap< String,String > map = null;
        int nset = 0;
        String sFiledName;
        String sFileValue;

        String str="";
        try {
            connection = this.getConnection();
            preparedStatement = connection.prepareStatement(sSql);
            if (list!=null && list.size()!=0) {
                int nArg = list.size();
                for (int i = 0; i < nArg; i++) {
                    //如果是英文双引号替换成中文半角双引号
                    str = list.get(i);
                    if (str!=null&&!"".equals(str)) {
                        str = str.replace("\'", "’");
                        str = str.replace("\"", "”");
                    }
                    preparedStatement.setString((i+1), list.get(i));
                    str="";
                }
                nArg = 0;
            }
            rSet = preparedStatement.executeQuery();

            arrList = new ArrayList<Map<String, String>>();
            
            while ( rSet.next() ) {
                map = new HashMap< String, String >();
                rsmd = rSet.getMetaData();
                nset=rsmd.getColumnCount();
                for (int i = 1; i <= nset; i++){
                    sFiledName = rsmd.getColumnName( i );
                    sFileValue = rSet.getString( sFiledName );
                    map.put( sFiledName , sFileValue );
                }
                arrList.add(map);
                map = null;
                rsmd = null;
                nset = 0;
                sFiledName = null;
                sFileValue = null;
            }
            str = null;
            return arrList;
        } catch (SQLException e){
            ExceptionHeading.getException(this.getClass().getName(), e,"SQL :"+sSql+" execute failed");
            return null;
        } finally {
            closeResultSet(rSet);
            closeStatement(preparedStatement);
            releaseConnection(connection);
        }
    }
}