4

I am new to Java and started with Spring Boot and Spring Data JPA, so I know 2 ways on how to fetch data:

  • by Repository layer, with Literal method naming: FindOneByCity(String city);
  • by custom repo, with @Query annotation: @Query('select * from table where city like ?');

Both ways are statical designed.

How should I do to get data of a query that I have to build at run time?

What I am trying to achieve is the possibility to create dynamic reports without touching the code. A table would have records of reports with names and SQl queries with default parameters like begin_date, end_date etc, but with a variety of bodies. Example: 

"Sales report by payment method" | select * from sales where met_pay = %pay_method% and date is between %begin_date% and %end_date%;
Improve this question
6
  • 1
    Can you elaborate on what you want to achieve? I'm a bit puzzled why QueryDsl is deprecated. Care to post a reference to that? Commented Feb 6, 2018 at 12:38
  • spring.io/blog/2015/11/25/… Commented Feb 6, 2018 at 12:39
  • 1
    your first point is not correct. Literal method naming is at repository layer not custom repository. For your requirement you can create custom repository layer and use EntityManger and build dynamic queries Commented Feb 6, 2018 at 12:41
  • @mp911de it says in Spring docs its deprecated, so my boss care not to use it: docs.spring.io/spring-data/jdbc/docs/current/reference/html/… Commented Feb 6, 2018 at 13:11
  • 1
    @PSyLoCKe, QueryDSL support has been deprecated only for Spring JDBC extensions, not for Spring Data JPA. Commented Feb 6, 2018 at 13:22

3 Answers 3

Reset to default
3

The Criteria API is mainly designed for that.
It provides an alternative way to define JPA queries.
With it you could build dynamic queries according to data provided at runtime.

To use it, you will need to create a custom repository implementation ant not only an interface.
You will indeed need to inject an EntityManager to create needed objects to create and execute the CriteriaQuery.
You will of course have to write boiler plate code to build the query and execute it.

This section explains how to create a custom repository with Spring Boot.

About your edit :

What I am trying to achieve is the possibility to create dynamic reports without touching the code. A table would have records of reports with names and SQl queries with default parameters like begin_date, end_date etc, but with a variety of bodies.

If the queries are written at the hand in a plain text file, Criteria will not be the best choice as JPQL/SQL query and Criteria query are really not written in the same way.
In the Java code, mapping the JPQL/SQL queries defined in a plain text file to a Map<String, String> structure would be more adapted.

But I have some doubts on the feasibility of what you want to do. Queries may have specific parameters, for some cases, you would not other choice than modifying the code. Specificities in parameters will do query maintainability very hard and error prone. Personally, I would implement the need by allowing the client to select for each field if a condition should be applied.
Then from the implementation side, I would use this user information to build my CriteriaQuery. And there Criteria will do an excellent job : less code duplication, more adaptability for the query building and in addition more type-checks at compile type.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi David! Can I use it to achieve what is explained in the question edit?
Hey ! My comment was too long. I updated my answer instead.
Thank you, I will use that knowledge you gave me. I already did that kind of approach in another application I've made in 2011 with Delphi. It worked very well and it's used until now to delivery many admin and financial reports to the client. But now I want to move on to next level and make it a web application in Java. Thnkz again!
Hey. You are very welcome. Good luck for your task !
2

Spring-data repositories use EntityManager beneath. Repository classes are just another layer for the user not to worry about the details. But if a user wants to get his hands dirty, then of course spring wouldn't mind.
That is when you can use EntityManager directly.

Let us assume you have a Repository Class like AbcRepository

interface AbcRepository extends JpaRepository<Abc, String> {

}

You can create a custom repository like 

interface CustomizedAbcRepository {
  void someCustomMethod(User user);
}

The implementation class looks like 

class CustomizedAbcRepositoryImpl implements CustomizedAbcRepository {

@Autowired
EntityManager entityManager;


  public void someCustomMethod(User user) {
    // You can build your custom query using Criteria or Criteria Builder
    // and then use that in entityManager  methods
  }
}

Just a word of caution, the naming of the Customized interface and Customized implementating class is very important

Improve this answer

2 Comments

Can't I implement it directly into a JpaRepository?
Hi, how do I autowire CustomizedAbcRepositoryImpl ?
1

In last versions of Spring Data was added ability to use JPA Criteria API. For more information see blog post https://jverhoelen.github.io/spring-data-queries-jpa-criteria-api/ .

Improve this answer

1 Comment

FYI - link is broken

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.