JPA query enhancement

As I mentioned in my answer to this question on stackoverflow , I mentioned some useful tips to enhance your hibernate query performance :

1- use @NamedQuery instead of @Query

2- For reporting queries, don't run it inside a transaction

3- You can set the flush mode to COMMIT if you don't need to flush the persistence context before the query runs

4- check the generated query, take it and run it on SQL developer od TOAD, check its cost and run strategy, you can also consult your DBA if you can enhance it with some native DB functions / provcedures , hence use a native query instead of JPQL query

5- if data returning is large, consider making this query a DB view or materialized view and calling it directly

6- make use of query hints to activate a certain index for example, note that indexes may be ignored in case of JPQL

7- you can use native query if the query hint didn't work on JPQL

8- While comparing the query on SQL Developer with that from the code make sure that you are comparing right , the query might run very quickly initially on DB directly but takes loong time to fetch all the data , and you might be comparing this initial short time with the application data fetch time

9- use fetch size hint according to your provider

10- According to my knowledge, you might escape prepared statement if you use native non parametrized query (thus using manual placeholders and replacing values manually) but generally this should be used with care and avoided as much as possible because of SQL injection vulnerabilities and also disallows the DB query engine from as well as the hibernate engine from precompiling the queries

Gson StackOverFlowError

This usually happens when the class you are converting to json refers to itself ..... most commonly when you are using jpa entities with one to many relations ... where parent refers to many children and child refers again to its parent so that you are able to cascade updates.

Solutions :
1- make the variable in the child transient ... but won't work in case of JPA , because you need the entity attributes not transient
2- make another DTO for JSON transfer that doesn't contain this cyclic dependency and map from/to it ...... but too much effort and error prone
3- the clean way : exclude the variable from parsing by gson , how ? mark all attributes that you want it to be parsed with @Expose annotation .... and then on creation of json object use : 


GsonBuilder.excludeFieldsWithoutExposeAnnotation().create()

Now the non exposed attribute won't be converted in json