public AnnotationVO getTFWStringFromDB(String registrationID) {
BaseDAO baseDAO = new BaseDAO();
StringBuffer query = new StringBuffer();
ArrayList list = new ArrayList();
Connection conn = null;
Statement stmt = null;
AnnotationVO vo = null;
try {
conn = baseDAO.getStandAloneConnectionForOracle();
stmt = conn.createStatement();
query
.append(" select CX, RX, RY, CY, IX, IY ")
.append("from MOUSE_SPATIAL_REGISTRY ")
.append("where REGISTRATION_ID = '" + registrationID + "' ");
System.out.println("getImageRegistrationDataForSliceID - Query is - " + query.toString());
ResultSet rs = stmt.executeQuery(query.toString());
while (rs.next()) {
vo = new AnnotationVO();
vo.setTfwLine1(rs.getString("CX"));
vo.setTfwLine2(rs.getString("RX"));
vo.setTfwLine3(rs.getString("RY"));
vo.setTfwLine4(rs.getString("CY"));
vo.setTfwLine5(rs.getString("IX"));
vo.setTfwLine6(rs.getString("IY"));
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return vo;
}
/**
* This method is used to create a new record in the mouseSpatialRegistryTable table
*
* @param Value object with the data
* @return void
*/
public void addAnnotation(ArrayList list, String filePath) throws SQLException, Exception {
// Used for Oracle connection
Connection connection = getStandAloneConnectionForPostgres();
PreparedStatement stmt1 = null;
PreparedStatement stmt2 = null;
Statement stmt3 = null;
PreparedStatement stmt4 = null;
PreparedStatement stmt5 = null;
// Delete from annotation_sdo
StringBuffer deleteQuery1 = new StringBuffer();
deleteQuery1.append(
"delete from annotation_sdo where id in (select id from annotation_dataset where dataset_name = '"
+ filePath
+ "')");
StringBuffer deleteQuery2 = new StringBuffer();
deleteQuery2.append("delete from annotation_dataset where dataset_name = '" + filePath + "'");
StringBuffer annotationIDQuery = new StringBuffer();
annotationIDQuery.append("select nextval('annotation_id_seq') as annotation_id");
String errorMessage = "";
String uniqueAnnotationID = "";
try {
// Delete a record from 1st table
System.out.println("BaseDAO - Delete Query1 is - " + deleteQuery1.toString());
stmt1 = connection.prepareStatement(deleteQuery1.toString());
// stmt1.executeUpdate();
if (stmt1.executeUpdate() != 1) {
errorMessage =
"Unable to delete a record, please contact the administrator to resolve this issue";
}
System.out.println("Query 1 Done");
// Delete a record from 2nd table
System.out.println("BaseDAO - Delete Query2 is - " + deleteQuery2.toString());
stmt2 = connection.prepareStatement(deleteQuery2.toString());
if (stmt2.executeUpdate() != 1) {
errorMessage =
"Unable to delete a record, please contact the administrator to resolve this issue";
}
System.out.println("Query 2 Done");
System.out.println("BaseDAO - AnnotationIDQuery Query3 is - " + annotationIDQuery.toString());
stmt3 = connection.createStatement();
ResultSet rs = stmt3.executeQuery(annotationIDQuery.toString());
while (rs.next()) {
uniqueAnnotationID = rs.getString("annotation_id");
}
System.out.println("Query 3 Done");
StringBuffer insertQuery4 = new StringBuffer();
insertQuery4
.append(" INSERT INTO annotation_dataset (id, dataset_name)")
.append(" VALUES (" + uniqueAnnotationID + ", '" + filePath + "');");
System.out.println("BaseDAO - Insert Query4 is - " + insertQuery4.toString());
stmt4 = connection.prepareStatement(insertQuery4.toString());
if (stmt4.executeUpdate() != 1) {
errorMessage =
"Unable to insert a record, please contact the administrator to resolve this issue";
}
System.out.println("Query 4 Done");
// Start - Loop
Iterator iterator = list.iterator();
AnnotationVO vo = null;
while (iterator.hasNext()) {
vo = (AnnotationVO) iterator.next();
// Insert a record into 2nd table
StringBuffer insertQuery5 = new StringBuffer();
insertQuery5
.append(" INSERT INTO annotation_sdo (id, coordinates, sdo) ")
.append(
" VALUES ("
+ uniqueAnnotationID
+ ",'"
+ vo.getPolygonString()
+ "', ST_GeomFromText('POLYGON(("
+ vo.getPolygonString()
+ "))')); ");
System.out.println("BaseDAO - Insert Query5 is - " + insertQuery5.toString());
stmt5 = connection.prepareStatement(insertQuery5.toString());
if (stmt5.executeUpdate() != 1) {
errorMessage =
"Unable to insert a record, please contact the administrator to resolve this issue";
}
System.out.println("Query 5 Done");
}
// End - Loop
// connection.commit();
} catch (SQLException sqle) {
throw new SQLException("SQLException thrown " + "From BaseDAO(): " + sqle.getMessage());
} catch (Exception e) {
e.printStackTrace();
} finally {
stmt1.close();
stmt2.close();
stmt3.close();
stmt4.close();
stmt5.close();
connection.close();
}
}
