CODEWITHSUNDEEP
javaspringhibernateormjoin
Gregorius Edwadr
Gregorius Edwadr

Reputation: 399

Join multiple table in Java Spring Hibernate

I am having problem with joining tables in Java Spring hibernate. Here is my problem :

I am developing a web site and in one of my page I need the data from three tables, they are x, y, z. I mapped each of them in to entity and has its own repository. In this page I am showing the data using this query : 

SELECT x.a, y.b, z.c FROM x,y,z WHERE x.id = y.name AND z.id = y.personalId AND
(x.id like %'searchedId'% or y.id like %'searchedId'% or z.id like %'searchedId'%) AND
(x.name like %'searchedName'% or y.name like %'searchedName'% or z.name like %'searchedName'%)

The problem is, the searchedId and searchedName is an optional column and they could be empty string and when they are empty strings I don't need to put them into my query to speed it up. I tried using criteria builder but since the result is joined column from multiple tables I can't put them into any repository.

Upvotes: 1

Views: 533

Answers (1)

shankarsh15
shankarsh15

Reputation: 1967

In this case, you can then dynamically build native query something like below:

StringBuilder query = new StringBuilder("SELECT x.a, y.b, z.c FROM x,y,z WHERE x.id = y.name AND z.id = y.personalId ");

if (searchId !=null)
    query .append(" AND
(x.id like %'searchedId'% or y.id like %'searchedId'% or z.id like %'searchedId'%) ");

if (searchedName !=null)
    query .append(" AND
(x.name like %'searchedName'% or y.name like %'searchedName'% or z.name like %'searchedName'%) ");

SQLQuery query = session.createSQLQuery(query);
List result = query.list();

Upvotes: 2

Related Questions