Example usage for java.sql DatabaseMetaData getDatabaseProductName

List of usage examples for java.sql DatabaseMetaData getDatabaseProductName

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getDatabaseProductName

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getDatabaseProductName.

Prototype

String getDatabaseProductName() throws SQLException;

Source Link

Document

Retrieves the name of this database product.

Usage

From source file:org.openbizview.util.Indh12.java

/**
 * Leer Datos de paises/* w  ww .  jav  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 (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totusd, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM Indh12 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totusd, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh12 select = new Indh12();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotusd(r.getString(4));
        select.setZdesc1(r.getString(5));
        select.setZciadesc(r.getString(1) + " - " + r.getString(5));
        select.setZsemana(r.getString(3) + " - " + r.getString(6) + " al " + r.getString(7));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.enlacerh.util.Seg007.java

/**
 * Borra Ciaseg//  w  ww  .jav  a 2s.co  m
 * <p>
 * Parametros del metodo: String p_coduser. Pool de conecciones
 * @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 seg007 WHERE P_CODUSER||P_CODCIA||grupo in (" + param + ")";
                    break;
                case "PostgreSQL":
                    query = "DELETE FROM seg007 WHERE P_CODUSER||P_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("seg03", "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"), "");
                        }
                    } 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.enlacerh.util.Seg007.java

/**
  * Leer Datos de Ciaseg/* w  w w  .ja  va2 s  .  c o m*/
  * @throws NamingException 
 * @throws SQLException 
  * @throws IOException 
  **/
