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.Cst006.java

/**
 * Leer Datos de paises/*from   ww  w  .  j a  va  2 s. 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 (ordf == null) {
        ordf = " - ";
    }
    if (ordf == "") {
        ordf = " - ";
    }

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

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

    String[] vecordf = ordf.split("\\ - ", -1);
    String[] vecanocal = anocal.split("\\ - ", -1);
    String[] vecmescal = mescal.split("\\ - ", -1);

    //Consulta paginada
    String query = "SELECT * FROM";
    query += "(select query.*, rownum as rn from";
    query += "(SELECT A.ORDEN, A.ANOCAL, A.MESCAL, A.ORDINI, A.ORDFIN, B.DESCR AS DESC2 ";
    query += " FROM CST006 A, CST002 B";
    query += " WHERE A.ORDEN = B.CODIGO";
    query += " AND TRIM(A.ORDEN)  LIKE TRIM('" + vecordf[0] + "%')";
    query += " AND TRIM(A.ANOCAL) LIKE TRIM('" + vecanocal[0] + "%')";
    query += " AND TRIM(A.MESCAL) LIKE TRIM('" + vecmescal[0] + "%')";
    query += " GROUP BY A.ORDEN, A.ANOCAL, A.MESCAL, A.ORDINI, A.ORDFIN, B.DESCR";
    query += ")query ) ";
    //query += " WHERE ROWNUM <="+pageSize;
    //query += " AND rn > ("+ first +")";
    query += " ORDER BY ORDEN, ANOCAL, MESCAL, ORDINI, ORDFIN " + sortField.replace("z", "");

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

    r = pstmt.executeQuery();

    while (r.next()) {
        Cst006 select = new Cst006();
        select.setZordf(r.getString(1));
        select.setZdesc1(r.getString(1) + " - " + r.getString(6));
        select.setZanocal(r.getString(2));
        select.setZmescal(r.getString(3));
        select.setZordini(r.getString(4));
        select.setZordfin(r.getString(5));
        select.setZdesc2(r.getString(1));
        select.setZdesc3(r.getString(6));
        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();

}

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

/**
* Leer registros en la tabla// w  ww. j a  v a  2s . 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 = null;

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

        String[] vecValores = p_codpai.split("\\ - ", -1);

        switch (productName) {
        case "Oracle":
            query = "SELECT count_pnt004(" + Integer.parseInt(grupo) + ",'"
                    + ((String) filterValue).toUpperCase() + "','" + vecValores[0] + "') from dual";
            break;
        case "PostgreSQL":
            query = "SELECT count_pnt004(" + Integer.parseInt(grupo) + ",'"
                    + ((String) filterValue).toUpperCase() + "','" + vecValores[0] + "')";
            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.Indt05.java

/**
 * Leer Datos de paises//from w w w  .j  av  a  2  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 (codubi == null) {
        codubi = " - ";
    }
    if (codubi == "") {
        codubi = " - ";
    }
    if (coduser == null) {
        coduser = " - ";
    }
    if (coduser == "") {
        coduser = " - ";
    }

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

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

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

    r = pstmt.executeQuery();

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

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

}

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

/**
 * Leer Datos de paises/*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 {

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

    String[] veccodu = coduser.split("\\ - ", -1);

    //Consulta paginada
    String query = "SELECT * FROM";
    query += "(select query.*, rownum as rn from";
    query += "(SELECT A.CODIGO, A.DESCR AS DESC1 ";
    query += " FROM SGC010 A ";
    query += " WHERE TRIM(A.CODIGO) LIKE TRIM('%" + veccodu[0] + "%')";
    query += " GROUP BY A.CODIGO, A.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()) {
        Sgc010 select = new Sgc010();
        select.setZdesc(r.getString(2));
        select.setZcoduser(r.getString(1));
        select.setZvaldel(r.getString(1) + " - " + r.getString(2));

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

}

From source file:com.nridge.core.ds.rdbms.SQLConnection.java

private void identifyVendor() throws NSException {
    Logger appLogger = mAppMgr.getLogger(this, "identifyVendor");

    appLogger.trace(mAppMgr.LOGMSG_TRACE_ENTER);

    if (mVendorName.equals(VENDOR_UNKNOWN_NAME)) {
        try {/*  w  ww .ja  v a2s  .c om*/
            mConnection.setAutoCommit(mIsAutoCommitEnabled);
            DatabaseMetaData dbMetaData = mConnection.getMetaData();
            mVendorName = dbMetaData.getDatabaseProductName();
            appLogger.debug("RDBMS vendor name is " + mVendorName);
        } catch (SQLException e) {
            mVendorName = VENDOR_UNKNOWN_NAME;
            throw new NSException("Unable to identify RDBMS vendor name: " + e.getMessage());
        }
    }

    appLogger.trace(mAppMgr.LOGMSG_TRACE_DEPART);
}

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

