Spring data jpa: projection, return custom object from jpa query

In this post, I am going to explain how to project specific columns from repository methods. Projection is very helpful, if you are interested only in specific columns of an entity.

 

Interface closed projection

Create an interface with only getter methods that you are interested to extract from the entity.

 

UserBasicInfo.java

public interface UserBasicInfo {
  public Integer getId();

  public String getFirstName();

  public Set<Role> getRoles();

}

 

Create a repository method with the return type as this interface ‘UserBasicInfo’.

public interface UserRepository extends CrudRepository<User, Integer> {

  List<UserBasicInfo> findByIdIn(List<Integer> ids);

}

 

Interface open projection

In open projection model, you can use SpEL expression to define new property from existing properties. For example, I can create fullName property by combining firstName and lastName.

 

UserBasicInfoOpenProjection.java

public interface UserBasicInfoOpenProjection {
	public Integer getId();

	@Value("#{target.firstName} #{target.lastName}")
	public String getFullName();

	public Set<Role> getRoles();
}

 

Create a repository method with the return type as this interface ‘UserBasicInfoOpenProjection’.

 

List<UserBasicInfoOpenProjection> findByFirstName(String firstName);

 

Approach 3: Dynamic projection.

Just add a repository method with generics.

<T> List<T> findByFirstName(String firstName, Class<T> tClass);

 

Approach 4: Class based projection.

Define a class with the properties of your interest and make sure you defined a parameterized constructor to initialize instance properties.

public class UserBasicDetails {
	private Integer id;
	private String firstName;

	public UserBasicDetails(Integer id, String firstName) {
		super();
		this.id = id;
		this.firstName = firstName;
	}

	.......
	.......
}

 

Write a repository method to populate UserBasicDetails.

@Query("select new com.sample.app.model.UserBasicDetails(u.id, u.firstName) from User u where u.firstName=:firstName")
List<UserBasicDetails> getUsersByFirstName(@Param("firstName") String firstName);

 

Find the below working application.

 

Step 1: Create new maven project ‘custom_object_from_jpa_query’.

 

Step 2: Update pom.xml with maven dependencies.

 

pom.xml

 

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.sample.app</groupId>
	<artifactId>custom_object_from_jpa_query</artifactId>
	<version>1</version>

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

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>

		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-annotations</artifactId>
		</dependency>


	</dependencies>
</project>

Step 3: Create application.properties file under src/main/resources folder.

 

application.properties

logging.level.root=WARN
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## JPA specific properties
# Creates the schema, destroying previous data.
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

#spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

Step 4: Create package 'com.sample.app.model' and define User, Role and UserBasicDetails classes.

 

Role.java

package com.sample.app.model;

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

@Entity
@Table(name = "roles")
public class Role {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "ACCOUNT_ID")
	private Integer id;

	@Column(name = "ROLE_NAME")
	private String roleName;

	public Role() {

	}

	public Role(String roleName) {
		super();
		this.roleName = roleName;
	}

	public Integer getId() {
		return id;
	}

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

	public String getRoleName() {
		return roleName;
	}

	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}

	@Override
	public String toString() {
		return "Role [id=" + id + ", roleName=" + roleName + "]";
	}

}

User.java

package com.sample.app.model;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonManagedReference;

@Entity
@Table(name = "USERS")
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "USER_ID")
	private Integer id;

	@Column(name = "FIRST_NAME")
	private String firstName;

	@Column(name = "LAST_NAME")
	private String lastName;

	@Column(name = "AGE")
	private Integer age;

	@Column(name = "ADDRESS")
	private String address;

	@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@JoinColumn(name = "MY_USER_ID")
	@JsonManagedReference
	private Set<Role> roles;

	public User() {

	}

	public User(String firstName, String lastName, Integer age, String address, Set<Role> roles) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.age = age;
		this.address = address;
		this.roles = roles;
	}

	public Integer getId() {
		return id;
	}

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

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Set<Role> getRoles() {
		return roles;
	}

	public void setRoles(Set<Role> roles) {
		this.roles = roles;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", age=" + age + ", address="
				+ address + ", roles=" + roles + "]";
	}

}

UserBasicDetails.java

package com.sample.app.model;

public class UserBasicDetails{
	private Integer id;
	private String firstName;

	public UserBasicDetails(Integer id, String firstName) {
		super();
		this.id = id;
		this.firstName = firstName;
	}

	public Integer getId() {
		return id;
	}

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

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

}

Step 5: Create package 'com.sample.app.repository.interfaces' and define interfaces UserBasicInfo and UserBasicInfoOpenProjection.

 

UserBasicInfo.java

package com.sample.app.repository.interfaces;

import java.util.Set;

import com.sample.app.model.Role;

public interface UserBasicInfo {
	public Integer getId();

	public String getFirstName();

	public Set<Role> getRoles();

}

UserBasicInfoOpenProjection.java

package com.sample.app.repository.interfaces;

import java.util.Set;

import org.springframework.beans.factory.annotation.Value;

