Java Code Examples for java.sql.PreparedStatement#setQueryTimeout()

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();
    }
  }
}