Hibernate annotation for single select object (with where) instead of on-to-many collection

Question

Currently we have a class that looks something like that (depersonalised and non-relevant parts removed):

@Entity
@Table(name = "MAIN_TABLE")
public class MainTable extends AbstractTable {

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "mainTable")
  @OrderBy("CREATED_ON DESC")
  private Set<MainTableState> states;

  ...

  public MainTableState getActiveState(){
    if(this.states == null || this.states.isEmpty()){
      return null;
    }
    MainTableState latest = states.iterator().next();
    // The reason we use this for-loop, even though we have the @OrderBy annotation,
    // Is because we can later add states to this list, which aren't automatically ordered
    for(MainTableState state : states){
      if(state.getCreatedOn() != null && latest.getCreatedOn() != null &&
           state.getCreatedOn().after(latest.getCreatedOn()){
        latest = state;
      }
    }
    return latest;
  }

  ...
}

So currently it will retrieve all MainTableStates from the DB by default, and if we need the activeState we use the for-loop method. Obviously this is pretty bad for performance. Currently we don't use this list at all (the purpose was to have a history of states, but this has been postponed to the future), but we do use the getActiveState() method quite a bit, mostly to show a String inside of the MainTableState-class in the UI.

In addition, even if we would always use a TreeSet and keep it sorted so we won't need the loop but only need states.iterator().next() instead, it will still initialize the list of states. With some heavy performance testing we had more than 1 million MainTableState-instances when it crashed with an java.lang.OutOfMemoryError: GC overhead limit exceeded.

So, we want to change it to the following instead:

@Entity
@Table(name = "MAIN_TABLE")
public class MainTable extends AbstractEntity {

  @???
  private MainTableState activeState;

  ...

  public MainTableStates getActiveState(){
    return activeState;
  }

  ...
}

So, my question, what should I put at the @??? to accomplish this? I'm assuming I need the @Formula or something similar, but how can I say to hibernate it should return a MainTableState object? I've seen @Formula being used with MAX for a date, but that was to get that date-property, not get an entire object based on that max date.

---

After @user2447161's suggestion I've used a @Where-annotation, which does indeed help to reduce the Collection size to 1 (sometimes), but I have two more related questions:

1. How to use @OnToMany and @Where but get a single object, instead of a list of objects of size one? Is this even possible? Here in a answer from December 2010 it is stated it isn't. Has this been fixed somewhere in the last six years?

2. How to deal with the random alias in the where clause? I could do something like this:

   @OneToMany(fetch = FetchType.LAZY, mappedBy = "mainTable") @Where(clause = "CREATED_ON = (SELECT MAX(mts.CREATED_ON) FROM MAIN_TABLE_STATES mts WHERE mts.FK_MAIN_ID = ???.MAIN_ID)") private Set states; // TODO Get single object instead of collection with size 1

The problem with is that ??? is a random alias generated by hibernate (sometimes it's this_, sometimes it's something along the lines of mainTable_1_, etc.). How to set this alias for the entire query to the DB to use it here? I also tried MAIN_TABLE.MAIN_ID instead which doesn't work, and with no alias it also doesn't work because it uses the MainTableState-alias instead of MainTable-alias (like this below).

from
    MAIN_TABLE this_ 
left outer join
    MAIN_TABLE_STATUSES mainstat2_ 
        on this_.main_id=mainstat2_.fk_main_id 
        and (
            mainstat2_.created_on = (
                SELECT
                    MAX(mts.created_on) 
            FROM
                MAIN_TABLE_STATUSES mts 
            WHERE
-- mainstat2_.main_id should be this_.main_id instead here:
                mts.fk_main_id = mainstat2_.main_id
        )
    )

Answer 1

Well, regarding your question #2, as it looks like you need a quick solution with minimal impact in your existing code, this may be acceptable: you can use an Interceptor to deal with the alias and generate the right sql statement. Do this:

1. use a unique string as alias placeholder in your @Where clause, for instance: ...WHERE mts.FK_MAIN_ID = ${MAIN_TABLE_ALIAS}.MAIN_ID...

2. if your application doesn't have one yet, create an Interceptor class extending EmptyInterceptor and configure it as a SessionFactory interceptor

3. override the onPrepareStatement method to replace the placeholder with the alias found after 'from MAIN_TABLE' with something like this:

   public String onPrepareStatement(String sql) { String modifiedSql = sql; if (sql.contains("${MAIN_TABLE_ALIAS}")) { String mainTableAlias = findMainTableAlias(sql); modifiedSql = sql.replace("${MAIN_TABLE_ALIAS}", mainTableAlias); } return modifiedSql; }

Be aware that this method will be called for every sql statement that hibernate generates in your application.

Additionaly, your @Where clause only works properly when a join is used, so you should set the fetch mode explicitly adding @Fetch(FetchMode.JOIN) to the states property to avoid that hibernate may use the select mode.