下面列出了java.sql.PreparedStatement#setQueryTimeout ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
public Date getFirstEventTms(Integer triggerId, Date from, Date to, ACS acs) throws SQLException {
ResultSet rs = null;
PreparedStatement ps = null;
Connection c = acs.getDataSource().getConnection();
try {
DynamicStatement ds = new DynamicStatement();
ds.addSqlAndArguments(
"SELECT timestamp_ FROM trigger_event WHERE trigger_id = ? AND timestamp_ >= ? AND timestamp_ < ? ORDER BY timestamp_ ASC LIMIT 1",
triggerId,
from,
to);
ps = ds.makePreparedStatement(c);
ps.setQueryTimeout(60);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getTimestamp("timestamp_");
} else {
return null;
}
} finally {
if (ps != null) {
ps.close();
}
c.close();
}
}
public StatementExecutor(PreparedStatement statement,
boolean doFetch,
int timeout)
{
this.statement = statement;
this.doFetch = doFetch;
this.timeout = timeout;
highestRunTime = 0;
sqlException = null;
if (timeout > 0) {
try {
statement.setQueryTimeout(timeout);
} catch (SQLException e) {
sqlException = e;
}
}
}
public int execute(QueryParameters parameters, SessionImplementor session) throws HibernateException {
coordinateSharedCacheCleanup( session );
PreparedStatement st = null;
RowSelection selection = parameters.getRowSelection();
try {
try {
st = session.getBatcher().prepareStatement( sql );
Iterator paramSpecifications = getWalker().getParameters().iterator();
int pos = 1;
while ( paramSpecifications.hasNext() ) {
final ParameterSpecification paramSpec = ( ParameterSpecification ) paramSpecifications.next();
pos += paramSpec.bind( st, parameters, session, pos );
}
if ( selection != null ) {
if ( selection.getTimeout() != null ) {
st.setQueryTimeout( selection.getTimeout().intValue() );
}
}
return st.executeUpdate();
}
finally {
if ( st != null ) {
session.getBatcher().closeStatement( st );
}
}
}
catch( SQLException sqle ) {
throw JDBCExceptionHelper.convert(
getFactory().getSQLExceptionConverter(),
sqle,
"could not execute update query",
sql
);
}
}
public Map<String, String> queryAliasMapping(String key) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Map<String, String> aliasMapping = new HashMap<>();
try {
conn = DataSourceContainer.getInstance().getConn(key);
ps = conn.prepareStatement("select ds.ds_name, map.alias from t_dbus_datasource ds, t_name_alias_mapping map where ds.id = map.name_id and map.type = 2");
Integer queryTimeout = HeartBeatConfigContainer.getInstance().getHbConf().getQueryTimeout();
if (queryTimeout == null) queryTimeout = 5;
ps.setQueryTimeout(queryTimeout);
rs = ps.executeQuery();
while (rs.next()) {
aliasMapping.put(rs.getString("alias"), rs.getString("ds_name"));
}
} catch (Exception e) {
LoggerFactory.getLogger().error("[db-LoadAliasMapping]", e);
} finally {
DBUtil.close(rs);
DBUtil.close(ps);
DBUtil.close(conn);
}
LoggerFactory.getLogger().info("[db-LoadAliasMapping] key: " + key + ", alias mapping: " + JsonUtil.toJson(aliasMapping));
return aliasMapping;
}
public List<String> getUnitIdsFromSessionUnitParameters() throws SQLException {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs;
boolean wasAutoCommit = false;
try {
connection = dataSource.getConnection();
wasAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
DynamicStatement ds = new DynamicStatement();
ds.addSql("SELECT unit_id FROM unit_param_session");
ps = ds.makePreparedStatement(connection);
ps.setQueryTimeout(60);
rs = ps.executeQuery();
List<String> unitIds = new ArrayList<>();
while (rs.next()) {
unitIds.add(rs.getString("unit_id"));
}
return unitIds;
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.setAutoCommit(wasAutoCommit);
connection.close();
}
}
}
private void addOrChangeUnittypeParameterValues(
UnittypeParameter unittypeParameter, PreparedStatement s) throws SQLException {
String sql;
sql = "DELETE FROM unit_type_param_value WHERE ";
sql += "unit_type_param_id = " + unittypeParameter.getId();
s.setQueryTimeout(60);
int rowsDeleted = s.executeUpdate(sql);
if (rowsDeleted > 0) {
logger.info("Deleted all unittype parameter values for utp:" + unittypeParameter.getName());
}
UnittypeParameterValues values = unittypeParameter.getValues();
if (values.getType().equals(UnittypeParameterValues.REGEXP) && values.getPattern() != null) {
sql = "INSERT INTO unit_type_param_value ";
sql += "(unit_type_param_id, value, priority, type) VALUES (";
sql += unittypeParameter.getId() + ", '";
String pattern = values.getPattern().toString().replaceAll("\\\\", "\\\\\\\\");
sql += pattern + "', 1, '" + UnittypeParameterValues.REGEXP + "')";
s.setQueryTimeout(60);
s.executeUpdate(sql);
} else if (values.getType().equals(UnittypeParameterValues.ENUM)
&& !values.getValues().isEmpty()) {
for (int i = 0; i < values.getValues().size(); i++) {
sql = "INSERT INTO unit_type_param_value ";
sql += "(unit_type_param_id, value, priority, type) VALUES (";
sql += unittypeParameter.getId() + ", '";
sql += values.getValues().get(i) + "', " + i + ", '" + UnittypeParameterValues.ENUM + "')";
s.setQueryTimeout(60);
s.executeUpdate(sql);
}
logger.info("Added all unittype parameter values for utp:" + unittypeParameter.getName());
}
}
@Override
public Set<MonitorNodeVo> queryMonitorNode(String key) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Set<MonitorNodeVo> list = new LinkedHashSet<>();
try {
conn = DataSourceContainer.getInstance().getConn(key);
ps = conn.prepareStatement(getQueryMonitorNodeSql(HeartBeatConfigContainer.getInstance().getHbConf().getExcludeSchema()));
// ps.setString(1, HeartBeatConfigContainer.getInstance().getHbConf().getExcludeSchema());
Set<String> excludeSchema = getExcludeDbSchema(HeartBeatConfigContainer.getInstance().getHbConf().getExcludeSchema());
Integer queryTimeout = HeartBeatConfigContainer.getInstance().getHbConf().getQueryTimeout();
if (queryTimeout == null) queryTimeout = 5;
ps.setQueryTimeout(queryTimeout);
rs = ps.executeQuery();
while (rs.next()) {
MonitorNodeVo vo = new MonitorNodeVo();
vo.setDsName(rs.getString("ds_name"));
vo.setDsPartition(rs.getString("ds_partition"));
vo.setSchema(rs.getString("schema_name"));
vo.setTableName(rs.getString("table_name"));
if (!isContainedByExcludeSchema(excludeSchema, vo.getDsName(), vo.getSchema()))
list.add(vo);
}
} catch (Exception e) {
LoggerFactory.getLogger().error("[db-LoadDbusConfigDao]", e);
} finally {
DBUtil.close(rs);
DBUtil.close(ps);
DBUtil.close(conn);
}
LoggerFactory.getLogger().info("[db-LoadDbusConfigDao] key: " + key + ", schema数量 " + list.size());
return list;
}
/**
* Count number of events for each unitid for a trigger within a specified time frame. This result
* can also return the total number of event for a trigger within a specified time frame.
*
* @param triggerId
* @param from
* @param to
* @param acs
* @return
* @throws SQLException
*/
public Map<String, Integer> countEventsPrUnit(Integer triggerId, Date from, Date to, ACS acs)
throws SQLException {
ResultSet rs;
PreparedStatement ps = null;
Connection c = acs.getDataSource().getConnection();
Map<String, Integer> unitMap = new HashMap<>();
try {
DynamicStatement ds = new DynamicStatement();
ds.addSqlAndArguments(
"SELECT unit_id, COUNT(*) FROM trigger_event WHERE trigger_id = ? AND timestamp_ >= ? AND timestamp_ < ? GROUP BY unit_id",
triggerId,
from,
to);
ps = ds.makePreparedStatement(c);
ps.setQueryTimeout(60);
rs = ps.executeQuery();
int totalCounter = 0;
while (rs.next()) {
String unitId = rs.getString("unit_id");
Integer count = rs.getInt(2);
totalCounter += count;
unitMap.put(unitId, count);
}
unitMap.put("TEC-TotalEventsCounter", totalCounter);
return unitMap;
} finally {
if (ps != null) {
ps.close();
}
c.close();
}
}
/**
* Performs the execute query
*
* @param queryParameters The query parameters
* @param session The session
*
* @return The number of affected rows as returned by the JDBC driver
*
* @throws HibernateException Indicates a problem performing the query execution
*/
public int performExecuteUpdate(
QueryParameters queryParameters,
SharedSessionContractImplementor session) throws HibernateException {
coordinateSharedCacheCleanup( session );
if ( queryParameters.isCallable() ) {
throw new IllegalArgumentException("callable not yet supported for native queries");
}
int result = 0;
PreparedStatement ps;
RowSelection selection = queryParameters.getRowSelection();
try {
queryParameters.processFilters( this.customQuery.getSQL(), session );
final String sql = session.getJdbcServices().getDialect()
.addSqlHintOrComment(
queryParameters.getFilteredSQL(),
queryParameters,
session.getFactory().getSessionFactoryOptions().isCommentsEnabled()
);
ps = session.getJdbcCoordinator().getStatementPreparer().prepareStatement( sql, false );
try {
int col = 1;
for ( ParameterBinder binder : this.customQuery.getParameterValueBinders() ) {
col += binder.bind( ps, queryParameters, session, col );
}
if ( selection != null && selection.getTimeout() != null ) {
ps.setQueryTimeout( selection.getTimeout() );
}
result = session.getJdbcCoordinator().getResultSetReturn().executeUpdate( ps );
}
finally {
if ( ps != null ) {
session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( ps );
session.getJdbcCoordinator().afterStatementExecution();
}
}
}
catch (SQLException sqle) {
throw session.getFactory().getSQLExceptionHelper().convert(
sqle,
"could not execute native bulk manipulation query",
this.sourceQuery
);
}
return result;
}
public static FeatureCollection updateFeatures(String schema, String table, String streamId, FeatureCollection collection,
List<FeatureCollection.ModificationFailure> fails, List<Feature> updates,
Connection connection, boolean handleUUID)
throws SQLException, JsonProcessingException {
final PreparedStatement updateStmt = createUpdateStatement(connection, schema, table, handleUUID);
final PreparedStatement updateWithoutGeometryStmt = createUpdateWithoutGeometryStatement(connection,schema,table,handleUUID);
updateStmt.setQueryTimeout(TIMEOUT);
updateWithoutGeometryStmt.setQueryTimeout(TIMEOUT);
List<String> updateIdList = new ArrayList<>();
List<String> updateWithoutGeometryIdList = new ArrayList<>();
int[] batchUpdateResult = null;
int[] batchUpdateWithoutGeometryResult = null;
for (int i = 0; i < updates.size(); i++) {
final Feature feature = updates.get(i);
final String puuid = feature.getProperties().getXyzNamespace().getPuuid();
if (feature.getId() == null) {
throw new NullPointerException("id");
}
final PGobject jsonbObject= featureToPGobject(feature);
if (feature.getGeometry() == null) {
updateWithoutGeometryStmt.setObject(1, jsonbObject);
updateWithoutGeometryStmt.setString(2, feature.getId());
if(handleUUID)
updateWithoutGeometryStmt.setString(3, puuid);
updateWithoutGeometryStmt.addBatch();
updateWithoutGeometryIdList.add(feature.getId());
} else {
updateStmt.setObject(1, jsonbObject);
final WKBWriter wkbWriter = new WKBWriter(3);
Geometry jtsGeometry = feature.getGeometry().getJTSGeometry();
//Avoid NAN values
assure3d(jtsGeometry.getCoordinates());
updateStmt.setBytes(2, wkbWriter.write(jtsGeometry));
updateStmt.setString(3, feature.getId());
if(handleUUID) {
updateStmt.setString(4, puuid);
}
updateStmt.addBatch();
updateIdList.add(feature.getId());
}
collection.getFeatures().add(feature);
}
if (updateIdList.size() > 0) {
batchUpdateResult = updateStmt.executeBatch();
fillFailList(batchUpdateResult, fails, updateIdList, handleUUID);
}
if (updateWithoutGeometryIdList.size() > 0) {
batchUpdateWithoutGeometryResult = updateWithoutGeometryStmt.executeBatch();
fillFailList(batchUpdateWithoutGeometryResult, fails, updateWithoutGeometryIdList, handleUUID);
}
if(fails.size() > 0)
throw new SQLException(UPDATE_ERROR_GENERAL);
return collection;
}
public PreparedStatement defineStatement(IMetaData storeMeta, IDataSource datasource, Connection connection) throws DataBaseException {
PreparedStatement statement;
int fieldCount = storeMeta.getFieldCount();
if (fieldCount == 0)
return null;
String insertQuery = "insert into " + getTableName();
String values = " values ";
// createQuery used only for HSQL at this time
String createQuery = "create table " + getTableName() + " (";
insertQuery += " (";
values += " (";
String separator = "";
for (int i = 0; i < fieldCount; i++) {
IFieldMetaData fieldMeta = storeMeta.getFieldMeta(i);
String columnName = getSQLColumnName(fieldMeta);
String escapedColumnName = AbstractJDBCDataset.encapsulateColumnName(columnName, datasource);
insertQuery += separator + escapedColumnName;
values += separator + "?";
createQuery += separator + escapedColumnName + getDBFieldType(datasource, fieldMeta);
separator = ",";
}
values += ") ";
createQuery += ") ";
insertQuery += ") ";
String totalQuery = insertQuery + values;
logger.debug("create table statement: " + createQuery);
try {
statement = connection.prepareStatement(totalQuery);
// set query timeout (if necessary)
if (queryTimeout > 0) {
statement.setQueryTimeout(queryTimeout);
}
logger.debug("Prepared statement for persist record as : " + totalQuery);
} catch (Exception e) {
throw new SpagoBIEngineRuntimeException("Error persisting the dataset into table", e);
}
return statement;
}
protected static FeatureCollection updateFeatures( String schema, String table, String streamId, FeatureCollection collection,
List<FeatureCollection.ModificationFailure> fails,
List<Feature> updates, Connection connection,
boolean handleUUID)
throws SQLException {
final PreparedStatement updateStmt = createUpdateStatement(connection, schema, table, handleUUID);
final PreparedStatement updateWithoutGeometryStmt = createUpdateWithoutGeometryStatement(connection,schema,table,handleUUID);
updateStmt.setQueryTimeout(TIMEOUT);
updateWithoutGeometryStmt.setQueryTimeout(TIMEOUT);
for (int i = 0; i < updates.size(); i++) {
String fId = "";
try {
final Feature feature = updates.get(i);
final String puuid = feature.getProperties().getXyzNamespace().getPuuid();
int rows = 0;
if (feature.getId() == null) {
fails.add(new FeatureCollection.ModificationFailure().withId(fId).withMessage(UPDATE_ERROR_ID_MISSING));
continue;
}
fId = feature.getId();
if (handleUUID && puuid == null){
fails.add(new FeatureCollection.ModificationFailure().withId(fId).withMessage(UPDATE_ERROR_PUUID_MISSING));
continue;
}
final PGobject jsonbObject= featureToPGobject(feature);
if (feature.getGeometry() == null) {
updateWithoutGeometryStmt.setObject(1, jsonbObject);
updateWithoutGeometryStmt.setString(2, fId);
if(handleUUID)
updateWithoutGeometryStmt.setString(3, puuid);
rows = updateWithoutGeometryStmt.executeUpdate();
} else {
updateStmt.setObject(1, jsonbObject);
final WKBWriter wkbWriter = new WKBWriter(3);
Geometry jtsGeometry = feature.getGeometry().getJTSGeometry();
//Avoid NAN values
assure3d(jtsGeometry.getCoordinates());
updateStmt.setBytes(2, wkbWriter.write(jtsGeometry));
updateStmt.setString(3, fId);
if(handleUUID) {
updateStmt.setString(4, puuid);
}
rows = updateStmt.executeUpdate();
}
if(rows == 0) {
fails.add(new FeatureCollection.ModificationFailure().withId(fId).withMessage((handleUUID ? UPDATE_ERROR_UUID : UPDATE_ERROR_NOT_EXISTS)));
}else
collection.getFeatures().add(feature);
} catch (Exception e) {
fails.add(new FeatureCollection.ModificationFailure().withId(fId).withMessage(UPDATE_ERROR_GENERAL));
logException(e,streamId,i, LOG_EXCEPTION_UPDATE);
}
}
updateStmt.close();
updateWithoutGeometryStmt.close();
connection.close();
return collection;
}
public void testSimpleSelectQuery() throws Exception {
startVMs(1, 2);
Connection cxn = TestUtil.getConnection();
final Statement stmt = cxn.createStatement();
stmt.execute("create table MyTable(x int, y int) partition by column(x)");
final PreparedStatement pstmt1 = cxn
.prepareStatement("insert into MyTable values " + "(?, ?)");
for (int i = 1; i <= 10; i++) {
pstmt1.setInt(1, i);
pstmt1.setInt(2, i);
pstmt1.execute();
}
final PreparedStatement pstmt2 = cxn
.prepareStatement("select * from MyTable where x > ?");
pstmt2.setInt(1, 0);
CacheSerializableRunnable csr2 = new CacheSerializableRunnable(
"_testSimpleSelectQuery_") {
@Override
public void run2() {
GemFireXDQueryObserver old = GemFireXDQueryObserverHolder
.setInstance(new GemFireXDQueryObserverAdapter() {
@Override
public void onGetNextRowCoreOfBulkTableScan(
com.pivotal.gemfirexd.internal.iapi.sql.ResultSet resultSet) {
try {
getLogWriter().info("onGetNextRowCoreOfBulkTableScan called");
Thread.sleep(2000);
} catch (InterruptedException e) {
}
}
});
}
};
// delay the execution so that the query times out
clientExecute(1, csr2);
serverExecute(1, csr2);
serverExecute(2, csr2);
addExpectedException(new int[] { 1 }, new int[] { 1, 2 },
SQLException.class);
try {
pstmt2.setQueryTimeout(1);
ResultSet rs = pstmt2.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
fail("This test should have thrown exception "
+ "due to query cancellation (exception state XCL52)");
} catch (SQLException se) {
if (!se.getSQLState().equals("XCL52")) {
throw se;
} // else ignore
} finally {
removeExpectedException(new int[] { 1 }, new int[] { 1, 2 },
SQLException.class);
}
}
private void setStatementTimeout(PreparedStatement preparedStatement) throws SQLException {
final int remainingTransactionTimeOutPeriod = jdbcCoordinator.determineRemainingTransactionTimeOutPeriod();
if ( remainingTransactionTimeOutPeriod > 0 ) {
preparedStatement.setQueryTimeout( remainingTransactionTimeOutPeriod );
}
}
public List<TriggerRelease> readTriggerReleases(
Trigger trigger, Date from, Date to, ACS acs, Integer limit) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
Connection c = acs.getDataSource().getConnection();
try {
List<TriggerRelease> thList = new ArrayList<>();
DynamicStatement ds = new DynamicStatement();
ds.addSql(
"SELECT tr.id, tr.trigger_id, tr.no_events, tr.no_events_pr_unit, tr.no_units, tr.first_event_timestamp, tr.release_timestamp, tr.sent_timestamp ");
ds.addSqlAndArguments(
"FROM trigger_release tr, trigger_ t WHERE tr.release_timestamp >= ? AND tr.release_timestamp < ? ",
from,
to);
if (trigger != null) {
ds.addSqlAndArguments("AND tr.trigger_id = ? ", trigger.getId());
}
ds.addSqlAndArguments("AND tr.trigger_id = t.id AND t.unit_type_id = ? ", unittype.getId());
ds.addSql("ORDER BY tr.release_timestamp DESC");
if (limit != null) {
ds.addSql(" LIMIT " + limit);
}
ps = ds.makePreparedStatement(c);
ps.setQueryTimeout(60);
rs = ps.executeQuery();
while (rs.next()) {
Integer id = rs.getInt("id");
Integer triggerId = rs.getInt("trigger_id");
Integer noEvents = rs.getInt("no_events");
Integer noEventsPrUnit = rs.getInt("no_events_pr_unit");
Integer noUnits = rs.getInt("no_units");
Date firstEventTms = rs.getTimestamp("first_event_timestamp");
Date releaseTms = rs.getTimestamp("release_timestamp");
Date sentTms = rs.getTimestamp("sent_timestamp");
TriggerRelease th =
new TriggerRelease(
getById(triggerId),
noEvents,
noEventsPrUnit,
noUnits,
firstEventTms,
releaseTms,
sentTms);
th.setId(id);
thList.add(th);
}
return thList;
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
c.close();
}
}
private void readHeartbeats(Unittypes unittypes) throws SQLException {
DynamicStatement ds = new DynamicStatement();
PreparedStatement ps = null;
ResultSet rs = null;
boolean wasAutoCommit = false;
Connection connection = null;
try {
Map<String, Heartbeat> nameMap = null;
Map<Integer, Heartbeat> idMap = null;
Unittype lastUnittype = null;
ds.addSqlAndArguments("SELECT * FROM heartbeat ORDER BY unit_type_id ASC");
connection = getDataSource().getConnection();
wasAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
ps = ds.makePreparedStatement(connection);
ps.setQueryTimeout(60);
rs = ps.executeQuery();
int counter = 0;
while (rs.next()) {
counter++;
Heartbeat hb = new Heartbeat();
Unittype unittype = unittypes.getById(rs.getInt("unit_type_id"));
hb.validateInput(false);
hb.setUnittype(unittype);
hb.setId(rs.getInt("id"));
hb.setName(rs.getString("name"));
hb.setGroup(unittype.getGroups().getById(rs.getInt("heartbeat_group_id")));
hb.setExpression(rs.getString("heartbeat_expression"));
hb.setTimeoutHours(rs.getInt("heartbeat_timeout_hour"));
hb.validateInput(true);
if (lastUnittype == null || lastUnittype != unittype) {
nameMap = new MapWrapper<Heartbeat>(isStrictOrder()).getMap();
idMap = new HashMap<>();
unittype.setHeartbeats(new Heartbeats(idMap, nameMap, unittype));
lastUnittype = unittype;
}
idMap.put(hb.getId(), hb);
nameMap.put(hb.getName(), hb);
}
if (logger.isDebugEnabled()) {
logger.debug("Read " + counter + " heartbeats");
}
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.setAutoCommit(wasAutoCommit);
connection.close();
}
}
}
protected Unit addSessionParameters(Unit unit) throws SQLException {
DynamicStatement ds = new DynamicStatement();
ds.addSqlAndArguments("SELECT * FROM unit_param_session WHERE unit_id = ?", unit.getId());
ResultSet rs = null;
PreparedStatement pp = null;
Unittype ut = unit.getUnittype();
try {
pp = ds.makePreparedStatement(connection);
pp.setQueryTimeout(60);
rs = pp.executeQuery();
if (logger.isDebugEnabled()) {
logger.debug(ds.getDebugMessage());
}
while (rs.next()) {
String unittypeParameterIdStr = rs.getString("unit_type_param_id");
String value = rs.getString("value");
if (value == null) {
value = "";
}
if (unittypeParameterIdStr != null) {
Integer unittypeParameterId = Integer.parseInt(unittypeParameterIdStr);
UnittypeParameter unittypeParameter =
ut.getUnittypeParameters().getById(unittypeParameterId);
UnitParameter sp =
new UnitParameter(unittypeParameter, unit.getId(), value, unit.getProfile());
unit.getSessionParameters().put(unittypeParameter.getName(), sp);
}
}
return unit;
} catch (SQLException sqle) {
logger.error("The sql that failed:" + ds.getSqlQuestionMarksSubstituted());
throw sqle;
} finally {
if (rs != null) {
rs.close();
}
if (pp != null) {
pp.close();
}
}
}
private void addOrChangeTriggerImpl(Trigger trigger, ACS acs) throws SQLException {
PreparedStatement ps = null;
Connection c = acs.getDataSource().getConnection();
try {
InsertOrUpdateStatement ious =
new InsertOrUpdateStatement("trigger_", new Field("id", trigger.getId()));
ious.addField(new Field("name", trigger.getName()));
ious.addField(new Field("description", trigger.getDescription()));
ious.addField(new Field("trigger_type", trigger.getTriggerType()));
ious.addField(new Field("notify_type", trigger.getNotifyType()));
ious.addField(new Field("active", trigger.isActive() ? 1 : 0));
ious.addField(new Field("unit_type_id", trigger.getUnittype().getId()));
// if (trigger.getGroup() != null)
// ious.addField(new Field("group_id", trigger.getGroup().getId()));
ious.addField(new Field("eval_period_minutes", trigger.getEvalPeriodMinutes()));
ious.addField(new Field("notify_interval_hours", trigger.getNotifyIntervalHours()));
if (trigger.getScript() != null) {
ious.addField(new Field("filestore_id", trigger.getScript().getId()));
} else {
ious.addField(new Field("filestore_id", (Integer) null));
}
if (trigger.getParent() != null) {
ious.addField(new Field("parent_trigger_id", trigger.getParent().getId()));
} else {
ious.addField(new Field("parent_trigger_id", (Integer) null));
}
ious.addField(new Field("to_list", trigger.getToList()));
if (trigger.getSyslogEvent() != null) {
ious.addField(new Field("syslog_event_id", trigger.getSyslogEvent().getId()));
} else {
ious.addField(new Field("syslog_event_id", (Integer) null));
}
ious.addField(new Field("no_events", trigger.getNoEvents()));
ious.addField(new Field("no_events_pr_unit", trigger.getNoEventsPrUnit()));
ious.addField(new Field("no_units", trigger.getNoUnits()));
ps = ious.makePreparedStatement(c);
ps.setQueryTimeout(60);
ps.executeUpdate();
if (ious.isInsert()) {
ResultSet gk = ps.getGeneratedKeys();
if (gk.next()) {
trigger.setId(gk.getInt(1));
}
logger.info("Inserted trigger " + trigger.getName());
if (acs.getDbi() != null) {
acs.getDbi().publishAdd(trigger, trigger.getUnittype());
}
} else {
if (trigger.isSyslogEventChanged()) { // delete all trigger_events for this trigger
deleteEvents(trigger.getId(), new Date(), acs);
trigger.setSyslogEventChangeCompleted();
}
logger.info("Updated trigger " + trigger.getName());
if (acs.getDbi() != null) {
acs.getDbi().publishChange(trigger, trigger.getUnittype());
}
}
} finally {
if (ps != null) {
ps.close();
}
c.close();
}
}
@Test
public void testPopulateData() throws Exception {
int count = 100000;
int actual = testCheckData();
if (actual>=count) {
System.out.println("Test tables has "+actual+" rows of data. No need to populate.");
return;
}
datasource.setJdbcInterceptors(ResetAbandonedTimer.class.getName());
String insert = "insert into test values (?,?,?,?,?)";
this.datasource.setRemoveAbandoned(false);
Connection con = datasource.getConnection();
boolean commit = con.getAutoCommit();
con.setAutoCommit(false);
if (recreate) {
Statement st = con.createStatement();
try {
st.execute("drop table test");
}catch (Exception ignore) {}
st.execute("create table test(id int not null, val1 varchar(255), val2 varchar(255), val3 varchar(255), val4 varchar(255))");
st.close();
}
PreparedStatement ps = con.prepareStatement(insert);
ps.setQueryTimeout(0);
for (int i=actual; i<count; i++) {
ps.setInt(1,i);
String s = getRandom();
ps.setString(2, s);
ps.setString(3, s);
ps.setString(4, s);
ps.setString(5, s);
ps.addBatch();
ps.clearParameters();
if ((i+1) % 1000 == 0) {
System.out.print(".");
}
if ((i+1) % 10000 == 0) {
System.out.print("\n"+(i+1));
ps.executeBatch();
ps.close();
con.commit();
ps = con.prepareStatement(insert);
ps.setQueryTimeout(0);
}
}
ps.close();
con.setAutoCommit(commit);
con.close();
}
/** Deletes the unit and all the unitparameters in that unit. */
public int deleteUnit(Unit unit) throws SQLException {
Connection connection = null;
PreparedStatement ps = null;
boolean wasAutoCommit = false;
try {
connection = dataSource.getConnection();
wasAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
DynamicStatement ds = new DynamicStatement();
ds.addSqlAndArguments("DELETE FROM unit_param WHERE unit_id = ?", unit.getId());
ps = ds.makePreparedStatement(connection);
ps.setQueryTimeout(60);
int paramsDeleted = ps.executeUpdate();
ps.close();
ds = new DynamicStatement();
ds.addSqlAndArguments("DELETE FROM unit_job WHERE unit_id = ?", unit.getId());
ps = ds.makePreparedStatement(connection);
ps.setQueryTimeout(60);
int unitJobsDeleted = ps.executeUpdate();
ps.close();
ds = new DynamicStatement();
ds.addSqlAndArguments("DELETE FROM unit WHERE unit_id = ?", unit.getId());
ps = ds.makePreparedStatement(connection);
ps.setQueryTimeout(60);
int rowsDeleted = ps.executeUpdate();
ps.close();
connection.commit();
if (paramsDeleted > 0) {
logger.info("Deleted " + paramsDeleted + " unit parameters for unit " + unit.getId());
}
if (unitJobsDeleted > 0) {
logger.info("Deleted " + unitJobsDeleted + " unit jobs for unit " + unit.getId());
}
if (rowsDeleted == 0) {
logger.warn("No unit deleted, possibly because it did not exist.");
} else {
SyslogClient.info(unit.getId(), "Deleted unit", syslog);
logger.info("Deleted unit " + unit.getId());
}
return rowsDeleted;
} catch (SQLException sqle) {
if (connection != null) {
connection.rollback();
}
throw sqle;
} finally {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.setAutoCommit(wasAutoCommit);
connection.close();
}
}
}