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.enlacerh.util.Seg001.java

/**
  * Leer Datos de paises// w w w.  j a  va 2s.  c  o  m
  * @throws NamingException 
  * @throws IOException 
  **/
public void select(int first, int pageSize, String sortField, 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 = "";

        switch (productName) {
        case "Oracle":
            //Consulta paginada
            query = "  select * from ";
            query += " ( select query.*, rownum as rn from";
            query += " ( SELECT grupo, canttrab, to_char(fecven, 'dd/mm/yyyy'), case when estatus ='0' then '"
                    + getMessage("pnt009Opc0") + "' else '" + getMessage("pnt009Opc1")
                    + "' end, estatus, empresa, JNDI";
            query += " FROM seg001";
            query += " where grupo||empresa like '%" + ((String) filterValue).toUpperCase() + "%'";
            //query += " order by " + sortField.replace("v", "") + ") query";
            query += ") query";
            query += ") where rn <= " + pageSize;
            query += " and rn > (" + first + ")";

            break;
        case "PostgreSQL":
            //Consulta paginada
            query = "SELECT grupo, canttrab, to_char(fecven, 'dd/mm/yyyy'), case when estatus ='0' then '"
                    + getMessage("pnt009Opc0") + "' else '" + getMessage("pnt009Opc1")
                    + "' end, estatus, empresa, JNDI";
            query += " FROM seg001";
            query += " where CAST(grupo AS text)||empresa like '%" + ((String) filterValue).toUpperCase()
                    + "%'";
            query += " order by 1";
            query += " LIMIT " + pageSize;
            query += " OFFSET " + first;
            break;
        }

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

        r = pstmt.executeQuery();

        while (r.next()) {
            GruposSeg select = new GruposSeg();
            select.setVgrupo(r.getString(1));
            select.setVcantrab(r.getString(2));
            select.setVfecven(r.getString(3));
            select.setVestatus(r.getString(4));
            select.setEstatuscode(r.getString(5));
            select.setVempresa(r.getString(6));
            select.setVjndi(r.getString(7));
            //Agrega la lista
            list.add(select);

        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();
    r.close();
}

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

/**
* Leer Datos de paises// www .  j a v a 2 s.  c  o m
* @throws NamingException 
* @throws IOException 
**/
public void select(int first, int pageSize, String sortField, Object filterValue)
        throws SQLException, NamingException {
    try {
        //System.out.println("JNDI: " + JNDI);
        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 = "";

        switch (productName) {
        case "Oracle":
            //Consulta paginada
            query = "  select * from ";
            query += " ( select query.*, rownum as rn from";
            query += " ( SELECT codpai, trim(despai), grupo";
            query += " FROM Pnt003";
            query += " where codpai like '" + codpai + "%'";
            query += " and codpai||despai like '%" + ((String) filterValue).toUpperCase() + "%'";
            query += " and grupo = '" + grupo + "'";
            query += " order by " + sortField.replace("v", "") + ") query";
            query += " ) where rownum <= " + pageSize;
            query += " and rn > (" + first + ")";

            break;
        case "PostgreSQL":
            //Consulta paginada
            query = "SELECT codpai, trim(despai), grupo";
            query += " FROM Pnt003";
            query += " where CAST(codpai AS text) like '" + codpai + "%'";
            query += " and cast(codpai as text)||despai like '%" + ((String) filterValue).toUpperCase() + "%'";
            query += " and CAST(grupo AS text) = '" + grupo + "'";
            query += " order by " + sortField;
            query += " LIMIT " + pageSize;
            query += " OFFSET " + first;

            break;
        }

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

        r = pstmt.executeQuery();

        while (r.next()) {
            Pnt003 select = new Pnt003();
            select.setCodpai(r.getString(1));
            select.setDespai(r.getString(2));
            select.setLocalgrupo(r.getString(3));
            //Agrega la lista
            list.add(select);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();
    r.close();

}

From source file:org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCClusterLock.java

public void init(DataAccessManager dataAccessManager, String clusterLockTableStatement)
        throws RegistryException {
    synchronized (lockObject) {
        String clusterLockTable;/*from   w w  w  . java 2s  .c o  m*/
        if (log.isTraceEnabled()) {
            log.trace("Initializing cluster wide database locks");
        }
        if (!(dataAccessManager instanceof JDBCDataAccessManager)) {
            String msg = "Failed to get logs. Invalid data access manager.";
            log.error(msg);
            throw new RegistryException(msg);
        }
        setDataSource(((JDBCDataAccessManager) dataAccessManager).getDataSource());

        if (clusterLockTableStatement != null) {
            clusterLockTable = clusterLockTableStatement;
        } else {
            clusterLockTable = defaultClusterLockTableStatement;
        }

        Connection conn = null;

        try {
            conn = getDataSource().getConnection();

            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet result = metaData.getTables(null, null, DBUtils.getConvertedAutoGeneratedColumnName(
                    metaData.getDatabaseProductName(), "REG_CLUSTER_LOCK"), null);
            boolean lockTableCreated = false;
            try {
                if (result.next()) {
                    if (log.isTraceEnabled()) {
                        log.trace("Cluster lock table is already created in the Registry " + "database.");
                    }
                    lockTableCreated = true;
                }
            } finally {
                if (result != null) {
                    result.close();
                }
            }

            if (!lockTableCreated) {
                PreparedStatement ps1 = conn.prepareStatement(clusterLockTable);
                try {
                    ps1.executeUpdate();
                } finally {
                    if (ps1 != null) {
                        ps1.close();
                    }
                }
            }

            int rowCount = 0;
            PreparedStatement psRowCheck = conn.prepareStatement(LOCK_ROW_CHECK);
            try {
                ResultSet rowCheckResults = psRowCheck.executeQuery();
                try {
                    if (rowCheckResults.next()) {
                        rowCount = rowCheckResults.getInt(1);
                    }
                } finally {
                    if (rowCheckResults != null) {
                        rowCheckResults.close();
                    }
                }
            } finally {
                if (psRowCheck != null) {
                    psRowCheck.close();
                }
            }

            // lock table is not created. let's try to create it. we may fail if another node
            // creates this table concurrently.
            conn.setAutoCommit(false);

            // we can't assume that lock row is created even though the lock table is already
            // created. this is because, lock table can be created from a script.
            if (rowCount == 0) {
                PreparedStatement ps2 = conn.prepareStatement(initRow);
                try {
                    ps2.executeUpdate();
                } finally {
                    if (ps2 != null) {
                        ps2.close();
                    }
                }
            }

            conn.commit();

        } catch (SQLException e) {

            String msg = "Attempt create the cluster lock table is unsuccessful. "
                    + "Examining the reasons to failure. "
                    + "(Ignore the below error log if this Registry instance is " + "running in a cluster).";
            log.error(msg, e);

            boolean clusterLockTableCreated = false;
            if (conn != null) {

                try {
                    DatabaseMetaData metaData = conn.getMetaData();
                    ResultSet result = metaData.getTables(null, null,
                            DBUtils.getConvertedAutoGeneratedColumnName(metaData.getDatabaseProductName(),
                                    "REG_CLUSTER_LOCK"),
                            null);
                    try {
                        if (result.next()) {
                            clusterLockTableCreated = true;
                            if (log.isTraceEnabled()) {
                                log.trace("Cluster lock table is created by another node in "
                                        + "the cluster. Cluster lock table creation is " + "successful.");
                            }
                        }
                    } finally {
                        if (result != null) {
                            result.close();
                        }
                    }
                } catch (SQLException e1) {

                    String msg1 = "Failed to check the existence of the cluster lock table. " + "Caused by: "
                            + e1.getMessage();
                    log.error(msg1, e1);
                }

                if (!clusterLockTableCreated) {

                    String msg1 = "Failed to create the cluster lock table. Cluster lock "
                            + "table is not created by any other node. Caused by: " + e.getMessage();
                    log.fatal(msg1, e);

                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        String msg2 = "Failed to rollback the database operation after "
                                + "failing the cluster lock table creation. Caused by: " + e1.getMessage();
                        log.error(msg2, e1);
                    }

                    throw new RegistryException(msg1, e);
                }
            } else {
                String msg1 = "Failed to obtain database connection to create the cluster "
                        + "lock table. Caused by: " + e.getMessage();
                log.error(msg1, e);
                throw new RegistryException(msg1, e);
            }

        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("Failed to close the database connection. Caused by: " + e.getMessage(), e);
            }
        }

        if (log.isTraceEnabled()) {
            log.trace("Cluster wide database locks initialized successfully.");
        }
    }
}

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

/**
* Borra Paises/*from w  w w  .  jav  a 2 s .c  o  m*/
* <p>
* Parametros del metodo: String codpai. Pool de conecciones
* @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 (codrol.equals(".")) {
            codrol = "";
        }

        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 seg002 WHERE CODROL||GRUPO in (" + param + ")";
                    break;
                case "PostgreSQL":
                    query = "DELETE FROM seg002 WHERE CODROL||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("seg01", "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();
                    } catch (SQLException e) {
                        msj = new FacesMessage(FacesMessage.SEVERITY_FATAL, e.getMessage(), "");
                    }

                    pstmt.close();
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        FacesContext.getCurrentInstance().addMessage(null, msj);
    }
}

