List of usage examples for java.sql DatabaseMetaData getDatabaseProductName
String getDatabaseProductName() throws SQLException;
From source file:org.enlacerh.util.Pnt001.java
/** * Leer Datos de paises// w w w . j a v a 2 s . c om * @throws SQLException * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException { 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 trim(a.codcia), trim(a.nomcia1), trim(a.nomcia2), trim(a.id1), trim(a.id2), trim(a.dir),"; query += " a.p_codpai, a.p_codciu, a.tlf1, a.tlf2, trim(b.despai), trim(c.desciu), a.grupo"; query += " from Pnt001 a, Pnt003 b, Pnt004 c"; query += " where a.p_codpai=b.codpai"; query += " and a.grupo=b.grupo"; query += " and a.p_codciu=c.codciu"; query += " and a.grupo=c.grupo"; query += " and a.codcia like '" + codcia.toUpperCase() + "%'"; query += " and a.codcia||a.nomcia2||a.id1 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " and a.grupo = '" + grupo + "'"; query += " order by " + sortField.replace("v", "") + ") query"; query += " ) where rownum <= " + pageSize; query += " and rn > (" + first + ")"; break; case "PostgreSQL": //Consulta paginada //Consulta paginada query = " select trim(a.codcia), trim(a.nomcia1), trim(a.nomcia2), trim(a.id1), trim(a.id2), trim(a.dir),"; query += " a.p_codpai, a.p_codciu, a.tlf1, a.tlf2, trim(b.despai), trim(c.desciu), a.grupo"; query += " from Pnt001 a, Pnt003 b, Pnt004 c"; query += " where a.p_codpai=b.codpai"; query += " and a.grupo=b.grupo"; query += " and a.p_codciu=c.codciu"; query += " and a.grupo=c.grupo"; query += " and a.codcia like '" + codcia.toUpperCase() + "%'"; query += " and a.codcia||a.nomcia2||a.id1 like '%" + ((String) filterValue).toUpperCase() + "%'"; query += " and CAST(a.grupo AS text) = '" + grupo + "'"; query += " order by " + sortField.replace("v", ""); query += " LIMIT " + pageSize; query += " OFFSET " + first; break; } pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Cia select = new Cia(); select.setVcodcia(r.getString(1)); select.setVnomcia1(r.getString(2)); select.setVnomcia2(r.getString(3)); select.setVid(r.getString(4)); select.setVid2(r.getString(5)); select.setVdir(r.getString(6)); select.setVpcodpaidespai(r.getString(7) + " - " + r.getString(11)); select.setVpcodciudesciu(r.getString(8) + " - " + r.getString(12)); select.setTlf1(r.getString(9)); select.setTlf2(r.getString(10)); select.setVpcodpai(r.getString(11)); select.setVgrupo(r.getString(13)); //Agrega la lista list.add(select); } } catch (Exception e) { } //Cierra las conecciones pstmt.close(); con.close(); r.close(); }
From source file:org.apache.geronimo.console.databasemanager.wizard.DatabasePoolPortlet.java
private static String attemptConnect(PortletRequest request, PoolData data) throws SQLException, IllegalAccessException, InstantiationException { Class driverClass = attemptDriverLoad(request, data); Driver driver = (Driver) driverClass.newInstance(); if (driver.acceptsURL(data.url)) { Properties props = new Properties(); if (data.user != null) { props.put("user", data.user); }/*from ww w .j a va 2 s.co m*/ if (data.password != null) { props.put("password", data.password); } Connection con = null; try { con = driver.connect(data.url, props); final DatabaseMetaData metaData = con.getMetaData(); return metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion(); } finally { if (con != null) { try { con.close(); } catch (SQLException e) { //ignore } } } } else throw new SQLException("Driver " + data.getDriverClass() + " does not accept URL " + data.url); }
From source file:org.openbizview.util.Sgc006.java
/** * Leer Datos de paises/* w ww .ja v a 2 s. c o m*/ * @throws NamingException * @throws IOException **/ public void select(int first, int pageSize, String sortField, Object filterValue) throws SQLException, ClassNotFoundException, NamingException { //System.out.println("entre al metodo SELECT"); Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin if (comp == null) { comp = " - "; } if (comp == "") { comp = " - "; } String[] veccomp = comp.split("\\ - ", -1); //Consulta paginada String query = "SELECT * FROM"; query += "(select query.*, rownum as rn from"; query += "(SELECT A.COMP, A.CODIGO, A.DESCR, B.DESCR AS DESC1, A.MAIL1, A.MAIL2, A.MAIL3,A.MAIL4, A.MAIL5, A.CCOSTO "; query += " FROM SGC006 A, SGC005 B "; query += " WHERE A.COMP = B.CODIGO "; query += " AND TRIM(A.COMP) LIKE TRIM('%" + veccomp[0] + "%')"; query += " GROUP BY A.COMP, A.CODIGO, A.DESCR, B.DESCR, A.MAIL1, A.MAIL2, A.MAIL3, A.MAIL4, A.MAIL5, A.CCOSTO"; query += ")query ) "; query += " WHERE ROWNUM <=" + pageSize; query += " AND rn > (" + first + ")"; query += " ORDER BY 2"; pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { Sgc006 select = new Sgc006(); select.setZcomp(r.getString(1) + " - " + r.getString(4)); select.setZcodigo(r.getString(2)); select.setZdesc(r.getString(3)); select.setZdesc1(r.getString(4)); select.setZcompa(r.getString(1)); //select.setZmail1(r.getString(5)); //select.setZmail2(r.getString(6)); //select.setZmail3(r.getString(7)); //select.setZmail4(r.getString(8)); //select.setZmail5(r.getString(9)); select.setZccosto(r.getString(10)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:com.amazon.carbonado.repo.jdbc.JDBCStorableIntrospector.java
/** * Uses the given database connection to query database metadata. This is * used to bind storables to tables, and properties to columns. Other * checks are performed to ensure that storable type matches well with the * definition in the database.//w w w .ja va 2s . c o m */ private static <S extends Storable> JDBCStorableInfo<S> examine(StorableInfo<S> mainInfo, Connection con, final String searchCatalog, final String searchSchema, SchemaResolver resolver, boolean primaryKeyCheckDisabled) throws SQLException, SupportException { final DatabaseMetaData meta = con.getMetaData(); final String databaseProductName = meta.getDatabaseProductName(); final String userName = meta.getUserName(); String[] tableAliases; if (mainInfo.getAliasCount() > 0) { tableAliases = mainInfo.getAliases(); } else { String name = mainInfo.getStorableType().getSimpleName(); tableAliases = generateAliases(name); } // Try to find matching table from aliases. String catalog = null, schema = null, tableName = null, tableType = null; findName: { // The call to getTables may return several matching tables. This // map defines the "best" table type we'd like to use. The higher // the number the better. Map<String, Integer> fitnessMap = new HashMap<String, Integer>(); fitnessMap.put("LOCAL TEMPORARY", 1); fitnessMap.put("GLOBAL TEMPORARY", 2); fitnessMap.put("VIEW", 3); fitnessMap.put("SYSTEM TABLE", 4); fitnessMap.put("TABLE", 5); fitnessMap.put("ALIAS", 6); fitnessMap.put("SYNONYM", 7); for (int i = 0; i < tableAliases.length; i++) { ResultSet rs = meta.getTables(searchCatalog, searchSchema, tableAliases[i], null); try { int bestFitness = 0; while (rs.next()) { String type = rs.getString("TABLE_TYPE"); Integer fitness = fitnessMap.get(type); if (fitness != null) { String rsSchema = rs.getString("TABLE_SCHEM"); if (searchSchema == null) { if (userName != null && userName.equalsIgnoreCase(rsSchema)) { // Favor entities whose schema name matches // the user name. fitness += 7; } } if (fitness > bestFitness) { bestFitness = fitness; catalog = rs.getString("TABLE_CAT"); schema = rsSchema; tableName = rs.getString("TABLE_NAME"); tableType = type; } } } } finally { rs.close(); } if (tableName != null) { // Found a match, so stop checking aliases. break; } } } if (tableName == null && !mainInfo.isIndependent()) { StringBuilder buf = new StringBuilder(); buf.append("Unable to find matching table name for type \""); buf.append(mainInfo.getStorableType().getName()); buf.append("\" by looking for "); appendToSentence(buf, tableAliases); buf.append(" with catalog " + searchCatalog + " and schema " + searchSchema); throw new MismatchException(buf.toString()); } String qualifiedTableName = tableName; String resolvedTableName = tableName; // Oracle specific stuff... // TODO: Migrate this to OracleSupportStrategy. if (tableName != null && databaseProductName.toUpperCase().contains("ORACLE")) { if ("TABLE".equals(tableType) && searchSchema != null) { // Qualified table name references the schema. Used by SQL statements. qualifiedTableName = searchSchema + '.' + tableName; } else if ("SYNONYM".equals(tableType)) { // Try to get the real schema. This call is Oracle specific, however. String select = "SELECT TABLE_OWNER,TABLE_NAME " + "FROM ALL_SYNONYMS " + "WHERE OWNER=? AND SYNONYM_NAME=?"; PreparedStatement ps = con.prepareStatement(select); ps.setString(1, schema); // in Oracle, schema is the owner ps.setString(2, tableName); try { ResultSet rs = ps.executeQuery(); try { if (rs.next()) { schema = rs.getString("TABLE_OWNER"); resolvedTableName = rs.getString("TABLE_NAME"); } } finally { rs.close(); } } finally { ps.close(); } } } // Gather information on all columns such that metadata only needs to // be retrieved once. Map<String, ColumnInfo> columnMap = new TreeMap<String, ColumnInfo>(String.CASE_INSENSITIVE_ORDER); if (resolvedTableName != null) { ResultSet rs = meta.getColumns(catalog, schema, resolvedTableName, null); rs.setFetchSize(1000); try { while (rs.next()) { ColumnInfo info = new ColumnInfo(rs); columnMap.put(info.columnName, info); } } finally { rs.close(); } } // Make sure that all properties have a corresponding column. Map<String, ? extends StorableProperty<S>> mainProperties = mainInfo.getAllProperties(); Map<String, String> columnToProperty = new HashMap<String, String>(); Map<String, JDBCStorableProperty<S>> jProperties = new LinkedHashMap<String, JDBCStorableProperty<S>>( mainProperties.size()); ArrayList<String> errorMessages = new ArrayList<String>(); for (StorableProperty<S> mainProperty : mainProperties.values()) { if (mainProperty.isDerived() || mainProperty.isJoin() || tableName == null) { jProperties.put(mainProperty.getName(), new JProperty<S>(mainProperty, primaryKeyCheckDisabled)); continue; } String[] columnAliases; if (mainProperty.getAliasCount() > 0) { columnAliases = mainProperty.getAliases(); } else { columnAliases = generateAliases(mainProperty.getName()); } JDBCStorableProperty<S> jProperty = null; boolean addedError = false; findName: for (int i = 0; i < columnAliases.length; i++) { ColumnInfo columnInfo = columnMap.get(columnAliases[i]); if (columnInfo != null) { AccessInfo accessInfo = getAccessInfo(mainProperty, columnInfo.dataType, columnInfo.dataTypeName, columnInfo.columnSize, columnInfo.decimalDigits); if (accessInfo == null) { TypeDesc propertyType = TypeDesc.forClass(mainProperty.getType()); String message = "Property \"" + mainProperty.getName() + "\" has type \"" + propertyType.getFullName() + "\" which is incompatible with database type \"" + columnInfo.dataTypeName + '"'; if (columnInfo.decimalDigits > 0) { message += " (decimal digits = " + columnInfo.decimalDigits + ')'; } errorMessages.add(message); addedError = true; break findName; } if (columnInfo.nullable) { if (!mainProperty.isNullable() && !mainProperty.isIndependent()) { errorMessages.add( "Property \"" + mainProperty.getName() + "\" must have a Nullable annotation"); } } else { if (mainProperty.isNullable() && !mainProperty.isIndependent()) { errorMessages.add("Property \"" + mainProperty.getName() + "\" must not have a Nullable annotation"); } } boolean autoIncrement = mainProperty.isAutomatic(); if (autoIncrement) { // Need to execute a little query to check if column is // auto-increment or not. This information is not available in // the regular database metadata prior to jdk1.6. PreparedStatement ps = con.prepareStatement( "SELECT " + columnInfo.columnName + " FROM " + tableName + " WHERE 1=0"); try { ResultSet rs = ps.executeQuery(); try { autoIncrement = rs.getMetaData().isAutoIncrement(1); } finally { rs.close(); } } finally { ps.close(); } } jProperty = new JProperty<S>(mainProperty, columnInfo, autoIncrement, primaryKeyCheckDisabled, accessInfo.mResultSetGet, accessInfo.mPreparedStatementSet, accessInfo.getAdapter()); break findName; } } if (jProperty != null) { jProperties.put(mainProperty.getName(), jProperty); columnToProperty.put(jProperty.getColumnName(), jProperty.getName()); } else { if (mainProperty.isIndependent()) { jProperties.put(mainProperty.getName(), new JProperty<S>(mainProperty, primaryKeyCheckDisabled)); } else if (!addedError) { StringBuilder buf = new StringBuilder(); buf.append("Unable to find matching database column for property \""); buf.append(mainProperty.getName()); buf.append("\" by looking for "); appendToSentence(buf, columnAliases); errorMessages.add(buf.toString()); } } } if (errorMessages.size() > 0) { throw new MismatchException(mainInfo.getStorableType(), errorMessages); } // Now verify that primary or alternate keys match. if (resolvedTableName != null) checkPrimaryKey: { ResultSet rs; try { rs = meta.getPrimaryKeys(catalog, schema, resolvedTableName); } catch (SQLException e) { getLog().info("Unable to get primary keys for table \"" + resolvedTableName + "\" with catalog " + catalog + " and schema " + schema + ": " + e); break checkPrimaryKey; } List<String> pkProps = new ArrayList<String>(); try { while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); String propertyName = columnToProperty.get(columnName); if (propertyName == null) { errorMessages .add("Column \"" + columnName + "\" must be part of primary or alternate key"); continue; } pkProps.add(propertyName); } } finally { rs.close(); } if (errorMessages.size() > 0) { // Skip any extra checks. break checkPrimaryKey; } if (pkProps.size() == 0) { // If no primary keys are reported, don't even bother checking. // There's no consistent way to get primary keys, and entities // like views and synonyms don't usually report primary keys. // A primary key might even be logically defined as a unique // constraint. break checkPrimaryKey; } if (matchesKey(pkProps, mainInfo.getPrimaryKey())) { // Good. Primary key in database is same as in Storable. break checkPrimaryKey; } // Check if Storable has an alternate key which matches the // database's primary key. boolean foundAnyAltKey = false; for (StorableKey<S> altKey : mainInfo.getAlternateKeys()) { if (matchesKey(pkProps, altKey)) { // Okay. Primary key in database matches a Storable // alternate key. foundAnyAltKey = true; // Also check that declared primary key is a strict subset // of the alternate key. If not, keep checking alt keys. if (matchesSubKey(pkProps, mainInfo.getPrimaryKey())) { break checkPrimaryKey; } } } if (foundAnyAltKey) { errorMessages.add("Actual primary key matches a declared alternate key, " + "but declared primary key must be a strict subset. " + mainInfo.getPrimaryKey().getProperties() + " is not a subset of " + pkProps); } else { errorMessages.add("Actual primary key does not match any " + "declared primary or alternate key: " + pkProps); } } if (errorMessages.size() > 0) { if (primaryKeyCheckDisabled) { for (String errorMessage : errorMessages) { getLog().warn("Suppressed error: " + errorMessage); } errorMessages.clear(); } else { throw new MismatchException(mainInfo.getStorableType(), errorMessages); } } // IndexInfo is empty, as querying for it tends to cause a table analyze to run. IndexInfo[] indexInfo = new IndexInfo[0]; if (needsQuotes(tableName)) { String quote = meta.getIdentifierQuoteString(); if (quote != null && !quote.equals(" ")) { tableName = quote + tableName + quote; qualifiedTableName = quote + qualifiedTableName + quote; } } return new JInfo<S>(mainInfo, catalog, schema, tableName, qualifiedTableName, indexInfo, jProperties); }
From source file:org.openbizview.util.Acccat3.java
/** * Leer Datos de categoria2/*from w w w. 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 { 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 = " - "; } if (b_codcat2 == null) { b_codcat2 = " - "; } if (b_codcat2 == "") { b_codcat2 = " - "; } String[] veccodrol = b_codrol.split("\\ - ", -1); String[] veccodcat1 = b_codcat1.split("\\ - ", -1); String[] veccodcat2 = b_codcat2.split("\\ - ", -1); switch (productName) { case "Oracle": query += " select * 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), trim(a.b_codcat3), trim(d.descat3)"; query += " FROM acccat3 a, bvtcat1 b, bvtcat2 c, bvtcat3 d"; query += " WHERE a.b_codcat1=b.codcat1 "; query += " and a.b_codcat1=c.b_codcat1"; query += " and a.b_codcat2=c.codcat2"; query += " and a.b_codcat1=d.b_codcat1"; query += " and a.b_codcat2=d.b_codcat2"; query += " and a.b_codcat3=d.codcat3"; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and a.b_codrol like '" + veccodrol[0] + "%'"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " AND a.b_codcat1||b.descat1||a.b_codcat2||c.descat2||a.b_codcat3||d.descat3 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), trim(a.b_codcat3), trim(d.descat3)"; query += " FROM acccat3 a, bvtcat1 b, bvtcat2 c, bvtcat3 d"; query += " WHERE a.b_codcat1=b.codcat1 "; query += " and a.b_codcat1=c.b_codcat1"; query += " and a.b_codcat2=c.codcat2"; query += " and a.b_codcat1=d.b_codcat1"; query += " and a.b_codcat2=d.b_codcat2"; query += " and a.b_codcat3=d.codcat3"; query += " and A.instancia=B.instancia"; query += " and A.instancia=c.instancia"; query += " and A.instancia=d.instancia"; query += " and a.b_codrol like '" + veccodrol[0] + "%'"; query += " and A.b_codcat1 like '" + veccodcat1[0].toUpperCase() + "%'"; query += " and A.b_codcat2 like '" + veccodcat2[0].toUpperCase() + "%'"; query += " AND a.b_codcat1||b.descat1||a.b_codcat2||c.descat2||a.b_codcat3||d.descat3 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.ROW_NUM, "; query += " TOT.B_CODROL, "; query += " TOT.B_CODCAT1, "; query += " TOT.DESCAT1, "; query += " TOT.B_CODCAT2, "; query += " TOT.DESCAT2, "; query += " TOT.B_CODCAT3, "; query += " TOT.DESCAT3 "; query += " FROM (SELECT "; query += " ROW_NUMBER() OVER (ORDER BY A.B_CODROL ASC) AS ROW_NUM, "; query += " A.B_CODROL, "; query += " A.B_CODCAT1, "; query += " B.DESCAT1, "; query += " A.B_CODCAT2, "; query += " C.DESCAT2, "; query += " A.B_CODCAT3, "; query += " D.DESCAT3 "; query += " FROM ACCCAT3 A, BVTCAT1 B, BVTCAT2 C, BVTCAT3 D "; query += " WHERE "; query += " A.B_CODCAT1=B.CODCAT1 "; query += " AND A.B_CODCAT1=C.B_CODCAT1 "; query += " AND A.B_CODCAT2=C.CODCAT2 "; query += " AND A.B_CODCAT1=D.B_CODCAT1 "; query += " AND A.B_CODCAT2=D.B_CODCAT2 "; query += " AND A.B_CODCAT3=D.CODCAT3) TOT "; query += " WHERE "; query += " TOT.B_CODROL = '" + veccodrol[0] + "'"; query += " AND TOT.B_CODCAT1 LIKE '" + veccodcat1[0].toUpperCase() + "%'"; query += " AND TOT.B_CODCAT2 LIKE '" + veccodcat2[0].toUpperCase() + "%'"; query += " AND TOT.B_CODCAT1+TOT.DESCAT1+TOT.B_CODCAT2+TOT.DESCAT2+TOT.B_CODCAT3+TOT.DESCAT3 LIKE '%" + ((String) filterValue).toUpperCase() + "%'"; query += " AND tot.instancia = '" + instancia + "'"; 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()) { Acccat3 select = new Acccat3(); 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)); select.setB_codcat3(r.getString(6)); select.setDescat3(r.getString(7)); //Agrega la lista list.add(select); } //Cierra las conecciones pstmt.close(); con.close(); }
From source file:org.openbizview.util.Acccat4.java
/** * Leer registros en la tabla/*ww w . j av a 2 s.com*/ * @throws NamingException * @throws IOException **/ public void counter(Object filterValue) throws SQLException, NamingException { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; if (b_codrol == null) { b_codrol = " - "; } if (b_codrol == "") { b_codrol = " - "; } if (b_codcat1 == null) { b_codcat1 = " - "; } if (b_codcat1 == "") { b_codcat1 = " - "; } if (b_codcat2 == null) { b_codcat2 = " - "; } if (b_codcat2 == "") { b_codcat2 = " - "; } if (b_codcat3 == null) { b_codcat3 = " - "; } if (b_codcat3 == "") { b_codcat3 = " - "; } String[] veccodrol = b_codrol.split("\\ - ", -1); String[] veccodcat1 = b_codcat1.split("\\ - ", -1); String[] veccodcat2 = b_codcat2.split("\\ - ", -1); String[] veccodcat3 = b_codcat3.split("\\ - ", -1); switch (productName) { case "Oracle": query = "SELECT count_acccat4('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + veccodcat3[0] + "','" + instancia + "') from dual"; break; case "PostgreSQL": query = "SELECT count_acccat4('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + veccodcat3[0] + "','" + instancia + "')"; break; case "Microsoft SQL Server": query = "SELECT DBO.count_acccat4('" + ((String) filterValue).toUpperCase() + "','" + veccodrol[0] + "','" + veccodcat1[0] + "','" + veccodcat2[0] + "','" + veccodcat3[0] + "','" + instancia + "')"; break; } pstmt = con.prepareStatement(query); //System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { rows = r.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } //Cierra las conecciones pstmt.close(); con.close(); r.close(); }
From source file:org.openbizview.util.Acccat4.java
/** * Leer Datos de nominas para asignar a menucheck * @throws NamingException /* w w w . java 2 s . c o m*/ * @throws SQLException * @throws IOException **/ private void selectAcccat4() { try { Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); con = ds.getConnection(); //Reconoce la base de datos de coneccin para ejecutar el query correspondiente a cada uno DatabaseMetaData databaseMetaData = con.getMetaData(); productName = databaseMetaData.getDatabaseProductName();//Identifica la base de datos de coneccin String query = ""; String cat1 = (String) FacesContext.getCurrentInstance().getExternalContext().getSessionMap() .get("cat1"); //Usuario logeado String cat2 = (String) FacesContext.getCurrentInstance().getExternalContext().getSessionMap() .get("cat2"); //Usuario logeado String cat3 = (String) FacesContext.getCurrentInstance().getExternalContext().getSessionMap() .get("cat3"); //Usuario logeado if (cat1 == null) { cat1 = " - "; } if (cat1 == "") { cat1 = " - "; } if (cat2 == null) { cat2 = " - "; } if (cat2 == "") { cat2 = " - "; } if (cat3 == null) { cat3 = " - "; } if (cat3 == "") { cat3 = " - "; } String[] veccat1 = cat1.split("\\ - ", -1); String[] veccat2 = cat2.split("\\ - ", -1); String[] veccat3 = cat3.split("\\ - ", -1); switch (productName) { case "Oracle": query = "Select codcat4, codcat4||' - '||descat4"; query += " from bvtcat4"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and B_CODCAT3 = '" + veccat3[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat4"; break; case "PostgreSQL": query = "Select codcat4, codcat4||' - '||descat4"; query += " from bvtcat4"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and B_CODCAT3 = '" + veccat3[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat4"; break; case "Microsoft SQL Server": query = "Select codcat4, codcat4+' - '+descat4"; query += " from bvtcat4"; query += " where B_CODCAT1 = '" + veccat1[0].toUpperCase() + "'"; query += " and B_CODCAT2 = '" + veccat2[0].toUpperCase() + "'"; query += " and B_CODCAT3 = '" + veccat3[0].toUpperCase() + "'"; query += " and instancia = '" + instancia + "'"; query += " order by codcat4"; break; } ////System.out.println(query); pstmt = con.prepareStatement(query); ////System.out.println(query); r = pstmt.executeQuery(); while (r.next()) { String cat4 = new String(r.getString(1)); String descat4 = new String(r.getString(2)); listAcccat4.put(descat4, cat4); sorted = sortByValues(listAcccat4); } //Cierra las conecciones pstmt.close(); con.close(); } catch (NamingException | SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.amazon.carbonado.repo.jdbc.JDBCRepository.java
/** * @param name name to give repository instance * @param isMaster when true, storables in this repository must manage * version properties and sequence properties * @param dataSource provides JDBC database connections * @param catalog optional catalog to search for tables -- actual meaning * is database independent/*from w w w . j av a 2 s . c o m*/ * @param schema optional schema to search for tables -- actual meaning is * is database independent * @param forceStoredSequence tells the repository to use a stored sequence * even if the database supports native sequences */ @SuppressWarnings("unchecked") JDBCRepository(AtomicReference<Repository> rootRef, String name, boolean isMaster, Iterable<TriggerFactory> triggerFactories, DataSource dataSource, boolean dataSourceClose, String catalog, String schema, Integer fetchSize, Map<String, Boolean> autoVersioningMap, Map<String, Boolean> suppressReloadMap, String sequenceSelectStatement, boolean forceStoredSequence, boolean primaryKeyCheckDisabled, SchemaResolver resolver) throws RepositoryException { super(name); if (dataSource == null) { throw new IllegalArgumentException("DataSource cannot be null"); } mIsMaster = isMaster; mTriggerFactories = triggerFactories; mRootRef = rootRef; mDataSource = dataSource; mDataSourceClose = dataSourceClose; mCatalog = catalog; mSchema = schema; mFetchSize = fetchSize; mPrimaryKeyCheckDisabled = primaryKeyCheckDisabled; mAutoVersioningMap = autoVersioningMap; mSuppressReloadMap = suppressReloadMap; mResolver = resolver; mOpenConnections = new IdentityHashMap<Connection, Object>(); mOpenConnectionsLock = new ReentrantLock(true); // Temporarily set to generic one, in case there's a problem during initialization. mExceptionTransformer = new JDBCExceptionTransformer(); mTxnMgr = new JDBCTransactionManager(this); getLog().info("Opening repository \"" + getName() + '"'); // Test connectivity and get some info on transaction isolation levels. Connection con = getConnection(); try { DatabaseMetaData md = con.getMetaData(); if (md == null || !md.supportsTransactions()) { throw new RepositoryException("Database does not support transactions"); } mDatabaseProductName = md.getDatabaseProductName(); boolean supportsSavepoints; try { supportsSavepoints = md.supportsSavepoints(); } catch (AbstractMethodError e) { supportsSavepoints = false; } if (supportsSavepoints) { con.setAutoCommit(false); // Some JDBC drivers (HSQLDB) lie about their savepoint support. try { con.setSavepoint(); } catch (SQLException e) { mLog.warn("JDBC driver for " + mDatabaseProductName + " reports supporting savepoints, but it " + "doesn't appear to work: " + e); supportsSavepoints = false; } finally { con.rollback(); con.setAutoCommit(true); } } mSupportsSavepoints = supportsSavepoints; mSupportsSelectForUpdate = md.supportsSelectForUpdate(); mSupportsScrollInsensitiveReadOnly = md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); mJdbcDefaultIsolationLevel = md.getDefaultTransactionIsolation(); mDefaultIsolationLevel = mapIsolationLevelFromJdbc(mJdbcDefaultIsolationLevel); mReadUncommittedLevel = selectIsolationLevel(md, IsolationLevel.READ_UNCOMMITTED); mReadCommittedLevel = selectIsolationLevel(md, IsolationLevel.READ_COMMITTED); mRepeatableReadLevel = selectIsolationLevel(md, IsolationLevel.REPEATABLE_READ); mSerializableLevel = selectIsolationLevel(md, IsolationLevel.SERIALIZABLE); } catch (SQLException e) { throw toRepositoryException(e); } finally { try { closeConnection(con); } catch (SQLException e) { // Don't care. } } mSupportStrategy = JDBCSupportStrategy.createStrategy(this); if (forceStoredSequence) { mSupportStrategy.setSequenceSelectStatement(null); } else if (sequenceSelectStatement != null && sequenceSelectStatement.length() > 0) { mSupportStrategy.setSequenceSelectStatement(sequenceSelectStatement); } mSupportStrategy.setForceStoredSequence(forceStoredSequence); mExceptionTransformer = mSupportStrategy.createExceptionTransformer(); getLog().info("Opened repository \"" + getName() + '"'); setAutoShutdownEnabled(true); }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public void logDatabaseAndDriverInfo(ConnectionManager connectionManager) throws SQLException { DatabaseMetaData databaseMetaData = connectionManager.getConnection().getMetaData(); LOG.info("Database Product name: {}", databaseMetaData.getDatabaseProductName()); LOG.info("Database product version: {}", databaseMetaData.getDatabaseProductVersion()); LOG.info("Driver name: {}", databaseMetaData.getDriverName()); LOG.info("Driver version: {}", databaseMetaData.getDriverVersion()); }
From source file:org.executequery.gui.importexport.AbstractImportExportWorker.java
protected boolean isOracle() throws SQLException { if (databaseProductName == null) { DatabaseMetaData metaData = conn.getMetaData(); databaseProductName = metaData.getDatabaseProductName().toUpperCase(); } else {/* ww w . jav a 2s . co m*/ return databaseProductName.contains(ORACLE); } return false; }