List of usage examples for java.sql ResultSet CONCUR_UPDATABLE
int CONCUR_UPDATABLE
To view the source code for java.sql ResultSet CONCUR_UPDATABLE.
Click Source Link
ResultSet object that may be updated. From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@Override public void deleteDocument(final String user, final String password, final String db, final String table, final String name) throws BackendException { Connection conn = null;/* ww w. j a v a 2s . co m*/ Statement st = null; try { conn = this.connectToDB(user, password, db); st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String queryString = String.format("delete from \"%s\".\"%s\" where \"%s\"='%s'", this.getSchemaName(table, db), this.getPlainTableName(table), this.getNameField(conn, table, db), name); this.logString(queryString, "?"); st.executeUpdate(queryString); } catch (SQLException e) { throw new BackendException(e); } catch (ClassNotFoundException e) { throw new BackendException(e); } finally { DbUtils.closeQuietly(st); DbUtils.closeQuietly(conn); } }
From source file:org.acmsl.queryj.tools.handlers.DatabaseMetaDataLoggingHandler.java
/** * Handles given information./* ww w .ja va 2s. com*/ * @param metaData the database metadata. * @return <code>true</code> if the chain should be stopped. * @throws QueryJBuildException if the metadata cannot be logged. */ protected boolean handle(@NotNull final DatabaseMetaData metaData) throws QueryJBuildException { final boolean result = false; @Nullable Log t_Log = null; try { t_Log = UniqueLogFactory.getLog(DatabaseMetaDataLoggingHandler.class); if (t_Log != null) { t_Log.debug("Numeric functions:" + metaData.getNumericFunctions()); t_Log.debug("String functions:" + metaData.getStringFunctions()); t_Log.debug("System functions:" + metaData.getSystemFunctions()); t_Log.debug("Time functions:" + metaData.getTimeDateFunctions()); t_Log.debug("insertsAreDetected(TYPE_FORWARD_ONLY):" + metaData.insertsAreDetected(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("insertsAreDetected(TYPE_SCROLL_INSENSITIVE):" + metaData.insertsAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("insertsAreDetected(TYPE_SCROLL_SENS):" + metaData.insertsAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("isCatalogAtStart():" + metaData.isCatalogAtStart()); t_Log.debug("isReadOnly():" + metaData.isReadOnly()); /* * Fails for MySQL with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.locatorsUpdateCopy() t_Log.debug( "locatorsUpdateCopy():" + metaData.locatorsUpdateCopy()); */ t_Log.debug("nullPlusNonNullIsNull():" + metaData.nullPlusNonNullIsNull()); t_Log.debug("nullsAreSortedAtEnd():" + metaData.nullsAreSortedAtEnd()); t_Log.debug("nullsAreSortedAtStart():" + metaData.nullsAreSortedAtStart()); t_Log.debug("nullsAreSortedHigh():" + metaData.nullsAreSortedHigh()); t_Log.debug("nullsAreSortedLow():" + metaData.nullsAreSortedLow()); t_Log.debug("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("ownInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.ownInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("ownInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.ownInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("ownInsertsAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.ownInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY):" + metaData.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENS):" + metaData.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("storesLowerCaseIdentifiers():" + metaData.storesLowerCaseIdentifiers()); t_Log.debug("storesLowerCaseQuotedIdentifiers():" + metaData.storesLowerCaseQuotedIdentifiers()); t_Log.debug("storesMixedCaseIdentifiers():" + metaData.storesMixedCaseIdentifiers()); t_Log.debug("storesMixedCaseQuotedIdentifiers():" + metaData.storesMixedCaseQuotedIdentifiers()); t_Log.debug("storesUpperCaseIdentifiers():" + metaData.storesUpperCaseIdentifiers()); t_Log.debug("storesUpperCaseQuotedIdentifiers():" + metaData.storesUpperCaseQuotedIdentifiers()); t_Log.debug("supportsAlterTableWithAddColumn():" + metaData.supportsAlterTableWithAddColumn()); t_Log.debug("supportsAlterTableWithDropColumn():" + metaData.supportsAlterTableWithDropColumn()); t_Log.debug("supportsANSI92EntryLevelSQL():" + metaData.supportsANSI92EntryLevelSQL()); t_Log.debug("supportsANSI92FullSQL():" + metaData.supportsANSI92FullSQL()); t_Log.debug("supportsANSI92IntermediateSQL():" + metaData.supportsANSI92IntermediateSQL()); t_Log.debug("supportsBatchUpdates():" + metaData.supportsBatchUpdates()); t_Log.debug( "supportsCatalogsInDataManipulation():" + metaData.supportsCatalogsInDataManipulation()); t_Log.debug( "supportsCatalogsInIndexDefinitions():" + metaData.supportsCatalogsInIndexDefinitions()); t_Log.debug("supportsCatalogsInPrivilegeDefinitions():" + metaData.supportsCatalogsInPrivilegeDefinitions()); t_Log.debug("supportsCatalogsInProcedureCalls():" + metaData.supportsCatalogsInProcedureCalls()); t_Log.debug( "supportsCatalogsInTableDefinitions():" + metaData.supportsCatalogsInTableDefinitions()); t_Log.debug("supportsColumnAliasing():" + metaData.supportsColumnAliasing()); t_Log.debug("supportsConvert():" + metaData.supportsConvert()); t_Log.debug("supportsCoreSQLGrammar():" + metaData.supportsCoreSQLGrammar()); t_Log.debug("supportsCorrelatedSubqueries():" + metaData.supportsCorrelatedSubqueries()); t_Log.debug("supportsDataDefinitionAndDataManipulationTransactions():" + metaData.supportsDataDefinitionAndDataManipulationTransactions()); t_Log.debug("supportsDataManipulationTransactionsOnly():" + metaData.supportsDataManipulationTransactionsOnly()); t_Log.debug("supportsDifferentTableCorrelationNames():" + metaData.supportsDifferentTableCorrelationNames()); t_Log.debug("supportsExpressionsInOrderBy():" + metaData.supportsExpressionsInOrderBy()); t_Log.debug("supportsExtendedSQLGrammar():" + metaData.supportsExtendedSQLGrammar()); t_Log.debug("supportsFullOuterJoins():" + metaData.supportsFullOuterJoins()); String t_strSupportsGetGeneratedKeys = Boolean.FALSE.toString(); try { t_strSupportsGetGeneratedKeys = "" + metaData.supportsGetGeneratedKeys(); } catch (@NotNull final SQLException sqlException) { t_strSupportsGetGeneratedKeys += sqlException.getMessage(); } t_Log.debug("supportsGetGeneratedKeys():" + t_strSupportsGetGeneratedKeys); t_Log.debug("supportsGroupBy():" + metaData.supportsGroupBy()); t_Log.debug("supportsGroupByBeyondSelect():" + metaData.supportsGroupByBeyondSelect()); t_Log.debug("supportsGroupByUnrelated():" + metaData.supportsGroupByUnrelated()); t_Log.debug("supportsIntegrityEnhancementFacility():" + metaData.supportsIntegrityEnhancementFacility()); t_Log.debug("supportsLikeEscapeClause():" + metaData.supportsLikeEscapeClause()); t_Log.debug("supportsLimitedOuterJoins():" + metaData.supportsLimitedOuterJoins()); t_Log.debug("supportsMinimumSQLGrammar():" + metaData.supportsMinimumSQLGrammar()); t_Log.debug("supportsMixedCaseIdentifiers():" + metaData.supportsMixedCaseIdentifiers()); t_Log.debug( "supportsMixedCaseQuotedIdentifiers():" + metaData.supportsMixedCaseQuotedIdentifiers()); /* * Fails in MySQL 3.23.53 with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.supportsMultipleOpenResults() t_Log.debug( "supportsMultipleOpenResults():" + metaData.supportsMultipleOpenResults()); */ t_Log.debug("supportsMultipleResultSets():" + metaData.supportsMultipleResultSets()); t_Log.debug("supportsMultipleTransactions():" + metaData.supportsMultipleTransactions()); /* * Fails in MySQL 3.23.53 with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.supportsNamedParameters() t_Log.debug( "supportsNamedParameters():" + metaData.supportsNamedParameters()); */ t_Log.debug("supportsNonNullableColumns():" + metaData.supportsNonNullableColumns()); t_Log.debug("supportsOpenCursorsAcrossCommit():" + metaData.supportsOpenCursorsAcrossCommit()); t_Log.debug("supportsOpenCursorsAcrossRollback():" + metaData.supportsOpenCursorsAcrossRollback()); t_Log.debug( "supportsOpenStatementsAcrossCommit():" + metaData.supportsOpenStatementsAcrossCommit()); t_Log.debug("supportsOpenStatementsAcrossRollback():" + metaData.supportsOpenStatementsAcrossRollback()); t_Log.debug("supportsOrderByUnrelated():" + metaData.supportsOrderByUnrelated()); t_Log.debug("supportsOuterJoins():" + metaData.supportsOuterJoins()); t_Log.debug("supportsPositionedDelete():" + metaData.supportsPositionedDelete()); t_Log.debug("supportsPositionedUpdate():" + metaData.supportsPositionedUpdate()); t_Log.debug("supportsResultSetConcurrency(TYPE_FORWARD_ONLY,CONCUR_READ_ONLY):" + metaData .supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)); t_Log.debug("supportsResultSetConcurrency(TYPE_FORWARD_ONLY,CONCUR_UPDATABLE):" + metaData .supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)); t_Log.debug("supportsResultSetConcurrency(TYPE_SCROLL_INSENSITIVE,CONCUR_READ_ONLY):" + metaData.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)); t_Log.debug("supportsResultSetConcurrency(TYPE_SCROLL_INSENSITIVE,CONCUR_UPDATABLE):" + metaData.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)); t_Log.debug("supportsResultSetConcurrency(TYPE_SCROLL_SENSITIVE,CONCUR_READ_ONLY):" + metaData .supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)); t_Log.debug("supportsResultSetConcurrency(TYPE_SCROLL_SENSITIVE,CONCUR_UPDATABLE):" + metaData .supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)); /* * Fails in MySQL 3.23.53 with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.supportsResultSetHoldability() t_Log.debug( "supportsResultSetHoldability(" + "HOLD_CURSORS_OVER_COMMIT):" + metaData.supportsResultSetHoldability( ResultSet.HOLD_CURSORS_OVER_COMMIT)); t_Log.debug( "supportsResultSetHoldability(" + "CLOSE_CURSORS_AT_COMMIT):" + metaData.supportsResultSetHoldability( ResultSet.CLOSE_CURSORS_AT_COMMIT)); */ t_Log.debug("supportsResultSetType(TYPE_FORWARD_ONLY):" + metaData.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE):" + metaData.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("supportsResultSetType(TYPE_SCROLL_SENSITIVE):" + metaData.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)); /* * Fails in MySQL 3.23.53 with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.supportsSavePoints() t_Log.debug( "supportsSavepoints():" + metaData.supportsSavepoints()); */ t_Log.debug("supportsSchemasInDataManipulation():" + metaData.supportsSchemasInDataManipulation()); t_Log.debug("supportsSchemasInIndexDefinitions():" + metaData.supportsSchemasInIndexDefinitions()); t_Log.debug("supportsSchemasInPrivilegeDefinitions():" + metaData.supportsSchemasInPrivilegeDefinitions()); t_Log.debug("supportsSchemasInProcedureCalls():" + metaData.supportsSchemasInProcedureCalls()); t_Log.debug("supportsSchemasInTableDefinitions():" + metaData.supportsSchemasInTableDefinitions()); t_Log.debug("supportsSelectForUpdate():" + metaData.supportsSelectForUpdate()); /* * Fails in MySQL 3.23.53 with a java.lang.AbstractMethodError * com.mysql.jdbc.jdbc2.DatabaseMetaData.supportsStatementPooling() t_Log.debug( "supportsStatementPooling():" + metaData.supportsStatementPooling()); */ t_Log.debug("supportsStoredProcedures():" + metaData.supportsStoredProcedures()); t_Log.debug("supportsSubqueriesInComparisons():" + metaData.supportsSubqueriesInComparisons()); t_Log.debug("supportsSubqueriesInExists():" + metaData.supportsSubqueriesInExists()); t_Log.debug("supportsSubqueriesInIns():" + metaData.supportsSubqueriesInIns()); t_Log.debug("supportsSubqueriesInQuantifieds():" + metaData.supportsSubqueriesInQuantifieds()); t_Log.debug("supportsTableCorrelationNames():" + metaData.supportsTableCorrelationNames()); t_Log.debug("supportsTransactionIsolationLevel(TRANSACTION_NONE):" + metaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE)); t_Log.debug("supportsTransactionIsolationLevel(TRANSACTION_READ_COMMITTED):" + metaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)); t_Log.debug("supportsTransactionIsolationLevel(TRANSACTION_READ_UNCOMMITTED):" + metaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)); t_Log.debug("supportsTransactionIsolationLevel(TRANSACTION_REPEATABLE_READ):" + metaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)); t_Log.debug("supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE):" + metaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)); t_Log.debug("supportsTransactions():" + metaData.supportsTransactions()); t_Log.debug("supportsUnion():" + metaData.supportsUnion()); t_Log.debug("supportsUnionAll():" + metaData.supportsUnionAll()); t_Log.debug("updatesAreDetected(TYPE_FORWARD_ONLY):" + metaData.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)); t_Log.debug("updatesAreDetected(TYPE_SCROLL_INSENSITIVE):" + metaData.updatesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)); t_Log.debug("updatesAreDetected(" + "TYPE_SCROLL_SENS):" + metaData.updatesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE)); t_Log.debug("usesLocalFilePerTable():" + metaData.usesLocalFilePerTable()); t_Log.debug("usesLocalFiles():" + metaData.usesLocalFiles()); } } catch (@NotNull final SQLException sqlException) { t_Log.error("Database metadata request failed.", sqlException); } return result; }
From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java
public Map find(String type, String query, Map parameters) throws WGQueryException { if (type == null || type.equals("native")) { type = "sql"; }// w w w. j a va 2 s . co m List nativeOptions = new ArrayList(); String nativeOptionsString = (String) parameters.get(WGDatabase.QUERYOPTION_NATIVEOPTIONS); if (nativeOptionsString != null) { nativeOptions.addAll(WGUtils.deserializeCollection(nativeOptionsString.toLowerCase(), ",", true)); } ResultSet resultSet = null; String table = null; PreparedStatement stmt; Boolean resetAutocommit = null; Connection connection = null; try { // Create statement connection = getConnection(); boolean isUpdate = nativeOptions.contains("update"); if (isUpdate && !connection.getAutoCommit()) { resetAutocommit = connection.getAutoCommit(); connection.setAutoCommit(true); } if (type.equals("sql")) { stmt = connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, (isUpdate ? ResultSet.CONCUR_UPDATABLE : ResultSet.CONCUR_READ_ONLY)); } else if (type.startsWith("table:")) { table = type.substring(6).trim(); if (!_tables.keySet().contains(table.toLowerCase())) { throw new WGQueryException(query, "Table '" + table + "' does not exist or has no primary key"); } if (query != null && !query.trim().equals("")) { query = "SELECT * FROM " + table + " WHERE " + query; } else { query = "SELECT * FROM " + table; } stmt = connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); } else { throw new WGQueryException(query, "Unknown query type: " + type); } // Apply parameters if (parameters.containsKey(WGDatabase.QUERYOPTION_MAXRESULTS)) { stmt.setMaxRows(((Number) parameters.get(WGDatabase.QUERYOPTION_MAXRESULTS)).intValue()); } applyQueryParameters(parameters, stmt, query); // Execute and extract data stmt.execute(); resultSet = stmt.getResultSet(); Map results; if (resultSet != null) { results = extractRowResults(resultSet, table); } else { results = new HashMap(); } return results; } catch (SQLException e) { throw new WGQueryException(query, e.getMessage(), e); } finally { if (connection != null && resetAutocommit != null) { try { connection.setAutoCommit(resetAutocommit); } catch (SQLException e) { throw new WGQueryException(query, "Exception resetting autocommit", e); } } closeResultSet(resultSet); } }
From source file:org.apache.ambari.server.checks.CheckDatabaseHelper.java
protected void checkServiceConfigs() { String GET_SERVICES_WITHOUT_CONFIGS_QUERY = "select c.cluster_name, service_name from clusterservices cs " + "join clusters c on cs.cluster_id=c.cluster_id " + "where service_name not in (select service_name from serviceconfig sc where sc.cluster_id=cs.cluster_id and sc.service_name=cs.service_name and sc.group_id is null)"; String GET_SERVICE_CONFIG_WITHOUT_MAPPING_QUERY = "select c.cluster_name, sc.service_name, sc.version from serviceconfig sc " + "join clusters c on sc.cluster_id=c.cluster_id " + "where service_config_id not in (select service_config_id from serviceconfigmapping) and group_id is null"; String GET_STACK_NAME_VERSION_QUERY = "select c.cluster_name, s.stack_name, s.stack_version from clusters c " + "join stack s on c.desired_stack_id = s.stack_id"; String GET_SERVICES_WITH_CONFIGS_QUERY = "select c.cluster_name, cs.service_name, cc.type_name, sc.version from clusterservices cs " + "join serviceconfig sc on cs.service_name=sc.service_name and cs.cluster_id=sc.cluster_id " + "join serviceconfigmapping scm on sc.service_config_id=scm.service_config_id " + "join clusterconfig cc on scm.config_id=cc.config_id and sc.cluster_id=cc.cluster_id " + "join clusters c on cc.cluster_id=c.cluster_id and sc.stack_id=c.desired_stack_id " + "where sc.group_id is null and sc.service_config_id=(select max(service_config_id) from serviceconfig sc2 where sc2.service_name=sc.service_name and sc2.cluster_id=sc.cluster_id) " + "group by c.cluster_name, cs.service_name, cc.type_name, sc.version"; String GET_NOT_SELECTED_SERVICE_CONFIGS_QUERY = "select c.cluster_name, cs.service_name, cc.type_name from clusterservices cs " + "join serviceconfig sc on cs.service_name=sc.service_name and cs.cluster_id=sc.cluster_id " + "join serviceconfigmapping scm on sc.service_config_id=scm.service_config_id " + "join clusterconfig cc on scm.config_id=cc.config_id and cc.cluster_id=sc.cluster_id " + "join clusterconfigmapping ccm on cc.type_name=ccm.type_name and cc.version_tag=ccm.version_tag and cc.cluster_id=ccm.cluster_id " + "join clusters c on ccm.cluster_id=c.cluster_id " + "where sc.group_id is null and sc.service_config_id = (select max(service_config_id) from serviceconfig sc2 where sc2.service_name=sc.service_name and sc2.cluster_id=sc.cluster_id) " + "group by c.cluster_name, cs.service_name, cc.type_name " + "having sum(ccm.selected) < 1"; Multimap<String, String> clusterServiceMap = HashMultimap.create(); Map<String, Map<String, String>> clusterStackInfo = new HashMap<>(); Map<String, Multimap<String, String>> clusterServiceVersionMap = new HashMap<>(); Map<String, Multimap<String, String>> clusterServiceConfigType = new HashMap<>(); ResultSet rs = null;//ww w. j a v a 2s .co m try { Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = statement.executeQuery(GET_SERVICES_WITHOUT_CONFIGS_QUERY); if (rs != null) { while (rs.next()) { clusterServiceMap.put(rs.getString("cluster_name"), rs.getString("service_name")); } for (String clusterName : clusterServiceMap.keySet()) { LOG.error("Service(s): {}, from cluster {} has no config(s) in serviceconfig table!", StringUtils.join(clusterServiceMap.get(clusterName), ","), clusterName); errorAvailable = true; } } rs = statement.executeQuery(GET_SERVICE_CONFIG_WITHOUT_MAPPING_QUERY); if (rs != null) { String serviceName = null, version = null, clusterName = null; while (rs.next()) { serviceName = rs.getString("service_name"); clusterName = rs.getString("cluster_name"); version = rs.getString("version"); if (clusterServiceVersionMap.get(clusterName) != null) { Multimap<String, String> serviceVersion = clusterServiceVersionMap.get(clusterName); serviceVersion.put(serviceName, version); } else { Multimap<String, String> serviceVersion = HashMultimap.create(); ; serviceVersion.put(serviceName, version); clusterServiceVersionMap.put(clusterName, serviceVersion); } } for (String clName : clusterServiceVersionMap.keySet()) { Multimap<String, String> serviceVersion = clusterServiceVersionMap.get(clName); for (String servName : serviceVersion.keySet()) { LOG.error( "In cluster {}, service config mapping is unavailable (in table serviceconfigmapping) for service {} with version(s) {}! ", clName, servName, StringUtils.join(serviceVersion.get(servName), ",")); errorAvailable = true; } } } //get stack info from db rs = statement.executeQuery(GET_STACK_NAME_VERSION_QUERY); if (rs != null) { while (rs.next()) { Map<String, String> stackInfoMap = new HashMap<>(); stackInfoMap.put(rs.getString("stack_name"), rs.getString("stack_version")); clusterStackInfo.put(rs.getString("cluster_name"), stackInfoMap); } } Set<String> serviceNames = new HashSet<>(); Map<String, Map<Integer, Multimap<String, String>>> dbClusterServiceVersionConfigs = new HashMap<>(); Multimap<String, String> stackServiceConfigs = HashMultimap.create(); rs = statement.executeQuery(GET_SERVICES_WITH_CONFIGS_QUERY); if (rs != null) { String serviceName = null, configType = null, clusterName = null; Integer serviceVersion = null; while (rs.next()) { clusterName = rs.getString("cluster_name"); serviceName = rs.getString("service_name"); configType = rs.getString("type_name"); serviceVersion = rs.getInt("version"); serviceNames.add(serviceName); //collect data about mapped configs to services from db if (dbClusterServiceVersionConfigs.get(clusterName) != null) { Map<Integer, Multimap<String, String>> dbServiceVersionConfigs = dbClusterServiceVersionConfigs .get(clusterName); if (dbServiceVersionConfigs.get(serviceVersion) != null) { dbServiceVersionConfigs.get(serviceVersion).put(serviceName, configType); } else { Multimap<String, String> dbServiceConfigs = HashMultimap.create(); dbServiceConfigs.put(serviceName, configType); dbServiceVersionConfigs.put(serviceVersion, dbServiceConfigs); } } else { Map<Integer, Multimap<String, String>> dbServiceVersionConfigs = new HashMap<>(); Multimap<String, String> dbServiceConfigs = HashMultimap.create(); dbServiceConfigs.put(serviceName, configType); dbServiceVersionConfigs.put(serviceVersion, dbServiceConfigs); dbClusterServiceVersionConfigs.put(clusterName, dbServiceVersionConfigs); } } } //compare service configs from stack with configs that we got from db for (Map.Entry<String, Map<String, String>> clusterStackInfoEntry : clusterStackInfo.entrySet()) { //collect required configs for all services from stack String clusterName = clusterStackInfoEntry.getKey(); Map<String, String> stackInfo = clusterStackInfoEntry.getValue(); String stackName = stackInfo.keySet().iterator().next(); String stackVersion = stackInfo.get(stackName); Map<String, ServiceInfo> serviceInfoMap = ambariMetaInfo.getServices(stackName, stackVersion); for (String serviceName : serviceNames) { ServiceInfo serviceInfo = serviceInfoMap.get(serviceName); Set<String> configTypes = serviceInfo.getConfigTypeAttributes().keySet(); for (String configType : configTypes) { stackServiceConfigs.put(serviceName, configType); } } //compare required service configs from stack with mapped service configs from db Map<Integer, Multimap<String, String>> dbServiceVersionConfigs = dbClusterServiceVersionConfigs .get(clusterName); for (Integer serviceVersion : dbServiceVersionConfigs.keySet()) { Multimap<String, String> dbServiceConfigs = dbServiceVersionConfigs.get(serviceVersion); for (String serviceName : dbServiceConfigs.keySet()) { Collection<String> serviceConfigsFromStack = stackServiceConfigs.get(serviceName); Collection<String> serviceConfigsFromDB = dbServiceConfigs.get(serviceName); if (serviceConfigsFromDB != null && serviceConfigsFromStack != null) { serviceConfigsFromStack.removeAll(serviceConfigsFromDB); if (!serviceConfigsFromStack.isEmpty()) { LOG.error( "Required config(s): {} is(are) not available for service {} with service config version {} in cluster {}", StringUtils.join(serviceConfigsFromStack, ","), serviceName, Integer.toString(serviceVersion), clusterName); errorAvailable = true; } } } } } //getting services which has mapped configs which are not selected in clusterconfigmapping rs = statement.executeQuery(GET_NOT_SELECTED_SERVICE_CONFIGS_QUERY); if (rs != null) { String serviceName = null, configType = null, clusterName = null; while (rs.next()) { clusterName = rs.getString("cluster_name"); serviceName = rs.getString("service_name"); configType = rs.getString("type_name"); if (clusterServiceConfigType.get(clusterName) != null) { Multimap<String, String> serviceConfigs = clusterServiceConfigType.get(clusterName); serviceConfigs.put(serviceName, configType); } else { Multimap<String, String> serviceConfigs = HashMultimap.create(); serviceConfigs.put(serviceName, configType); clusterServiceConfigType.put(clusterName, serviceConfigs); } } } for (String clusterName : clusterServiceConfigType.keySet()) { Multimap<String, String> serviceConfig = clusterServiceConfigType.get(clusterName); for (String serviceName : serviceConfig.keySet()) { LOG.error("You have non selected configs: {} for service {} from cluster {}!", StringUtils.join(serviceConfig.get(serviceName), ","), serviceName, clusterName); errorAvailable = true; } } } catch (SQLException e) { LOG.error("Exception occurred during complex service check procedure: ", e); } catch (AmbariException e) { LOG.error("Exception occurred during complex service check procedure: ", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error("Exception occurred during result set closing procedure: ", e); } } } }
From source file:org.integratedmodelling.sql.SQLServer.java
/** * Submits the given query to the server and saves the result in a new * QueryResult object. To be used knowingly. * /*w w w.j a v a 2 s. c o m*/ * @param sql * @return * @throws ThinklabStorageException */ public QueryResult query(String sql) throws ThinklabStorageException { QueryResult ret = null; Connection conn = null; Statement stmt = null; ResultSet rset = null; try { conn = getConnection(); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rset = stmt.executeQuery(sql); ret = new QueryResult(rset); } catch (SQLException e) { throw new ThinklabStorageException(e); } finally { try { rset.close(); } catch (Exception e) { } try { stmt.close(); } catch (Exception e) { } try { conn.close(); } catch (Exception e) { } } return ret; }
From source file:com.tascape.reactor.report.MySqlBaseBean.java
public void importJson(JSONObject json) throws NamingException, SQLException { JSONObject sr = json.getJSONObject("suite_result"); String srid = sr.getString(SuiteResult.SUITE_RESULT_ID); LOG.debug("srid {}", srid); try (Connection conn = this.getConnection()) { String sql = "SELECT * FROM " + SuiteResult.TABLE_NAME + " WHERE " + SuiteResult.SUITE_RESULT_ID + " = ?;"; PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setString(1, srid);/*from w w w . j a va 2 s .c o m*/ ResultSet rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); if (rs.first()) { LOG.debug("already imported {}", srid); return; } rs.moveToInsertRow(); for (int col = 1; col <= rsmd.getColumnCount(); col++) { String cn = rsmd.getColumnLabel(col); rs.updateObject(cn, sr.opt(cn)); } rs.insertRow(); rs.last(); rs.updateRow(); LOG.debug("sr imported"); } try (Connection conn = this.getConnection()) { String sql = "SELECT * FROM " + SuiteProperty.TABLE_NAME + " WHERE " + SuiteProperty.SUITE_RESULT_ID + " = ?;"; PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setString(1, srid); ResultSet rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); JSONArray sps = sr.getJSONArray("suite_properties"); int len = sps.length(); for (int i = 0; i < len; i++) { rs.moveToInsertRow(); JSONObject tr = sps.getJSONObject(i); for (int col = 1; col <= rsmd.getColumnCount(); col++) { String cn = rsmd.getColumnLabel(col); if (SuiteProperty.SUITE_PROPERTY_ID.equals(cn)) { continue; } rs.updateObject(cn, tr.get(cn)); } rs.insertRow(); rs.last(); rs.updateRow(); } LOG.debug("sps imported"); } JSONArray trs = sr.getJSONArray("case_results"); int len = trs.length(); try (Connection conn = this.getConnection()) { String sql = String.format("SELECT * FROM %s WHERE %s=? AND %s=? AND %s=? AND %s=? AND %s=?;", TaskCase.TABLE_NAME, TaskCase.SUITE_CLASS, TaskCase.CASE_CLASS, TaskCase.CASE_METHOD, TaskCase.CASE_DATA_INFO, TaskCase.CASE_DATA); PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setMaxRows(1); for (int i = 0; i < len; i++) { JSONObject tr = trs.getJSONObject(i); stmt.setString(1, tr.getString(TaskCase.SUITE_CLASS)); stmt.setString(2, tr.getString(TaskCase.CASE_CLASS)); stmt.setString(3, tr.getString(TaskCase.CASE_METHOD)); stmt.setString(4, tr.getString(TaskCase.CASE_DATA_INFO)); stmt.setString(5, tr.getString(TaskCase.CASE_DATA)); ResultSet rs = stmt.executeQuery(); if (!rs.first()) { rs.moveToInsertRow(); rs.updateString(TaskCase.SUITE_CLASS, tr.getString(TaskCase.SUITE_CLASS)); rs.updateString(TaskCase.CASE_CLASS, tr.getString(TaskCase.CASE_CLASS)); rs.updateString(TaskCase.CASE_METHOD, tr.getString(TaskCase.CASE_METHOD)); rs.updateString(TaskCase.CASE_DATA_INFO, tr.getString(TaskCase.CASE_DATA_INFO)); rs.updateString(TaskCase.CASE_DATA, tr.getString(TaskCase.CASE_DATA)); rs.insertRow(); rs.last(); rs.updateRow(); rs = stmt.executeQuery(); rs.first(); } tr.put(TaskCase.TASK_CASE_ID, rs.getLong(TaskCase.TASK_CASE_ID)); } LOG.debug("tcid updated"); } try (Connection conn = this.getConnection()) { String sql = "SELECT * FROM " + CaseResult.TABLE_NAME + " WHERE " + CaseResult.SUITE_RESULT + " = ?;"; PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setString(1, srid); ResultSet rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < len; i++) { rs.moveToInsertRow(); JSONObject tr = trs.getJSONObject(i); for (int col = 1; col <= rsmd.getColumnCount(); col++) { String cn = rsmd.getColumnLabel(col); rs.updateObject(cn, tr.opt(cn)); } rs.insertRow(); rs.last(); rs.updateRow(); } LOG.debug("crs imported"); } try (Connection conn = this.getConnection()) { String sql = "SELECT * FROM " + CaseResultMetric.TABLE_NAME + ";"; PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setMaxRows(1); ResultSet rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < len; i++) { JSONArray jarr = trs.getJSONObject(i).optJSONArray("CASE_result_metrics"); if (jarr == null) { continue; } int l = jarr.length(); for (int j = 0; j < l; j++) { JSONObject trm = jarr.getJSONObject(j); rs.moveToInsertRow(); for (int col = 1; col <= rsmd.getColumnCount(); col++) { String cn = rsmd.getColumnLabel(col); if (cn.equals(CaseResultMetric.CASE_RESULT_METRIC_ID)) { continue; } rs.updateObject(cn, trm.get(cn)); } rs.insertRow(); rs.last(); rs.updateRow(); } } LOG.debug("crms imported"); } }
From source file:org.apache.ambari.server.orm.DBAccessorImpl.java
@Override public void updateTable(String tableName, DBColumnInfo columnNameFrom, DBColumnInfo columnNameTo) throws SQLException { LOG.info("Executing query: UPDATE TABLE " + tableName + " SET " + columnNameTo.getName() + "=" + columnNameFrom.getName()); String statement = "SELECT * FROM " + tableName; int typeFrom = getColumnType(tableName, columnNameFrom.getName()); int typeTo = getColumnType(tableName, columnNameTo.getName()); ResultSet rs = executeSelect(statement, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); while (rs.next()) { convertUpdateData(rs, columnNameFrom, typeFrom, columnNameTo, typeTo); rs.updateRow();/*from w w w. ja va 2 s . c om*/ } rs.close(); }
From source file:org.integratedmodelling.sql.SQLServer.java
/** * Return one string corresponding to field 0 of row 0 of the result after * executing the passed query. Return null if no results are returned or * query generates errors./*w w w .j a v a2 s . c o m*/ * * @param sql * @return * @throws ThinklabStorageException */ public String getResult(String sql) throws ThinklabStorageException { String ret = null; Connection conn = null; Statement stmt = null; ResultSet rset = null; try { conn = getConnection(); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rset = stmt.executeQuery(sql); if (rset.first()) { for (; !rset.isAfterLast(); rset.next()) { ret = rset.getString(1); break; } } } catch (SQLException e) { throw new ThinklabStorageException(e); } finally { try { rset.close(); } catch (Exception e) { } try { stmt.close(); } catch (Exception e) { } try { conn.close(); } catch (Exception e) { } } return ret; }
From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCLogsDAO.java
private List<ResourceActivity> internalGetLogs(boolean paginate, String resourcePath, int action, String userName, Date from, Date to, boolean descending, Connection conn) throws RepositoryException { try {/*from ww w .j a va 2 s . c o m*/ String dbName = conn.getMetaData().getDatabaseProductName(); if (dbName.contains("Microsoft") || dbName.equals("Oracle")) { enableApiPagination = "false"; } } catch (SQLException e) { throw new RepositoryDBException("Failed to get Database product name ", e); } if (conn == null) { log.fatal( "Failed to get Logs. Communications link failure. The connection to the database could not be acquired."); throw new RepositoryDBException( "Failed to get Logs. Communications link failure. The connection to the database could not be acquired."); } PreparedStatement s = null; ResultSet results = null; boolean paginated = false; int start = 0; int count = 0; // String sortOrder =""; // String sortBy =""; // MessageContext messageContext = null; /* // enableApiPagination is the value of system property - enable.registry.api.paginating if (enableApiPagination == null || enableApiPagination.equals("true")) { messageContext = MessageContext.getCurrentMessageContext(); if (messageContext != null && PaginationUtils.isPaginationHeadersExist(messageContext)) { PaginationContext paginationContext = PaginationUtils.initPaginationContext(messageContext); start = paginationContext.getStart(); count = paginationContext.getCount(); if(start == 0){ start =1; } sortBy = paginationContext.getSortBy(); sortOrder = paginationContext.getSortOrder(); paginated = paginate; } }*/ String sql = "SELECT REG_PATH, REG_USER_ID, REG_LOGGED_TIME, REG_ACTION, REG_ACTION_DATA FROM " + "REG_LOG"; boolean queryStarted = false; sql = addWherePart(resourcePath, queryStarted, sql, userName, from, to, action); if (descending) { sql = sql + " ORDER BY REG_LOGGED_TIME DESC"; } try { if (enableApiPagination == null || enableApiPagination.equals("true")) { s = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } else { s = conn.prepareStatement(sql); } int paramNumber = 1; if (resourcePath != null) { s.setString(paramNumber, resourcePath); paramNumber++; } if (userName != null) { s.setString(paramNumber, userName); paramNumber++; } if (from != null) { s.setTimestamp(paramNumber, new Timestamp(from.getTime())); paramNumber++; } if (to != null) { s.setTimestamp(paramNumber, new Timestamp(to.getTime())); paramNumber++; } if (action != -1) { s.setInt(paramNumber, action); paramNumber++; } s.setInt(paramNumber, CurrentContext.getTenantId()); results = s.executeQuery(); List<ResourceActivity> resultList = new ArrayList<ResourceActivity>(); if (paginated) { if (results.relative(start)) { //This is to get cursor to correct position to execute results.next(). results.previous(); int i = 0; while (results.next() && i < count) { i++; resultList.add(getActivity(results)); } } else { log.debug("start index doesn't exist in the result set"); } //move the cursor to the last index if (results.last()) { log.debug("cursor move to the last index of result set"); } else { log.debug("cursor doesn't move to the last index of result set"); } //set row count to the message context. // PaginationUtils.setRowCount(messageContext, Integer.toString(results.getRow())); } else { while (results.next()) { resultList.add(getActivity(results)); } // Activity[] logEntries = getPaginatedLogs(resultList.toArray(new Activity[resultList.size()])); // resultList = Arrays.asList(logEntries); } return resultList; } catch (SQLException e) { String msg = "Failed to get logs. " + e.getMessage(); log.error(msg, e); throw new RepositoryDBException(msg, e); } finally { try { try { if (results != null) { results.close(); } } finally { if (s != null) { s.close(); } } } catch (SQLException ex) { String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:net.ymate.platform.persistence.jdbc.scaffold.JdbcScaffold.java
/** * @param dbName ???/* w ww .ja v a 2s . com*/ * @param dbUserName ?? * @param tableName ?? * @return ????? */ private TableMeta getTableMeta(String dbName, String dbUserName, String tableName) { IConnectionHolder _connHolder = null; Statement _statement = null; ResultSet _resultSet = null; Map<String, ColumnInfo> _tableFields = new LinkedHashMap<String, ColumnInfo>(); List<String> _pkFields = new LinkedList<String>(); TableMeta _meta = new TableMeta(_pkFields, _tableFields); try { _connHolder = JDBC.getConnectionHolder(); String _dbType = JDBC_SCAFFOLD_CONF.getProperty("ymp.scaffold.jbdc.db_type", "unknow"); DatabaseMetaData _dbMetaData = _connHolder.getConnection().getMetaData(); _resultSet = _dbMetaData.getPrimaryKeys(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName); if (_resultSet == null) { _meta = null; System.err.println("Database table \"" + tableName + "\" primaryKey resultSet is null, ignored"); } else { while (_resultSet.next()) { _pkFields.add(_resultSet.getString(4).toLowerCase()); } if (_pkFields.isEmpty()) { _meta = null; System.err .println("Database table \"" + tableName + "\" does not set the primary key, ignored"); } else { _statement = _connHolder.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); _resultSet = _statement .executeQuery("select * from " + _connHolder.getDialect().wapperQuotedIdent(tableName)); ResultSetMetaData _rsMetaData = _resultSet.getMetaData(); // for (int _idx = 1; _idx <= _rsMetaData.getColumnCount(); _idx++) { // ?? ResultSet _column = _dbMetaData.getColumns(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName, _rsMetaData.getColumnName(_idx)); if (_column.next()) { // ???? _tableFields.put(_rsMetaData.getColumnName(_idx).toLowerCase(), new ColumnInfo(_rsMetaData.getColumnName(_idx).toLowerCase(), compressType(_rsMetaData.getColumnClassName(_idx)), _rsMetaData.isAutoIncrement(_idx), _rsMetaData.isNullable(_idx), _column.getString("COLUMN_DEF"))); } } // System.err.println("TABLE_NAME: " + tableName + " ---------------->>"); System.err.println("COLUMN_NAME\tPK\tCOLUMN_TYPE\tIS_AUTOINCREMENT\tIS_NULLABLE\tCOLUMN_DEF"); for (ColumnInfo _cInfo : _tableFields.values()) { System.err .println(_cInfo.getColumnName() + "\t" + _pkFields.contains(_cInfo.getColumnName()) + "\t" + _cInfo.getColumnType() + "\t" + _cInfo.isAutoIncrement() + "\t" + _cInfo.getNullable() + "\t" + _cInfo.getDefaultValue()); } } } } catch (Throwable e) { if (e instanceof Error) { throw (Error) e; } throw new Error(RuntimeUtils.unwrapThrow(e)); } finally { _connHolder.release(); _statement = null; _resultSet = null; } return _meta; }