From source file:org.rimudb.Database.java

/**
 * Get this database's meta data/*from w  ww.ja v  a 2 s . co  m*/
 */
private RimuDBDatabaseMetaData lookupDatabaseInfo() throws RimuDBException {
    RimuDBDatabaseMetaData metaData = new RimuDBDatabaseMetaData();
    Connection con = null;
    DatabaseMetaData dbmd = null;
    ResultSet rs = null;

    try {
        con = getDatabaseConnection();
        dbmd = con.getMetaData();

        metaData.setDatabaseProductName(dbmd.getDatabaseProductName());
        metaData.setDatabaseMajorVersion(dbmd.getDatabaseMajorVersion());
        metaData.setSupportsGetGeneratedKeys(dbmd.supportsGetGeneratedKeys());

    } catch (SQLException e) {
        throw new RimuDBException(e);

    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
            }
        }
    }

    return metaData;
}

From source file:org.sha.util.Shasuc.java

/**
 * Leer Datos de paises// w  ww.j ava2 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

    //Consulta paginada
    String query = "SELECT * FROM";
    query += "(select query.*, rownum as rn from";
    query += "(SELECT A.CODSUC, A.DESSUC, A.SUCURSAL, B.DESSUC AS DESCR";
    query += " FROM SHASUCURSAL A, NM_TRABAJADOR@INFOCENT_CALENDARIO B";
    query += " WHERE A.SUCURSAL = B.CODSUC";
    query += " AND A.CODSUC || A.DESSUC || A.SUCURSAL || B.DESSUC like '%"
            + ((String) filterValue).toUpperCase() + "%'";
    query += " GROUP BY A.CODSUC, A.DESSUC, A.SUCURSAL, B.DESSUC";
    query += " ORDER BY TO_NUMBER(A.CODSUC))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()) {
        Shasuc select = new Shasuc();
        select.setZcodigo(r.getString(1));
        select.setZdesc(r.getString(2));
        select.setZcodsucspi(r.getString(3));
        select.setZdessucspi(r.getString(3) + " - " + r.getString(4));
        select.setZdelete(r.getString(1) + "" + r.getString(3));

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

}

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

/**
* Borra Paises/*from  w w  w.  j ava 2  s  .co m*/
* <p>
* Parametros del metodo: String codpai. Pool de conecciones
* @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("pnt003DelPai"), "");
        } 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 PNT003 WHERE codpai||grupo in (" + param + ")";
                    break;
                case "PostgreSQL":
                    query = "DELETE FROM PNT003 WHERE CAST(codpai AS text)||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("bas01", "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();
                    } 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.Indt06.java

