public ArrayList FindListProduct(String id, String pName) throws Exception { // Log.debug("List WatchdogForm.."); Connection conn = null; ResultSet rs = null; PreparedStatement pstmt = null; String sql = ""; ProductBean obj = null; ArrayList result = new ArrayList(); try { conn = open(); // Common.beginTransaction(conn); Log.debug("-->Query."); // setTransaction sql = " SELECT p.product_id," + " p.product_name ," + " p.type_product_id ," + " p.brand_product_id ," + " p.price_cost," + " p.price_sale ," + " p.product_item ," + " p.product_date_start," + " p.product_date_expire ," + " p.product_desc ," + " b.brand_product_name," + " t.type_product_name" + " FROM product p, brand_product b, type_product t" + " WHERE (p.delete_flag <> 'D' OR p.delete_flag is null OR p.delete_flag <> '' ) " + " and b.brand_product_id = p.brand_product_id " + " and t.type_product_id = p.type_product_id "; if (!"".equals(id) && id != null) { sql += " and (p.product_id = ? OR p.product_id LIKE ? )"; } if (!"".equals(pName) && pName != null) { sql += " and (p.product_name = ? OR product_name LIKE ? )"; } System.out.println("SQL : " + sql); int i = 1; pstmt = conn.prepareStatement(sql); if (!"".equals(id) && id != null) { pstmt.setString(i++, id); pstmt.setString(i++, "%" + id + "%"); } if (!"".equals(pName) && pName != null) { pstmt.setString(i++, pName); pstmt.setString(i++, "%" + pName + "%"); } rs = pstmt.executeQuery(); Log.debug("--->execute SQL Query."); int c = 0; while (rs.next()) { obj = new ProductBean(); obj.setProductId(rs.getString("product_id")); obj.setProductName(rs.getString("product_name")); obj.setTypeProductId(rs.getString("type_product_id")); obj.setBrandProductId(rs.getString("brand_product_id")); obj.setPriceCost(rs.getFloat("price_cost")); obj.setPriceSale(rs.getFloat("price_sale")); obj.setProductItem(rs.getInt("product_item")); obj.setProductDateStart(rs.getString("product_date_start")); obj.setProductDateExpire(rs.getString("product_date_expire")); obj.setProductDesc(rs.getString("product_desc")); obj.setBrandProductName(rs.getString("brand_product_name")); obj.setTypeProductName(rs.getString("type_product_name")); result.add(obj); c++; } // Log.debug("get UserInfo FetchSize :"+c); // Commit Transaction Log.debug("--->Query completed."); } catch (Exception e) { e.printStackTrace(); Log.debug("Exception : " + e.getMessage()); } finally { // close try { rs.close(); pstmt.close(); close(conn); } catch (Exception e) { } } return result; }