public void select(int first, int pageSize, String sortField, Object filterValue)
        throws NamingException, SQLException, IOException {

    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 (pcoduser == null) {
        pcoduser = " - ";
    }
    if (pcoduser == "") {
        pcoduser = " - ";
    }

    String[] vecuser = pcoduser.split("\\ - ", -1);

    switch (productName) {
    case "Oracle":
        //Consulta paginada
        query = "  select * from ";
        query += " ( select query.*, rownum as rn from";
        query += " ( SELECT trim(a.p_coduser), trim(c.nbr), trim(a.p_codcia), trim(b.nomcia1), trim(b.nomcia2), a.grupo";
        query += " FROM SEG007 a , Pnt001 b, autos01 c";
        query += " where a.p_codcia=b.codcia";
        query += " and a.p_coduser=c.coduser";
        query += " and a.grupo=c.grupo";
        query += " and a.p_coduser like trim('" + vecuser[0] + "%')";
        query += " and a.p_coduser||a.p_codcia||b.nomcia2 like '%" + ((String) filterValue).toUpperCase()
                + "%'";
        query += " and a.grupo = '" + grupo + "'";
        query += " order by " + sortField.replace("vp", "p_") + ", a.p_codcia" + ") query";
        query += " ) where rownum <= " + pageSize;
        query += " and rn > (" + first + ")";

        break;
    case "PostgreSQL":
        //Consulta paginada
        //Consulta paginada
        query = "SELECT trim(a.p_coduser), trim(c.nbr), trim(a.p_codcia), trim(b.nomcia1), trim(b.nomcia2), a.grupo";
        query += " FROM SEG007 a , Pnt001 b, autos01 c";
        query += " where a.p_codcia=b.codcia";
        query += " and a.p_coduser=c.coduser";
        query += " and a.grupo=c.grupo";
        query += " and a.p_coduser like trim('" + vecuser[0] + "%')";
        query += " and a.p_coduser||a.p_codcia||b.nomcia2 like '%" + ((String) filterValue).toUpperCase()
                + "%'";
        query += " and CAST(a.grupo AS text) = '" + grupo + "'";
        query += " order by " + sortField.replace("vp", "p_") + ", a.p_codcia";
        query += " LIMIT " + pageSize;
        query += " OFFSET " + first;
        break;
    }

    pstmt = con.prepareStatement(query);
    System.out.println(query);

    ResultSet r = pstmt.executeQuery();

    while (r.next()) {
        Segcia select = new Segcia();
        select.setVpcodciadescia(r.getString(3) + " - " + r.getString(5));
        select.setVpcoduser(r.getString(1));
        select.setVdesuser(r.getString(2));
        select.setVpcodcia(r.getString(3));
        select.setVpdescia(r.getString(5));
        select.setVpcoduserdesuser(r.getString(1) + " - " + r.getString(2));
        select.setVgrupo(r.getString(6));
        //Agrega la lista
        list.add(select);

    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.openbizview.util.Bvtcat3.java

/**
 * Leer registros en la tabla//  w w  w .  j  av a 2 s.  c  o  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_codcat1 == null) {
            b_codcat1 = " - ";
        }
        if (b_codcat1 == "") {
            b_codcat1 = " - ";
        }

        if (b_codcat2 == null) {
            b_codcat2 = " - ";
        }
        if (b_codcat2 == "") {
            b_codcat2 = " - ";
        }

        String[] veccodcat1 = b_codcat1.split("\\ - ", -1);
        String[] veccodcat2 = b_codcat2.split("\\ - ", -1);

        switch (productName) {
        case "Oracle":
            query = "SELECT count_bvtcat3('" + ((String) filterValue).toUpperCase() + "','" + veccodcat1[0]
                    + "','" + veccodcat2[0] + "','" + instancia + "') from dual";
            break;
        case "PostgreSQL":
            query = "SELECT count_bvtcat3('" + ((String) filterValue).toUpperCase() + "','" + veccodcat1[0]
                    + "','" + veccodcat2[0] + "','" + instancia + "')";
            break;
        case "Microsoft SQL Server":
            query = "SELECT DBO.count_bvtcat3('" + ((String) filterValue).toUpperCase() + "','" + 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:com.xpn.xwiki.internal.store.hibernate.HibernateStore.java

/**
 * Retrieve the current database product name.
 * <p>/*from   ww w.j  a  v  a 2s .c o m*/
 * Note that the database product name is cached for improved performances.
 * </p>
 *
 * @return the database product name, see {@link DatabaseProduct}
 * @since 4.0M1
 */
public DatabaseProduct getDatabaseProductName() {
    DatabaseProduct product = this.databaseProductCache;

    if (product == DatabaseProduct.UNKNOWN) {
        DatabaseMetaData metaData = getDatabaseMetaData();
        if (metaData != null) {
            try {
                product = DatabaseProduct.toProduct(metaData.getDatabaseProductName());
            } catch (SQLException ignored) {
                // do not care, return UNKNOWN
            }
        } else {
            // do not care, return UNKNOWN
        }
    }

    return product;
}

From source file:org.openbizview.util.Indh13.java

/**
 * Leer Datos de paises/* w ww .j  a  v  a2  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 (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM Indh13 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh13 select = new Indh13();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotusd(r.getString(4));
        select.setZtotpor(r.getString(5));
        select.setZdesc1(r.getString(6));
        select.setZciadesc(r.getString(1) + " - " + r.getString(6));
        select.setZsemana(r.getString(3) + " - " + r.getString(7) + " al " + r.getString(8));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.openbizview.util.Indh14.java

/**
 * Leer Datos de paises//from   ww  w.ja  v a2 s .  com
 * @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 (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM Indh14 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh14 select = new Indh14();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotusd(r.getString(4));
        select.setZtotpor(r.getString(5));
        select.setZdesc1(r.getString(6));
        select.setZciadesc(r.getString(1) + " - " + r.getString(6));
        select.setZsemana(r.getString(3) + " - " + r.getString(7) + " al " + r.getString(8));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.openbizview.util.Indh15.java

/**
 * Leer Datos de paises/*  ww  w. j ava 2s. c om*/
 * @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 (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM Indh15 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh15 select = new Indh15();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotusd(r.getString(4));
        select.setZtotpor(r.getString(5));
        select.setZdesc1(r.getString(6));
        select.setZciadesc(r.getString(1) + " - " + r.getString(6));
        select.setZsemana(r.getString(3) + " - " + r.getString(7) + " al " + r.getString(8));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.openbizview.util.Indh16.java

/**
 * Leer Datos de paises/*from  w  ww.  j a  v  a2 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 (codcia == null) {
        codcia = " - ";
    }
    if (codcia == "") {
        codcia = " - ";
    }
    if (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM Indh16 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totusd, A.totpor, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh16 select = new Indh16();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotusd(r.getString(4));
        select.setZtotpor(r.getString(5));
        select.setZdesc1(r.getString(6));
        select.setZciadesc(r.getString(1) + " - " + r.getString(6));
        select.setZsemana(r.getString(3) + " - " + r.getString(7) + " al " + r.getString(8));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

From source file:org.openbizview.util.Indh09.java

/**
 * Leer Datos de paises//  w w  w  . 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 (codcia == null) {
        codcia = " - ";
    }
    if (codcia == "") {
        codcia = " - ";
    }
    if (anocal == null) {
        anocal = "";
    }
    if (semcal == null) {
        semcal = "";
    }

    String[] veccia = codcia.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.ANOCAL, A.SEMCAL, A.totdia, A.totusd, B.DESCIA, TO_CHAR(D.FECINI,'DD/MM/YY') AS FECINI, TO_CHAR(D.FECFIN,'DD/MM/YY') AS FECFIN ";
    query += " FROM INDH09 A, INDT03 B, TUBDER03A D";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.ANOCAL = D.ANOCAL";
    query += " AND A.SEMCAL = D.SEMCAL";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
    query += " AND TRIM(A.SEMCAL) LIKE TRIM('%" + vecsem[0] + "%')";
    query += " GROUP BY A.CODCIA, A.ANOCAL, A.SEMCAL, A.totdia, A.totusd, B.DESCIA, D.FECINI, D.FECFIN";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, ANOCAL, SEMCAL " + sortField.replace("z", "");

    pstmt = con.prepareStatement(query);
    //System.out.println(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Indh09 select = new Indh09();
        select.setZcodcia(r.getString(1));
        select.setZanocal(r.getString(2));
        select.setZsemcal(r.getString(3));
        select.setZtotdia(r.getString(4));
        select.setZtotusd(r.getString(5));
        select.setZdesc1(r.getString(6));
        select.setZciadesc(r.getString(1) + " - " + r.getString(6));
        select.setZsemana(r.getString(3) + " - " + r.getString(7) + " al " + r.getString(8));

        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}