Rewrite SQL query with JOINS in JPA

Question

I have this SQL query for MariaDB.

select @ref:=id as id, unique_id, reference_id
from mytable
join (select @ref:=id from mytable WHERE unique_id = 55544)tmp
where reference_id=@ref

https://www.db-fiddle.com/f/jKJodfVfvw65aMaVDyFySd/0

How this query can be implemented in HQL query? I would like to use it in JPA?

Duplicate of [this](https://stackoverflow.com/questions/54397510/rewrite-sql-query-in-jpa). — manish, Jan 31 '19 at 04:19
Perhaps, just perhaps the reason nobody answers this is because you make no effort to say what you have tried, and you dont mention the entities themselves (which JPQL is based on, and can't exist without). But feel free to not post it ... — , Feb 01 '19 at 16:13
@BillyFrost I tried just a basic JPA query but with no luck. Can you give me hand please because this problem is very critical for my project? — Peter Penzov, Feb 01 '19 at 16:17
Here's an idea, and you can read it in my previous comment. POST WHAT YOU HAVE TRIED. POST YOUR ENTITIES. — , Feb 01 '19 at 17:55
@PeterPenzov As a hierarchical query is complicated to accomplish using JPA or HQL, another option is to create a database function with a hierarchical SQL and call the function from JPA. — Jacob, Feb 03 '19 at 04:07

df778899 · Answer 1 · 2019-02-05T19:48:28.653

(Answer largely re-written after comments below)

JPA doesn't have built-in support for hierarchical queries. The main option is a native query.

E.g. with this entity class:

@Entity
public class MyTable {
    @Id
    @GeneratedValue
    private int id;

    private int uniqueId;

    @ManyToOne
    private MyTable reference;

    // ... getters and setters ...
}

The following is an example of a native hierachical SQL query (actually against MySQL, just in case):

    Query query = entityManager.createNativeQuery(
            "select @ref\\:=id as id, unique_id, reference_id\r\n" +
                    "from my_table\r\n" +
                    "join (select @ref\\:=?)tmp\r\n" +
                    "where reference_id=@ref",
            MyTable.class);
    query.setParameter(1, 1);
    query.getResultList();

This was chasing down a chain of references successfully.

(Other alternatives)

There probably aren't too many other options that can do this as a single query. If scalability is less of a concern, adding a back reference would be a simple way to navigate the model:

    @OneToMany(mappedBy = "reference")
    private Set<MyTable> backReferences;

Those would then be straightforward to recursively navigate. Clearly the relation defaults to lazy loading, so would add little overhead until used.

Thanks! It's one table. See here https://stackoverflow.com/questions/54393865/implement-recursive-cte-for-hierarchical-query-to-mariadb. How @ref should be rewritten in JPA? — Peter Penzov, Feb 01 '19 at 18:24
Understood! Unfortunately, in short, to perform a hierarchical query (aka CONNECT BY in Oracle worlds) is a tough ask in JPA. Eclipselink has a [custom extension](https://wiki.eclipse.org/Using_Advanced_Query_API_(ELUG)#How_to_Use_Hierarchical_Queries) for the Oracle equivalent, but otherwise the main choice is a native query - similar information in [this answer](https://stackoverflow.com/a/2598605/1794485). — df778899, Feb 01 '19 at 19:10
Can you show what will be the solution native query jut to see what are the alternatives? — Peter Penzov, Feb 01 '19 at 21:15

score 1 · Answer 2 · answered Feb 03 '19 at 01:33

With @df778899's MyTable in spring-data it could look like:

@Repository
public interface MyRepository extends ...

   @Query("select @ref:=id as id, unique_id, reference_id "+
           "from mytable join (select @ref:=id from mytable WHERE unique_id = :pUid) tmp "+
           "where reference_id=@ref", //just copy paste the query, use :pUid instead of constant...
            nativeQuery = true) // and this!
   List<MyTable> myCustomHirachicalQuery(@Param("pUid") Integer uid/*String/Long/...*/);
...

Rewrite SQL query with JOINS in JPA

2 Answers2