CODE - 多筆查詢的 java sample code

很久沒寫了, 突然要寫還真是花了很多時間

把以前寫的 code 翻出來記到 blog, 方便以後再碰到~

Model (Data Access Object)

...
public ArrayList doQuery(String ban) throws SQLException, Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT a.ban, a.storage_phone, a.storage_cont, a.storage_addr");
    sql.append("  FROM sc_loadplace_profile a");
    sql.append(" WHERE a.ban = '"+ban+"'");

    ArrayList aryList = new ArrayList();
    HashMap hashMap = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = this.con.createStatement();
        rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            hashMap = new HashMap();
            hashMap.put("ban", this.setEmpty(rs.getString("ban")));
            hashMap.put("storage_phone", this.setEmpty(rs.getString("storage_phone")));
            hashMap.put("storage_cont", this.setEmpty(rs.getString("storage_cont")));
            hashMap.put("storage_addr", this.setEmpty(rs.getString("storage_addr")));
            aryList.add(hashMap);
            hashMap = null;
        }
        rs.close();
        stmt.close();
    } catch(SQLException e) {
        System.out.println(sql.toString());
        throw e;
    } catch(Exception e) {sql.toString());
        throw e;
    } finally {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
    }
    return aryList;
}
...

Controller (Servlet)

...
public void exeQuery(HttpServletRequest req, HttpServletResponse resp, String exist_msg) {
    String ban = req.getParameter("ban");
    String goPage = "mt7U00.jsp";
    String msg = "";
    
    MT7U00DAO daoObj = new MT7U00DAO();
    ArrayList aryList = new ArrayList(); //¬d¸ßµ²ªG
    try {
        daoObj.openConnection();
        aryList = daoObj.doQuery(ban);
        msg = "11";
    } catch(SQLException e) {            
        e.printStackTrace(System.out);
        msg = "12";
    } catch(Exception e) {            
        e.printStackTrace(System.out);
        msg = "12";
    } finally {
        try {
            daoObj.closeConnection();
        } catch(SQLException se) {
            se.printStackTrace(System.out);
        }
    }

    daoObj = null;
    req.setAttribute("aryList", aryList);
    msg = (!exist_msg.equals("")) ? exist_msg : msg;
    try {
        RequestDispatcher rd = req.getRequestDispatcher(goPage+"?msg="+msg);
        rd.forward(req, resp);
    } catch (Exception e) {
        e.printStackTrace(System.out);
        System.out.println("RequestDispatcher Exception!!");
    }
}
...

View (JSP)

...
<%@ include file="mt7V00_cfg.jsp" %>
<%
String trp_id = (request.getParameter("trp_id") != null) ? request.getParameter("trp_id") : "";
String ban = (request.getParameter("ban") != null) ? request.getParameter("ban") : "";

ArrayList aryList = new ArrayList();
Hashtable hashTable = null;
int numRows = 0;

if (request.getAttribute("aryList") != null) {
    aryList = (ArrayList) request.getAttribute("aryList");
    numRows = aryList.size();
    if (numRows == 1) {
        hashTable = (Hashtable)aryList.get(0);
        String trp_id1 = (String)hashTable.get("trp_id");
        String ban1 = (String)hashTable.get("ban");
        String car_id1 = (String)hashTable.get("car_id");
        hashTable = null;
        response.sendRedirect("MT7V00?func=QUERYDETAIL&trp_id="+trp_id1+"&ban="+ban1+"&car_id="+car_id1); 
        return;
    } else if (numRows == 0) {
        response.sendRedirect("nodata.jsp");
        return;
    }
    SHOWMSG = numRows;
}
%>
...
...
<%
for (int i = 0; i < numRows; i++) {
    hashTable = (Hashtable)aryList.get(i);      
    String h_trp_id = (String)hashTable.get("trp_id");
    String h_ban = (String)hashTable.get("ban");
    String h_car_id = (String)hashTable.get("car_id");
    String h_driver = (String)hashTable.get("driver");
    String h_trp_sname = (String)hashTable.get("trp_sname");
    
    // 這裡顯示 table 的 tr 們

    hashTable = null;
} //end of for
%>
...