[java程式碼]業務邏輯查詢幫助類

用於執行查詢語句

以HashMap的形式返回單條記錄.

以HashMap列表的形式返回多條記錄.

package com.common.businesslogic;

import com.exception.GFPortalException;
import com.util.CommonUtility;
import com..util.ConnMgr;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Description : 業務邏輯查詢幫助類
 *

 * Author : husthxd
 * Date: 2005-02-28
 * Time: 14:51:43
 * Version 1.0
 */
public class BusinessLogicQueryHelper {
    static Logger logger = Logger.getLogger(BusinessLogicQueryHelper.class);

    private BusinessLogicQueryHelper() {
    }

    /**
     * @return
     */
    public static BusinessLogicQueryHelper factory() {
        return new BusinessLogicQueryHelper();
    }
    //設定引數
    /**
     * @param pstmt
     * @param values
     * @throws SQLException
     */
    private void setParameters(PreparedStatement pstmt, List values)
            throws SQLException {
        for (int i = 0; i < values.size(); i++) {
            logger.info("第" + i + "個引數為:" + values.get(i));
            try {
                pstmt.setObject(i + 1, values.get(i));
            } catch (Exception e) {
                logger.debug("不能設定引數 : " + e);
                throw new SQLException("不能設定引數 : " + e);
            }
        }
    }

    /**
     * 執行sql,不帶引數並以List的形式獲取結果集
     *
     * @param sqlstmt
     * @return
     * @throws GFPortalException
     */
    public final List getRecordSet(Connection conn, String sqlstmt) throws GFPortalException {
        return getRecordSet(conn, sqlstmt, null);
    }

    /**
     * 獲取記錄集列表
     *
     * @param conn
     * @param sqlstmt
     * @param values
     * @return
     * @throws GFPortalException
     */
    public final List getRecordSet(Connection conn, String sqlstmt, List values) throws GFPortalException {
        //HashMap集合
        List resultList = new ArrayList();
        //執行語句
        PreparedStatement pstmt;
        ResultSet rs = null;
        //連線
        try {
            //獲取sql語句
            pstmt =
                    conn.prepareStatement(sqlstmt,
                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
            //如果列表物件不為空並且列表中有資料,設定引數
            if (!CommonUtility.isNull(values) && !values.isEmpty()) {
                setParameters(pstmt, values);
            }
        } catch (Exception e) {
            logger.debug("查詢初始化失敗!" + e);
            throw new GFPortalException("查詢初始化失敗!" + e);
        }
        //獲取結果集
        try {
            rs = pstmt.executeQuery(); //結果集
            ResultSetMetaData rsmd = rs.getMetaData(); //後設資料
            while (rs.next()) {
                //構造HashMap
                HashMap record = new HashMap();
                //獲取其中的一條記錄
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    //放入到HashMap中
                    record.put(rsmd.getColumnName(i).toLowerCase(),
                            rs.getObject(i));
                }
                //放入結果集列表中
                resultList.add(record);
            }
        } catch (SQLException e) {
            logger.debug("查詢出錯!" + e);
            throw new GFPortalException("查詢出錯!" + e.getMessage());
        } finally {
            try {
                ConnMgr.closePreparedStatement(pstmt);
                rs.close();
                pstmt.close();
            } catch (Exception e2) {

            }
        }
        //返回HashMap
        return resultList;
    }

    /**
     * 獲取單條記錄的Map
     *
     * @param conn
     * @param sqlstmt
     * @param values
     * @return
     * @throws GFPortalException
     */
    public final HashMap getRecord(Connection conn, String sqlstmt, List values) throws GFPortalException {
        HashMap record = new HashMap();
        try {
            List recordList = getRecordSet(conn, sqlstmt, values);
            record = (HashMap) recordList.get(0);
        } catch (Exception e) {
            logger.debug("獲取資訊出錯!" + e);
            throw new GFPortalException("獲取資訊出錯!" + e);
        }
        //返回HashMap
        return record;
    }

    /**
     * @param conn
     * @param sqlstmt
     * @return
     * @throws GFPortalException
     */
    public final HashMap getRecord(Connection conn, String sqlstmt) throws GFPortalException {
        return getRecord(conn, sqlstmt, null);
    }

}