/**
* Creates a SQL Insert Statement stub with placeholders for inserting values in a
* PreparedStatement and placeholders foreign key columns and values.
*
* @param extraForm Object that contains the extra-form submission information that should be
* inserted into the extra-form table
* @param tableName The name of the CORE TABLE into which the extra-form data is inserted
* @return The SQL INSERT with placeholders
* @see ExtraForm
*/
private String createInsertSqlStatementWithPlaceholders(ExtraForm extraForm, String tableName) {
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb1.append("INSERT INTO " + schemaName + "." + tableName);
sb1.append(" ( ");
sb2.append(" VALUES (");
int count = 1;
for (ExtraForm.Data d : extraForm.getData()) {
sb1.append(d.columnName);
sb2.append("?");
if (count < extraForm.getData().size()) {
sb1.append(", ");
sb2.append(", ");
}
count++;
}
// ADD FOREIGN KEY DATA placeholders
sb1.append(" %s");
sb2.append(" %s");
sb1.append(" )");
sb2.append(" );");
String query = sb1.toString() + " " + sb2.toString();
return query.toString();
}
/**
* Inserts the supplied ExtraForm data submission into the extra-form table.
*
* <p>First the CORE TABLE name will be read from the forms table. Then the SQL INSERT statement
* is prepared with placeholders for values and foreign key(s). This SQL INSERT statement is then
* parsed for certain keywords to get the foreign key references. Afterwards the final SQL INSERT
* statement is created, ready to be used in a PreparedStatement. Finally the insert statement is
* executed.
*
* @param extraForm Object that contains the extra-form submission information that should be
* inserted into the extra-form table
* @return A boolean, indicating if the insertion of the supplied extra-form submission was
* successful
* @throws ConstraintViolations if could not insert extra-form data
* @see ExtraForm
*/
@Override
public boolean insertExtraFormData(ExtraForm extraForm) throws ConstraintViolations {
String formName = extraForm.getFormName();
String tableName = getCoreTableNameFromExtraForm(formName);
String query = createInsertSqlStatementWithPlaceholders(extraForm, tableName);
// Check for foreign key and insert FK reference data
Map<String, String> foreignKeyData = getForeignKeyData(extraForm, query);
query = buildInsertStatement(query, foreignKeyData);
insertData(extraForm, query);
return false;
}
/**
* Inserts the supplied ExtraForm data submission into the extra-form table.
*
* <p>Will go through the ExtraForm.Data objects and inject the values into the the
* PreparedStatement according to the data type (INT, DECIMAL, etc.).
*
* @param extraForm Object that contains the extra-form submission information that should be
* inserted into the extra-form table
* @param query SQL INSERT statement with placeholders for the PreparedStatement
* @throws ConstraintViolations if extra-form data could not be inserted
* @see ExtraForm.Data
*/
private void insertData(ExtraForm extraForm, String query) throws ConstraintViolations {
try (Connection connection = dataSource.getConnection();
PreparedStatement pstmt = connection.prepareStatement(query); ) {
int pointer = 1;
for (ExtraForm.Data d : extraForm.getData()) {
if (d.value == null || d.value.length() == 0) {
String type = d.type;
if (type.equalsIgnoreCase("STRING")) {
// typeName = "VARCHAR";
type = "TEXT";
} else if (type.equalsIgnoreCase("JRDATE")) {
type = "DATETIME";
} else if (type.equalsIgnoreCase("JRDATETIME")) {
type = "DATETIME";
} else if (type.equalsIgnoreCase("SELECTN") || type.equalsIgnoreCase("SELECT1")) {
type = "TEXT";
} else if (type.equalsIgnoreCase("INTEGER")) {
type = "INT";
}
if (type.equalsIgnoreCase("INT")) {
pstmt.setNull(pointer, java.sql.Types.INTEGER);
} else if (type.equalsIgnoreCase("DECIMAL")) {
pstmt.setNull(pointer, java.sql.Types.DECIMAL);
} else if (type.equalsIgnoreCase("DATETIME")) {
pstmt.setNull(pointer, java.sql.Types.DATE);
} else if (type.equalsIgnoreCase("VARCHAR")) {
pstmt.setNull(pointer, java.sql.Types.VARCHAR);
} else {
pstmt.setString(pointer, d.value);
}
} else {
if ((d.type.equalsIgnoreCase("INTEGER") || d.type.equalsIgnoreCase("INT"))
&& d.value.equals("null")) {
pstmt.setNull(pointer, java.sql.Types.INTEGER);
} else pstmt.setString(pointer, d.value);
}
pointer++;
}
int rowCount = pstmt.executeUpdate();
} catch (SQLException e) {
if (e instanceof SQLIntegrityConstraintViolationException) {
throw new ConstraintViolations(e.getMessage());
} else {
throw new ConstraintViolations(e.getMessage());
}
} catch (Exception e) {
throw new ConstraintViolations(e.getMessage());
}
}
/**
* Parses through SQL INSERT query, looking for certain keyword, extracts FOREIGN KEY references
* according to the values found and returns them in a Map for further processing.
*
* <p>Will look for these keywords: - OPENHDS_VISIT_ID - OPENHDS_INDIVIDUAL_ID -
* OPENHDS_HOUSEHOLD_ID - OPENHDS_LOCATION_ID If one of these is found, gets the appropriate extId
* from the extraform object and puts the value into a map for further processing.
*
* @param extraForm Object that contains the extra-form submission information that should be
* inserted into the extra-form table
* @param query SQL INSERT statement with placeholders for FK data and VALUES
* @return A Map<String, String> containing the UUID of the references found
* @throws ConstraintViolations If an object could not be found with the given extId
*/
private Map<String, String> getForeignKeyData(ExtraForm extraForm, String query)
throws ConstraintViolations {
Map<String, String> foreignKeyData = new HashMap<String, String>();
if (query.contains("OPENHDS_VISIT_ID")) {
Visit visit = genericDao.findByProperty(Visit.class, "extId", extraForm.getVisitId());
if (visit == null)
throw new ConstraintViolations("Could not find visit with extId " + extraForm.getVisitId());
foreignKeyData.put("VISIT_UUID", "'" + visit.getUuid() + "'");
}
if (query.contains("OPENHDS_INDIVIDUAL_ID")
|| query.contains("INDIVIDUAL_INFO_INDIVIDUAL_ID")) {
Individual individual =
genericDao.findByProperty(Individual.class, "extId", extraForm.getIndividualId());
if (individual == null)
throw new ConstraintViolations(
"Could not find individual with extId " + extraForm.getIndividualId());
foreignKeyData.put("INDIVIDUAL_UUID", "'" + individual.getUuid() + "'");
}
if (query.contains("OPENHDS_HOUSEHOLD_ID")) {
SocialGroup socialGroup =
genericDao.findByProperty(SocialGroup.class, "extId", extraForm.getSocialGroupId());
if (socialGroup == null)
throw new ConstraintViolations(
"Could not find socialGroup with extId " + extraForm.getSocialGroupId());
foreignKeyData.put("HOUSEHOLD_UUID", "'" + socialGroup.getUuid() + "'");
}
if (query.contains("OPENHDS_LOCATION_ID")) {
Location location =
genericDao.findByProperty(Location.class, "extId", extraForm.getLocationId());
if (location == null)
throw new ConstraintViolations(
"Could not find location with extId " + extraForm.getLocationId());
foreignKeyData.put("LOCATION_UUID", "'" + location.getUuid() + "'");
}
return foreignKeyData;
}