Hibernate mapping a composite key with null values

Question

With Hibernate, can you create a composite ID where one of the columns you are mapping to the ID can have null values?

This is to deal with a legacy table that has a unique key which can have null values but no primary key.

I realise that I could just add a new primary key column to the table, but I'm wondering if there's any way to avoid doing this.

Ottie

I would like to know if this is also possible, my database (OpenEdge) doesn't support views and I can not add new fields to the table, how did you overcome this? – Meteorite

they might update the javadoc in the future hibernate.atlassian.net/browse/HHH-14535 – Aran

Answer 1

No. Primary keys can not be null.

Wolsky

Our customer provides a view without any primary key. For Hibernate I have to define a PK. But the only unique combination of fields includes a nullable field. – Bamberg

can we have a solution to ignore entries with null values ? – Durango

Answer 2

You wont get error but Hibernate wont be able to map those rows with NULL value for composite column to your Entity. That means you get entity with NULL values in result.

Setula

Answer 3

Unfortunatly, no. I either had to use a workaround:

I used composit Id for a view(! not table) where rows can be identified by 2 cols exactly (A, B). Although one of the cols (B) can have null values as well as positive integers. So my workaround is that i created a new col in the view: "BKey" and my view is written as if B is null then value of BKey is -1 else BKey = B. (Only positive integers occour in B and null). I also changed my composit id implementation to use BKey instead of B. Hope it helps for somebody..

Posthorse

Answer 4

This is not advisable. Could you use a view and map that instead? You could use COALESCE to supply a default if you are stuck with legacy data. We had lots of trouble with composite keys and I imagine null values will cause even more issues.

Swanskin

Answer 5

For composite keys (assumed that database allows nulls in PKs) you can have maximum number_of_cols^2 - 1 entries containing nulls, (for example for composite key of 2 columns you can have 3 rows having in their primary key null, the fourth is the PK without nulls).

Wolsky

Answer 6

I am having this problem with a model entity mapped to an OpenEdge Progress database that I can't modify. It has 3 different ID columns that basically make up a primary key.

I came up with a workaround so I could at least get the data, even if it's not very pretty in my opinion.

In my Repository class, which extends JpaRepository<MyEntity, Long>, I added a new method which returns List<Object[]>. So for example:

@Query(
value="SELECT * FROM pub.\"mytable\" WHERE \"id-one\" = ?1 AND \"id-two\" = ?2 AND \"id-three\" is null", 
nativeQuery=true
)
List<Object[]> getRecordsAsObject(Long aIdOne, Long aIdTwo);

Then in my service class I manually create the model record by extracting the data from the object array.

getRecordsAsObject(myIdOne, myIdTwo).forEach(aRecord -> {
   MyEntity myEntity = new MyEntity();
   myEntity.setIdOne((Long) aRecord[0]);
   myEntity.setIdTwo((Long) aRecord[1]);
   myEntity.setOtherValue((String) aRecord[2]);
   process(myEntity);
});

This at least helps me with extracting data from the table. I don't know that it would do anything in terms of further persisting/saving/updating the record in the table.

Isomorphism

Answer 7

There is nothing wrong with null fields in a composite key (or null keys theoretically), it is just Hibernate doesn't deal with them well due to implementation details.

To work around this I used Optional to represent nulls and mapped them using a converter. Something like:

public class OptionalNullConverter<T> implements AttributeConverter<Optional<T>, T> {

  @Override
  public T convertToDatabaseColumn(Optional<T> attribute) {
    return attribute == null ? null : attribute.orElse(null);
  }

  @Override
  public Optional<T> convertToEntityAttribute(T dbData) {
    return Optional.ofNullable(dbData);
  }
}

And then:

@lombok.Getter
@lombok.Setter
@lombok.NoArgsConstructor
public class SomeCompositeKey {
  @Convert(converter=OptionalNullConverter.class)
  Optional<Integer> id;

  @Convert(converter=OptionalNullConverter.class)
  Optional<String> name;
...
}

You can also fashion some kind of magic value null representation if you are against Optionals for some reason. Just use a converter that maps null to some magic value that is guaranteed not to be used. For auto-increment ids that can be Integer.MIN_VALUE or something. I'd advise against it purely for the code clarity reasons.

With respect to "can't do" comments above, the whole point of using ORM layer is to have an abstraction over a database. The concepts from MySQL world (like "can't have nulls in PK") should have no place in the domain design. If you have a legitimate null key in Java (rarely) or nulls in a composite key (kinda often), then that's what it should be in the domain model. The rest is up to the ORM and annotation soup to deal with.

Rosen

Answer 8

Why would you want to do that? Your composite ID should map the primary key of your table, and it doesn't sound wise to put null values in a key, does it?

EDIT: Hibernate does not allow to do so; you might put the property outside the key and tweak the DAO a little to take the field into account wherever necessary

Knudsen

Mapping to legacy tables is one reason why this might be required. I have a case where this is also required and the DB does not support views (Progress OpenEdge). – Meteorite

I am having the same problem. I have a @Embedded with 4 attributes (this is a view and I cannot control it). And some of them can be null, but the composition of them cannot be the same (ford,red,4,4), (ford,red,4,null) for example. How can I do? – Foretell