title: spring data JPA/Hibernate. Complex cross table SQL which doesn't map to a table entity
body: <p><strong>* Edited with possible solution - any comments ? *</strong></p>
  
  <p>spring 4.2.5 RELEASE</p>
  
  <p>I'm starting to create Java web services onto a Legacy database.</p>
  
  <p>Following the spring-data JPA repository pattern creating entities which map to tables, a repository extending CrudRepository is working well.  </p>
  
  <p><a href="http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-two-crud/" rel="nofollow">As described in this great tutorial</a></p>
  
  <p>All the examples I've seen assume simple mapping of a table to an entity.  Order -> OrderEntity, OrderLine, Customer etc.</p>
  
  <p>How would you deal with read-only reporting type queries which do not fit into this pattern where the query result contains columns from many tables and use complex cross table joins. </p>
  
  <p>I'm just struggling to get my head around how to deal with this scenario.</p>
  
  <hr>
  
  <p><strong>Possible Solution</strong> </p>
  
  <p>I've managed to run native SQL using the <code>NamedParameterJdbcTemplate</code> and map the results onto a POJO using a <code>BeanPropertyRowMapper</code></p>
  
  <p>ApplicationContext class</p>
  
  <p>The NamedParameterJdbcTemplate bean is defined (the rest of the beans HikariCP, JPA Session Factory, JPA Transaction Manager, DozerBean mapper have been left out for brevity)</p>
  
  <pre><code>@Configuration
  @EnableTransactionManagement
  
  @EnableJpaRepositories(basePackages = {"com.savant.test.spring.donorservicejpa.dao.repository"},
          repositoryBaseClass = com.savant.test.spring.donorservicejpa.dao.repository.BaseRepositoryImpl.class )
  
  @ComponentScan(
          {"com.savant.test.spring.donorservicejpa.dao.jdbc.repository",
           "com.savant.test.spring.donorservicejpa.dao.query.objects"})
  
  
  public class ApplicationContext {
  
      @Bean
      NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
          return new NamedParameterJdbcTemplate(dataSource);
      }
  
  }
  </code></pre>
  
  <p>POJO for search results. No Spring annotations, just a simple class</p>
  
  <pre><code>package com.savant.test.spring.donorservicejpa.dao.query.objects;
  
  public class SessionSearchResult {
  
      private String sessno;
      private String sesdate;
      // etc
  
      // setters/getters
  }
  </code></pre>
  
  <p>'Repository'.  It's not actually a repository in spring terms, just an interface/class implementation</p>
  
  <pre><code>package com.savant.test.spring.donorservicejpa.dao.jdbc.repository;
  
  public interface SessionSearchRepository{
      List&lt;SessionSearchResult&gt; findByCriteria(String searchCriteria);
  }
  </code></pre>
  
  <p>Base implementation</p>
  
  <pre><code>package com.savant.test.spring.donorservicejpa.dao.jdbc.repository;
  
  import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  
  public class BaseJdbcRepositoryImpl {
  
      protected final NamedParameterJdbcTemplate jdbcTemplate;
  
      BaseJdbcRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
          this.jdbcTemplate = jdbcTemplate;
      }
  
  }
  </code></pre>
  
  <p>Simple test implementation of the search.</p>
  
  <pre><code>package com.savant.test.spring.donorservicejpa.dao.jdbc.repository;
  
  
  import com.savant.test.spring.donorservicejpa.dao.query.objects.SessionSearchResult;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.BeanPropertyRowMapper;
  import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  import org.springframework.stereotype.Component;
  import org.springframework.transaction.annotation.Transactional;
  
  
  @Component
  public class SessionSearchRepositoryImpl extends BaseJdbcRepositoryImpl implements SessionSearchRepository {
  
      private static final String SESSION_SEARCH_SQL
              = "SELECT sesdet.sessno, sessdays.sesdate "
              + "FROM sesdet, sessdays "
              + "WHERE sessdays.sessno = sesdet.sessno "
              + "AND sesdet.sessno = :sessno";
  
  
      @Autowired
      public SessionSearchRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
          super(jdbcTemplate);
      }
  
      @Transactional(readOnly = true)
      @Override
      public List&lt;SessionSearchResult&gt; findByCriteria(String searchCriteria) {
          Map&lt;String, String&gt; queryParams = new HashMap&lt;&gt;();
  
          queryParams.put("sessno", searchCriteria);
          List&lt;SessionSearchResult&gt; searchResults = jdbcTemplate.query(SESSION_SEARCH_SQL, queryParams,
                  new BeanPropertyRowMapper&lt;&gt;(SessionSearchResult.class));
  
          return searchResults;
      }
  }
  </code></pre>
  
  <p>And a simple test just to run the SQL</p>
  
  <pre><code>@Autowired
  SessionSearchRepository sessionSearchRepository;
  
  
  @Test
  public void a_testSessionSearch() throws Exception, Throwable {
      List&lt;SessionSearchResult&gt; sl =  sessionSearchRepository.findByCriteria("CA04AS");
  
      for (SessionSearchResult sessionSearchEntity : sl) {
  
      }
  
  }
  </code></pre>