All geek questions in one place

Alternative to get connection with the "ON" sentence in sleep mode

This is the next domain model in the DBMS that maps to Hibernate in Java objects.

┌─────────┐ ┌─────────────┐ │ Project │ 1 0..n │ UserSetting │ ├─────────┼------------┼─────────────┤ │ name │ │ username │ └─────────┘ │ favorite │ └─────────────┘

Users can mark the project as a favorite, which is reflected in the associated (optional) UserSetting record. If the user has not added the project flag, there is no UserSetting entry in the database, which means that the project is not favorite. Think UserSetting is "rare."

Next: Objects must be obtained through JPA with Hibernate as a provider.

Suppose the Project and UserSetting can be very large. Database schema optimization should not be subject to this issue. Suppose that effective indexes exist.

For this user, I would like to receive effectively all projects, including the status of favorites for this specific user. Pay attention to the sorting criteria!

My initial approach was to formulate the following JPQL query:

 SELECT p, us FROM Project p LEFT JOIN FETCH UserSetting us WHERE us.username IS NULL OR us.username = :currentUser ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC

This does not work if currentUser has not UserSetting , but userB . UserSetting of userB will be merged, but the project will not be specified due to the WHERE .

Fortunately, JPA 2.1 introduces JOINS with an ON clause ( Javadoc ), which can be used to convert a query to:

 SELECT p, us FROM Project p LEFT JOIN FETCH UserSetting us ON us.username IS NULL OR us.username = :currentUser ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC

Unfortunately, Hibernate (4.3.6) does not support combining fetch with the ON clause (WITH clause in Hibernate lingus) and exits with the following exception:

 org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters

How can I reformulate a request to implement functionality and fulfill my limitations:

  • Fixed domain model
  • Efficient query that scales well
  • JPA + Hibernate
  • Special features of Hibernate should be avoided.
+6
source share
2 answers

A more recent discovery is that the JPA 2.1 specification (JSR 338) defines a grammar without allowing "ON clauses" to fetch together.

My workaround is to omit the object mapping function in Hibernate and explicitly select the necessary Project and UserSetting .

+1
source

You can use the following to also get a project without user settings:

 SELECT p, us FROM Project p LEFT JOIN FETCH UserSetting us WHERE us is null OR us.username IS NULL OR us.username = :currentUser ORDER BY COALESCE(us.favorite, false) DESC, p.name ASC 

  us is null

in the WHERE clause of the query, a trick is performed.

+6
source

Source: https://habr.com/ru/post/972992/

More articles:


All Articles