/**
 * Leer Datos de categoria2/*from ww w. ja va 2 s . c o 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 = " - ";
    }

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

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

    String query = "";

    switch (productName) {
    case "Oracle":
        query += "  select * from ";
        query += " ( select query.*, rownum as rn from";
        query += " (SELECT trim(A.codcat3), trim(A.descat3), trim(A.B_CODCAT1), trim(B.DESCAT1), trim(A.B_CODCAT2), trim(C.DESCAT2) ";
        query += " FROM BVTCAT3 A, BVTCAT1 B, BVTCAT2 C";
        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.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " and  A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'";
        query += " and  A.codcat3 ||a.descat3 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.codcat3), trim(A.descat3), trim(A.B_CODCAT1), trim(B.DESCAT1), trim(A.B_CODCAT2), trim(C.DESCAT2) ";
        query += " FROM BVTCAT3 A, BVTCAT1 B, BVTCAT2 C";
        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.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " and  A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        query += " and  A.codcat3 ||a.descat3 like  '%" + ((String) filterValue).toUpperCase() + "%'";
        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.CODCAT3 ASC) AS ROW_NUM, ";
        query += "         A.CODCAT3, ";
        query += "         A.DESCAT3, ";
        query += "         A.B_CODCAT1, ";
        query += "         B.DESCAT1, ";
        query += "         A.B_CODCAT2, ";
        query += "         C.DESCAT2 ";
        query += "         FROM BVTCAT3 A, BVTCAT1 B, BVTCAT2 C ";
        query += "         WHERE A.B_CODCAT1=B.CODCAT1 ";
        query += "         AND A.B_CODCAT1=C.B_CODCAT1 ";
        query += "         AND A.B_CODCAT2=C.CODCAT2) TOT ";
        query += " WHERE ";
        query += " TOT.B_CODCAT1 LIKE '" + veccodcat1[0].toUpperCase() + "%'";
        query += " AND TOT.B_CODCAT2 LIKE '" + veccodcat2[0].toUpperCase() + "%'";
        query += " AND TOT.CODCAT3 + TOT.DESCAT3 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;
    }

    pstmt = con.prepareStatement(query);

    r = pstmt.executeQuery();

    while (r.next()) {
        Bvtcat3 select = new Bvtcat3();
        select.setCodcat3(r.getString(1));
        select.setDescat3(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));

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

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

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

    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.equals("")) {
        b_codrol = " - ";
    }
    if (b_codcat1 == null) {
        b_codcat1 = " - ";
    }
    if (b_codcat1 == "") {
        b_codcat1 = " - ";
    }
    String[] veccodrol = b_codrol.split("\\ - ", -1);
    String[] veccodcat1 = b_codcat1.split("\\ - ", -1);

    switch (productName) {
    case "Oracle":
        query += "  select * from ";
        query += " ( select query.*, rownum as rn from";
        query += "  ( SELECT trim(a.b_codrol), trim(a.b_codcat1), trim(b.descat1), trim(a.b_codcat2), trim(c.descat2)";
        query += " FROM acccat2 a, bvtcat1 b, bvtcat2 c";
        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.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and  a.b_codrol like '" + veccodrol[0] + "%'";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " AND   a.b_codcat1||b.descat1||a.b_codcat2||c.descat2 like '%"
                + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        query += " order by " + sortField + ") query";
        query += " ) where rownum <= " + pageSize;
        query += " and rn > (" + first + ")";
        break;
    case "PostgreSQL":
        query += " SELECT trim(a.b_codrol), trim(a.b_codcat1), trim(b.descat1), trim(a.b_codcat2), trim(c.descat2)";
        query += " FROM acccat2 a, bvtcat1 b, bvtcat2 c";
        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.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and  a.b_codrol like '" + veccodrol[0] + "%'";
        query += " and  A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'";
        query += " AND  a.b_codcat1||b.descat1||a.b_codcat2||c.descat2 like '%"
                + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        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()) {
        Acccat2 select = new Acccat2();
        select.setB_codrol(r.getString(1));
        select.setB_codcat1(r.getString(2));
        select.setDescat1(r.getString(3));
        select.setB_codcat2(r.getString(4));
        select.setDescat2(r.getString(5));
        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();
}

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

/**
 * Actualiza ciudades/* w  ww. ja v  a  2  s .  c  o m*/
 **/
