CODEWITHSUNDEEP
springhibernatesortingspring-data-jpaparent-child
ScottM
ScottM

Reputation: 660

Hibernate: Sorting children through a onetomany + manytoone relation

I have a Contact entity connected to a ContactList entity through a @OneToMany relation. Then the ContactList entity connects to the Review List entity through @ManyToOne. The ContactList entity has data related to the contact on the list. I need to sort the ContactList children from the Contact by the createdOn timestamp in Review List. I am not sure this is possible.

Contact entity

@OneToMany(mappedBy = "contact")
@OrderBy("created_on DESC") <-- This does not work.
private SortedSet<ContactList> contactLists;

ContactList entity:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "LIST_ID")
private ReviewList reviewList;

ContactList compareTo:

@Override
public int compareTo(ContactList b) {
    if (this.getReviewList().getCreatedOn().getTime() == b.getReviewList().getCreatedOn().getTime()) {
        log.error("Two review lists have the exact same created time???");
        return 0;
    }

    return this.getReviewList().getCreatedOn().getTime() < b.getReviewList().getCreatedOn().getTime() ? -1 : 1;
}

Review List createdOn field I am trying to sort the parent by:

@Column(name="CREATED_ON", updatable=false)
private Timestamp createdOn;

I have tried it without the @OrderBy notation, and it will not compile. But everything I have tried to put in the quote fails. I have tried contactList as shown, reviewList, reviewList.createdOn, reviewList_createdOn, and a couple others that were really dumb.

It seems like Hibernate is taking the first part of the quoted string and putting it into the query directly. Whatever I put in there errors with this:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'contactlis0_.created_on' in 'order clause' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_91] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_91] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_91] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_91] at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar:5.1.44] at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.44.jar:5.1.44] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) ~[mysql-connector-java-5.1.44.jar:5.1.44]

And of course it does, since I'm trying to reference a field in another table. It needs to do some sort of JoinColumn in between.

Is there any way to do this? I could probably write a custom JPQL query to sort them how I want and pass that to the page separately, but I was trying to do this with annotation and hibernate.

Upvotes: 0

Views: 1793

Answers (2)

ScottM
ScottM

Reputation: 660

My solution so far is to add a 'list_created_on' field to the ContactList object, and then update the data so that every record has the ReviewList created_on date. Then whenever I create a new ContactList in the database, I just set it to the created date of the review list. I don't really like keeping the same data in two spots, but I can't get around it.

It seems inelegant, but I could not figure out how to reference the child of a child in hibernate using annotation. And the sorting now works the way I wanted.

I will not accept this as an answer in case anyone else has a better solution.

Upvotes: 0

lane.maxwell
lane.maxwell

Reputation: 5872

I believe what is causing you issues is that fact that you are using a SortedSet. Regardless of how hibernate inserts values, they will be ordered according to the contract of SortedSet. Unless you have implemented Comparable on ContactList, this will be the natural ordering of ContactList. If you have implemented Comparable, then your list will sort according to that implementation. I don't think either is your desired outcome.

Try defining your collection as a Set, if you want the DB to perform the ordering.

Upvotes: 1

Related Questions