public static ProjectData[] select( ConnectionProvider connectionProvider, String rownum, String language, String adUserClient, String adUserOrg, String key, String name, String bpartner, String orderBy, String oraLimit, String pgLimit, int firstRegister, int numberRegisters) throws ServletException { String strSql = ""; strSql = strSql + " SELECT * FROM ( SELECT "; strSql = strSql + ((rownum == null || rownum.equals("")) ? "" : rownum); strSql = strSql + " AS RN1, A.* FROM ( " + " SELECT P.C_PROJECT_ID, P.VALUE, P.NAME, P.C_BPARTNER_ID, " + " B.NAME AS BPARTNER, L.NAME AS PROJECTSTATUS," + " p.c_Project_Id || '@_##_@' || p.value || ' - ' || p.name as rowkey" + " FROM C_PROJECT P, C_BPARTNER B, AD_REF_LIST_V L " + " WHERE P.C_BPARTNER_ID = B.C_BPARTNER_ID " + " AND P.PROJECTSTATUS = L.VALUE " + " AND L.AD_REFERENCE_ID = '800002' " + " AND L.AD_LANGUAGE = ? " + " AND P.AD_CLIENT_ID IN ("; strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient); strSql = strSql + ") " + " AND P.AD_ORG_ID IN ("; strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg); strSql = strSql + ") " + " AND P.ISACTIVE = 'Y'"; strSql = strSql + ((key == null || key.equals("") || key.equals("%")) ? "" : " AND UPPER(P.Value) LIKE UPPER(?) "); strSql = strSql + ((name == null || name.equals("") || name.equals("%")) ? "" : " AND UPPER(P.Name) LIKE UPPER(?) "); strSql = strSql + ((bpartner == null || bpartner.equals("")) ? "" : " AND P.C_BPARTNER_ID = ? "); strSql = strSql + " ORDER BY "; strSql = strSql + ((orderBy == null || orderBy.equals("")) ? "" : orderBy); strSql = strSql + " ) A ) B" + " WHERE 1=1"; strSql = strSql + ((oraLimit == null || oraLimit.equals("")) ? "" : " AND RN1 BETWEEN " + oraLimit); strSql = strSql + ((pgLimit == null || pgLimit.equals("")) ? "" : " LIMIT " + pgLimit); ResultSet result; Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0); PreparedStatement st = null; int iParameter = 0; try { st = connectionProvider.getPreparedStatement(strSql); if (rownum != null && !(rownum.equals(""))) {} iParameter++; UtilSql.setValue(st, iParameter, 12, null, language); if (adUserClient != null && !(adUserClient.equals(""))) {} if (adUserOrg != null && !(adUserOrg.equals(""))) {} if (key != null && !(key.equals("")) && !(key.equals("%"))) { iParameter++; UtilSql.setValue(st, iParameter, 12, null, key); } if (name != null && !(name.equals("")) && !(name.equals("%"))) { iParameter++; UtilSql.setValue(st, iParameter, 12, null, name); } if (bpartner != null && !(bpartner.equals(""))) { iParameter++; UtilSql.setValue(st, iParameter, 12, null, bpartner); } if (orderBy != null && !(orderBy.equals(""))) {} if (oraLimit != null && !(oraLimit.equals(""))) {} if (pgLimit != null && !(pgLimit.equals(""))) {} result = st.executeQuery(); long countRecord = 0; long countRecordSkip = 1; boolean continueResult = true; while (countRecordSkip < firstRegister && continueResult) { continueResult = result.next(); countRecordSkip++; } while (continueResult && result.next()) { countRecord++; ProjectData objectProjectData = new ProjectData(); objectProjectData.rn1 = UtilSql.getValue(result, "rn1"); objectProjectData.cProjectId = UtilSql.getValue(result, "c_project_id"); objectProjectData.value = UtilSql.getValue(result, "value"); objectProjectData.name = UtilSql.getValue(result, "name"); objectProjectData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id"); objectProjectData.bpartner = UtilSql.getValue(result, "bpartner"); objectProjectData.projectstatus = UtilSql.getValue(result, "projectstatus"); objectProjectData.rowkey = UtilSql.getValue(result, "rowkey"); objectProjectData.position = Long.toString(countRecord); objectProjectData.InitRecordNumber = Integer.toString(firstRegister); vector.addElement(objectProjectData); if (countRecord >= numberRegisters && numberRegisters != 0) { continueResult = false; } } result.close(); } catch (SQLException e) { log4j.error("SQL error in query: " + strSql + "Exception:" + e); throw new ServletException( "@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage()); } catch (Exception ex) { log4j.error("Exception in query: " + strSql + "Exception:" + ex); throw new ServletException("@CODE=@" + ex.getMessage()); } finally { try { connectionProvider.releasePreparedStatement(st); } catch (Exception ignore) { ignore.printStackTrace(); } } ProjectData objectProjectData[] = new ProjectData[vector.size()]; vector.copyInto(objectProjectData); return (objectProjectData); }
public static ProjectData[] selectKey( ConnectionProvider connectionProvider, String adUserClient, String adUserOrg, String bpartner, String key, int firstRegister, int numberRegisters) throws ServletException { String strSql = ""; strSql = strSql + " SELECT P.C_PROJECT_ID, P.VALUE, P.NAME, P.C_BPARTNER_ID, B.NAME AS BPARTNER" + " FROM C_PROJECT P, C_BPARTNER B " + " WHERE P.C_BPARTNER_ID = B.C_BPARTNER_ID " + " AND P.AD_CLIENT_ID IN ("; strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient); strSql = strSql + ") " + " AND P.AD_ORG_ID IN ("; strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg); strSql = strSql + ") " + " AND P.ISACTIVE = 'Y'"; strSql = strSql + ((bpartner == null || bpartner.equals("")) ? "" : " AND P.C_BPARTNER_ID = ? "); strSql = strSql + " AND UPPER(P.VALUE) LIKE UPPER(?)" + " ORDER BY P.NAME"; ResultSet result; Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0); PreparedStatement st = null; int iParameter = 0; try { st = connectionProvider.getPreparedStatement(strSql); if (adUserClient != null && !(adUserClient.equals(""))) {} if (adUserOrg != null && !(adUserOrg.equals(""))) {} if (bpartner != null && !(bpartner.equals(""))) { iParameter++; UtilSql.setValue(st, iParameter, 12, null, bpartner); } iParameter++; UtilSql.setValue(st, iParameter, 12, null, key); result = st.executeQuery(); long countRecord = 0; long countRecordSkip = 1; boolean continueResult = true; while (countRecordSkip < firstRegister && continueResult) { continueResult = result.next(); countRecordSkip++; } while (continueResult && result.next()) { countRecord++; ProjectData objectProjectData = new ProjectData(); objectProjectData.cProjectId = UtilSql.getValue(result, "c_project_id"); objectProjectData.value = UtilSql.getValue(result, "value"); objectProjectData.name = UtilSql.getValue(result, "name"); objectProjectData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id"); objectProjectData.bpartner = UtilSql.getValue(result, "bpartner"); objectProjectData.InitRecordNumber = Integer.toString(firstRegister); vector.addElement(objectProjectData); if (countRecord >= numberRegisters && numberRegisters != 0) { continueResult = false; } } result.close(); } catch (SQLException e) { log4j.error("SQL error in query: " + strSql + "Exception:" + e); throw new ServletException( "@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage()); } catch (Exception ex) { log4j.error("Exception in query: " + strSql + "Exception:" + ex); throw new ServletException("@CODE=@" + ex.getMessage()); } finally { try { connectionProvider.releasePreparedStatement(st); } catch (Exception ignore) { ignore.printStackTrace(); } } ProjectData objectProjectData[] = new ProjectData[vector.size()]; vector.copyInto(objectProjectData); return (objectProjectData); }