private void update() {

    String[] vecValores = p_codpai.split("\\ - ", -1);

    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":
            query = "UPDATE PNT004 SET desciu = ?, estado = ?,";
            query += "p_codpai = " + Integer.parseInt(vecValores[0]);
            query += ",FECACT = '" + getFecha() + "' , USRACT = '" + login + "'";
            query += " WHERE codciu = '" + codciu + "'";
            query += " and grupo = " + Integer.parseInt(localgrupo);
            break;
        case "PostgreSQL":
            query = "UPDATE PNT004 SET desciu = ?, estado = ?,";
            query += "p_codpai = " + Integer.parseInt(vecValores[0]);
            query += ",FECACT = '" + getFecha() + "' , USRACT = '" + login + "'";
            query += " WHERE CAST(codciu AS text) = '" + codciu + "'";
            query += " and grupo = " + Integer.parseInt(localgrupo);
            break;
        }

        //System.out.println(query);
        pstmt = con.prepareStatement(query);
        pstmt.setString(1, desciu.toUpperCase());
        pstmt.setString(2, estado.toUpperCase());
        //Antes de insertar verifica si el rol del usuario tiene permisos para insertar
        vGacc = acc.valAccmnu("bas02", "update", login, JNDI);//LLama a la funcion que valida las opciones del rol
        if (vGacc) {
            msj = new FacesMessage(FacesMessage.SEVERITY_ERROR, getMessage("msnAccUpdate"), "");
        } else {
            try {
                //Avisando
                pstmt.executeUpdate();
                if (pstmt.getUpdateCount() == 0) {
                    msj = new FacesMessage(FacesMessage.SEVERITY_ERROR, getMessage("msnNoUpdate"), "");
                } else {
                    msj = new FacesMessage(FacesMessage.SEVERITY_INFO, getMessage("msnUpdate"), "");
                }
                desciu = "";
                p_codpai = "";
                estado = "";
                validarOperacion = 0;
                localgrupo = "";
            } catch (SQLException e) {
                e.printStackTrace();
                msj = new FacesMessage(FacesMessage.SEVERITY_FATAL, e.getMessage(), "");
            }

            pstmt.close();
            con.close();

        } //Fin validacion de licencia

    } catch (Exception e) {
        e.printStackTrace();
    }

    FacesContext.getCurrentInstance().addMessage(null, msj);
}

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