import com.sample.app.model.Role;

public interface UserBasicInfoOpenProjection {
	public Integer getId();

	@Value("#{target.firstName} #{target.lastName}")
	public String getFullName();

	public Set<Role> getRoles();
}

Step 6: Create a package ‘com.sample.app.repository’ and define UserRepository interface.

 

UserRepository.java

package com.sample.app.repository;

import java.util.List;

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

import com.sample.app.model.User;
import com.sample.app.model.UserBasicDetails;
import com.sample.app.repository.interfaces.UserBasicInfo;
import com.sample.app.repository.interfaces.UserBasicInfoOpenProjection;

public interface UserRepository extends CrudRepository<User, Integer> {

	List<UserBasicInfo> findByIdIn(List<Integer> ids);

	List<UserBasicInfoOpenProjection> findByFirstName(String firstName);

	<T> List<T> findByFirstName(String firstName, Class<T> tClass);

	@Query("select new com.sample.app.model.UserBasicDetails(u.id, u.firstName) from User u where u.firstName=:firstName")
	List<UserBasicDetails> getUsersByFirstName(@Param("firstName") String firstName);

}

Step 7: Define App class in com.sample.app package.

 

App.java

package com.sample.app;

import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.transaction.annotation.Transactional;

import com.sample.app.model.Role;
import com.sample.app.model.User;
import com.sample.app.model.UserBasicDetails;
import com.sample.app.repository.UserRepository;
import com.sample.app.repository.interfaces.UserBasicInfo;
import com.sample.app.repository.interfaces.UserBasicInfoOpenProjection;

@SpringBootApplication
public class App {

	public static void main(String args[]) {
		SpringApplication.run(App.class, args);
	}

	private static void print(List<UserBasicInfo> usersInfo) {
		for (UserBasicInfo info : usersInfo) {
			System.out.println(info.getId() + "," + info.getFirstName());

			for (Role role : info.getRoles()) {
				System.out.println(role);
			}
		}
	}

	private static void printBasicInfo(List<UserBasicInfoOpenProjection> usersInfo) {
		for (UserBasicInfoOpenProjection info : usersInfo) {
			System.out.println(info.getId() + "," + info.getFullName());

			for (Role role : info.getRoles()) {
				System.out.println(role);
			}
		}
	}

	private static void printBasicDetails(List<UserBasicDetails> usersBasicDetails) {
		for (UserBasicDetails info : usersBasicDetails) {
			System.out.println(info.getId() + "," + info.getFirstName());

		}
	}

	@Bean
	@Transactional
	public CommandLineRunner demo(UserRepository userRepository) {
		return (args) -> {

			Role role1 = new Role("Admin");
			Role role2 = new Role("Service_user");
			Role role3 = new Role("dept_admin");

			Set<Role> set1 = new HashSet<>();
			Set<Role> set2 = new HashSet<>();
			Set<Role> set3 = new HashSet<>();

			set1.add(role1);
			set2.add(role2);
			set3.add(role3);

			User user1 = new User("Krishna", "Gurram", 31, "Bangalore", set1);
			User user2 = new User("Sailu", "PTR", 32, "Hyderabad", set2);
			User user3 = new User("Gopi", "Battu", 32, "Canada", set3);

			userRepository.saveAll(Arrays.asList(user1, user2, user3));

			List<UserBasicInfo> info = userRepository.findByIdIn(Arrays.asList(1, 2));

			System.out.println("\nApproach 1: Printing user basic information");
			print(info);

			List<UserBasicInfoOpenProjection> usersBasicInfo = userRepository.findByFirstName("Krishna");
			System.out.println("\nApproach 2: Printing user basic information");
			printBasicInfo(usersBasicInfo);

			info = userRepository.findByFirstName("Krishna", UserBasicInfo.class);
			System.out.println("\nApproach 3: Dynamic projection");
			print(info);
			System.out.println();

			usersBasicInfo = userRepository.findByFirstName("Krishna", UserBasicInfoOpenProjection.class);
			printBasicInfo(usersBasicInfo);

			System.out.println("\nApproach 4: Class projection");
			List<UserBasicDetails> usersBasicDetails = userRepository.getUsersByFirstName("Krishna");
			printBasicDetails(usersBasicDetails);

		};
	}

}

Total project structure looks like below.

Run App.java, you will see below messages in console.

Approach 1: Printing user basic information
1,Krishna
Role [id=1, roleName=Admin]
2,Sailu
Role [id=2, roleName=Service_user]

Approach 2: Printing user basic information
1,Krishna Gurram
Role [id=1, roleName=Admin]

Approach 3: Dynamic projection
1,Krishna
Role [id=1, roleName=Admin]

1,Krishna Gurram
Role [id=1, roleName=Admin]

Approach 4: Class projection
1,Krishna

You can download the application from below github link.

https://github.com/harikrishna553/springboot/tree/master/jpa/custom_object_from_jpa_query

 

 

 

Previous                                                    Next                                                    Home