JPA entity mapping to optional SecondaryTable

I have an entity. I would like to optionally load additional information about the entity from a secondary table. I have the following mapping.

@Entity
@Table( name = "program", schema = "myschema" )
@SecondaryTable( name = "program_info", schema = "myschema", pkJoinColumns =
        @PrimaryKeyJoinColumn( name = "program_id", referencedColumnName = "program_id" ) )
public class Program { ... }

I have all the columns mapped appropriately, and a unit test that works. The trouble comes when I have a row in the program table that does not have a corresponding row in the program_info table. In th开发者_StackOverflowat case it won't load the program at all. I need to be able to mark the entire secondary table as optional. I would prefer to stay away from having to create another entity/dao/service and do a 1-1 mapping.

I'm using eclipselink but would like to stay away from provider specific details if I can.

---

I need to be able to mark the entire secondary table as optional.

EclipseLink will perform an INNER JOIN with a SecondaryTable so if there is no corresponding row in the secondary table, you'll get nothing. In other words EL expects to find a row in the secondary table (when creating, reading, updating, deleting).

If what you want is an OUTER JOIN, you should use an optional OneToOne association (maybe with a shared primary key in your case).

An alternative would be to use a database view (doing an OUTER JOIN) and to map the view.

I would prefer to stay away from having to create another entity/dao/service and do a 1-1 mapping.

I don't see why having an optional OneToOne forces you to have a corresponding DAO and service. Manage the optional part through the main "holder" part.

See also

• JPA Wiki book
  • Multiple table outer joins

---

TopLink, EclipseLink: If the database supports usage of outer join syntax in the where clause (Oracle, Sybase, SQL Server), then the multiple table join expression could be used to configure an outer join to be used to read the table.

There is my customizer for Eclipselink multiple table entity on oracle database

public class OuterJoinCustomizer implements DescriptorCustomizer {

  public void customize(ClassDescriptor descriptor) {
    Map<DatabaseTable, Map<DatabaseField, DatabaseField>> tables =
        descriptor.getAdditionalTablePrimaryKeyFields();

      ExpressionBuilder builder = new ExpressionBuilder();
      Expression expression = null;

      for (Map.Entry<DatabaseTable, Map<DatabaseField, DatabaseField>> table :
          descriptor.getAdditionalTablePrimaryKeyFields().entrySet()) {
        Expression criteria = null;

        for (Map.Entry<DatabaseField, DatabaseField> fields : table.getValue().entrySet()) {
              // NOTE: equalOuterJoin (+)= SUPPORTED BY ORACLE!
              Expression fieldExp =
              builder.getField(fields.getValue()).equalOuterJoin(builder.getField(fields.getKey()));
          criteria = (criteria == null) ? fieldExp : criteria.and(fieldExp);
        }

        if (criteria != null)
          expression = (expression == null) ? criteria : expression.and(criteria);
      }

      if (expression != null)
        descriptor.getQueryManager().setMultipleTableJoinExpression(expression);
    }

  }
}

In Hibernate it is mush easier

@Table(optional = true)