Learn to write tests which are easy to read and write: get started with Spock Framework.

Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation

The previous part of this tutorial described how we can create database queries from the method names of our query methods.

Although that strategy has its advantages, it has its weaknesses as well. This blog post describes how we can avoid those weaknesses by using the @Query annotation.

We will also implement a simple search function that has two requirements:

  • It must return todo entries whose title or description contains the given search term.
  • The search must be case insensitive.

Let’s get started.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

Creating Query Methods

We can configure the invoked database query by annotating the query method with the @Query annotation. It supports both JPQL and SQL queries, and the query that is specified by using the @Query annotation precedes all other query generation strategies.

In other words, if we create a query method called findbyId() and annotate it with the @Query annotation, Spring Data JPA won’t (necessarily) find the entity whose id property is equal than the given method parameter. It invokes the query that is configured by using the @Query annotation.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

Example:

Let’s assume that our repository interface looks as follows:

import org.springframework.data.repository.Repository;
 
import java.util.Optional;
 
interface TodoRepository extends Repository<Todo, Long> {
 
 	@Query("SELECT t FROM Todo t WHERE t.title = 'title'")
	public List<Todo> findById();
}

Even though the findById() method follows the naming convention that is used to create database queries from the method name of the query method, the findById() method returns todo entries whose title is ‘title’, because that is the query which is specified by using the @Query annotation.

Let’s find out how we can create both JPQL and SQL queries with the @Query annotation.

Creating JPQL Queries

We can create a JPQL query with the @Query annotation by following these steps:

  1. Add a query method to our repository interface.
  2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation.

The source code of our repository interface looks as follows:

import org.springframework.data.repository.Repository;
 
import java.util.Optional;
 
interface TodoRepository extends Repository<Todo, Long> {
 
 	@Query("SELECT t FROM Todo t WHERE t.title = 'title'")
	public List<Todo> findByTitle();
}

Creating SQL Queries

We can create a SQL query with the @Query annotation by following these steps:

  1. Add a query method to our repository interface.
  2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation’s value attribute.
  3. Set the value of the @Query annotation’s nativeQuery attribute to true.

The source code of our repository interface looks as follows:

import org.springframework.data.repository.Repository;
 
import java.util.Optional;
 
interface TodoRepository extends Repository<Todo, Long> {
 
 	@Query(value = "SELECT * FROM todos t WHERE t.title = 'title'",
			nativeQuery=true
	)
	public List<Todo> findByTitle();
}

Let’s move on and find out how we can create a query method that fulfils the requirements of our search function.

Implementing the Search Function

This section describes how we can implement a case insensitive search function that returns all todo entries whose title or description contains the given search term.

Implementing the Search Function With JPQL

We can create the JPQL query that fulfils the requirements of our search function by following these steps:

  1. Create a query method that returns a list of Todo objects.
  2. Annotate the method with the @Query annotation.
  3. Create the JPQL query which uses named parameters and returns todo entries whose title or description contains the given search term (remember to ignore case). Set the created query as the value of the @Query annotation.
  4. Add one method parameter to the query method and configure the name of the named parameter by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    @Query("SELECT t FROM Todo t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
    List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm);
}

Let’s find out how we can implement our search function by using SQL.

Implementing the Search Function With SQL

We can create the SQL query that fulfils the requirements of our search function by following these steps:

  1. Create a query method that returns a list of Todo objects.
  2. Annotate the method with the @Query annotation.
  3. Create the invoked SQL query by following these steps:
    1. Create the SQL query which uses named parameters and returns todo entries whose title or description contains the given search term (remember to ignore case). Set the created query as the value of the @Query annotation’s value attribute.
    2. Set the value of the @Query annotation’s nativeQuery attribute to true.
  4. Add one method parameter to the query method and configure the name of the named parameter by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    @Query(value = "SELECT * FROM todos t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))",
            nativeQuery = true
    )
    List<Todo> findBySearchTermNative(@Param("searchTerm") String searchTerm);
}

Let’s move on and find out when we should create our database queries with the @Query annotation.

When Should We Use the @Query Annotation?

The @Query annotation has the following benefits:

  • It supports both JPQL and SQL.
  • The invoked query is found above the query method. In other words, it is easy to find out what the query method does.
  • There is no naming convention for query method names.

The @Query annotation has the following drawbacks:

  • There is no support for dynamic queries.
  • If we use SQL queries, we cannot change the used database without testing that our SQL queries work as expected.

When we think about the pros and cons of the @Query annotation, it becomes clear that the query methods that use this strategy aren’t as easy to write or read as the query methods that use the query generation from the method name strategy.

