0

Normally I use annotiations:@Query("SELECT c FROM Country c") with JpaRepositoryor predefined methods like findAll

but in my case I want to generate dynamic query. 

String baseQuery =SELECT c FROM Country c` if(age!=null) baseQuery+="WHERE c.age=20"

I need to perform same query from code level like this:

Query q1 = em.createQuery("SELECT c FROM Country c");

but I dont use EntityManager in spring boot

How can I generate query from code level?

Improve this question
5
  • 1
    Sorry, but what prevents you from calling the findAll() method of your repository? Are you asking how to execute a JPA query when not using Spring Data JPA? If so, what have you tried, and what's the concrete problem you're facing? Commented Dec 3, 2016 at 15:47
  • I cant use findAll() because I want generate dynamic query. query must depends on the input values Commented Dec 3, 2016 at 17:07
  • And what problem are you facing? Have you read the spring-data-jpa documentation to understand how to create custom repository methods? docs.spring.io/spring-data/jpa/docs/1.7.2.RELEASE/reference/… Commented Dec 3, 2016 at 17:09
  • I did not know how to get access to EntityManager, but I think anwser is create new class MyRepositoryImpl and define there EntityManager entityManage. Am I right? Commented Dec 3, 2016 at 17:16
  • The entity manager can be injected using @PersistenceContext private EntityManager em;. Commented Dec 3, 2016 at 17:20

2 Answers 2

Reset to default
1

If you would like to create dynamic queries from code you can take advantage of Spring's JdbcTemplate. Using spring boot it is as simple as injecting JdbcOperations bean to your repository class (assuming you have provided spring-boot-starter-jdbc module to your project).

But remember! This solution uses SQL, not JPQL. That's why you have to use proper tables and columns names in queries and properly map result to objects (i.e. using RowMapper)

This simple example worked fine for me (with different entity, but in same manner - I've adapted it to your example):

@Repository public class CountryRepository { @Autowired private JdbcOperations jdbcOperations; private static String BASIC_QUERY = "SELECT * FROM COUNTRY"; public List<Country> selectCoutry(Long age){ String query = BASIC_QUERY; if (age != null){ query += " WHERE AGE = "; query += age.toString(); } //let's pretend that Country has constructor Conutry(String name, int age) return jdbcOperations.query(query, (rs, rowNum) -> { return new Country(rs.getString("NAME"), rs.getInt("AGE");} ); }; }

Then in service or whatever you inject CountryRepository and call method.

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

Comments

0

Since you're using Spring Boot, you can use Spring Data to create queries in your repository:

@Repository public interface CountryRepository extends JpaRepository<Country, Long> { }

Not a 100% on syntax, but should be something similar. Now you can autowire this class:

@Autowired public CountryRepository countryRepo;

And all basic methods are already available to you like:

countryRepo.findOne(id); countryRepo.find();

If you want to make more advanced queries, you can use Spring Data e.g.:

@Repository public interface CountryRepository extends JpaRepository<Country, Long> { public Country findByNameAndContinent(String name, String continent); }

This is just an example (a stupid one) of course and assumes your Country class has the field names 'name' and 'continent' and both are strings. More is available here: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/ Section 5.3 more specifically.

PS: Make sure your Country class has the @Entity annotation

Improve this answer

1 Comment

I'm sorry but I badly formulated the question, question was how to create custom query. Now I know I can do it by Implementation of custom repository functionality: docs.spring.io/spring-data/jpa/docs/1.7.2.RELEASE/reference/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.