예제 #1
0
 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;
 }
예제 #2
0
 /** 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();
 }
예제 #3
0
  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;
  }