How To Implement Custom Queries Using Spring Data And Jpa

This tutorial will describe how to implement custom JPA methods for dynamically-driven queries in Spring Boot.

Maven package dependencies

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.1.RELEASE</version>
    <relativePath />
  </parent>

  <dependencies>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
  </dependencies>

The JPA interface

Typically JPA is very useful because it removes a lot of implementation boilerplate code. Your DAO interface only needs to extend the JPARepository (or more generic CrudRepository), but the basic CRUD methods do not need to be defined or implement in a concrete class because the concrete implementation is automatically created at runtime.

 

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;

import com.crm.model.User;

@Transactional
public interface UserDao extends JpaRepository<User, Long> {
  
  // ... No need to define any methods here for basic crud operations
}

 

But if you want to start being more specific, JPA allows you to define custom query methods.

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;

import com.crm.model.User;

@Transactional
public interface UserDao extends JpaRepository<User, Long> {
  
  public User findByEmail(String email);
  
  public User findByUserNameAndEmailContaining(String username, String email);

}

The second of the above method queries would result in the following JPQL query:

... where username = ? and email like '%?%';

Click here for more information on JPA method queries.

Dynamic JPA queries

The above JPA query methods are restrictive because they rely on the method and query parameters being known. But what if we need to programmatically define a query without knowing how many parameters will be received or what criteria to use. A common example being search.

JPA allows you to define custom interfaces.

import javax.transaction.Transactional;

import com.crm.model.User;

@Transactional
public interface UserDaoCustom {
  
  public List<User> customMethod(String email);

}

You will then need an implementation class. Note, there is nothing stopping you using some other implementation, like JdbcTemplate here.

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

import org.springframework.stereotype.Repository;

import com.crm.model.User;

@Transactional
@Repository
public class UserDaoImpl implements UserDaoCustom {
  
  @PersistenceContext
  EntityManager em;

  public List<User> customMethod(String email) {
    return em.createQuery("from User", User.class).getResultList();
  }

}

You can then combine your custom interface with the method queries interface to access both the custom methods, query methods and the basic crud methods all at the same time.

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;

import com.crm.model.User;

@Transactional
public interface UserDao extends JpaRepository<User, Long>, UserDaoCustom {
  
  public User findByEmail(String email);
  
  public User findByUserNameAndEmailContaining(String username, String email);

}

You then wire in the UserDao to your service or controller.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.crm.dao.UserDao;
import com.crm.model.User;

@Controller
public class UserController {
  
  @Autowired
  private UserDao userDao;

  @RequestMapping("/searchUsers")
  @ResponseBody
  public String searchUsers(String search) {
    Long userId;
    try {
      User user = userDao.customMethod(search);
      userId = user.getId();
    } catch (Exception ex) {
      return "User not found";
    }
    return "The user id is: " + userId;
  }
...

Note the JPA is very strict with its naming policies. In the above examples note that the first interface userDao can extend a custom interface userDaoCustom, which can in turn be implemented by userDaoImpl.

The use of the suffixes here is very important and must be followed, otherwise, you will get a compiler error.