List of usage examples for java.sql DatabaseMetaData getDatabaseProductName
String getDatabaseProductName() throws SQLException;
From source file:org.enlacerh.util.Pnt001.java
/** * Leer registros en la tabla/*from ww w. ja v a2 s .c o m*/ * @throws NamingException * @throws IOException **/ public void counter(Object filterValue) throws SQLException, NamingException, IOException { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = null; switch (productName) { case "Oracle": query = "SELECT count_pnt001(" + Integer.parseInt(grupo) + ",'" + ((String) filterValue).toUpperCase() + "') from dual"; break; case "PostgreSQL": query = "SELECT count_pnt001(" + Integer.parseInt(grupo) + ",'" + ((String) filterValue).toUpperCase() + "') "; break; } pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { rows = r.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } //Cierra las conecciones pstmt.close(); con.close(); r.close(); }
From source file:org.openbizview.util.Acccat3.java
/** * Leer registros en la tabla/* w ww . j a v a2 s . co m*/ * @throws NamingException * @throws IOException **/ public void counter(Object filterValue) throws SQLException, NamingException { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; if (b_codrol == null) { b_codrol = " - "; } if (b_codrol == "") { b_codrol = " - "; } if (b_codcat1 == null) { b_codcat1 = " - "; } if (b_codcat1 == "") { b_codcat1 = " - "; } if (b_codcat2 == null) { b_codcat2 = " - "; } if (b_codcat2 == "") { b_codcat2 = " - "; } String[] veccodrol = b_codrol.split("\\ - ", -1); String[] veccodcat1 = b_codcat1.split("\\ - ", -1); String[] veccodcat2 = b_codcat2.split("\\ - ", -1); switch (productName) { case "Oracle": query = "SELECT count_acccat3('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + instancia + "') from dual"; break; case "PostgreSQL": query = "SELECT count_acccat3('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + instancia + "')"; break; case "Microsoft SQL Server": query = "SELECT DBO.count_acccat3('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + instancia + "')"; break; } pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { rows = r.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } //Cierra las conecciones pstmt.close(); con.close(); r.close(); }
From source file:org.openbizview.util.Indh02.java
/** * Leer Datos de paises//from w w w. j a v a 2 s . co m * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (codcia == null) { codcia = " - "; } if (codcia == "") { codcia = " - "; } if (codubi == null) { codubi = " - "; } if (codubi == "") { codubi = " - "; } if (codlin == null) { codlin = " - "; } if (codlin == "") { codlin = " - "; } if (anocal == null) { anocal = ""; } if (semcal == null) { semcal = ""; } String[] veccia = codcia.split("\\ - ", -1); String[] vecubi = codubi.split("\\ - ", -1); String[] veclin = codlin.split("\\ - ", -1); String[] vecsem = semcal.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.CODCIA, A.CODUBI, A.ANOCAL, A.SEMCAL, A.CODLIN, A.TOTTON, A.TOTSCP, B.DESCIA, C.DESUBI, D.DESLIN, TO_CHAR(E.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(E.FECFIN,'DD/MM/YY') AS FECFIN "; query += " FROM INDH02 A, INDT03 B, INDT04 C, INDT07 D, TUBDER03A E"; query += " WHERE A.CODCIA = B.CODCIA"; query += " AND A.CODUBI = C.CODUBI"; query += " AND A.CODLIN = D.CODLIN"; query += " AND A.ANOCAL = E.ANOCAL"; query += " AND A.SEMCAL = E.SEMCAL"; query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')"; query += " AND TRIM(A.CODUBI) LIKE TRIM('%" + vecubi[0] + "%')"; query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')"; query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')"; query += " AND TRIM(A.CODLIN) LIKE TRIM('%" + veclin[0] + "%')"; query += " GROUP BY A.CODCIA, A.CODUBI, A.ANOCAL, A.SEMCAL, A.CODLIN, A.TOTTON, A.TOTSCP, B.DESCIA, C.DESUBI, D.DESLIN, E.FECINI, E.FECFIN"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY CODCIA, CODUBI, ANOCAL, SEMCAL" + sortField.replace("z", ""); pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Indh02 select = new Indh02(); select.setZcodcia(r.getString(1)); select.setZcodubi(r.getString(2)); select.setZanocal(r.getString(3)); select.setZsemcal(r.getString(4)); select.setZcodlin(r.getString(5) + " - " + r.getString(10)); select.setZtotton(r.getString(6)); select.setZtotscp(r.getString(7)); select.setZciadesc(r.getString(1) + " - " + r.getString(8)); select.setZubidesc(r.getString(2) + " - " + r.getString(9)); select.setZdesc1(r.getString(8)); select.setZdesc2(r.getString(9)); select.setZdesc3(r.getString(10)); select.setZcodlindel(r.getString(5)); select.setZsemana(r.getString(4) + " - " + r.getString(11) + " al " + r.getString(12)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:org.openbizview.util.Indh05.java
/** * Leer Datos de paises/*from w w w .j a v a2s.c o m*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (codcia == null) { codcia = " - "; } if (codcia == "") { codcia = " - "; } if (codubi == null) { codubi = " - "; } if (codubi == "") { codubi = " - "; } if (anocal == null) { anocal = ""; } if (semcal == null) { semcal = ""; } if (codsec == null) { codsec = " - "; } if (codsec == "") { codsec = " - "; } String[] veccia = codcia.split("\\ - ", -1); String[] vecubi = codubi.split("\\ - ", -1); String[] vecsec = codsec.split("\\ - ", -1); String[] vecsem = semcal.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.CODCIA, A.CODUBI, A.CODSEC, A.ANOCAL, A.SEMCAL, A.TOTPES, A.USDKGR, B.DESCIA, C.DESUBI, D.DESSEC, TO_CHAR(E.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(E.FECFIN,'DD/MM/YY') AS FECFIN "; query += " FROM INDH05 A, INDT03 B, INDT04 C, INDT08 D, TUBDER03A E"; query += " WHERE A.CODCIA = B.CODCIA"; query += " AND A.CODUBI = C.CODUBI"; query += " AND A.CODSEC = D.CODSEC"; query += " AND A.ANOCAL = E.ANOCAL"; query += " AND A.SEMCAL = E.SEMCAL"; query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')"; query += " AND TRIM(A.CODUBI) LIKE TRIM('%" + vecubi[0] + "%')"; query += " AND TRIM(A.CODSEC) LIKE TRIM('%" + vecsec[0] + "%')"; query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')"; query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')"; query += " GROUP BY A.CODCIA, A.CODUBI, A.CODSEC, A.ANOCAL, A.SEMCAL, A.TOTPES, A.USDKGR, B.DESCIA, C.DESUBI, D.DESSEC, E.FECINI, E.FECFIN"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY CODCIA, CODUBI, CODSEC, ANOCAL, SEMCAL " + sortField.replace("z", ""); pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Indh05 select = new Indh05(); select.setZcodcia(r.getString(1)); select.setZcodubi(r.getString(2)); select.setZcodsec(r.getString(3)); select.setZanocal(r.getString(4)); select.setZsemcal(r.getString(5)); select.setZtotpes(r.getString(6)); select.setZusdkgr(r.getString(7)); select.setZdesc1(r.getString(8)); select.setZdesc2(r.getString(9)); select.setZdesc3(r.getString(10)); select.setZciadesc(r.getString(1) + " - " + r.getString(8)); select.setZubidesc(r.getString(2) + " - " + r.getString(9)); select.setZsecdesc(r.getString(3) + " - " + r.getString(10)); select.setZsemana(r.getString(5) + " - " + r.getString(11) + " al " + r.getString(12)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:org.openbizview.util.Acccat3.java
/** * Leer Datos de nominas para asignar a menucheck * @throws NamingException /*from w w w.j av a 2s. c om*/ * @throws SQLException * @throws IOException **/ private void selectAcccat3() { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; String cat1 = (String) FacesContext.getCurrentInstance().getExternalContext().getSessionMap() .get("cat1"); //Usuario logeado String cat2 = (String) FacesContext.getCurrentInstance().getExternalContext().getSessionMap() .get("cat2"); //Usuario logeado if (cat1 == null) { cat1 = " - "; } if (cat1 == "") { cat1 = " - "; } if (cat2 == null) { cat2 = " - "; } if (cat2 == "") { cat2 = " - "; } String[] veccat1 = cat1.split("\\ - ", -1); String[] veccat2 = cat2.split("\\ - ", -1); switch (productName) { case "Oracle": query = "Select codcat3, codcat3||' - '||descat3"; query += " from bvtcat3"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat3"; break; case "PostgreSQL": query = "Select codcat3, codcat3||' - '||descat3"; query += " from bvtcat3"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat3"; break; case "Microsoft SQL Server": query = "Select codcat3, codcat3+' - '+descat3"; query += " from bvtcat3"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat3"; break; } ////System.out.println(query); pstmt = con.prepareStatement(query); ////System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { String cat3 = new String(r.getString(1)); String descat3 = new String(r.getString(2)); listAcccat3.put(descat3, cat3); sorted = sortByValues(listAcccat3); } //Cierra las conecciones pstmt.close(); con.close(); } catch (NamingException | SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:org.openbizview.util.Indh08.java
/** * Leer Datos de paises/*from w w w. j a v a2s . co m*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (codcia == null) { codcia = " - "; } if (codcia == "") { codcia = " - "; } if (codubi == null) { codubi = " - "; } if (codubi == "") { codubi = " - "; } if (anocal == null) { anocal = ""; } if (semcal == null) { semcal = ""; } if (codinv == null) { codinv = " - "; } if (codinv == "") { codinv = " - "; } String[] veccia = codcia.split("\\ - ", -1); String[] vecubi = codubi.split("\\ - ", -1); String[] vecinv = codinv.split("\\ - ", -1); String[] vecsem = semcal.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.CODCIA, A.CODUBI, A.CODINV, A.ANOCAL, A.SEMCAL, A.TOTPES, A.TOTUSD, B.DESCIA, C.DESUBI, D.DESINV, TO_CHAR(E.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(E.FECFIN,'DD/MM/YY') AS FECFIN "; query += " FROM INDH08 A, INDT03 B, INDT04 C, INDT10 D, TUBDER03A E"; query += " WHERE A.CODCIA = B.CODCIA"; query += " AND A.CODUBI = C.CODUBI"; query += " AND A.CODINV = D.CODINV"; query += " AND A.ANOCAL = E.ANOCAL"; query += " AND A.SEMCAL = E.SEMCAL"; query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')"; query += " AND TRIM(A.CODUBI) LIKE TRIM('%" + vecubi[0] + "%')"; query += " AND TRIM(A.CODINV) LIKE TRIM('%" + vecinv[0] + "%')"; query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')"; query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')"; query += " GROUP BY A.CODCIA, A.CODUBI, A.CODINV, A.ANOCAL, A.SEMCAL, A.TOTPES, A.TOTUSD, B.DESCIA, C.DESUBI, D.DESINV, E.FECINI, E.FECFIN"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY CODCIA, CODUBI, CODINV, ANOCAL, SEMCAL " + sortField.replace("z", ""); pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Indh08 select = new Indh08(); select.setZcodcia(r.getString(1)); select.setZcodubi(r.getString(2)); select.setZcodinv(r.getString(3)); select.setZanocal(r.getString(4)); select.setZsemcal(r.getString(5)); select.setZtotpes(r.getString(6)); select.setZtotusd(r.getString(7)); select.setZdesc1(r.getString(8)); select.setZdesc2(r.getString(9)); select.setZdesc3(r.getString(10)); select.setZciadesc(r.getString(1) + " - " + r.getString(8)); select.setZubidesc(r.getString(2) + " - " + r.getString(9)); select.setZinvdesc(r.getString(3) + " - " + r.getString(10)); select.setZsemana(r.getString(5) + " - " + r.getString(11) + " al " + r.getString(12)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:org.enlacerh.util.Pnt001.java
/** * Borra Compaas//from ww w . j av a2 s . c om * @throws NamingException * @throws IOException **/ public void delete() throws NamingException, IOException { if (licencia(grupo)) { msj = new FacesMessage(FacesMessage.SEVERITY_WARN, getMessage("licven"), ""); FacesContext.getCurrentInstance().addMessage(null, msj); } else { HttpServletRequest request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext() .getRequest(); String[] chkbox = request.getParameterValues("toDelete"); if (chkbox == null) { msj = new FacesMessage(FacesMessage.SEVERITY_WARN, getMessage("del"), ""); } else { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; String param = "'" + StringUtils.join(chkbox, "','") + "'"; switch (productName) { case "Oracle": query = "DELETE FROM PNT001 WHERE CODCIA||grupo in (" + param + ")"; break; case "PostgreSQL": query = "DELETE FROM PNT001 WHERE CODCIA||CAST(grupo AS text) in (" + param + ")"; break; } pstmt = con.prepareStatement(query); //System.out.println(query); //Antes de insertar verifica si el rol del usuario tiene permisos para insertar vGacc = acc.valAccmnu("bas03", "delete", login, JNDI);//LLama a la funcion que valida las opciones del rol if (vGacc) { msj = new FacesMessage(FacesMessage.SEVERITY_ERROR, getMessage("msnAccDelete"), ""); } else { try { //Avisando pstmt.executeUpdate(); if (pstmt.getUpdateCount() <= 1) { msj = new FacesMessage(FacesMessage.SEVERITY_INFO, getMessage("msnDelete"), ""); } else { msj = new FacesMessage(FacesMessage.SEVERITY_INFO, getMessage("msnDeletes"), ""); } limpiarValores(); deleteFolders(); } catch (SQLException e) { e.printStackTrace(); msj = new FacesMessage(FacesMessage.SEVERITY_FATAL, e.getMessage(), ""); } pstmt.close(); con.close(); } } catch (Exception e) { e.printStackTrace(); } } FacesContext.getCurrentInstance().addMessage(null, msj); } }
From source file:org.openbizview.util.Bvtcat4.java
/** * Leer Datos de categoria2/*from www.j a v a 2 s. c om*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (b_codcat1 == null) { b_codcat1 = " - "; } if (b_codcat1 == "") { b_codcat1 = " - "; } if (b_codcat2 == null) { b_codcat2 = " - "; } if (b_codcat2 == "") { b_codcat2 = " - "; } if (b_codcat3 == null) { b_codcat3 = " - "; } if (b_codcat3 == "") { b_codcat3 = " - "; } String[] veccodcat1 = b_codcat1.split("\\ - ", -1); String[] veccodcat2 = b_codcat2.split("\\ - ", -1); String[] veccodcat3 = b_codcat3.split("\\ - ", -1); String query = ""; switch (productName) { case "Oracle": query += " select * from "; query += " ( select query.*, rownum as rn from"; query += " (SELECT trim(A.codcat4), trim(A.descat4), trim(A.B_CODCAT1), trim(B.DESCAT1), trim(A.B_CODCAT2), trim(C.DESCAT2), trim(A.B_CODCAT3), trim(D.DESCAT3), trim(a.equicat4), trim(a.tippro) "; query += " FROM BVTCAT4 A, BVTCAT1 B, BVTCAT2 C, BVTCAT3 D"; query += " WHERE A.B_CODCAT1=B.CODCAT1"; query += " AND A.B_CODCAT1=C.B_CODCAT1"; query += " AND A.B_CODCAT2=C.CODCAT2"; query += " AND A.B_CODCAT1=D.B_CODCAT1"; query += " AND A.B_CODCAT2=D.B_CODCAT2"; query += " AND A.B_CODCAT3=D.CODCAT3"; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " and A.b_codcat3 like '" + veccodcat3[0].toUpperCase() + "%'"; query += " and A.codcat4 ||a.descat4 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " and A.codcat4 like '" + codcat4 + "%'"; query += " AND a.instancia = '" + instancia + "'"; query += " order by a." + sortField + ") query"; query += " ) where rownum <= " + pageSize; query += " and rn > (" + first + ")"; break; case "PostgreSQL": query += " SELECT trim(A.codcat4), trim(A.descat4), trim(A.B_CODCAT1), trim(B.DESCAT1), trim(A.B_CODCAT2), trim(C.DESCAT2), trim(A.B_CODCAT3), trim(D.DESCAT3), trim(a.equicat4), trim(a.tippro) "; query += " FROM BVTCAT4 A, BVTCAT1 B, BVTCAT2 C, BVTCAT3 D"; query += " WHERE A.B_CODCAT1=B.CODCAT1"; query += " AND A.B_CODCAT1=C.B_CODCAT1"; query += " AND A.B_CODCAT2=C.CODCAT2"; query += " AND A.B_CODCAT1=D.B_CODCAT1"; query += " AND A.B_CODCAT2=D.B_CODCAT2"; query += " AND A.B_CODCAT3=D.CODCAT3"; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " and A.b_codcat3 like '" + veccodcat3[0].toUpperCase() + "%'"; query += " and A.codcat4 ||a.descat4 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " and A.codcat4 like '" + codcat4 + "%'"; query += " AND a.instancia = '" + instancia + "'"; query += " order by a." + sortField; query += " LIMIT " + pageSize; query += " OFFSET " + first; break; case "Microsft SQL Server": query += "SELECT * "; query += "FROM (SELECT "; query += " ROW_NUMBER() OVER (ORDER BY A.CODCAT4 ASC) AS ROW_NUM, "; query += " A.CODCAT4, "; query += " A.DESCAT4, "; query += " A.B_CODCAT1, "; query += " B.DESCAT1, "; query += " A.B_CODCAT2, "; query += " C.DESCAT2, "; query += " A.B_CODCAT3, "; query += " D.DESCAT3, "; query += " A.EQUICAT4, "; query += " A.TIPPRO "; query += " FROM "; query += " BVTCAT4 A, "; query += " BVTCAT1 B, "; query += " BVTCAT2 C, "; query += " BVTCAT3 D "; query += " WHERE A.B_CODCAT1=B.CODCAT1 "; query += " AND A.B_CODCAT1=C.B_CODCAT1 "; query += " AND A.B_CODCAT2=C.CODCAT2 "; query += " AND A.B_CODCAT1=D.B_CODCAT1 "; query += " AND A.B_CODCAT2=D.B_CODCAT2 "; query += " AND A.B_CODCAT3=D.CODCAT3) TOT "; query += "WHERE "; query += "TOT.B_CODCAT1 LIKE '" + veccodcat1[0].toUpperCase() + "%'"; query += "AND TOT.B_CODCAT2 LIKE '" + veccodcat2[0].toUpperCase() + "%'"; query += "AND TOT.B_CODCAT3 LIKE '" + veccodcat3[0].toUpperCase() + "%'"; query += "AND TOT.CODCAT4 + TOT.DESCAT4 LIKE '%" + ((String) filterValue).toUpperCase() + "%'"; query += " AND tot.instancia = '" + instancia + "'"; query += " and tot.codcat4 like '" + codcat4 + "%'"; query += "AND TOT.ROW_NUM <= " + pageSize; query += "AND TOT.ROW_NUM > " + first; query += "ORDER BY " + sortField; break; } pstmt = con.prepareStatement(query); ////System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Bvtcat4 select = new Bvtcat4(); select.setCodcat4(r.getString(1)); select.setDescat4(r.getString(2)); select.setB_codcat1(r.getString(3)); select.setCodcatdescat1(r.getString(3) + " - " + r.getString(4)); select.setB_codcat2(r.getString(5)); select.setCodcatdescat2(r.getString(5) + " - " + r.getString(6)); select.setB_codcat3(r.getString(7)); select.setCodcatdescat3(r.getString(7) + " - " + r.getString(8)); select.setEquicat4(r.getString(9)); select.setTippro(r.getString(10)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:org.openbizview.util.Sgc008.java
/** * Leer Datos de paises//from w ww . j a va 2 s. co m * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (coduser == null) { coduser = " - "; } if (coduser == "") { coduser = " - "; } if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (duepro == null) { duepro = " - "; } if (duepro == "") { duepro = " - "; } String[] veccodu = coduser.split("\\ - ", -1); String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] vecduepro = duepro.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.CODUSER, A.COMP, B.DESCR AS DESC1, A.AREA, C.DESCR AS DESC2, DECODE(A.DUEPRO,'1','1 - SI','0 - NO') DUEPRO "; query += " FROM SGC008 A, SGC005 B, SGC006 C "; query += " WHERE A.COMP = B.CODIGO AND A.COMP = C.COMP "; query += " AND A.AREA = C.CODIGO "; query += " AND TRIM(A.CODUSER) LIKE TRIM('%" + veccodu[0] + "%')"; query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')"; query += " AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%')"; query += " AND TRIM(A.DUEPRO) LIKE TRIM('%" + vecduepro[0] + "%')"; query += " GROUP BY A.CODUSER, A.COMP, B.DESCR, A.AREA, C.DESCR, A.DUEPRO"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY " + sortField.replace("z", ""); pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Sgc008 select = new Sgc008(); select.setZcomp(r.getString(2) + " - " + r.getString(3)); select.setZcoduser(r.getString(1)); select.setZarea(r.getString(4) + " - " + r.getString(5)); select.setZduepro(r.getString(6)); select.setZvaldel(r.getString(1) + "" + r.getString(2) + "" + r.getString(4)); //Agrega la lista list.add(select); } //Cierra las conexiones pstmt.close(); con.close(); }
From source file:org.openbizview.util.Sgc009.java
/** * Leer Datos de paises/*from www. ja v a 2 s. c o m*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (coduser == null) { coduser = " - "; } if (coduser == "") { coduser = " - "; } if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } if (area == null) { area = " - "; } if (area == "") { area = " - "; } if (codigo == null) { codigo = " - "; } if (codigo == "") { codigo = " - "; } String[] veccodu = coduser.split("\\ - ", -1); String[] veccomp = comp.split("\\ - ", -1); String[] vecarea = area.split("\\ - ", -1); String[] veccod = codigo.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.CODUSER, A.COMP, B.DESCR AS DESC1, A.AREA, C.DESCR AS DESC2, A.INDICA, D.NOMIND AS DESC3 "; query += " FROM SGC009 A, SGC005 B, SGC006 C, SGC001 D "; query += " WHERE A.COMP = B.CODIGO "; query += " AND A.AREA = C.CODIGO "; query += " AND A.INDICA = D.CODIGO "; query += " AND TRIM(A.CODUSER) LIKE TRIM('%" + veccodu[0] + "%')"; query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')"; query += " AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%')"; query += " AND TRIM(A.INDICA) LIKE TRIM('%" + veccod[0] + "%')"; query += " GROUP BY A.CODUSER, A.COMP, B.DESCR, A.AREA, A.INDICA, D.NOMIND, C.DESCR"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY " + sortField.replace("z", ""); pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Sgc009 select = new Sgc009(); select.setZcomp(r.getString(2) + " - " + r.getString(3)); select.setZcoduser(r.getString(1)); select.setZarea(r.getString(4) + " - " + r.getString(5)); select.setZcodigo(r.getString(6) + " - " + r.getString(7)); select.setZvaldel(r.getString(1) + "" + r.getString(2) + "" + r.getString(4) + "" + r.getString(6)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }