CODEWITHSUNDEEP
javaspringhibernatespring-dataspring-data-jpa
Kamil Nękanowicz
Kamil Nękanowicz

Reputation: 6532

How to use createQuery with spring boot

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?

Upvotes: 0

Views: 9529

Answers (2)

Jakub Ch.
Jakub Ch.

Reputation: 3717

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.

Upvotes: 1

Roel Strolenberg
Roel Strolenberg

Reputation: 2950

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

Upvotes: 0

Related Questions