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

/**
* Seleccin de roles/* www  . j  a  v a 2  s.  co  m*/
* @throws NamingException 
 * @throws SQLException 
* @throws IOException 
**/
public void selectRoles() throws NamingException, SQLException {
    //Para mostrar en lista los roles

    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 codrol, desrol ";
        query += " FROM bvt003";
        query += " where instancia = '" + instancia_insert + "' order by 1";

        break;
    case "PostgreSQL":
        query += " select codrol, desrol";
        query += " FROM bvt003";
        query += " where instancia = '" + instancia_insert + "' order by 1";
        break;
    case "Microsoft SQL Server":
        query += " select codrol, desrol ";
        query += " FROM Bvt008";
        query += " where instancia = '" + instancia_insert + "' order by 1";
    }

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

    r = pstmt.executeQuery();

    while (r.next()) {
        Bvt002 select = new Bvt002();
        select.setVcodrol(r.getString(1));
        select.setVdesrol(r.getString(2));
        //Agrega la lista
        listRoles.add(select);
    }

    setListRolesSession(listRoles);

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

From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java

/**
 * Set the specified PreparedStatement parameter to null,
 * respecting database-specific peculiarities.
 *///  w w w . jav a  2s.  com
private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName)
        throws SQLException {
    if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
        boolean useSetObject = false;
        Integer sqlTypeToUse = null;
        DatabaseMetaData dbmd = null;
        String jdbcDriverName = null;
        boolean checkGetParameterType = !shouldIgnoreGetParameterType;
        if (checkGetParameterType && !driversWithNoSupportForGetParameterType.isEmpty()) {
            try {
                dbmd = ps.getConnection().getMetaData();
                jdbcDriverName = dbmd.getDriverName();
                checkGetParameterType = !driversWithNoSupportForGetParameterType.contains(jdbcDriverName);
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (checkGetParameterType) {
            try {
                sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
            } catch (Throwable ex) {
                if (logger.isDebugEnabled()) {
                    logger.debug(
                            "JDBC 3.0 getParameterType call not supported - using fallback method instead: "
                                    + ex);
                }
            }
        }
        if (sqlTypeToUse == null) {
            // JDBC driver not compliant with JDBC 3.0 -> proceed with database-specific checks
            sqlTypeToUse = Types.NULL;
            try {
                if (dbmd == null) {
                    dbmd = ps.getConnection().getMetaData();
                }
                if (jdbcDriverName == null) {
                    jdbcDriverName = dbmd.getDriverName();
                }
                if (checkGetParameterType) {
                    driversWithNoSupportForGetParameterType.add(jdbcDriverName);
                }
                String databaseProductName = dbmd.getDatabaseProductName();
                if (databaseProductName.startsWith("Informix")
                        || jdbcDriverName.startsWith("Microsoft SQL Server")) {
                    useSetObject = true;
                } else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
                        || jdbcDriverName.startsWith("SQLServer")
                        || jdbcDriverName.startsWith("Apache Derby")) {
                    sqlTypeToUse = Types.VARCHAR;
                }
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (useSetObject) {
            ps.setObject(paramIndex, null);
        } else {
            ps.setNull(paramIndex, sqlTypeToUse);
        }
    } else if (typeName != null) {
        ps.setNull(paramIndex, sqlType, typeName);
    } else {
        ps.setNull(paramIndex, sqlType);
    }
}

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

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

    String validar = "1";
    String querycon = "SELECT BI_SGC014('" + login.toUpperCase() + "') AS VALIDAR FROM DUAL";

    //System.out.println(querycon);
    //System.out.println(JNDI);

    consulta.selectPntGenerica(querycon, JNDI);

    rows = consulta.getRows();
    tabla = consulta.getArray();
    //System.out.println(tabla[0][0]);

    if (tabla[0][0].equals(validar)) {

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }

        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.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.REGIST, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.ARCHIV ";
        query += " FROM SGC012B A, SGC001 B, SGC005 C, SGC006 D ";
        query += " WHERE A.CODIGO = B.CODIGO ";
        query += " AND A.COMP = C.CODIGO ";
        query += " AND A.AREA = D.CODIGO ";
        query += " AND C.CODIGO = D.COMP ";
        query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += " AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += " AND TRIM(A.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += " AND A.ANOCAL LIKE TRIM('%" + anocal + "%')";
        query += " AND A.MESCAL LIKE TRIM('%" + mescal + "%')";
        query += " GROUP BY A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.REGIST, B.NOMIND, C.DESCR, D.DESCR, A.ARCHIV";
        query += ")query ) ";
        query += " WHERE ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";
        query += " ORDER BY COMP, AREA, CODIGO, ANOCAL, MESCAL, REGIST";

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

        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012b select = new Sgc012b();
            select.setZcomp(r.getString(1) + " - " + r.getString(8));
            select.setZarea(r.getString(2) + " - " + r.getString(9));
            select.setZcodigo(r.getString(3) + " - " + r.getString(7));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZregist(r.getString(6));
            select.setZarchiv(r.getString(10));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(6));

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

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }

        String[] veccomp = comp.split("\\ - ", -1);
        String[] vecarea = area.split("\\ - ", -1);
        String[] veccod = codigo.split("\\ - ", -1);

        //Consulta paginada
        String query = " SELECT  ";
        query += " *  ";
        query += " FROM (select  ";
        query += "       query.*, rownum as rn  ";
        query += "       from (SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.REGIST, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.ARCHIV   ";
        query += "             FROM  ";
        query += "             SGC012B A, SGC001 B, SGC005 C, SGC006 D, SGC009 E ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP   ";
        query += "             AND A.COMP = E.COMP ";
        query += "             AND A.AREA = E.AREA ";
        query += "             AND A.CODIGO = E.INDICA ";
        query += "             AND E.CODUSER = '" + login.toUpperCase() + "'  ";
        query += "             UNION ALL ";
        query += "             SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.REGIST, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.ARCHIV   ";
        query += "             FROM  ";
        query += "             SGC012B A, SGC001 B, SGC005 C, SGC006 D   ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP   ";
        query += "             AND A.COMP||A.AREA IN (SELECT COMP||AREA FROM SGC008 WHERE CODUSER = '"
                + login.toUpperCase() + "' AND DUEPRO = '1') ";
        query += "         ) query  ";
        query += "         WHERE ";
        query += "          TRIM(query.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += "         AND TRIM(query.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += "         AND TRIM(query.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += "         AND query.ANOCAL LIKE TRIM('%" + anocal + "%')";
        query += "         AND query.MESCAL LIKE TRIM('%" + mescal + "%')";
        query += "       )   ";
        query += " WHERE  ";
        query += " ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";
        query += " ORDER BY COMP, AREA, CODIGO, ANOCAL, MESCAL, REGIST ";

        pstmt = con.prepareStatement(query);
        //System.out.println(query);
        //System.out.println("***NO ADMINISTRADOR");   
        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012b select = new Sgc012b();
            select.setZcomp(r.getString(1) + " - " + r.getString(8));
            select.setZarea(r.getString(2) + " - " + r.getString(9));
            select.setZcodigo(r.getString(3) + " - " + r.getString(7));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZregist(r.getString(6));
            select.setZarchiv(r.getString(10));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + 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.Bvt002.java

/**
 * Leer Datos de Usuarios/*  www.ja v a  2 s. c  o m*/
 *<p> Parametros del Metodo: String coduser, String desuser.
 * String pool
* @throws IOException 
 **/
public void selectLogin(String user, String pool) throws NamingException {

    //Pool de conecciones JNDI. Cambio de metodologia de conexion a Bd. Julio 2010
    Context initContext = new InitialContext();
    DataSource ds = (DataSource) initContext.lookup(JNDI);
    try {
        Statement stmt;
        ResultSet rs;
        Connection 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
        //Class.forName(getDriver());
        //con = DriverManager.getConnection(
        //        getUrl(), getUsuario(), getClave());
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "";

        //System.out.println( productName );

        switch (productName) {
        case "Oracle":
            query = "SELECT trim(coduser), trim(cluser), trim(B_CODROL), trim(desuser), trim(mail), instancia";
            query += " FROM BVT002";
            query += " where coduser = '" + user.toUpperCase() + "'";
            break;
        case "PostgreSQL":
            query = "SELECT trim(coduser), trim(cluser), trim(B_CODROL), trim(desuser), trim(mail), instancia";
            query += " FROM BVT002";
            query += " where coduser = '" + user.toUpperCase() + "'";
            break;
        case "Microsoft SQL Server":
            query = "SELECT coduser, cluser, B_CODROL, desuser, mail, instancia";
            query += " FROM BVT002";
            query += " where coduser = '" + user.toUpperCase() + "'";
            break;
        }

        // System.out.println(query);
        try {
            rs = stmt.executeQuery(query);
            rows = 1;
            rs.last();
            rows = rs.getRow();
            //System.out.println(rows);

            ResultSetMetaData rsmd = rs.getMetaData();
            columns = rsmd.getColumnCount();
            //System.out.println(columns);
            arr = new String[rows][columns];

            int i = 0;
            rs.beforeFirst();
            while (rs.next()) {
                for (int j = 0; j < columns; j++) {
                    arr[i][j] = rs.getString(j + 1);
                }
                i++;
            }
        } catch (SQLException e) {
        }
        stmt.close();
        con.close();

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

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

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

    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 * from ";
        query += " ( select query.*, rownum as rn from";
        query += " (SELECT trim(A.CODUSER) , trim(A.DESUSER), trim(A.CLUSER), trim(A.B_CODROL), trim(B.DESROL), trim(a.mail), a.instancia||' - '||trim(c.descripcion)";
        query += " FROM Bvt002 A, BVT003 B, INSTANCIAS C ";
        query += " WHERE A.B_CODROL=B.CODROL";
        query += " and A.instancia=b.instancia";
        query += " and A.instancia=c.instancia";
        query += " AND A.CODUSER like '" + coduser.toUpperCase() + "%'";
        query += " AND A.instancia like '" + instancia_insert + "%'";
        query += " AND A.CODUSER||A.DESUSER like '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " order by " + sortField + ") query";
        query += " ) where rownum <= " + pageSize;
        query += " and rn > (" + first + ")";
        break;
    case "PostgreSQL":
        query += " SELECT trim(A.CODUSER) , trim(A.DESUSER), trim(A.CLUSER), trim(A.B_CODROL), trim(B.DESROL), trim(a.mail), a.instancia||' - '||trim(c.descripcion)";
        query += " FROM Bvt002 A inner JOIN INSTANCIAS C ON a.INSTANCIA=c.INSTANCIA, BVT003 B";
        query += " WHERE A.B_CODROL=B.CODROL ";
        query += " and A.INSTANCIA=B.INSTANCIA ";
        query += " AND cast(A.instancia as text) like '" + instancia_insert + "%'";
        query += " AND A.CODUSER||A.DESUSER like '%" + ((String) filterValue).toUpperCase() + "%'";
        query += " AND A.CODUSER like '" + coduser.toUpperCase() + "%'";
        query += " order by " + sortField;
        query += " LIMIT " + pageSize;
        query += " OFFSET " + first;
        break;
    case "Microsoft SQL Server":
        query += "SELECT * FROM (SELECT ";
        query += "            ROW_NUMBER() OVER (ORDER BY A.CODUSER ASC) AS ROW_NUM, ";
        query += "            A.CODUSER , A.DESUSER, A.CLUSER, A.B_CODROL, B.DESROL, a.mail, a.instancia ";
        query += "            FROM Bvt002 A, BVT003 B ";
        query += "            WHERE A.B_CODROL=B.CODROL) TOT ";
        query += "WHERE  ";
        query += "TOT.CODUSER + TOT.DESUSER LIKE '%" + ((String) filterValue).toUpperCase() + "%')) ";
        query += " AND A.instancia like '" + instancia_insert + "%'";
        query += " AND TOT.CODUSER like '" + coduser.toUpperCase() + "%'";
        query += "AND TOT.ROW_NUM <= " + pageSize;
        query += "AND TOT.ROW_NUM > " + first;
        query += "ORDER BY " + sortField;
    }

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

    r = pstmt.executeQuery();

    while (r.next()) {
        Bvt002 select = new Bvt002();
        select.setCoduser(r.getString(1));
        select.setDesuser(r.getString(2));
        select.setCluser(r.getString(3));
        select.setb_codrol(r.getString(4) + " - " + r.getString(5));
        select.setDesrol(r.getString(5));
        select.setMail(r.getString(6));
        select.setInstancia(r.getString(7));
        //Agrega la lista
        list.add(select);
    }
    //Cierra las conecciones
    pstmt.close();
    con.close();
}

From source file:org.apache.jackrabbit.core.persistence.db.DatabasePersistenceManager.java

/**
 * {@inheritDoc}//w ww  .ja  v  a 2s. c o m
 */
public void init(PMContext context) throws Exception {
    if (initialized) {
        throw new IllegalStateException("already initialized");
    }

    // setup jdbc connection
    initConnection();

    DatabaseMetaData meta = con.getMetaData();
    try {
        log.info("Database: " + meta.getDatabaseProductName() + " / " + meta.getDatabaseProductVersion());
        log.info("Driver: " + meta.getDriverName() + " / " + meta.getDriverVersion());
    } catch (SQLException e) {
        log.warn("Can not retrieve database and driver name / version", e);
    }

    // make sure schemaObjectPrefix consists of legal name characters only
    prepareSchemaObjectPrefix();

    // check if schema objects exist and create them if necessary
    if (isSchemaCheckEnabled()) {
        checkSchema();
    }

    // build sql statements
    buildSQLStatements();

    // prepare statements
    initPreparedStatements();

    if (externalBLOBs) {
        /**
         * store BLOBs in local file system in a sub directory
         * of the workspace home directory
         */
        LocalFileSystem blobFS = new LocalFileSystem();
        blobFS.setRoot(new File(context.getHomeDir(), "blobs"));
        blobFS.init();
        this.blobFS = blobFS;
        blobStore = new FileSystemBLOBStore(blobFS);
    } else {
        /**
         * store BLOBs in db
         */
        blobStore = new DbBLOBStore();
    }

    initialized = true;
}

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

/**
 * Leer Datos de paises//www  .  j  ava2s . 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

    String validar = "1";
    String querycon = "SELECT BI_SGC014('" + login.toUpperCase() + "') AS VALIDAR FROM DUAL";

    //System.out.println(querycon);
    //System.out.println(JNDI);

    consulta.selectPntGenerica(querycon, JNDI);

    rows = consulta.getRows();
    tabla = consulta.getArray();
    //System.out.println(tabla[0][0]);

    if (tabla[0][0].equals(validar)) {

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }

        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.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.RESPON, TO_CHAR(A.FECACC,'DD/MM/YYYY') AS FECACC, A.REGIST, A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3 ";
        query += " FROM SGC012A A, SGC001 B, SGC005 C, SGC006 D ";
        query += " WHERE A.CODIGO = B.CODIGO ";
        query += " AND A.COMP = C.CODIGO ";
        query += " AND A.AREA = D.CODIGO ";
        query += " AND C.CODIGO = D.COMP ";
        query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += " AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += " AND TRIM(A.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += " AND TO_CHAR(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
        query += " AND TO_CHAR(A.MESCAL) LIKE TRIM('%" + mescal + "%')";
        query += " GROUP BY A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.RESPON, A.FECACC, A.REGIST, A.USRCRE,A.FECCRE, B.NOMIND, C.DESCR, D.DESCR";
        query += ")query ) ";
        query += " WHERE ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";
        query += " ORDER BY COMP, AREA, CODIGO, ANOCAL, MESCAL, REGIST" + sortField.replace("z", "");

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

        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012a select = new Sgc012a();
            select.setZcomp(r.getString(1) + " - " + r.getString(13));
            select.setZarea(r.getString(2) + " - " + r.getString(14));
            select.setZcodigo(r.getString(3) + " - " + r.getString(12));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZaccion(r.getString(6));
            select.setZrespon(r.getString(7));
            select.setZfecacc(r.getString(8));
            select.setZregist(r.getString(9));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(9));
            select.setZusrcre(r.getString(10));
            select.setZfeccre(r.getString(11));

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

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }

        String[] veccomp = comp.split("\\ - ", -1);
        String[] vecarea = area.split("\\ - ", -1);
        String[] veccod = codigo.split("\\ - ", -1);

        //Consulta paginada
        String query = " SELECT  ";
        query += " *  ";
        query += " FROM (select  ";
        query += "       query.*, rownum as rn  ";
        query += "       from (SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.RESPON, TO_CHAR(A.FECACC,'DD/MM/YYYY') AS FECACC, A.REGIST,  ";
        query += "             A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3   ";
        query += "             FROM  ";
        query += "             SGC012A A, SGC001 B, SGC005 C, SGC006 D, SGC009 E  ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP  ";
        query += "             AND A.COMP = E.COMP ";
        query += "             AND A.AREA = E.AREA  ";
        query += "             AND A.CODIGO = E.INDICA ";
        query += "             AND E.CODUSER = '" + login.toUpperCase() + "' ";
        query += "             UNION ALL ";
        query += "             SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.RESPON, TO_CHAR(A.FECACC,'DD/MM/YYYY') AS FECACC, A.REGIST,  ";
        query += "             A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3   ";
        query += "             FROM  ";
        query += "             SGC012A A, SGC001 B, SGC005 C, SGC006 D ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP  ";
        query += "             AND A.COMP||A.AREA IN (SELECT COMP||AREA FROM SGC008 WHERE CODUSER = '"
                + login.toUpperCase() + "' AND DUEPRO = '1') ";
        query += "             ) query ";
        query += "             WHERE ";
        query += "             TRIM(query.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += "             AND TRIM(query.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += "             AND TRIM(query.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += "             AND TO_CHAR(query.ANOCAL) LIKE TRIM('%" + anocal + "%')";
        query += "             AND TO_CHAR(query.MESCAL) LIKE TRIM('%" + mescal + "%')";
        query += "     )   ";
        query += " WHERE  ";
        query += " ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";
        query += " ORDER BY COMP, AREA, CODIGO, ANOCAL, MESCAL, REGIST ";

        pstmt = con.prepareStatement(query);
        //System.out.println(query);
        //System.out.println("***NO ADMINISTRADOR***");

        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012a select = new Sgc012a();
            select.setZcomp(r.getString(1) + " - " + r.getString(13));
            select.setZarea(r.getString(2) + " - " + r.getString(14));
            select.setZcodigo(r.getString(3) + " - " + r.getString(12));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZaccion(r.getString(6));
            select.setZrespon(r.getString(7));
            select.setZfecacc(r.getString(8));
            select.setZregist(r.getString(9));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(9));
            select.setZusrcre(r.getString(10));
            select.setZfeccre(r.getString(11));

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

From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java

public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern)
        throws IOException, SQLException {
    Map dataTypesMap = prepareJdbcTypeInfoMap();
    DatabaseMetaData dbmd = conn.getMetaData();
    TextUtils textUtils = TextUtils.getInstance();

    writer.write("<?xml version=\"1.0\"?>\n\n");
    writer.write("<!-- Reverse engineered by Axiom\n");
    writer.write("     driver: " + dbmd.getDriverName() + "\n");
    writer.write("     driver-version: " + dbmd.getDriverVersion() + "\n");
    writer.write("     product: " + dbmd.getDatabaseProductName() + "\n");
    writer.write("     product-version: " + dbmd.getDatabaseProductVersion() + "\n");

    writer.write("     available catalogs:");
    ResultSet rs = null;/*from   ww w  . jav  a2 s  .c o m*/
    try {
        rs = dbmd.getCatalogs();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }

    writer.write("\n");

    writer.write("     available schemas:");
    try {
        rs = dbmd.getSchemas();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }
    writer.write("\n");
    writer.write("-->\n\n");

    writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n");
    writer.write("    <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n");
    writer.write("    <schema name=\"" + catalog + "." + schemaPattern + "\">\n");

    Map dbmdTypeInfoByName = new HashMap();
    Map dbmdTypeInfoByJdbcType = new HashMap();
    ResultSet typesRS = null;
    try {
        typesRS = dbmd.getTypeInfo();
        while (typesRS.next()) {
            int colCount = typesRS.getMetaData().getColumnCount();
            Object[] typeInfo = new Object[colCount];
            for (int i = 1; i <= colCount; i++)
                typeInfo[i - 1] = typesRS.getObject(i);
            dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo);
            dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo);
        }
    } finally {
        if (typesRS != null)
            typesRS.close();
    }

    ResultSet tables = null;
    try {
        tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" });
        while (tables.next()) {
            String tableNameOrig = tables.getString(3);
            String tableName = textUtils.fixupTableNameCase(tableNameOrig);

            writer.write("        <table name=\"" + tableName + "\">\n");

            Map primaryKeys = new HashMap();
            ResultSet pkRS = null;
            try {
                pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig);
                while (pkRS.next()) {
                    primaryKeys.put(pkRS.getString(4), pkRS.getString(5));
                }

            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (pkRS != null)
                    pkRS.close();
            }

            Map fKeys = new HashMap();
            ResultSet fkRS = null;
            try {
                fkRS = dbmd.getImportedKeys(null, null, tableNameOrig);
                while (fkRS.next()) {
                    fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "."
                            + fkRS.getString(4).toLowerCase());
                }
            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (fkRS != null)
                    fkRS.close();
            }

            // we keep track of processed columns so we don't duplicate them in the XML
            Set processedColsMap = new HashSet();
            ResultSet columns = null;
            try {
                columns = dbmd.getColumns(null, null, tableNameOrig, null);
                while (columns.next()) {
                    String columnNameOrig = columns.getString(4);
                    if (processedColsMap.contains(columnNameOrig))
                        continue;
                    processedColsMap.add(columnNameOrig);

                    String columnName = columnNameOrig.toLowerCase();

                    writer.write("            <column name=\"" + columnName + "\"");
                    try {
                        if (fKeys.containsKey(columnNameOrig))
                            writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\"");
                        else {
                            short jdbcType = columns.getShort(5);
                            String dataType = (String) dataTypesMap.get(new Integer(jdbcType));
                            if (dataType == null)
                                dataType = Short.toString(jdbcType);
                            writer.write(" type=\"" + dataType + "\"");
                        }

                        if (primaryKeys.containsKey(columnNameOrig))
                            writer.write(" primary-key=\"yes\"");

                        if (columns.getString(18).equals("NO"))
                            writer.write(" required=\"yes\"");

                        String defaultValue = columns.getString(13);
                        if (defaultValue != null)
                            writer.write(" default=\"" + defaultValue + "\"");

                        String remarks = columns.getString(12);
                        if (remarks != null)
                            writer.write(" descr=\"" + remarks + "\"");

                    } catch (Exception e) {
                    }

                    writer.write("/>\n");
                }
            } finally {
                if (columns != null)
                    columns.close();
            }

            writer.write("        </table>\n");
        }
    } finally {
        tables.close();
    }

    writer.write("    </schema>\n");
    writer.write("</component>");
}

