Selecting from Multiple Tables in Spring Data
hql, java, spring, spring-data, spring-data-jpa
Solution
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();
}
}
Problem
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= ?; ```