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;
}
