Selecting from Multiple Tables in Spring Data

Question

I need to write a select query fetching data from multiple tables in Spring Data Repository layer. I know we can use @Query to write custom queries, but that returns value from a single table only?

SELECT s.service_id, s.name, us.rating_id 
FROM services s, 
     ratings r, 
     user_services us
where 
    us.service_id = s.service_id and
    us.rating_id = r.rating_id and
    us.user_id= ?;

I think the selected answer (http://stackoverflow.com/a/2359214/217324) on the question I picked as a dupe shows the exact case you're looking for. — Nathan Hughes, Jul 08 '14 at 19:28
@NathanHughes My question is not about JPQL, its about how to incorporate JPQL in Spring Data Respository, I tried first approach of souser which dint wor, but I am looking something similar. — Himalay Majumdar, Jul 08 '14 at 22:17
reopened. if you could find a way to reword the question to make it clearer that would probably help matters. I still don't get how the linked question isn't sufficient. — Nathan Hughes, Jul 09 '14 at 12:59
@NathanHughes I did mention SpringData in my Question, Heading and tagged it accordingly as well. Your link doesn't talk about spring data anywhere. Souser's answer helped my anyway, I am going to accept that. Please feel free to edit my question if you think it should be further clarified. Thank you. — Himalay Majumdar, Jul 09 '14 at 14:28
now that you mention it, the link didn't talk about spring data. You should be able to use the JPQL in the @Query and have the method on the spring data interface return a list of the dto populated by the select new. — Nathan Hughes, Jul 09 '14 at 14:42
I mentioned Spring data since beginning :), select new is a good trick though. Thank you! — Himalay Majumdar, Jul 09 '14 at 15:20

score 8 · Accepted Answer · edited May 23 '17 at 12:24

8

Your Interface method can use native SQL to select columns from multiple tables and the method will return a list of object arrays :

public interface MyRepository extends JpaRepository {
  @Query(name = [name], nativeQuery = true)
  List<Object[]> methodThatQueriesMultipleTables();
}

Each item in the list is Object array that is a row of data

You can also create a Custom Repository Implementation :

How to add custom method to Spring Data JPA

@NoRepositoryBean
public interface CustomRepository<[Your object]> {
    List<Object[]> methodThatQueriesMultipleTables();
}

public class MyRepositoryImpl<[Your object]> implements CustomRepository<[Your object] {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Object[]> methodThatQueriesMultipleTables() {
        //use JPA query to select columns from different tables
        Query nativeQuery = entityManager.createNativeQuery("query");
        return query.getResultList();
    }
}

edited May 23 '17 at 12:24

Community

1
1

answered Jul 08 '14 at 17:58

souser

796
2
8
25

2

For the first approach I am getting Caused by: `org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'individualProviderServicesRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Not an managed type: class java.lang.Object` – Himalay Majumdar Jul 08 '14 at 22:14
The second approach worked after playing with it for sometime. Thank you. – Himalay Majumdar Jul 09 '14 at 14:29
It is hard to figure out without looking at the code, but I am glad you could resolve your issue. – souser Jul 09 '14 at 15:58
Any idea how I could use this approach to have it map to an Entity directly? Is that possible when joining across tables if the resultset that's coming back is coming from a single table. – Kevin M Sep 25 '14 at 13:55

Selecting from Multiple Tables in Spring Data

1 Answers1

Linked