From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java

private void addDatabase(DatabaseMetaData dbMeta, PhysicalModel model) {
    try {//from www. j  a va 2 s . c  o m
        model.setDatabaseName(dbMeta.getDatabaseProductName());
        model.setDatabaseVersion(dbMeta.getDatabaseProductVersion());
    } catch (Throwable t) {
        throw new RuntimeException("Impossible to initialize database metadata", t);
    }
}

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

/**
 * Leer Datos de paises//from www .  j a  va  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

    String validar = "1";
    String querycon = "SELECT BI_SGC014('" + login.toUpperCase() + "') AS VALIDAR FROM DUAL";

    //System.out.println(querycon);
    //System.out.println(JNDI);

    consulta.selectPntGenerica(querycon, JNDI);

    rows = consulta.getRows();
    tabla = consulta.getArray();
    //System.out.println(tabla[0][0]);

    if (tabla[0][0].equals(validar)) {

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }
        if (accion == null) {
            accion = "";
        }

        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.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.PORCUM, TO_CHAR(E.FECACC,'DD/MM/YYYY') AS FECACC, TO_CHAR(A.FECCOM,'DD/MM/YYYY') AS FECCOM, A.REGIST, A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE, B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.COMENT ";
        query += " FROM SGC012C A, SGC001 B, SGC005 C, SGC006 D, SGC012A E ";
        query += " WHERE A.CODIGO = B.CODIGO ";
        query += " AND A.COMP = C.CODIGO ";
        query += " AND A.AREA = D.CODIGO ";
        query += " AND C.CODIGO = D.COMP ";
        query += " AND A.COMP = E.COMP ";
        query += " AND A.AREA = E.AREA ";
        query += " AND A.CODIGO = E.CODIGO ";
        query += " AND A.ANOCAL = E.ANOCAL ";
        query += " AND A.MESCAL = E.MESCAL ";
        query += " AND A.ACCION = E.ACCION ";
        query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += " AND TRIM(A.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += " AND TRIM(A.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += " AND TO_CHAR(A.ANOCAL) LIKE TRIM('%" + anocal + "%')";
        query += " AND TO_CHAR(A.MESCAL) LIKE TRIM('%" + mescal + "%')";
        query += " AND A.ACCION LIKE TRIM('%" + accion + "%')";
        query += " GROUP BY A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.PORCUM, A.FECCOM, E.FECACC, A.REGIST, A.USRCRE,A.FECCRE, B.NOMIND, C.DESCR, D.DESCR, A.COMENT";
        query += ")query ) ";
        query += " WHERE ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";
        query += " ORDER BY COMP, AREA, CODIGO, ANOCAL, MESCAL, ACCION, REGIST";

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

        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012c select = new Sgc012c();
            select.setZcomp(r.getString(1) + " - " + r.getString(14));
            select.setZarea(r.getString(2) + " - " + r.getString(15));
            select.setZcodigo(r.getString(3) + " - " + r.getString(13));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZaccion(r.getString(6));
            select.setZporcum(r.getString(7));
            select.setZfecacc(r.getString(8)); //
            select.setZfeccom(r.getString(9));
            select.setZregist(r.getString(10));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(9));
            select.setZusrcre(r.getString(11));
            select.setZfeccre(r.getString(12));
            select.setZcoment(r.getString(16));

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

        if (comp == null) {
            comp = " - ";
        }
        if (comp == "") {
            comp = " - ";
        }
        if (area == null) {
            area = " - ";
        }
        if (area == "") {
            area = " - ";
        }
        if (codigo == null) {
            codigo = " - ";
        }
        if (codigo == "") {
            codigo = " - ";
        }
        if (anocal == null) {
            anocal = "";
        }
        if (mescal == null) {
            mescal = "";
        }
        if (accion == null) {
            accion = "";
        }

        String[] veccomp = comp.split("\\ - ", -1);
        String[] vecarea = area.split("\\ - ", -1);
        String[] veccod = codigo.split("\\ - ", -1);

        //Consulta paginada
        String query = " SELECT  ";
        query += " *  ";
        query += " FROM (select  ";
        query += "       query.*, rownum as rn  ";
        query += "       from (SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.PORCUM, TO_CHAR(E.FECACC,'DD/MM/YYYY') AS FECACC,  ";
        query += "             TO_CHAR(A.FECCOM,'DD/MM/YYYY') AS FECCOM, A.REGIST, A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE,  ";
        query += "             B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.COMENT   ";
        query += "             FROM  ";
        query += "             SGC012C A, SGC001 B, SGC005 C, SGC006 D, SGC012A E, SGC009 F   ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP   ";
        query += "             AND A.COMP = E.COMP   ";
        query += "             AND A.AREA = E.AREA   ";
        query += "             AND A.CODIGO = E.CODIGO   ";
        query += "             AND A.ANOCAL = E.ANOCAL   ";
        query += "             AND A.MESCAL = E.MESCAL   ";
        query += "             AND A.ACCION = E.ACCION  ";
        query += "             AND A.COMP = F.COMP  ";
        query += "             AND A.AREA = F.AREA ";
        query += "             AND A.CODIGO = F.INDICA ";
        query += "             AND F.CODUSER = '" + login.toUpperCase() + "' ";
        query += "             UNION ALL ";
        query += "             SELECT  ";
        query += "             A.COMP, A.AREA, A.CODIGO, A.ANOCAL, A.MESCAL, A.ACCION, A.PORCUM, TO_CHAR(E.FECACC,'DD/MM/YYYY') AS FECACC,  ";
        query += "             TO_CHAR(A.FECCOM,'DD/MM/YYYY') AS FECCOM, A.REGIST, A.USRCRE, TO_CHAR(A.FECCRE,'DD/MM/YYYY') AS FECCRE,  ";
        query += "             B.NOMIND AS DESC1, C.DESCR AS DESC2, D.DESCR AS DESC3, A.COMENT   ";
        query += "             FROM  ";
        query += "             SGC012C A, SGC001 B, SGC005 C, SGC006 D, SGC012A E   ";
        query += "             WHERE  ";
        query += "             A.CODIGO = B.CODIGO   ";
        query += "             AND A.COMP = C.CODIGO   ";
        query += "             AND A.AREA = D.CODIGO   ";
        query += "             AND C.CODIGO = D.COMP   ";
        query += "             AND A.COMP = E.COMP   ";
        query += "             AND A.AREA = E.AREA   ";
        query += "             AND A.CODIGO = E.CODIGO   ";
        query += "             AND A.ANOCAL = E.ANOCAL   ";
        query += "             AND A.MESCAL = E.MESCAL   ";
        query += "             AND A.ACCION = E.ACCION       ";
        query += "             AND A.COMP||A.AREA IN (SELECT COMP||AREA FROM SGC008 WHERE CODUSER = '"
                + login.toUpperCase() + "' AND DUEPRO = '1') ";
        query += "       ) query  ";
        query += "       WHERE ";
        query += "       TRIM(query.COMP) LIKE TRIM('%" + veccomp[0] + "%')";
        query += "       AND TRIM(query.AREA) LIKE TRIM('%" + vecarea[0] + "%')";
        query += "       AND TRIM(query.CODIGO) LIKE TRIM('%" + veccod[0] + "%')";
        query += "       AND TO_CHAR(query.ANOCAL) LIKE TRIM('%" + anocal + "%')";
        query += "       AND TO_CHAR(query.MESCAL) LIKE TRIM('%" + mescal + "%')";
        query += "       AND query.ACCION LIKE TRIM('%" + accion + "%')";
        query += "       )   ";
        query += " WHERE  ";
        query += " ROWNUM <=" + pageSize;
        query += " AND rn > (" + first + ")";

        pstmt = con.prepareStatement(query);
        //System.out.println(query);
        //System.out.println("***NO ADMINISTRADOR***");

        r = pstmt.executeQuery();

        while (r.next()) {
            Sgc012c select = new Sgc012c();
            select.setZcomp(r.getString(1) + " - " + r.getString(14));
            select.setZarea(r.getString(2) + " - " + r.getString(15));
            select.setZcodigo(r.getString(3) + " - " + r.getString(13));
            select.setZanocal(r.getString(4));
            select.setZmescal(r.getString(5));
            select.setZaccion(r.getString(6));
            select.setZporcum(r.getString(7));
            select.setZfecacc(r.getString(8)); //
            select.setZfeccom(r.getString(9));
            select.setZregist(r.getString(10));
            select.setZorderby(r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", "
                    + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(9));
            select.setZusrcre(r.getString(11));
            select.setZfeccre(r.getString(12));
            select.setZcoment(r.getString(16));

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