JPA - Ignore join operation in namedQuery in EclipseLink

Question

I have below scenario

Entity TableA :

     @Entity
     @Table(name = "TABLE_A")
     @NamedQueries({
             @NamedQuery(name = "TableA.namedQ1", query = "SELECT t1 FROM TableA t1 JOIN FETCH t2.TableB t2"
                     + " WHERE <conditions here>"),
             @NamedQuery(<Need query here which will ignore mapping below and return rows only for TableA>)
             }   )
     public class TableA implements Serializable{

     @Id
     @Column(name = "id")
     private int id

     ...
     ...
     ...

     @OneToMany(mappedBy = "tableA", cascade = CascadeType.ALL ,fetch=FetchType.LAZY)
     private List<TableB> tableB;




     }

Entity TableB :

     @Entity
     @Table(name = "TABLE_B")
     public class TableB implements Serializable{

     @Id
     @Column(name = "id1")
     private int id1

     ...
     ...
     ...




     @ManyToOne
     @JoinColumn(name = "id",insertable = false, updatable = false)
     private TableA tableA;



     }

I am facing below two issues :

Query mentioned above i.e

      SELECT t1 FROM TableA t1 JOIN FETCH t2.TableB t2

takes long time to execute. around 30 seconds. But the same query for same dataset takles hardly 3-4 seconds in SQL developer. ANythnig I should do in code to make it run faster?

I have requirement where i dont need data from other table(retrived via mapping). I would be needing data only from TableA. I tried below named query but it run separate query against TableB for each row in TableA which takes 4+ minutes to execute.
```
   "SELECT t1 FROM TableA t1 where <condition goes here>"
```
What modifications I have to do in query to ignore mapping. I would need to retain annotations(@OneToMany) as I will need it in namedQ1.

Thanks in anticipation

If the contents of TableA is sufficient, just use a JOIN instead of a JOIN FETCH (see https://stackoverflow.com/questions/17431312/what-is-the-difference-between-join-and-join-fetch-when-using-jpa-and-hibernate). — user871611, Apr 02 '21 at 20:44
@user871611 I tried mentioned post. But its firing subquery for each row in TableA — nikhil, Apr 02 '21 at 20:53
Seems like you are accessing fields from TableB. If so (and you really need the data from TableB) JOIN FETCH is your friend and your SQL client is so much faster because it's only fetching data from TableA. That would be my guess. — user871611, Apr 02 '21 at 21:35

Davide D'Alto · Accepted Answer · 2021-04-02T20:59:57.483

2

When you use FETCH, you are asking to retrieve the data for the collection in advance. If you don't need the elements from TableB then your query should be:

SELECT t1 FROM TableA t1 left join t1.tableB t2

Note that the join is only necessary if you need to add some condition to t2. For example:

SELECT t1 FROM TableA t1 left join t1.tableB t2 WHERE t2.field = 123

if this is not your case, than this should be enough:

SELECT t1 FROM TableA t1;

In all these cases, it will create a proxy for the collection TableB and won't need to access the table on the database, unless you need to use the collection later on.

edited Apr 02 '21 at 20:59

answered Apr 02 '21 at 20:47

Davide D'Alto

7,421
2
16
30

Thanks for the reply. I have already tried "SELECT t1 FROM TableA t1;" but it fires subquery against tableB for each row in TableA. – nikhil Apr 02 '21 at 20:55
That can only happen if you are accessing the collection field from the retrieved elements – Davide D'Alto Apr 02 '21 at 20:57
How do you expect it to get the data without querying the db? – Davide D'Alto Apr 02 '21 at 20:59
Are you doing something with the `TableB` field? – Davide D'Alto Apr 02 '21 at 21:00
I have two namedQuery. in query one I would need to joing TableA with TableB using @OneToMany and in second named query I want to ignore mapping. – nikhil Apr 02 '21 at 21:01
It's clear that you need data from `TableB`, otherwise you wouldn't see that additional SQL queries. Having two named queries has nothing to do with lazy fetching. – Davide D'Alto Apr 02 '21 at 21:04
1

What's happening is that after you run the named query `SELECT t1 FROM TableA t1;`, you are accessing the collection `List tableB` from one of the results. Because the first query didn't fetch those values, now EclipseLink needs to run additional queries to get the values. It's seems that there is something wrong with your app or that you need to load this values one way or another. – Davide D'Alto Apr 02 '21 at 21:09
1

Maybe if you show us the code you are running after the query has been executed we might tell you more. – Davide D'Alto Apr 02 '21 at 21:11
1

Thanks. I revisted my code and found that i was having reference made to tableB data. I removed references based on conditions and it started working as expected for me. – nikhil Apr 03 '21 at 12:07

score 0 · Answer 2 · answered Apr 03 '21 at 12:09

0

For anyone having similar questions

  Question 1 in OP : https://stackoverflow.com/questions/61573091/long-time-of-fetching-data-from-oracledb-using-eclipselink

  Question 2 in OP: Solved using @Davide solution

answered Apr 03 '21 at 12:09

nikhil

877
3
11
36

JPA - Ignore join operation in namedQuery in EclipseLink

2 Answers2