/** * Populate the passed query with bindCount number of placeholders '?'. The method will not put necessary parenteses * around the placeholders. * * @param query the original query including text to replace * @param replaceable the part of the original query that should be replaced by '?'s. * @param bindCount how many placeholders '?' should be generated * * @return The modified query */ public static String transformQueryForMultipleInParameters(String query, String replaceable, int bindCount) { String replacement = generateInBinds(bindCount); return query.replace(replaceable, replacement); }
public ResourceAvailability getLatestAvailability(int resourceId) { Query query = entityManager.createNamedQuery(ResourceAvailability.QUERY_FIND_BY_RESOURCE_ID); query.setParameter("resourceId", resourceId); try { ResourceAvailability result = (ResourceAvailability) query.getSingleResult(); return result; } catch (NoResultException nre) { return null; } catch (RuntimeException re) { Throwable cause = re.getCause(); if (cause instanceof SQLException) { log.error("Failed to get latest avail for Resource [" + resourceId + "]: " + JDBCUtil.convertSQLExceptionToString((SQLException) cause)); } throw re; } }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for all servers * @throws SQLException */ public int setVersionForAllServers(Connection connection, String version) throws SQLException { PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement("UPDATE RHQ_SERVER SET VERSION = ?"); preparedStatement.setString(1, version); return preparedStatement.executeUpdate(); } finally { JDBCUtil.safeClose(null, preparedStatement, null); } } }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for all nodes * @throws SQLException */ public int setVersionForAllNodes(Connection connection, String version) throws SQLException { PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement("UPDATE RHQ_STORAGE_NODE SET VERSION = ?"); preparedStatement.setString(1, version); return preparedStatement.executeUpdate(); } finally { JDBCUtil.safeClose(null, preparedStatement, null); } } }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for server with the specified <code>name</code> * @param name server name * @throws java.sql.SQLException */ public int setVersionForServerWithName(Connection connection, String version, String name) throws SQLException { PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement("UPDATE RHQ_SERVER SET VERSION = ? WHERE NAME = ?"); preparedStatement.setString(1, version); preparedStatement.setString(2, name); return preparedStatement.executeUpdate(); } finally { JDBCUtil.safeClose(null, preparedStatement, null); } }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for node with the specified <code>address</code> * @param address storage node address * @throws SQLException */ public int setVersionForNodeWithAddress(Connection connection, String version, String address) throws SQLException { PreparedStatement preparedStatement = null; try { preparedStatement = connection .prepareStatement("UPDATE RHQ_STORAGE_NODE SET VERSION = ? WHERE ADDRESS = ?"); preparedStatement.setString(1, version); preparedStatement.setString(2, address); return preparedStatement.executeUpdate(); } finally { JDBCUtil.safeClose(null, preparedStatement, null); } }
@PostConstruct public void init() { Connection conn = null; try { conn = rhqDs.getConnection(); databaseType = DatabaseTypeFactory.getDatabaseType(conn); } catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtil.safeClose(conn); } }
/** * Get the most recent measurement. */ private long getMaxTimestamp(String dataTable) throws SQLException { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); String sql = "SELECT MAX(time_stamp) FROM " + dataTable; // returns null rows if nothing exists stmt = conn.createStatement(); rs = stmt.executeQuery(sql); // We'll have a single result if (rs.next()) { return rs.getLong(1); } else { // New installation return 0L; } } finally { JDBCUtil.safeClose(conn, stmt, rs); } }
/** * Generate the correct SQL statement to obtain the next value from a sequence/table * generator for the passed table. The passed connection gets closed in case of an error. * @param conn A valid database connection * @param tableName The name of the table to use * @return A statement that obtains the next value for the passed table */ public static String getNextValSql(Connection conn, String tableName) { String nextvalSql; try { DatabaseMetaData meta = conn.getMetaData(); String name = meta.getDatabaseProductName().toLowerCase(); if (name.contains("postgres")) { nextvalSql = POSTGRES_NEXTVAL_SQL; } else if (name.contains("oracle")) { nextvalSql = ORACLE_NEXTVAL_SQL; } else if (name.contains("h2")) { nextvalSql = H2_NEXTVAL_SQL; } else { JDBCUtil.safeClose(conn); throw new IllegalStateException("Unsupported database type: " + name); } } catch (Exception e) { JDBCUtil.safeClose(conn); throw new IllegalStateException("Failed to determine database type."); } nextvalSql = String.format(nextvalSql, tableName); return nextvalSql; }
private void clearImplicitResources(int resourceGroupId) throws ResourceGroupUpdateException { Connection conn = null; PreparedStatement removeImplicitStatement = null; try { conn = rhqDs.getConnection(); removeImplicitStatement = conn.prepareStatement(ResourceGroup.QUERY_UPDATE_REMOVE_IMPLICIT); removeImplicitStatement.setInt(1, resourceGroupId); removeImplicitStatement.executeUpdate(); } catch (SQLException sqle) { log.error("Error removing implicit resources from group[id=" + resourceGroupId + "]: ", sqle); throw new ResourceGroupUpdateException("Error removing implicit resources from group[id=" + resourceGroupId + "]: " + sqle.getMessage()); } finally { JDBCUtil.safeClose(removeImplicitStatement); JDBCUtil.safeClose(conn); } }
/** * Get the oldest timestamp in the database. Getting the minimum time is expensive, so this is only called once when * the compression routine runs for the first time. After the first call, the range is cached. */ private long getMinTimestamp(String dataTable) throws SQLException { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); String sql = "SELECT MIN(time_stamp) FROM " + dataTable; // returns null rows if nothing exists stmt = conn.createStatement(); rs = stmt.executeQuery(sql); // We'll have a single result if (rs.next()) { return rs.getLong(1); } else { throw new SQLException("Unable to determine oldest measurement"); } } finally { JDBCUtil.safeClose(conn, stmt, rs); } }
private void makeImplicitMirrorExplicit(int resourceGroupId) throws ResourceGroupUpdateException { Connection conn = null; PreparedStatement updateImplicitMirrorExplicitStatement = null; try { conn = rhqDs.getConnection(); updateImplicitMirrorExplicitStatement = conn .prepareStatement(ResourceGroup.QUERY_UPDATE_IMPLICIT_MIRROR_EXPLICIT); updateImplicitMirrorExplicitStatement.setInt(1, resourceGroupId); updateImplicitMirrorExplicitStatement.executeUpdate(); } catch (SQLException sqle) { log.error("Error making implicit resources mirror explicit resources for group[id=" + resourceGroupId + "]: ", sqle); throw new ResourceGroupUpdateException( "Error making implicit resources mirror explicit resources for group[id=" + resourceGroupId + "]: " + sqle.getMessage()); } finally { JDBCUtil.safeClose(updateImplicitMirrorExplicitStatement); JDBCUtil.safeClose(conn); } }
public MeasurementAggregate getAggregateByScheduleId(long beginTime, long endTime, long scheduleId) throws MeasurementNotFoundException { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = datasource.getConnection(); String condition = " AND d.schedule_id = ? \n"; ps = getFullQuery("getAggregateByScheduleId", connection, beginTime, endTime, 1, condition, scheduleId); rs = ps.executeQuery(); if (rs.next()) { MeasurementAggregate measurementAggregate = fillAggregateFromResultSet(rs); return measurementAggregate; } throw new MeasurementNotFoundException("Data not found"); } catch (SQLException e) { throw new MeasurementNotFoundException(e); } finally { JDBCUtil.safeClose(connection, ps, rs); } }
private void dropForeignKey(String foreignKey) { Statement statement = null; try { String sql = "ALTER TABLE " + table + " DROP CONSTRAINT " + foreignKey; log(sql, MSG_INFO); statement = getConnection().createStatement(); statement.executeUpdate(sql); } catch (SQLException e) { if (!isIgnoreError()) { log(ThrowableUtil.getAllSqlExceptionMessages(e), MSG_WARN); } else { throw new BuildException(e); } } finally { safeClose(statement); } }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for all rows * @return true if the column was not present and upgraded * @throws Exception */ public boolean upgrade(Connection connection, String version) throws Exception { PreparedStatement preparedStatement = null; try { DatabaseType db = DatabaseTypeFactory.getDatabaseType(connection); if (!db.checkColumnExists(connection, "RHQ_SERVER", "VERSION")) { db.addColumn(connection, "RHQ_SERVER", "VERSION", "VARCHAR2", "255"); preparedStatement = connection.prepareStatement("UPDATE RHQ_SERVER SET VERSION = ?"); preparedStatement.setString(1, "PRE-" + version); preparedStatement.executeUpdate(); db.closeStatement(preparedStatement); // set column not null after it's been set db.alterColumn(connection, "RHQ_SERVER", "VERSION", "VARCHAR2", null, "255", false, false); return true; } } finally { JDBCUtil.safeClose(null, preparedStatement, null); } return false; }
/** * @param connection a connection to the DB (won't be closed by this method) * @param version the version to set for all rows * @return true if the column was not present and upgraded * @throws Exception */ public boolean upgrade(Connection connection, String version) throws Exception { PreparedStatement preparedStatement = null; try { DatabaseType db = DatabaseTypeFactory.getDatabaseType(connection); boolean columnExists = db.checkColumnExists(connection, "RHQ_STORAGE_NODE", "VERSION"); if (!columnExists) { db.addColumn(connection, "RHQ_STORAGE_NODE", "VERSION", "VARCHAR2", "255"); preparedStatement = connection.prepareStatement("UPDATE RHQ_STORAGE_NODE SET VERSION = ?"); preparedStatement.setString(1, "PRE-" + version); preparedStatement.executeUpdate(); db.closeStatement(preparedStatement); // set column not null after it's been set db.alterColumn(connection, "RHQ_STORAGE_NODE", "VERSION", "VARCHAR2", null, "255", false, false); return true; } } finally { JDBCUtil.safeClose(null, preparedStatement, null); } return false; }
private int deleteRows(List<KEY> selectedKeys) throws Exception { Connection connection = null; PreparedStatement preparedStatement = null; try { userTransaction.begin(); String deleteRowByKeyQuery = getDeleteRowByKeyQuery(databaseType); connection = dataSource.getConnection(); preparedStatement = connection.prepareStatement(deleteRowByKeyQuery); for (KEY key : selectedKeys) { setDeleteRowByKeyQueryParams(preparedStatement, key); preparedStatement.addBatch(); } int[] batchResults = preparedStatement.executeBatch(); userTransaction.commit(); return evalDeletedRows(batchResults); } finally { JDBCUtil.safeClose(connection, preparedStatement, null); rollbackIfTransactionActive(); } }
public MeasurementAggregate getAggregateByDefinitionAndContext(long beginTime, long endTime, int definitionId, EntityContext context) throws MeasurementNotFoundException { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { String conditions = getQueryConditionsByDefinitionAndContext(definitionId, context); Object[] bindParams = getBindParamsByDefinitionAndContext(definitionId, context); connection = datasource.getConnection(); ps = getFullQuery("getAggregateByContext", connection, beginTime, endTime, 1, conditions, bindParams); rs = ps.executeQuery(); if (rs.next()) { MeasurementAggregate measurementAggregate = fillAggregateFromResultSet(rs); return measurementAggregate; } throw new MeasurementNotFoundException("Data not found"); } catch (SQLException e) { throw new MeasurementNotFoundException(e); } finally { JDBCUtil.safeClose(connection, ps, rs); } }