How to write custom queries in Spring Data at method level?

Say you want to write a custom JPA query for one of your data requirements.

Spring Data provides great abstraction over JPA to perform CRUD operations.

You can easily retrieve entity objects from database based on field values using methods like ‘findByName’ , ‘findByNameAndAge’ etc in your interface extending Spring Data repositories.

If you need to write custom queries instead , you can do it in three ways:

  1. Maintain the custom queries in a separate ORM mapping file
  2. Write named queries at the entity class level
  3. Use @Query annotation on your repository methods.

This post looks at how to use @Query annotation to write custom queries.

Let’s create a database named DevTeam to hold members of a development team.

Below is the entity class :

package com.fullstackdeveloper.springboot.data.query;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "DEV_TEAM")
public class DevTeam {
	
	
	@Id
	@Column(name = "id")
	private Integer id;
	
	
	@Column(name="name")
	private String name;
	
	
	@Column(name="experience")
	private String experience;
	
	@Column(name="technology")
	private String technology;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getExperience() {
		return experience;
	}

	public void setExperience(String experience) {
		this.experience = experience;
	}

	public String getTechnology() {
		return technology;
	}

	public void setTechnology(String technology) {
		this.technology = technology;
	}

	@Override
	public String toString() {
		return "DevTeam [id=" + id + ", name=" + name + ", experience=" + experience + ", technology=" + technology
				+ "]";
	}
	
	

}

Now let’s create a repository interface by extending Spring Data’s CrudRepository.

package com.fullstackdeveloper.springboot.data.query;

import java.util.List;

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

public interface DevTeamRepository extends CrudRepository<DevTeam, Integer> {
	
	@Query("select d from DevTeam d")
	List<DevTeam> getEntireTeam();
	
	
	@Query("select d from DevTeam d where d.technology = ?1")
	List<DevTeam> findTechMembers(String technology);
	
	
	@Query("select d from DevTeam d where d.technology = :technology")
	List<DevTeam> findTechMembersNamedParam(@Param("technology")String technology);

	
	@Query(value="select * from DEV_TEAM  where TECHNOLOGY = :technology",nativeQuery=true)
	List<DevTeam> findTechMembersNativeQuery(@Param("technology")String technology);


}

As you can see I have written custom methods which are annotated with @Query and the JPA query is passed as a parameter to the annotation.

Few things to notice here:

  • You can use parameter index to pass values for the fields, based on which you want to query data.
    • The method findTechMembers() follows this . It fetches all members who work on a particular technology. The technology is passed as a parameter and mapped with the index 1 (?1) in the query.
    • Here is the query : ” select d from DevTeam d where d.technology = ?1″
  • You can pass named parameters using @Param annotation.
    • The method findTechMembersNamedParam() follows this. It also fetches all members who work on a particular technology. The technology is passed as a named parameter , The parameter name is specified using @Param() annotation.
    • Here is the query: “select d from DevTeam d where d.technology = :technology”
  • You can use native queries to query data
    • To utilize this feature do the following:
      • Pass the native SQL query as a “value” to the @Query annoation
      • Also set nativeQuery flag to true and pass it to @Query annotation

I called these methods from my controller class given below:

package com.fullstackdeveloper.springboot.data.query;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DevTeamController {

	
	@Autowired
	private DevTeamRepository repository;
	
	@GetMapping("/getDevTeam")
	public List<DevTeam> getDevTeam(){
		
		
		return repository.getEntireTeam();
		
	}
	
	@GetMapping("/getTechMembers")
	public List<DevTeam> getTechMembers(@RequestParam
			(name="technology")String technology){
		
		
		return repository.findTechMembers(technology);
		
	}
	
	@GetMapping("/getTechMembersNamed")
	public List<DevTeam> getTechMembersNamed(@RequestParam(name="technology")String technology){
		
		
		return repository.findTechMembersNamedParam(technology);
		
	}
	
	@GetMapping("/getTechMembersNativeQuery")
	public List<DevTeam> getTechMembersNativeQuery(@RequestParam(name="technology")String technology){
		
		
		return repository.findTechMembersNativeQuery(technology);
		
	}
}

Here are the outputs:

getDevTeam API without any query parameter

getTechMembers API with a query parameter ‘technology’

getTechMembersNamed API with the same query parameter “technology” . Here the parameter is passed as a named parameter to the repository method.

getTechMembersNativeQuery API which uses native sql query to fetch data. Here again technology is passed as a query parameter.

I used the in memory database H2.

Here is the entire code :

https://github.com/vijaysrj/spring-boot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s