Optimize Spring-Data JPA queries

Question

I am looking for possible optimizations for framework-generated queries. As far as I understand, the process is the following:

• you could declare your domain objects as POJOs and adding several annotations like @Entity, @Table, @ManyToOneetc.

• you declare your repositories e.g. per interfaces

With (2) you have several options to describe your query: e.g. per Methodnames or @Query

If I write a query like:

@Query("select t from Order t LEFT join fetch t.orderPositions where t.id = ?1")
Page<Order> findById(Pageable pageable, String id);

a SQL-query is autogenerated, where every column of the order is resolved and subsequentially for orderpositions and depending obejcts/tables. As if I wrote:

select * from order

So in case, that I need some Information from several joined objects, a query could be quite expensive: and more interesting quite ineffective. I stumbled upon a slow query and MySQL-explain told me, that in the generated query the optimizer could not make use of indices, which is bad.

Of course (I am aware) I have to deal with a tradeoff, that generated SQL isn't as optimal as manually written and have the advantage of writing less boilerplate code.

My question is: what are good strategies to improve queries, queryexecution?

I have thought for some options by myself:

1) Is it possible to define several "Entities" for different purposes, like Order for access to the full characteristics of an order and something like FilteredOrder with fewer columns and no resolution of Join-columns? Both would reference the same tables, but one would use all of the columns and the other only some.

2) Use @Query(... native="true") with a selection of all columns, which I want to use. The advantage of that would be, that I would not double my domain-objects and litter my codebase with hundreds of Filtered-Objects. What about paging? Is using pageable in combination with @Query( ...native="true") still possible (I am afraid not).

3) Last but in my eyes "worst"/boilerplate solution: Use JDBCTemplates and do stuff at a lower level.

Are there other options, of which I haven't thought? Thank you for any inspiration on that topic :]

Update: Our current strategy is the following

1) Where possible, I work with select new As I have seen, this works for every Object (be it an Entity or POJO)

2) In combination with database views it is possible to take the best of SQL and ORM. For some usecases it might be of interest to have an aggregated resultset at hand. Defining this resultset as a view makes it easy from the db-perspective to watch the result with a simple select-statement. For the ORM-side this means, you could easily define an entity matching this view and you get the whole ORM-goodness on top: Paging incl.

Answer 1

One solution is to use DTO's:

@Query("select new FilteredOrder(o.name, o.size, o.cost) from Order o where o.id = ?1")
Page<FilteredOrder> findFilteredOrderById(Pageable pageable, String id);

If you want to have entities for some reports generation maybe you should think about using nosql datastore?