license-manage/src/main/java/jp/gr/java_conf/u6k/license_manage/web/servlet/ListServlet.java

   1 \r
   2 package jp.gr.java_conf.u6k.license_manage.web.servlet;\r
   3 \r
   4 import java.io.IOException;\r
   5 import java.sql.Connection;\r
   6 import java.sql.DriverManager;\r
   7 import java.sql.PreparedStatement;\r
   8 import java.sql.ResultSet;\r
   9 import java.sql.SQLException;\r
  10 import java.util.ArrayList;\r
  11 import java.util.HashMap;\r
  12 import java.util.List;\r
  13 import java.util.Map;\r
  14 import java.util.ResourceBundle;\r
  15 \r
  16 import javax.servlet.ServletException;\r
  17 import javax.servlet.http.HttpServlet;\r
  18 import javax.servlet.http.HttpServletRequest;\r
  19 import javax.servlet.http.HttpServletResponse;\r
  20 import javax.servlet.http.HttpSession;\r
  21 \r
  22 @SuppressWarnings("serial")\r
  23 public class ListServlet extends HttpServlet {\r
  24 \r
  25     public static final int PAGE_MAX_RECORD_NUMBER = 100; // TODO\r
  26 \r
  27     @Override\r
  28     protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {\r
  29         System.out.println("page_no: " + req.getParameter("p"));\r
  30 \r
  31         HttpSession se = req.getSession(true);\r
  32 \r
  33         if (req.getParameter("p") != null) {\r
  34             try {\r
  35                 int pageNo = Integer.parseInt(req.getParameter("p"));\r
  36                 se.setAttribute("page_no", pageNo);\r
  37             } catch (NumberFormatException e) {\r
  38                 // TODO エラーログ出力。\r
  39             }\r
  40         }\r
  41 \r
  42         if (se.getAttribute("key_product_id") == null) {\r
  43             se.setAttribute("key_product_id", new int[0]);\r
  44         }\r
  45         int[] keyProductIds = (int[]) se.getAttribute("key_product_id");\r
  46 \r
  47         if (se.getAttribute("key_section_id") == null) {\r
  48             se.setAttribute("key_section_id", new int[0]);\r
  49         }\r
  50         int[] keySectionIds = (int[]) se.getAttribute("key_section_id");\r
  51 \r
  52         if (se.getAttribute("key_license_number_from") == null) {\r
  53             se.setAttribute("key_license_number_from", null);\r
  54         }\r
  55         Integer keyLicenseNumberFrom = (Integer) se.getAttribute("key_license_number_from");\r
  56 \r
  57         if (se.getAttribute("key_license_number_to") == null) {\r
  58             se.setAttribute("key_license_number_to", null);\r
  59         }\r
  60         Integer keyLicenseNumberTo = (Integer) se.getAttribute("key_license_number_to");\r
  61 \r
  62         if (se.getAttribute("key_license_used_number_from") == null) {\r
  63             se.setAttribute("key_license_used_number_from", null);\r
  64         }\r
  65         Integer keyLicenseUsedNumberFrom = (Integer) se.getAttribute("key_license_used_number_from");\r
  66 \r
  67         if (se.getAttribute("key_license_used_number_to") == null) {\r
  68             se.setAttribute("key_license_used_number_to", null);\r
  69         }\r
  70         Integer keyLicenseUsedNumberTo = (Integer) se.getAttribute("key_license_used_number_to");\r
  71 \r
  72         if (se.getAttribute("key_keyword") == null) {\r
  73             se.setAttribute("key_keyword", null);\r
  74         }\r
  75         String keyKeyword = (String) se.getAttribute("key_keyword");\r
  76 \r
  77         if (se.getAttribute("page_no") == null) {\r
  78             se.setAttribute("page_no", 0);\r
  79         }\r
  80         int pageNo = (Integer) se.getAttribute("page_no");\r
  81 \r
  82         try {\r
  83             Object[] result = this.findLicenseBy(keyProductIds, keySectionIds, keyLicenseNumberFrom, keyLicenseNumberTo, keyLicenseUsedNumberFrom, keyLicenseUsedNumberTo, keyKeyword, pageNo);\r
  84             List<Map<String, Object>> licenseList = (List<Map<String, Object>>) result[0];\r
  85             int recordCount = (Integer) result[1];\r
  86             req.setAttribute("licenseList", licenseList);\r
  87             req.setAttribute("recordCount", recordCount);\r
  88 \r
  89             List<Map<String, Object>> productList = this.findProductAll();\r
  90             req.setAttribute("productList", productList);\r
  91 \r
  92             List<Map<String, Object>> sectionList = this.findSectionAll();\r
  93             req.setAttribute("sectionList", sectionList);\r
  94 \r
  95             req.getRequestDispatcher("/WEB-INF/jsp/list.jsp").forward(req, resp);\r
  96         } catch (ClassNotFoundException e) {\r
  97             throw new ServletException(e);\r
  98         } catch (SQLException e) {\r
  99             throw new ServletException(e);\r
 100         }\r
 101     }\r
 102 \r
 103     @Override\r
 104     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {\r
 105         HttpSession se = req.getSession(true);\r
 106 \r
 107         String[] pKeyProductIds = req.getParameterValues("key_product_id");\r
 108         String[] pKeySectionIds = req.getParameterValues("key_section_id");\r
 109         String pKeyLicenseNumberFrom = req.getParameter("key_license_number_from");\r
 110         String pKeyLicenseNumberTo = req.getParameter("key_license_number_to");\r
 111         String pKeyLicenseUsedNumberFrom = req.getParameter("key_license_used_number_from");\r
 112         String pKeyLicenseUsedNumberTo = req.getParameter("key_license_used_number_to");\r
 113         String pKeyKeyword = req.getParameter("key_keyword");\r
 114 \r
 115         if (pKeyProductIds == null) {\r
 116             se.setAttribute("key_product_id", new int[0]);\r
 117         } else {\r
 118             List<Integer> l = new ArrayList<Integer>();\r
 119 \r
 120             for (String p : pKeyProductIds) {\r
 121                 if (p != null) {\r
 122                     try {\r
 123                         int v = Integer.parseInt(p.trim());\r
 124                         if (v != Integer.MIN_VALUE) {\r
 125                             l.add(v);\r
 126                         }\r
 127                     } catch (NumberFormatException e) {\r
 128                     }\r
 129                 }\r
 130             }\r
 131 \r
 132             int[] keyProductIds = new int[l.size()];\r
 133             for (int i = 0; i < l.size(); i++) {\r
 134                 keyProductIds[i] = l.get(i);\r
 135             }\r
 136 \r
 137             se.setAttribute("key_product_id", keyProductIds);\r
 138         }\r
 139 \r
 140         if (pKeySectionIds == null) {\r
 141             se.setAttribute("key_section_id", new int[0]);\r
 142         } else {\r
 143             List<Integer> l = new ArrayList<Integer>();\r
 144 \r
 145             for (String p : pKeySectionIds) {\r
 146                 if (p != null) {\r
 147                     try {\r
 148                         int v = Integer.parseInt(p.trim());\r
 149                         if (v != Integer.MIN_VALUE) {\r
 150                             l.add(v);\r
 151                         }\r
 152                     } catch (NumberFormatException e) {\r
 153                     }\r
 154                 }\r
 155             }\r
 156 \r
 157             int[] keySectionIds = new int[l.size()];\r
 158             for (int i = 0; i < l.size(); i++) {\r
 159                 keySectionIds[i] = l.get(i);\r
 160             }\r
 161 \r
 162             se.setAttribute("key_section_id", keySectionIds);\r
 163         }\r
 164 \r
 165         if (pKeyLicenseNumberFrom == null) {\r
 166             se.setAttribute("key_license_number_from", null);\r
 167         } else {\r
 168             try {\r
 169                 Integer v = new Integer(pKeyLicenseNumberFrom.trim());\r
 170                 se.setAttribute("key_license_number_from", v);\r
 171             } catch (NumberFormatException e1) {\r
 172                 se.setAttribute("key_license_number_from", null);\r
 173             }\r
 174         }\r
 175 \r
 176         if (pKeyLicenseNumberTo == null) {\r
 177             se.setAttribute("key_license_number_to", null);\r
 178         } else {\r
 179             try {\r
 180                 Integer v = new Integer(pKeyLicenseNumberTo.trim());\r
 181                 se.setAttribute("key_license_number_to", v);\r
 182             } catch (NumberFormatException e1) {\r
 183                 se.setAttribute("key_license_number_to", null);\r
 184             }\r
 185         }\r
 186 \r
 187         if (pKeyLicenseUsedNumberFrom == null) {\r
 188             se.setAttribute("key_license_used_number_from", null);\r
 189         } else {\r
 190             try {\r
 191                 Integer v = new Integer(pKeyLicenseUsedNumberFrom.trim());\r
 192                 se.setAttribute("key_license_used_number_from", v);\r
 193             } catch (NumberFormatException e1) {\r
 194                 se.setAttribute("key_license_used_number_from", null);\r
 195             }\r
 196         }\r
 197 \r
 198         if (pKeyLicenseUsedNumberTo == null) {\r
 199             se.setAttribute("key_license_used_number_to", null);\r
 200         } else {\r
 201             try {\r
 202                 Integer v = new Integer(pKeyLicenseUsedNumberTo.trim());\r
 203                 se.setAttribute("key_license_used_number_to", v);\r
 204             } catch (NumberFormatException e1) {\r
 205                 se.setAttribute("key_license_used_number_to", null);\r
 206             }\r
 207         }\r
 208 \r
 209         if (pKeyKeyword == null) {\r
 210             se.setAttribute("key_keyword", null);\r
 211         } else {\r
 212             se.setAttribute("key_keyword", pKeyKeyword.trim());\r
 213         }\r
 214 \r
 215         se.setAttribute("page_no", 0);\r
 216 \r
 217         int[] keyProductIds = (int[]) se.getAttribute("key_product_id");\r
 218         int[] keySectionIds = (int[]) se.getAttribute("key_section_id");\r
 219         Integer keyLicenseNumberFrom = (Integer) se.getAttribute("key_license_number_from");\r
 220         Integer keyLicenseNumberTo = (Integer) se.getAttribute("key_license_number_to");\r
 221         Integer keyLicenseUsedNumberFrom = (Integer) se.getAttribute("key_license_used_number_from");\r
 222         Integer keyLicenseUsedNumberTo = (Integer) se.getAttribute("key_license_used_number_to");\r
 223         String keyKeyword = (String) se.getAttribute("key_keyword");\r
 224         int pageNo = (Integer) se.getAttribute("page_no");\r
 225 \r
 226         try {\r
 227             Object[] result = this.findLicenseBy(keyProductIds, keySectionIds, keyLicenseNumberFrom, keyLicenseNumberTo, keyLicenseUsedNumberFrom, keyLicenseUsedNumberTo, keyKeyword, pageNo);\r
 228             List<Map<String, Object>> licenseList = (List<Map<String, Object>>) result[0];\r
 229             int recordCount = (Integer) result[1];\r
 230             req.setAttribute("licenseList", licenseList);\r
 231             req.setAttribute("recordCount", recordCount);\r
 232 \r
 233             List<Map<String, Object>> productList = this.findProductAll();\r
 234             req.setAttribute("productList", productList);\r
 235 \r
 236             List<Map<String, Object>> sectionList = this.findSectionAll();\r
 237             req.setAttribute("sectionList", sectionList);\r
 238 \r
 239             req.getRequestDispatcher("/WEB-INF/jsp/list.jsp").forward(req, resp);\r
 240         } catch (ClassNotFoundException e) {\r
 241             throw new ServletException(e);\r
 242         } catch (SQLException e) {\r
 243             throw new ServletException(e);\r
 244         }\r
 245     }\r
 246 \r
 247     private Object[] findLicenseBy(int[] keyProductIds, int[] keySectionIds, Integer keyLicenseNumberFrom, Integer keyLicenseNumberTo, Integer keyLicenseUsedNumberFrom, Integer keyLicenseUsedNumberTo, String keyKeyword, int pageNo) throws ClassNotFoundException, SQLException {\r
 248         List<Map<String, Object>> l = new ArrayList<Map<String, Object>>();\r
 249         int recordCount;\r
 250 \r
 251         ResourceBundle rb = ResourceBundle.getBundle("setting");\r
 252         String jdbcClass = rb.getString("jdbc.class");\r
 253         String jdbcUrl = rb.getString("jdbc.url");\r
 254         String jdbcUser = rb.getString("jdbc.user");\r
 255         String jdbcPassword = rb.getString("jdbc.password");\r
 256 \r
 257         Class.forName(jdbcClass);\r
 258 \r
 259         Connection con = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);\r
 260         try {\r
 261             // TODO SQLの組み立て方、及びパラメータの設定方法を考える。\r
 262             // TODO キーワード検索の方法を考える。\r
 263 \r
 264             List<Object> sqlParamList = new ArrayList<Object>();\r
 265 \r
 266             StringBuilder whereBuf = new StringBuilder();\r
 267             if (keyProductIds.length > 0) {\r
 268                 whereBuf.append(" and c_product_id in (");\r
 269                 for (int i = 0; i < keyProductIds.length; i++) {\r
 270                     if (i != 0) {\r
 271                         whereBuf.append(", ");\r
 272                     }\r
 273                     whereBuf.append("?");\r
 274 \r
 275                     sqlParamList.add(keyProductIds[i]);\r
 276                 }\r
 277                 whereBuf.append(")");\r
 278             }\r
 279             if (keySectionIds.length > 0) {\r
 280                 whereBuf.append(" and c_owned_section_id in (");\r
 281                 for (int i = 0; i < keySectionIds.length; i++) {\r
 282                     if (i != 0) {\r
 283                         whereBuf.append(", ");\r
 284                     }\r
 285                     whereBuf.append("?");\r
 286 \r
 287                     sqlParamList.add(keySectionIds[i]);\r
 288                 }\r
 289                 whereBuf.append(")");\r
 290             }\r
 291             if (keyLicenseNumberFrom != null) {\r
 292                 whereBuf.append(" and c_number >= ?");\r
 293 \r
 294                 sqlParamList.add(keyLicenseNumberFrom);\r
 295             }\r
 296             if (keyLicenseNumberTo != null) {\r
 297                 whereBuf.append(" and c_number <= ?");\r
 298 \r
 299                 sqlParamList.add(keyLicenseNumberTo);\r
 300             }\r
 301             String whereLicense = "";\r
 302             if (whereBuf.length() > 0) {\r
 303                 whereLicense = " where " + whereBuf.substring(4);\r
 304             }\r
 305 \r
 306             whereBuf.delete(0, whereBuf.length());\r
 307             if (keyProductIds.length > 0) {\r
 308                 whereBuf.append(" and c_id in (");\r
 309                 for (int i = 0; i < keyProductIds.length; i++) {\r
 310                     if (i != 0) {\r
 311                         whereBuf.append(", ");\r
 312                     }\r
 313                     whereBuf.append("?");\r
 314 \r
 315                     sqlParamList.add(keyProductIds[i]);\r
 316                 }\r
 317                 whereBuf.append(")");\r
 318             }\r
 319             String whereProduct = "";\r
 320             if (whereBuf.length() > 0) {\r
 321                 whereProduct = " where " + whereBuf.substring(4);\r
 322             }\r
 323 \r
 324             whereBuf.delete(0, whereBuf.length());\r
 325             if (keyLicenseUsedNumberFrom != null) {\r
 326                 whereBuf.append(" and sum_number >= ?");\r
 327 \r
 328                 sqlParamList.add(keyLicenseUsedNumberFrom);\r
 329             }\r
 330             if (keyLicenseUsedNumberTo != null) {\r
 331                 whereBuf.append(" and sum_number <= ?");\r
 332 \r
 333                 sqlParamList.add(keyLicenseUsedNumberTo);\r
 334             }\r
 335             String whereLicenseUsed = "";\r
 336             if (whereBuf.length() > 0) {\r
 337                 whereLicenseUsed = " where " + whereBuf.substring(4);\r
 338             }\r
 339 \r
 340             whereBuf.delete(0, whereBuf.length());\r
 341             if (keySectionIds.length > 0) {\r
 342                 whereBuf.append(" and c_id in (");\r
 343                 for (int i = 0; i < keySectionIds.length; i++) {\r
 344                     if (i != 0) {\r
 345                         whereBuf.append(", ");\r
 346                     }\r
 347                     whereBuf.append("?");\r
 348 \r
 349                     sqlParamList.add(keySectionIds[i]);\r
 350                 }\r
 351                 whereBuf.append(")");\r
 352             }\r
 353             String whereSection = "";\r
 354             if (whereBuf.length() > 0) {\r
 355                 whereSection = " where " + whereBuf.substring(4);\r
 356             }\r
 357 \r
 358             StringBuffer sql = new StringBuffer();\r
 359             sql.append("select" + "\n");\r
 360             sql.append("    license_result.c_id as license_id," + "\n");\r
 361             sql.append("    product_result.c_name as product_name," + "\n");\r
 362             sql.append("    license_result.c_product_key as product_key," + "\n");\r
 363             sql.append("    license_result.c_number as license_number," + "\n");\r
 364             sql.append("    license_result.c_owned_section_id as owned_section_id," + "\n");\r
 365             sql.append("    section_result.c_name as owned_section_name," + "\n");\r
 366             sql.append("    license_used_result.sum_number as license_used_number" + "\n");\r
 367             sql.append(" from" + "\n");\r
 368             sql.append("    (" + "\n");\r
 369             sql.append("        select" + "\n");\r
 370             sql.append("            c_id," + "\n");\r
 371             sql.append("            c_product_id," + "\n");\r
 372             sql.append("            c_product_key," + "\n");\r
 373             sql.append("            c_number," + "\n");\r
 374             sql.append("            c_owned_section_id" + "\n");\r
 375             sql.append("        from" + "\n");\r
 376             sql.append("            t_license" + "\n");\r
 377             sql.append(whereLicense + "\n");\r
 378             sql.append("    ) as license_result," + "\n");\r
 379             sql.append("    (" + "\n");\r
 380             sql.append("        select" + "\n");\r
 381             sql.append("            c_id," + "\n");\r
 382             sql.append("            c_name" + "\n");\r
 383             sql.append("        from" + "\n");\r
 384             sql.append("            t_product" + "\n");\r
 385             sql.append(whereProduct + "\n");\r
 386             sql.append("    ) as product_result," + "\n");\r
 387             sql.append("    (" + "\n");\r
 388             sql.append("        select" + "\n");\r
 389             sql.append("            c_license_id," + "\n");\r
 390             sql.append("            sum(c_number) as sum_number" + "\n");\r
 391             sql.append("        from" + "\n");\r
 392             sql.append("            t_license_used" + "\n");\r
 393             sql.append(whereLicenseUsed + "\n");\r
 394             sql.append("        group by" + "\n");\r
 395             sql.append("            c_license_id" + "\n");\r
 396             sql.append("    ) as license_used_result," + "\n");\r
 397             sql.append("    (" + "\n");\r
 398             sql.append("        select" + "\n");\r
 399             sql.append("            c_id," + "\n");\r
 400             sql.append("            c_name" + "\n");\r
 401             sql.append("        from" + "\n");\r
 402             sql.append("            t_section" + "\n");\r
 403             sql.append(whereSection + "\n");\r
 404             sql.append("    ) as section_result" + "\n");\r
 405             sql.append(" where" + "\n");\r
 406             sql.append("    license_result.c_product_id = product_result.c_id" + "\n");\r
 407             sql.append("    and license_result.c_id = license_used_result.c_license_id" + "\n");\r
 408             sql.append("    and license_result.c_owned_section_id = section_result.c_id" + "\n");\r
 409             sql.append(" order by" + "\n");\r
 410             sql.append("    product_result.c_name," + "\n");\r
 411             sql.append("    section_result.c_name," + "\n");\r
 412             sql.append("    license_result.c_product_key" + "\n");\r
 413 \r
 414             System.out.println(sql); // TODO\r
 415 \r
 416             PreparedStatement ps = con.prepareStatement(sql.toString());\r
 417             try {\r
 418                 for (int i = 0; i < sqlParamList.size(); i++) {\r
 419                     Object sqlParam = sqlParamList.get(i);\r
 420                     if (sqlParam instanceof String) {\r
 421                         ps.setString(i + 1, (String) sqlParam);\r
 422                     } else if (sqlParam instanceof Integer) {\r
 423                         ps.setInt(i + 1, (Integer) sqlParam);\r
 424                     } else {\r
 425                         throw new SQLException("SQLパラメータが予期しない型です。");\r
 426                     }\r
 427                 }\r
 428 \r
 429                 // TODO\r
 430                 long t = System.currentTimeMillis();\r
 431 \r
 432                 ResultSet rs = ps.executeQuery();\r
 433 \r
 434                 t = System.currentTimeMillis() - t;\r
 435                 System.out.println(t + "msec");\r
 436 \r
 437                 try {\r
 438                     int recordNo = 0;\r
 439                     final int recordNoMin = PAGE_MAX_RECORD_NUMBER * pageNo;\r
 440                     final int recordNoMax = PAGE_MAX_RECORD_NUMBER * (pageNo + 1);\r
 441 \r
 442                     while (rs.next()) {\r
 443                         if (recordNoMin <= recordNo && recordNo < recordNoMax) {\r
 444                             Map<String, Object> license = new HashMap<String, Object>();\r
 445                             license.put("license_id", rs.getInt("license_id"));\r
 446                             license.put("product_name", rs.getString("product_name"));\r
 447                             license.put("product_key", rs.getString("product_key"));\r
 448                             license.put("license_number", rs.getInt("license_number"));\r
 449                             if (rs.getInt("owned_section_id") != -1) {\r
 450                                 license.put("owned_section_name", rs.getString("owned_section_name"));\r
 451                             } else {\r
 452                                 license.put("owned_section_name", "");\r
 453                             }\r
 454                             license.put("license_used_number", rs.getInt("license_used_number"));\r
 455 \r
 456                             l.add(license);\r
 457                         }\r
 458 \r
 459                         recordNo++;\r
 460                     }\r
 461 \r
 462                     recordCount = recordNo;\r
 463                 } finally {\r
 464                     rs.close();\r
 465                 }\r
 466             } finally {\r
 467                 ps.close();\r
 468             }\r
 469         } finally {\r
 470             con.close();\r
 471         }\r
 472 \r
 473         return new Object[] { l, recordCount };\r
 474     }\r
 475 \r
 476     private List<Map<String, Object>> findProductAll() throws ClassNotFoundException, SQLException {\r
 477         List<Map<String, Object>> l = new ArrayList<Map<String, Object>>();\r
 478 \r
 479         ResourceBundle rb = ResourceBundle.getBundle("setting");\r
 480         String jdbcClass = rb.getString("jdbc.class");\r
 481         String jdbcUrl = rb.getString("jdbc.url");\r
 482         String jdbcUser = rb.getString("jdbc.user");\r
 483         String jdbcPassword = rb.getString("jdbc.password");\r
 484 \r
 485         Class.forName(jdbcClass);\r
 486 \r
 487         Connection con = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);\r
 488         try {\r
 489             String sql = "select c_id as product_id, c_name as product_name from t_product order by c_name";\r
 490 \r
 491             PreparedStatement ps = con.prepareStatement(sql);\r
 492             try {\r
 493                 ResultSet rs = ps.executeQuery();\r
 494                 try {\r
 495                     while (rs.next()) {\r
 496                         Map<String, Object> product = new HashMap<String, Object>();\r
 497                         product.put("product_id", rs.getInt("product_id"));\r
 498                         product.put("product_name", rs.getString("product_name"));\r
 499 \r
 500                         l.add(product);\r
 501                     }\r
 502                 } finally {\r
 503                     rs.close();\r
 504                 }\r
 505             } finally {\r
 506                 ps.close();\r
 507             }\r
 508         } finally {\r
 509             con.close();\r
 510         }\r
 511 \r
 512         return l;\r
 513     }\r
 514 \r
 515     private List<Map<String, Object>> findSectionAll() throws ClassNotFoundException, SQLException {\r
 516         List<Map<String, Object>> l = new ArrayList<Map<String, Object>>();\r
 517 \r
 518         ResourceBundle rb = ResourceBundle.getBundle("setting");\r
 519         String jdbcClass = rb.getString("jdbc.class");\r
 520         String jdbcUrl = rb.getString("jdbc.url");\r
 521         String jdbcUser = rb.getString("jdbc.user");\r
 522         String jdbcPassword = rb.getString("jdbc.password");\r
 523 \r
 524         Class.forName(jdbcClass);\r
 525 \r
 526         Connection con = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);\r
 527         try {\r
 528             String sql = "select c_id as section_id, c_name as section_name from t_section order by c_name";\r
 529 \r
 530             PreparedStatement ps = con.prepareStatement(sql);\r
 531             try {\r
 532                 ResultSet rs = ps.executeQuery();\r
 533                 try {\r
 534                     while (rs.next()) {\r
 535                         Map<String, Object> section = new HashMap<String, Object>();\r
 536                         section.put("section_id", rs.getInt("section_id"));\r
 537                         section.put("section_name", rs.getString("section_name"));\r
 538 \r
 539                         l.add(section);\r
 540                     }\r
 541                 } finally {\r
 542                     rs.close();\r
 543                 }\r
 544             } finally {\r
 545                 ps.close();\r
 546             }\r
 547         } finally {\r
 548             con.close();\r
 549         }\r
 550 \r
 551         return l;\r
 552     }\r
 553 \r
 554 }\r