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

Spring from the Trenches: New Like Expressions of Spring Data JPA

Today’s war story talks about the like expression handling of Spring Data JPA.

Although I have written earlier about a better solution for implementing text based search functions, using an external search server like Solr is not a viable option if the implemented search function is rather simple.

Let’s consider the following example.

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 >>

Using the Like Expressions in the Old Way

We have a simple entity called Person which has two fields: id and lastName. The source code of this entity looks as follows:

import javax.persistence.*;

@Entity
@Table(name = "persons")
public class Person {
   
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
   
    @Column(name = "last_name", nullable = false)
    private String lastName;
   
	//Methods are omitted.
}

Let’s assume that we have to implement a search function which returns persons whose last name matches with the given like expression. Since this task is obviously so demanding, we have to implement the search function by using both SQL and JPQL queries. The query method used in the production version of our application is decided after we have figured out which implementation is faster.

After we have implemented the both query methods, the source code of our repository interface looks as follows:

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

public interface PersonRepository extends JpaRepository<Person, Long> {

    @Query(
            value = "SELECT * FROM persons WHERE last_name LIKE :searchTerm",
            nativeQuery = true
    )
    public List<Person> searchWithNativeQuery(@Param("searchTerm") String searchTerm);

    @Query("SELECT p FROM Person p WHERE p.lastName LIKE :searchTerm")
    public List<Person> searchWithJPQLQuery(@Param("searchTerm") String searchTerm);

}

The problem of this approach is that we have to create the like expression on the service layer. The source code of our service implementation looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class RepositoryPersonService implements PersonService {

	private PersonRepository repository;
	
	@Autowired
	public RepositoryPersonService(PersonRepository repository) {
		this.repository = repository;
	}
	
	@Transactional(readOnly=true)
	@Override
	public List<Person> search(String searchTerm) {
		String likeExpression = "%" + searchTerm + "%";
		//Call the correct query method, pass the like expression as method parameter
		//and return the found persons.
	}
}

Although I would not call this a huge problem, the creation of the like expression does not belong to the responsibilities of the service layer. Of course we could solve this problem by adding a custom method to our repository but that would be a bit extreme.

Luckily, Spring Data JPA offers a better to do this. Let’s find out what it is.

A New and Better World

Spring Data JPA version 1.3.1 added a possibility to specify like expressions “inside” the executed query. We can modify our example to use this strategy by following these steps:

  1. Modify the query method and specify the like expressions “inside” the queries.
  2. Clean up the RepositoryPersonService class by removing the like expression creation logic from it.

Let’s move on and find out how we move the like expression creation logic from the service layer to our repository interface.

Modifying the Query Methods

Because we want to find persons whose last name contains the given search term, we must modify the query methods of our repository by adding the character ‘%’ to the start and end of the placeholder (:searchTerm) which is replaced with the given search term when the executed query is constructed.

The source code of the PersonRepository interface looks as follows:

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

public interface PersonRepository extends JpaRepository<Person, Long> {

    @Query(
            value = "SELECT * FROM persons WHERE last_name LIKE %:searchTerm%",
            nativeQuery = true
    )
    public List<Person> searchWithNativeQuery(@Param("searchTerm") String searchTerm);

    @Query("SELECT p FROM Person p WHERE p.lastName LIKE %:searchTerm%")
    public List<Person> searchWithJPQLQuery(@Param("searchTerm") String searchTerm);

}

Note: We can naturally use other like expressions as well:

  • If we want to find persons whose last name starts with the given search term, we must add the character ‘%’ to the end of the placeholder.
  • We can find the persons whose last name ends with the given search term by adding the character ‘%’ to the start of the placeholder.

Enough with the theory. Let’s take a quick look at our new service class.

Cleaning up the Service Class

We can now remove the like expression creation logic from our service class. The source code of the RepositoryPersonService class looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class RepositoryPersonService implements PersonService {

	private PersonRepository repository;
	
	@Autowired
	public RepositoryPersonService(PersonRepository repository) {
		this.repository = repository;
	}
	
	@Transactional(readOnly=true)
	@Override
	public List<Person> search(String searchTerm) {
		//Call the correct query method and and return the results.
	}
}

