public boolean childDataRowsExist(Connection conn, TableInfo childTable) throws SQLException {
// Find the relation field in child table that relates to parent
boolean exists = false;
for (BaseField field : childTable.getFields()) {
if (field instanceof RelationField) {
RelationField relationField = (RelationField) field;
TableInfo relatedTable = relationField.getRelatedTable();
if (relatedTable.equals(this.table)) {
String SQLCode = "SELECT " + childTable.getPrimaryKey().getInternalFieldName();
SQLCode += " FROM " + childTable.getInternalTableName();
SQLCode += " WHERE " + relationField.getInternalFieldName() + " = " + this.rowid;
SQLCode += " LIMIT 1";
PreparedStatement statement = conn.prepareStatement(SQLCode);
ResultSet results = statement.executeQuery();
if (results.next()) {
exists = true;
}
results.close();
statement.close();
if (exists) {
return true;
}
}
}
}
return false;
}
/** Caller must close statment and conn */
private void loadDataRow(Connection conn, PreparedStatement statement)
throws SQLException, ObjectNotFoundException, CodingErrorException {
// 0) Obtain all display values taken from other sources:
Map<BaseField, Map<String, String>> displayLookups =
new HashMap<BaseField, Map<String, String>>();
for (BaseField field : this.table.getFields()) {
if (field instanceof RelationField) {
// Buffer the set of display values for this field:
RelationField relationField = (RelationField) field;
String relatedKey = relationField.getRelatedField().getInternalFieldName();
String relatedDisplay = relationField.getDisplayField().getInternalFieldName();
String relatedSource = relationField.getRelatedTable().getInternalTableName();
Map<String, String> displayLookup =
getKeyToDisplayMapping(conn, relatedSource, relatedKey, relatedDisplay);
displayLookups.put(relationField, displayLookup);
}
}
statement.setInt(1, this.rowid);
ResultSet results = statement.executeQuery();
if (results.next()) {
for (BaseField field : this.table.getFields()) {
String keyValue = "";
String displayValue = "";
if (field instanceof RelationField) {
RelationField relationField = (RelationField) field;
keyValue = results.getString(relationField.getInternalFieldName());
displayValue = displayLookups.get(relationField).get(keyValue);
} else if (field instanceof DateField) {
// need a lot of converting between different types
Timestamp keyValueDate = results.getTimestamp(field.getInternalFieldName());
if (keyValueDate != null) {
DateValue keyValueDateValue = new DateValueDefn(keyValueDate.getTime());
try {
keyValueDateValue.setDateResolution(((DateField) field).getDateResolution());
} catch (CantDoThatException cdtex) {
throw new CodingErrorException(
"Date resolution value for field "
+ field.getFieldName()
+ " not recognised by date value object",
cdtex);
}
keyValue = keyValueDateValue.toString();
displayValue = keyValue;
}
} else if (!field.getFieldCategory().savesData()) {
// no data for these fields
} else {
keyValue = results.getString(field.getInternalFieldName());
displayValue = keyValue;
}
DataRowField dataRowField = new DataRowField(keyValue, displayValue);
this.row.put(field, dataRowField);
}
} else {
throw new ObjectNotFoundException("Record with identifier " + rowid + " not found");
}
results.close();
}
public Map<RelationField, List<DataRow>> getChildDataRows(
DatabaseInfo databaseDefn, Connection conn, HttpServletRequest request)
throws SQLException, ObjectNotFoundException, CodingErrorException {
// declare the return value:
Map<RelationField, List<DataRow>> childDataRows = new HashMap<RelationField, List<DataRow>>();
// obtain a set of all tables containing any field from this table as a
// RelationField
SortedSet<TableInfo> relationTables =
databaseDefn.getDirectlyDependentTables(this.table, request);
String localTableInternalName = this.table.getInternalTableName();
String localTablePrimaryKeyName = this.table.getPrimaryKey().getInternalFieldName();
// obtain the relation field(s) for each table & generate sql to get
// rows where related:
for (TableInfo relatedTable : relationTables) {
String relatedTableInternalName = relatedTable.getInternalTableName();
String relatedTablePrimaryKeyName = relatedTable.getPrimaryKey().getInternalFieldName();
for (BaseField baseField : relatedTable.getFields()) {
if (baseField instanceof RelationField) {
RelationField relationField = (RelationField) baseField;
StringBuilder sql = new StringBuilder();
// generate sql:
// "SELECT relatedTable.[row id field]
// FROM localTable, relatedTable
// WHERE localTable.field = relatedTable.field
// AND localTable.primaryKeyField = rowid"
// select b1.b1rowid from a1, b1
// where b1.a1rowid = a1.a1rowid
// and a1.a1rowid = 5
sql.append("SELECT "); // change
sql.append(relatedTableInternalName);
sql.append(".");
sql.append(relatedTablePrimaryKeyName);
sql.append(" FROM ");
sql.append(localTableInternalName);
sql.append(", ");
sql.append(relatedTableInternalName);
sql.append(" WHERE (");
sql.append(relatedTableInternalName);
sql.append(".");
sql.append(relationField.getInternalFieldName());
sql.append("=");
sql.append(localTableInternalName);
sql.append(".");
sql.append(relationField.getRelatedField().getInternalFieldName());
sql.append(") AND (");
sql.append(localTableInternalName);
sql.append(".");
sql.append(localTablePrimaryKeyName);
sql.append("=");
sql.append(this.rowid);
sql.append(")");
PreparedStatement statement = conn.prepareStatement(sql.toString());
ResultSet results = statement.executeQuery();
List<DataRow> relationDataRows = new ArrayList<DataRow>();
while (results.next()) {
int foreign_rowid = results.getInt(relatedTablePrimaryKeyName);
DataRow dataRow =
new DataRow(
relationField.getTableContainingField(),
relationField.getTableContainingField().getPrimaryKey(),
foreign_rowid,
conn);
relationDataRows.add(dataRow);
}
if (relationDataRows.size() > 0) {
childDataRows.put(relationField, relationDataRows);
}
}
}
}
return childDataRows;
}
