public int updateProfileById(Integer profileId, String profileXML) {
if (profileId == null) {
throw new IllegalArgumentException("profileId must not be 'null'");
}
// if (profileXML == null) {
// throw new IllegalArgumentException("domain must not be an empty String!");
// }
//
// StringBuilder sqlString = new StringBuilder("UPDATE ");
// sqlString.append(DEFAULT_TABLE_NAME);
// sqlString.append(" SET ");
// sqlString.append(DEFAULT_PROFILE_DATA_COLUMN_NAME);
// sqlString.append(" =? WHERE ");
// sqlString.append(DEFAULT_PROFILE_ID_COLUMN_NAME);
// sqlString.append(" =?");
Object[] args = {profileXML, profileId};
int[] argTypes = {Types.BLOB, Types.INTEGER};
PreparedStatementCreatorFactory factory =
new PreparedStatementCreatorFactory(UPDATE_PROFILE_BY_ID_QUERY, argTypes);
int rowsAffected = getJdbcTemplate().update(factory.newPreparedStatementCreator(args));
return rowsAffected;
}
public List<Code> searchCodesByDevice(String deviceId, Date start, Date end) {
// @formatter:on
String query =
"SELECT\n"
+ "DISTINCT "
+ "code, "
+ "code_system "
+ "FROM "
+ "t5_observation "
+ "JOIN "
+ "t5_device "
+ "ON "
+ "t5_observation.id = t5_device.observation_id "
+ "WHERE "
+ "start_time BETWEEN ? AND ? "
+ "AND device_id = ? ";
// @formatter:off
long queryStartMilli = System.currentTimeMillis();
PreparedStatementCreatorFactory pscf =
new PreparedStatementCreatorFactory(query, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR);
PreparedStatementCreator psc =
pscf.newPreparedStatementCreator(Arrays.asList(start, end, deviceId));
List<Code> result = jdbcTemplate.query(psc, new SummaryRowMapper());
logger.debug(
"Fetching summary from SQL DB took " + (System.currentTimeMillis() - queryStartMilli));
return result;
}
public int storeProfile(Integer tenantId, Integer itemId, Integer itemTypeId, String profileXML) {
if (tenantId == null) {
throw new IllegalArgumentException("tenantId must not be 'null'!");
}
if (itemId == null) {
throw new IllegalArgumentException("itemId must not be 'null'!");
}
if (itemTypeId == null) {
throw new IllegalArgumentException("itemTypeId must not be 'null'");
}
// if (profileXML == null) {
// throw new IllegalArgumentException("domain must not be an empty String!");
// }
//
// StringBuilder sqlString = new StringBuilder("INSERT INTO ");
// sqlString.append(DEFAULT_TABLE_NAME);
// sqlString.append(" SET ");
// sqlString.append(DEFAULT_TENANT_ID_COLUMN_NAME);
// sqlString.append(" =?, ");
// sqlString.append(DEFAULT_ITEM_ID_COLUMN_NAME);
// sqlString.append(" =?, ");
// sqlString.append(DEFAULT_ITEM_TYPE_ID_COLUMN_NAME);
// sqlString.append(" =?, ");
// sqlString.append(DEFAULT_PROFILE_DATA_COLUMN_NAME);
// sqlString.append(" =? ON DUPLICATE KEY UPDATE ");
// sqlString.append(DEFAULT_PROFILE_DATA_COLUMN_NAME);
// sqlString.append(" =?");
Object[] args = {tenantId, itemId, itemTypeId, profileXML, profileXML};
int[] argTypes = {Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.BLOB, Types.BLOB};
PreparedStatementCreatorFactory factory =
new PreparedStatementCreatorFactory(STORE_PROFILE_QUERY, argTypes);
int rowsAffected = getJdbcTemplate().update(factory.newPreparedStatementCreator(args));
return rowsAffected;
}
public static PreparedStatementCreator newPreparedStatementCreator(
String sql, int[] types, Object[] params) {
PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(sql, types);
factory.setReturnGeneratedKeys(true);
return factory.newPreparedStatementCreator(params);
}
public List<SqlObservation> searchByDevice(
String deviceId, String observationTypeCode, Date start, Date end) {
List<SqlObservation> result = new ArrayList<>();
// @formatter:off
String query =
"SELECT "
+ "t5_observation.id, "
+ "message_id, "
+ "uid, "
+ "set_id, "
+ "start_time, "
+ "end_time, "
+ "value, "
+ "value_type, "
+ "code, "
+ "code_system, "
+ "unit, "
+ "unit_system, "
+ "sample_rate, "
+ "data_range, "
+ "array_agg(device_id) AS device_id_list, "
+ "array_agg(level) AS device_level_list "
+ "FROM "
+ "t5_observation "
+ "JOIN "
+ "t5_device "
+ "ON "
+ "t5_observation.id = t5_device.observation_id "
+ "WHERE "
+ "code = ? "
+ "AND "
+ "start_time BETWEEN ? AND ? "
+ "AND "
+ "device_id = ? "
+ "GROUP BY "
+ "t5_observation.id, "
+ "message_id, "
+ "uid, "
+ "set_id, "
+ "start_time, "
+ "end_time, "
+ "value, "
+ "value_type, "
+ "code, "
+ "code_system, "
+ "unit, "
+ "unit_system, "
+ "sample_rate, "
+ "data_range "
+ "ORDER BY start_time ";
// @formatter:on
long queryStartMilli = System.currentTimeMillis();
PreparedStatementCreatorFactory pscf =
new PreparedStatementCreatorFactory(
query, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR);
PreparedStatementCreator psc =
pscf.newPreparedStatementCreator(Arrays.asList(observationTypeCode, start, end, deviceId));
result = jdbcTemplate.query(psc, new ObservationRowMapper());
logger.debug("Fetching from SQL DB took " + (System.currentTimeMillis() - queryStartMilli));
return result;
}