/**
 * Leer Datos de paises/*from   www. 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 (codubi == null) {
        codubi = " - ";
    }
    if (codubi == "") {
        codubi = " - ";
    }

    String[] veccia = codcia.split("\\ - ", -1);
    String[] vecubi = codubi.split("\\ - ", -1);

    //Consulta paginada
    String query = "SELECT * FROM";
    query += "(select query.*, rownum as rn from";
    query += "(SELECT A.CODCIA, A.CODUBI, B.DESCIA AS DESC1, C.DESUBI AS DESC2 ";
    query += " FROM INDT06 A, INDT03 B, INDT04 C";
    query += " WHERE A.CODCIA = B.CODCIA";
    query += " AND A.CODUBI = C.CODUBI";
    query += " AND TRIM(A.CODCIA) LIKE TRIM('%" + veccia[0] + "%')";
    query += " AND TRIM(A.CODUBI) LIKE TRIM('%" + vecubi[0] + "%')";
    query += " GROUP BY A.CODCIA, A.CODUBI, B.DESCIA, C.DESUBI";
    query += ")query ) ";
    query += " WHERE ROWNUM <=" + pageSize;
    query += " AND rn > (" + first + ")";
    query += " ORDER BY CODCIA, CODUBI" + sortField.replace("z", "");

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

    r = pstmt.executeQuery();

    while (r.next()) {
        Indt06 select = new Indt06();
        select.setZcodcia(r.getString(1));
        select.setZcodubi(r.getString(2));
        select.setZdesc1(r.getString(3));
        select.setZdesc2(r.getString(4));
        select.setZdesc3(r.getString(1) + " - " + r.getString(3));
        select.setZdesc4(r.getString(2) + " - " + r.getString(4));

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

}

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

/**
 * Leer Datos de categoria2//from  ww  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 {

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

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

    String query = "";

    switch (productName) {
    case "Oracle":
        query += "  select * from ";
        query += " ( select query.*, rownum as rn from";
        query += " (SELECT trim(A.codcat2), trim(A.descat2), trim(A.B_CODCAT1), trim(B.DESCAT1) ";
        query += " FROM BVTcat2 A, BVTCAT1 B";
        query += " WHERE A.B_CODCAT1=B.CODCAT1";
        query += " and A.instancia=B.instancia";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " and  A.codcat2 ||a.descat2 like  '%" + ((String) filterValue).toUpperCase() + "%'";
        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.codcat2), trim(A.descat2), trim(A.B_CODCAT1), trim(B.DESCAT1) ";
        query += " FROM BVTcat2 A, BVTCAT1 B";
        query += " WHERE A.B_CODCAT1=B.CODCAT1";
        query += " and A.instancia=B.instancia";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " and  A.codcat2 ||a.descat2 like  '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        query += " order by a." + sortField;
        query += " LIMIT " + pageSize;
        query += " OFFSET " + first;
        break;
    case "Microsoft SQL Server":
        query += " SELECT * ";
        query += " FROM (SELECT ";
        query += "         ROW_NUMBER() OVER (ORDER BY A.CODCAT2 ASC) AS ROW_NUM,  ";
        query += "         A.CODCAT2,  ";
        query += "         A.DESCAT2,  ";
        query += "         A.B_CODCAT1,  ";
        query += "         B.DESCAT1  ";
        query += "         FROM  ";
        query += "         BVTCAT2 A, BVTCAT1 B ";
        query += "         WHERE  ";
        query += "         A.B_CODCAT1=B.CODCAT1) TOT ";
        query += " WHERE ";
        query += " TOT.B_CODCAT1 LIKE '" + veccodcat1[0].toUpperCase() + "%'";
        query += " AND TOT.CODCAT2 + TOT.DESCAT2 LIKE  '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   tot.instancia = '" + instancia + "'";
        query += " AND TOT.ROW_NUM <= " + pageSize;
        query += " AND TOT.ROW_NUM > " + first;
        query += " ORDER BY " + sortField;
        break;
    }
    //System.out.println(query);

    pstmt = con.prepareStatement(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Bvtcat2 select = new Bvtcat2();
        select.setCodcat2(r.getString(1));
        select.setDescat2(r.getString(2));
        select.setb_codcat1(r.getString(3));
        select.setBcodcatdescat1(r.getString(3) + " - " + r.getString(4));

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

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

/**
 * Leer Datos de paises//from  ww w.j a  va 2  s .com
 * @throws NamingException 
* @throws IOException 
 **/
