1

I want to implement a search function with five optional variables and in every combination, so a switch/case is not a possible way. So i can't use the build in spring boot functions, because they are not dynamic (correct me if i'm wrong). I know there is the @query annotation in the crudrepository, but there is no way to write a query with optional parameters? I tried to write my own database access with jpa, without the help of spring boot CrudRepository. I read in the manual this should work: 

@Autowired
@PersistenceContext
private EntityManager em;

@Transactional
public List<Persons>searchPersons(params...){}

But here is the problem, my EntityManager is always null and i have no idea why. I searched some hours and found nothing.

Maybe you guys know a way to write a dynamic SQL query in Spring Boot. Is there a way in the CrudRepository to define optional parameters for the query? Btw i use a postgreSQL database.

Many thanks for your help.

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
Mortalis
  • 23
  • 1
  • 3
  • Your app should have the separated dao methods for each case but not dynamic one, it seems like bad practice. Then service layer should to decide which one dao method to call. You can check for it's optionality there. – WildDev Jun 03 '16 at 08:05
  • You're not instantiating that class yourself (`new WhateverClassThatCodeIsIn()`)? http://stackoverflow.com/questions/19896870/why-is-my-spring-autowired-field-null (read: *"I have a Spring `@Repository` class ..*) – zapl Jun 03 '16 at 08:27
  • @WildDev this is not possible. You mean a switch case or some if cases to decide what to take. But that would be by 5 optional parameters 5! cases. And why should this be bad practice to generate dynamic sql statements? – Mortalis Jun 03 '16 at 08:41
  • @Mortalis, [http://stackoverflow.com/a/3415629/3019024](http://stackoverflow.com/a/3415629/3019024). The logic you're trying to implement is complicated, that indicates about a mistake in your business layer design. – WildDev Jun 03 '16 at 09:54
  • also read this http://stackoverflow.com/questions/3997070/jpa-criteria-tutorial – Grinish Nepal Jun 08 '16 at 01:53

2 Answers2

4

You might want to have a look at Specifications.

See the documentation here

For that to work, your repository interface needs to implement JpaSpecificationExecutor.

Andreas Siegel
  • 1,050
  • 8
  • 9
0

You can use a custom repository (create your own interface, write one Impl class for it and extend your repository by that interface.

You should then have:

PersonRepositoryCustom

PersonRepositoryCustomImpl

Next, you implement a query, using the EntityManager autowired into your repository. You can do this by using JPQL or the JPA 2.1 Criteria API.

For each parameter, have a condition to add it to the query itself, as well as the prepared statement parameters. That way, you can build a dynamic query.

The following thread is related: Best way to create JPA query that might contain a parameter or might not

Community
  • 1
  • 1
Schaka
  • 772
  • 9
  • 20
  • Tried this now. But it doesn't work. My EntityManager is always null. ` @Autowired @PersistenceContext private EntityManager em; ` – Mortalis Jun 09 '16 at 08:44
  • You need an active transaction to inject the entitymanager in a Spring Context. You can do this by annotating your repository with @Transactional, which by default is of type "REQUIRED" (so it creates one if it doesn't exist). – Schaka Jun 09 '16 at 09:40
  • Ok thanks. It seems to work now, but i have to test some more things. I'm calling the PersonsRepositoryImpl from my PersonService with the '@Autowired' annotation. My service have the '@Transactional' and '@Service' annotation. – Mortalis Jun 09 '16 at 10:17