[FIXED] JPA - Filter results

Issue

I've done some research and this question has been asked with no valid solution, basically I have a business with a connection, when disconnected the record is kept but marked with a disconnect date, I want jpa to only pull the current active record (or none).

@Entity
@Table(name = "business", schema = "public")
public class Business {
  ...
  @JsonManagedReference
  @OneToOne(mappedBy = "business", cascade = CascadeType.ALL)
  private Connection connection;
  ...
}

For the connection, I put 'OneToOne' because there should always be one or none active connections, and I want the result as an object not an array, but technically this is incorrect, I'm not sure how to resolve this either.

Is there a way to add conditions on joins somehow? This can't be uncommon.

Solution

Correct, you cannot use an OneToOne, you need to use a @OneToMany with a Set<Connection>

But you can use the Set<Connection> in combination with the hibernate Where-annotation and 'hide' the set, and only return the active Connection (or null)

@Entity
class Business {
  ...
  @OneToMany(mappedBy = "business", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  @Where(clause = "active = true") // <--- only load the active connection if any
  private Set<Connection> connections = new HashSet<>();

  @JsonManagedReference // <--- reference to the active connection
  public Connection getConnection() {
    return connections.stream().findFirst().orElse(null);
  }

  public void setConnection(Connection connection) {
    connections.add(connection);
  }

  // other getters/setters, but no getter for Set<Connection> connections
}

remark:

alternatively the disconnected-date @Where(clause = "disconnected is null") can be used in the where clause.

Answered By - Dirk Deyne