We are done. Let’s spend a moment summarizing what we have learned.

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 two things:

  • We can specify a like expression “inside” the executed query if we are using Spring Data JPA 1.3.1 or newer.
  • We can use this approach for both native and JPQL queries.

Like I said earlier, this might seem like a minor improvement but we must remember that a clean code base is build by making a lot of small improvements.

A word of warning though. Spring Data JPA 1.3.2 introduced a bug which breaks the backwards compatibility of like expression handling. This means that if we want to use Spring Data JPA 1.3.2, we have to update our queries by using the approach described in this blog post.

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 →

17 comments… add one
  • Hi Petri,

    I eagerly tried your example with a:
    @Query(“SELECT a FROM Admin a WHERE LOWER(a.firstname) LIKE LOWER(%:searchTerm%) OR LOWER(a.lastname) LIKE LOWER(%:searchTerm%) ORDER BY a.lastname ASC, a.firstname ASC”)
    public List search(@Param(“searchTerm”) String searchTerm, Pageable page);

    But it gave me a NPE.

    Kind Regards,

    Reply
    • Hi Stephane,

      I tried using the LOWER keyword in my example application and got NPE as well.

      I think that at the moment using other keywords than LIKE with the new like expressions is not supported by Spring Data JPA (this is just my theory about the situation).

      However, I think that adding for the LOWER keyword would be useful since it is often required that the search is not case sensitive. I should probably create a Jira issue about this.

      Reply
      • Today I learned that it is possible create like expressions by using the CONCAT function of JPQL.

        If you want to create a case insensitive JPQL query which returns all persons whose last name contains the given search term, you can do that by annotating your query method with the following @Query annotation:

        @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) LIKE LOWER(CONCAT('%',:searchTerm,'%'))")

        Remember that you can learn something new every day as long as you keep your eyes open. This is a lesson which I should remember as well.

        Reply
        • Hello Petri,

          I just saw your reply. Thanks for that. I had used a concat function as well and it does work fine against a MySQL database. But it fails against the H2 database. I posted a Jira about this at https://jira.springsource.org/browse/DATAJPA-354

          Kind Regards,

          Reply
          • Hi Stephane,

            which version of H2 database are you using?

            I am asking this because I wrote a blog post about the integration testing of Spring Data JPA repositories and the example application works with the H2 database (it uses the CONCAT function).

          • Hello Petri,

            I’m using this one: com.h2database h2 1.3.164

            Kind Regards,

          • Hi Stephane,

            I used H2 version 1.3.166 and I had no problems with the CONCAT function. You should probably use the latest version of H2 database as well.

          • Thanks for the point Petri, I did upgrade to 1.3.166 and then to 1.3.172 but the error remained the same on both:

            Tests in error:
            testSearch(com.thalasoft.learnintouch.core.jpa.AdminRepositoryTest): JDBC exception on Hibernate data access: SQLException for SQL [select count(admin0_.id) as col_0_0_ from admin admin0_ where lower(admin0_.firstname) like lower((‘%’||?||’%’)) or lower(admin0_.lastname) like lower((‘%’||?||’%’)) or lower(admin0_.email) like lower((‘%’||?||’%’)) or lower(admin0_.login) like lower((‘%’||?||’%’)) order by admin0_.lastname ASC, admin0_.firstname ASC]; SQL state [90016]; error code [90016]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query

            To my noob eye, it looks like a query generation issue and I’d tend to put the ball in Spring Data’s camp, that’s why I raised the above Jira.

            Thanks.

          • Hi Stephane,

            The error with code 90016 is thrown when a column was used in the expression list or the order by clause of a group or aggregate query, and that column is not in the GROUP BY clause.H2 error codes

            Also, I noticed that the query which causes the problem is a count query. Are you maybe trying to paginate the query results of the annotated query? If that is the case, you can specify the count query by setting the countQuery attribute of the @Query annotation.

            Here is an example of this:

            
            @Query(value="SELECT a FROM Admin a WHERE LOWER(a.firstname) " +
            	"LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.lastname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.email) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.login) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "ORDER BY a.lastname ASC, a.firstname ASC",
                    countQuery = "SELECT COUNT(a) FROM Admin a WHERE LOWER(a.firstname) " +
            	"LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.lastname) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.email) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
                    "OR LOWER(a.login) LIKE LOWER(CONCAT('%', :searchTerm, '%'))"
            )
            
            

            Does this solve your problem?

            Also, you might want to consider putting the sorting and pagination logic into the same place. Check this comment for more details about this.

        • It’s really awesome. The kind of bleeding-edge features that they’re now building into JPQL, nowadays ;-)

          Reply
  • Hello Petri,

    It would be nice to have this simple use case explained in the book :-)

    http://forum.springsource.org/showthread.php?138791-Could-not-create-query-metamodel-for-method&p=448433#post448433

    Kind Regards,

    Reply
    • Hi Stephane,

      The problem is that you cannot use the OrderBy keyword in the name of your query method if you want to paginate the results of your query.

      You have two options:

      1. Use web pagination and provide the page.sort and page.sort.dir request parameters.
      2. Create a Sort object manually and pass it as a constructor parameter when you create a new PageRequest object.

      I hope that this answered to your question.

      P.S. My book has an example which demonstrates the pagination of query results which are obtained by using the query generation from method name strategy. However, the example does not state that you cannot use the OrderBy keyword in this situation. If I have a chance to do a second edition, I will add this to the book. Thanks for the feedback!

      Reply
      • Hi Petri,

        Now I get it, Thanks for your patience !

        Kind Regards,

        Reply
        • You are welcome!

          Reply
  • Hi Petri,

    We are having a problem with parameter replacement when using the H2 database that we do not have when running against Oracle.

    Here is our repository query definition:
    public interface RevisionAugementationRepository extends JpaRepository {

    public static final String ELIGIBLE_FOR_PURGE_QUERY = “select * from RevisionAugmentation ra where ra.timestamp < CURRENT_TIMESTAMP(0) – :daysToKeep and 0 = (select count(*) from site_aud sarev where sarev.rev = ra.id and sarev.revtype = 2) and 0 = (select count(*) from node_aud narev where narev.rev = ra.id and narev.revtype = 2) and 0 = (select count(*) from page_aud parev where parev.rev = ra.id and parev.revtype = 2)"

    @Query(nativeQuery = true, value = RevisionAugementationRepository.ELIGIBLE_FOR_PURGE_QUERY)
    List findOlderThan(@Param(“daysToKeep”) String daysToKeep)
    }

    And when run against H2 we get the following exception:

    Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: “?”; SQL statement:
    select * from RevisionAugmentation ra where ra.timestamp < CURRENT_TIMESTAMP(0) – ? and 0 = (select count(*) from site_aud sarev where sarev.rev = ra.id and sarev.revtype = 2) and 0 = (select count(*) from node_aud narev where narev.rev = ra.id and narev.revtype = 2) and 0 = (select count(*) from page_aud parev where parev.rev = ra.id and parev.revtype = 2) [50004-174]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.message.DbException.get(DbException.java:149)
    at org.h2.value.DataType.getDataType(DataType.java:719)
    at org.h2.expression.Operation.optimize(Operation.java:306)
    at org.h2.expression.Comparison.optimize(Comparison.java:179)
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
    at org.h2.command.dml.Select.prepare(Select.java:813)
    at org.h2.command.Parser.prepareCommand(Parser.java:231)
    at org.h2.engine.Session.prepareLocal(Session.java:437)
    at org.h2.engine.Session.prepareCommand(Session.java:380)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:70)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement
    (NewProxyConnection.java:162)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare
    (StatementPreparerImpl.java:161)
    at
    Any suggestions or ideas?

    Thanks,

    Cully.

    Update: I removed the “irrelevant” part of the stack trace – Petri

    Reply

Leave a Comment