However, these query methods have two crucial advantages:

  1. If we need to find out what database query is invoked by our query method, we can find the invoked query above the query method.
  2. The code that uses our query methods is easy to read because we don’t have to use long method names.

Thus, if we don’t want to use the query generation from the method name strategy, because the names of our query methods would be too long, we should use the @Query annotation.

Let’s move on and summarize what we learned from this blog post.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

Summary

This blog post has taught us three things:

  • The database queries specified by using the @Query annotation precedes all other query generation strategies.
  • The @Query annotation supports both JPQL and SQL queries.
  • If we cannot create our database queries from the method names of our query methods, because the method names would be too long, we should create them by using the @Query annotation.

The next part of my Spring Data JPA tutorial describes how we can create database queries by using named queries.

P.S. You get can get the example application of this blog post from Github.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

27 comments… add one
  • Thanks very іnteresting blog!

    Reply
    • You are welcome!

      Reply
  • This is the first tym m reading about “Spring Data JPA” and this blog has given me crystal clear idea about the topic.
    Thanks :)

    Reply
    • You are welcome! Also, thank you for your kind words. I really appreciate them.

      Reply
  • Your tutorials have been very helpful. I’ve been following along with my own tables/entities, and I’m having an issue I haven’t been able to solve at this point. My table is named poc_site, and my entity class is Site. I can’t use any of these as queries:

     
    "select s from Site s where s.site_name = 'Test Site'
    "select s from PocSite s where s.site_name = 'Test Site'"
    "select s from Poc_site s where s.site_name = 'Test Site'"
    

    I've been googling for how to determine the appropriate name to enter, and haven't found anything... how are these names determined here?
    Thanks!

    Reply
    • Actually, I figured out my issue…

      Reply
      • Hi Marissa,

        I am happy to hear that you where able to solve your problem. I assume that your problem was that the site_name is the name of the database column and not the name of the entity class’ field. Am I correct?

        Reply
  • Hi Petri,
    Thank You for the blog.
    How we can give setfetchsize with @Query.
    Thanks in advance

    Reply
  • thanks a lot! this passage in the custom query gave me headaches before I read this: LIKE LOWER(CONCAT(‘%’,:searchTerm, ‘%’))

    Now its clear!
    :)

    Reply
    • You are welcome! I am happy to hear that this blog post was useful to you.

      Reply
  • Hey Petri, your blog is awesome. It is being very useful to me and my projects.. I want to ask you if could you make a post about calling stored procedures that returns data (for example a list of articles) with JPA (Spring or Hibernate).

    Thank you!

    Reply
  • Hi Petri!

    Is there a way to eager load enities wher usin native queries?

    Assume EntityA has a list of EntityB and a list of EntityC. What i want is:

    @Query(value = “SELECT a.*, b.*, c.* ” +
    “FROM A a ” +
    “JOIN B b ON b.idA = a.id ” +
    “JOIN C c ON c.idA = a.id ” +
    “WHERE a.title = ‘title'”,
    nativeQuery=true
    )
    public EntityA findByTitle();

    i would like thies method to return the full graph(A’s with its B’s and C’s), and not only A’s, lazy loading B’s and C’s.

    Reply
    • Hi,

      I have to admit that I don’t know if JPA supports this. However, it is possible to do this if you are using Hibernate. Unfortunately you have to use Hibernate specific API. If you want to do this, you should check out this blog post.

      Reply
  • Hello,

    I am having problems getting Page results for generic repository.

    
    // find all by username - pageable
    Page findByUsername(final String username,Pageable pageable);  returns empty.
    
    

    also

    
    @Query("select d from #{#entityName} d where d.username= :username")
    Page findByUsername(@Param("username")final String username,Pageable pageable);
    
    

    Interesting thing is if i use List everything works just fine.
    Do you have any idea how to get around this issue?

    Thanks.

    Reply
    • Let me correct myself. It is not generic repository, rather another interface with custom methods.

      public interface MyRepository extends jpaRepository,IRepositoryWithUsername

      then

      
      public interface IRepositoryWithUsername {
      
      	@Query("select count (*) from #{#entityName} a where a.username=:username")
      	Long countForUsername(@Param("username")final String username);
      	
      	// find one
      	T2 findByIdAndUsername(final Long id,final String username);
      	
      	// find all by username - pageable
      	Page findByUsername(final String username,Pageable pageable);
      	
      	// find all by username 
      	List findByUsername(final String username);
      
      	// find all by username Sort
      	Iterable findAllByUsername(final String username,Sort sort);
      
      	T2 findOneByUsername(final String username);
      }
      
      

      Works fine for one instance and also List but Slice and Page are returning empty result.

      Reply
      • Hi,

        Hi have a vague memory that I had a similar problem (although I used the @Query annotation). If I remember correctly, I solved it by specifying the count query. You can specify it by annotating your query method with the @Query annotation and setting the count query as the value of the @Query annotation’s countQuery attribute.

        Let me know if this solved your problem.

        Reply
  • Hi Dear,

    Your link has helped me a lot but I have a question.
    I want to write the native query in @Query annotation with where clause customized like select a.name from tableA a where [param1]=?1 and [param1]=?2

    If i do not send a param1 value it should not restrict the query upon it.

    Reply
    • Hi,

      Unfortunately you cannot use that query with the @Query annotation. You have to use either JPA criteria API or Querydsl.

      Reply
      • Hi Dear,

        I am using StringBuilder to use with createNamedQuery but on line javax.persistence.Query query = em.createNamedQuery(queryBuilder.toString()); it returns null pointer.

        do not know what is causing the exception.

        here is my code.

        
        @Repository
        @Transactional
        public class JpaUserDaoImpl implements UserDao 
        {
        	@PersistenceContext
        	private EntityManager em;
        	
        	@Override
        	@Transactional(readOnly=true)
        	public List getCustomerReport(String custId, String brandId) {
        		
        		 StringBuilder queryBuilder = new StringBuilder(
        			        "SELECT a FROM tableA c WHERE ");
        			    List paramList = new ArrayList();
        			    if(custId!=null && !custId.isEmpty()){
        			    paramList.add(" c.customer_id = '?'"
        			        .replace("?", custId));
        			    }
        			    if(brandId!=null && !brandId.isEmpty()){
        			    paramList.add(" o.brand_id = '?'"
        			        .replace("?", brandId));
        			    }			 
        			    Iterator itr = paramList.iterator();
        			    while(itr.hasNext()) {
        			        queryBuilder.append(itr.next());			       
        			        if (itr.hasNext()) {
        			            queryBuilder.append(" and ");
        			        }
        			    }
        ----> NPE       javax.persistence.Query query = em
        						.createNamedQuery(queryBuilder.toString()); 
        
        			    List resultList = (List)query.getResultList();
        			    return resultList;
        	}
        }
        
        

        Project based on Spring boot Spring JPA repositories.

        Thanks

        Reply
        • I cannot find any obvious bugs from your code. If the NullPointerException is thrown from that line, either EntityManager or StringBuilder is null. I recommend that you use a debugger since it will tell you which object is null.

          Also, since you are writing a dynamic query, you should use JPA Criteria API or Querydsl. These tools will make your code cleaner (if you use Querydsl) and safer.

          Reply
  • Dear Petri,
    I have just implemented the JDBC through which run queries and returning the list.
    See the chunk of code.
    [
    Connection con = getConnection();
    ResultSet resultset = getStatement(con).executeQuery(queryBuilder.toString());

    CustomerReportBean customerReportBean = new CustomerReportBean();
    while (resultset.next()) {
    customerReportBean = new CustomerReportBean();
    if (resultset.getString(1) != null) {
    customerReportBean.setDate(resultset.getString(1));
    customerReportList.add(customerReportBean);
    }
    // Closed connection
    con.close();
    ]

    private Connection getConnection() {
    DataSource ds = (DataSource) ApplicationContextProvider.getApplicationContext().getBean(“dataSource”);
    Connection con = null;
    try {
    con = ds.getConnection();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    return con;
    }

    Kindly let me know is it a good practice or not, will it reduce the efficiency of the application.
    Just i am doing for reporting purpose as i am not using any reporting tool.

    Thanks

    Reply
    • Hi,

      Spring Framework provides an excellent support for invoking SQL queries. If you use the JdbcTemplate (or NamedParameterJdbcTemplate) you don’t have to write so much boilerplate code. This keeps your code a lot cleaner. In other words, use the components provided by Spring Framework (you won’t be disappointed).

      Reply
      • ✢ ✥ ✦ ✧ ❂ ❉ ✱ ✲ ✴ ✵ ✶ ✷ ✸ ❇ ✹ ✺ ✻ ✼ ❈ ✮ ✡
        Dear Petri,
        Thank you very much …for helping…
        have implemented JdbcTemplate. now my code looks a lot cleaner .
        ✢ ✥ ✦ ✧ ❂ ❉ ✱ ✲ ✴ ✵ ✶ ✷ ✸ ❇ ✹ ✺ ✻ ✼ ❈ ✮ ✡

        Reply
        • You are welcome!

          Reply

Leave a Comment