public List<Instance> getInstances() {
List<Instance> instances = new ArrayList<Instance>();
Connection connection = null;
try {
connection = DbManager.getConnection();
String query =
"SELECT I.id AS id, I.message_id AS message_id, AI.codes AS codes "
+ "FROM AnnotationInstance AI "
+ "JOIN Instance I ON AI.instance_id = I.id "
+ "WHERE AI.pid=? ORDER BY AI.`index` ASC";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, getPid());
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Instance instance = new Instance(InstanceType.GOLD);
instance.setId(resultSet.getInt("id"));
instance.setMessageId(resultSet.getString("message_id"));
instance.setCodes(toInstanceCodes(resultSet.getString("codes")));
instances.add(instance);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != connection) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return instances;
}
public Instance getDbInstance(int index) {
Instance instance = new Instance(InstanceType.GOLD);
Connection connection = null;
try {
connection = DbManager.getConnection();
String query =
"SELECT I.id AS id, I.text AS text, AI.codes AS codes, AI.time_spent AS time_spent "
+ "FROM AnnotationInstance AI "
+ "JOIN Instance I ON AI.instance_id = I.id "
+ "WHERE AI.pid=? AND AI.`index`=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, getPid());
statement.setInt(2, index);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
instance.setId(resultSet.getInt("id"));
instance.setText(resultSet.getString("text"));
instance.setCodes(toInstanceCodes(resultSet.getString("codes")));
instance.setTimeSpent(resultSet.getInt("time_spent"));
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != connection) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return instance;
}