public void select() 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

    String query = "";

    switch (productName) {
    case "Oracle":
        query += " SELECT anocal, numper, to_char(fecini,'dd/mm/yyyy'), to_char(fecfin,'dd/mm/yyyy'), descrip";
        query += " FROM bvt011";
        query += " where anocal  like trim('" + anocal + "%')";
        query += " and numper  like trim('" + numper + "%')";
        query += " ORDER BY 1 desc";
        break;
    case "PostgreSQL":
        query += " SELECT anocal, numper, to_char(fecini,'dd/mm/yyyy'), to_char(fecfin,'dd/mm/yyyy'), descrip";
        query += " FROM bvt011";
        query += " where anocal  like trim('" + anocal + "%')";
        query += " and numper  like trim('" + numper + "%')";
        query += " ORDER BY 1 desc";
        break;
    case "Microsoft SQL Server":
        query += " SELECT ";
        query += " ANOCAL, ";
        query += " NUMPER, ";
        query += " CONVERT(VARCHAR,FECINI,103) FECINI, ";
        query += " CONVERT(VARCHAR,FECFIN,103) FECFIN, ";
        query += " DESCRIP ";
        query += " FROM BVT011 ";
        query += " WHERE ANOCAL  LIKE '" + anocal + "%'";
        query += " AND NUMPER  LIKE '" + numper + "%'";
        query += " ORDER BY 1 DESC ";
        break;
    }

    //Consulta paginada

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

    r = pstmt.executeQuery();

    while (r.next()) {
        Periodos select = new Periodos();
        select.setVanocal(r.getString(1));
        select.setVnumper(r.getString(2));
        select.setVfecini(r.getString(3));
        select.setVfecfin(r.getString(4));
        select.setVdescrip(r.getString(5));
        //Agrega la lista
        list.add(select);
        rows = list.size();
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}