/**
 * Leer Datos de categoria2//  www.j a  v  a 2s. c  o  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

    String query = "";
    if (b_codrol == null) {
        b_codrol = " - ";
    }
    if (b_codrol.equals("")) {
        b_codrol = " - ";
    }
    String[] veccodrol = b_codrol.split("\\ - ", -1);

    switch (productName) {
    case "Oracle":
        query += "  select * from ";
        query += " ( select query.*, rownum as rn from";
        query += " (SELECT trim(a.b_codrol), trim(b.desrol), trim(a.b_codcat1), trim(c.descat1)";
        query += " FROM acccat1 a, bvt003 b, bvtcat1 c";
        query += " WHERE a.b_codrol=b.codrol ";
        query += " and   a.b_codcat1=c.codcat1 ";
        query += " and A.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and   a.b_codrol like '" + veccodrol[0] + "%'";
        query += " AND   a.b_codcat1||c.descat1 like '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        query += " order by " + sortField + ") query";
        query += " ) where rownum <= " + pageSize;
        query += " and rn > (" + first + ")";
        break;
    case "PostgreSQL":
        query += " SELECT trim(a.b_codrol), trim(b.desrol), trim(a.b_codcat1), trim(c.descat1)";
        query += " FROM acccat1 a, bvt003 b, bvtcat1 c";
        query += " WHERE a.b_codrol=b.codrol ";
        query += " and   a.b_codcat1=c.codcat1 ";
        query += " and A.instancia=B.instancia";
        query += " and A.instancia=c.instancia";
        query += " and   a.b_codrol like '" + veccodrol[0] + "%'";
        query += " AND   a.b_codcat1||c.descat1 like '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   a.instancia = '" + instancia + "'";
        query += " order by " + sortField;
        query += " LIMIT " + pageSize;
        query += " OFFSET " + first;
        break;
    case "Microsoft SQL Server":
        query += " SELECT TOP " + pageSize;
        query += " TOT.B_CODROL, ";
        query += " TOT.DESROL, ";
        query += " TOT.B_CODCAT1, ";
        query += " TOT.DESCAT1 ";
        query += " FROM (SELECT ";
        query += "       ROW_NUMBER() OVER (ORDER BY A.B_CODROL ASC) AS ROW_NUM, ";
        query += "         A.B_CODROL, ";
        query += "         B.DESROL, ";
        query += "         A.B_CODCAT1, ";
        query += "         C.DESCAT1 ";
        query += "         FROM ACCCAT1 A, BVT003 B, BVTCAT1 C ";
        query += "         WHERE ";
        query += "         A.B_CODROL=B.CODROL ";
        query += "         AND   A.B_CODCAT1=C.CODCAT1) TOT ";
        query += " WHERE ";
        query += " TOT.B_CODROL = '" + veccodrol[0] + "'";
        query += " AND TOT.B_CODCAT1 + TOT.DESCAT1 LIKE '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND   tot.instancia = '" + instancia + "'";
        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()) {
        Acccat1 select = new Acccat1();
        select.setB_codrol(r.getString(1));
        select.setDesrol(r.getString(1) + " - " + r.getString(2));
        select.setB_codcat1(r.getString(3));
        select.setDescat1(r.getString(4));
        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();
}

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

/**
 * Borra Ciudades//from   w  w  w  .ja va2  s  .c  o  m
 * <p>
 * Parametros del metodo: String param. String cuenta
 * @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  PNT004 WHERE codciu||grupo in (" + param + ")";
                    break;
                case "PostgreSQL":
                    query = "DELETE FROM  PNT004 WHERE CAST(codciu AS text)||CAST(grupo AS text) in (" + param
                            + ")";
                    break;
                }

                pstmt = con.prepareStatement(query);

                //Antes de insertar verifica si el rol del usuario tiene permisos para insertar
                vGacc = acc.valAccmnu("bas02", "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();
                        list.clear();
                    } 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);
    }
}