Spring Data Table of Content




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published. Required fields are marked *

Spring Data Projection

Spring Data query methods usually return one or multiple instances of the aggregate root managed by the repository. However, it might sometimes be desirable to create projections based on certain attributes of those types. Spring Data allows modeling dedicated return types, to more selectively retrieve partial views of the managed aggregates.

Projection is just a way to retrieve certain fields from the database without retrieving all fields. This improves performance as it does not retrieve all fields.

There are 3 different types of projection: Scalar, DTO, Entity projection

Scalar Projection

Scalar projection allows you to select columns you need.

@Repository
public interface BookRepository extends JpaRepository<User, Long> {
 
    @Query("SELECT u.name, u.email FROM Book b")
    List<Object[]> getNameAndEmail();   
}

DTO Projection

DTO projection uses a constructor which Hibernate uses to populate data from the database.

@Repository
public interface UserRepository extends CrudRepository<User, Long> {
 
    @Query("SELECT new com.kaveinga.user.dto.UserDetailDTO(u.firstName, u.lastName) FROM User user WHERE user.firstName = :firstName")
    List<UserDetailDTO> findByFirstName(String firstName);
}

JPA DTO

As long as the DTO class has only one constructor and its parameter names match your entity class’s attribute names, Spring generates a query with the required constructor expression.

public interface UserRepository extends JpaRepository<User, Long> {
    UserDTO findByEmail(String email);

}
public class UserDTO {

    private Long       id;

    private String     uid;

    private String     name;

    private String     email;

    private int        age;

    private Address address;

    public UserDTO(Long id, String uid, String name, String email, int age, Address address) {
        super();
        this.id = id;
        this.uid = uid;
        this.name = name;
        this.email = email;
        this.age = age;
        this.address = address;

    }

    // getters and setters
}

JPA DTO as interface

Here is another way you can retrieve data from the database. Instead of having a DTO class you can use an interface. Your interface only defines getter methods for basic attributes.

public interface UserRepository extends JpaRepository<User, Long> {

    UserView findByUid(String uuid);

}
public interface UserView {

    Long getId();

    String getName();

    String getEmail();

    int getAge();

    String getUid();
    // nested object
    AddressView getAddress();

}

 

September 15, 2020

Spring Data Query to DTO

We will show here how you can map a DTO to a query using constructor.

First create your entity class. Here we will have a User class.

@Entity
@Table(name = "user")
public class User implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id", nullable = false, updatable = false, unique = true)
	private Long id;
	
	@Column(name = "uid", unique = true, nullable=false, updatable=false)
	private String uid;

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

	@Column(name = "email")
	private String email;

	@Column(name = "age")
	private int age;
	
	@JsonIgnoreProperties(value= {"user"})
	@OneToOne(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
	@JoinColumn(name="address_id")
	private Address address;

	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
     // setters and getters
}

Second create your DTO class. In this case we will have a UserDto.

public class UserDto {
	
	private String uid;

	private String name;

	private String email;

	private int age;
	
	private Address address;
	
	public UserDto() {
		this(null,null,null,0);
	}
	
	public UserDto(String uid, String name, String email, int age) {
		this(uid,name,email,age,null);
	}

	public UserDto(String uid, String name, String email, int age, Address address) {
		super();
		this.uid = uid;
		this.name = name;
		this.email = email;
		this.age = age;
		this.address = address;
	}
        // getters and setters
}
public interface UserRepository extends JpaRepository<User, Long> {
	
	@Query("select new com.lovemesomecoding.dto.UserDto(user.uid,user.name,user.email,user.age) from User user where user.id = :id")
    UserDto getUserProfile(@Param("id") Long id);
	
	@Query("select new com.lovemesomecoding.dto.UserDto(user.uid,user.name,user.email,user.age,user.address) from User user where user.id = :id")
    UserDto getUserProfileWithRoles(@Param("id") Long id);
}

As you can see that all fields and values must be loaded into the UserDto through the constructors like this one.

com.lovemesomecoding.dto.UserDto(user.uid,user.name,user.email,user.age,user.address)

May 2, 2019

Spring Data Native Query to DTO

Let’s show how you can map a native SQL query to a DTO directly.

Using AliasToBeanResultTransformer

All the fields in the DTO, in this case UserFullDto, must have the same names as the result set returned from the query. If a query field name is not found in the dto then it will through an exception.

public class UserFullDto {
	
	private String name;

	private String email;

	private int age;
	
	private String state;

	private String zip;

	public UserFullDto() {
		super();
		// TODO Auto-generated constructor stub
	}

        // getters & setters
}
String searchQuery = "SELECT u.name, u.email, u.age, addr.state FROM user as u JOIN address as addr ON u.address_id = addr.id WHERE u.id = :id"; 

Query query = this.entityManager.createNativeQuery(searchQuery).unwrap(org.hibernate.query.Query.class).setResultTransformer(new AliasToBeanResultTransformer( UserFullDto.class));
		
		
query.setParameter("id", userId);

UserFullDto result = (UserFullDto) query.getSingleResult();

Using ResultTransformer

If you don’t want to use the same field names in your dto as the database table then your can manually transform the field values like below.

String searchQuery = "SELECT u.name, u.email, u.age, addr.state, addr.zipcode FROM user as u JOIN address as addr ON u.address_id = addr.id WHERE u.uid = :uid";

@SuppressWarnings("deprecation")
Query query = this.entityManager.createNativeQuery(searchQuery).unwrap(org.hibernate.query.Query.class).setResultTransformer(

  new ResultTransformer() {

	private static final long serialVersionUID = -1L;

	@Override
	public UserFullDto transformTuple(Object[] tuple, String[] aliases) {

								UserFullDto dto = new UserFullDto();
								
								dto.setName((String) tuple[0]);

								dto.setEmail((String) tuple[1]);

								dto.setAge((int) tuple[2]);
								
								dto.setZipcode((String) tuple[4]);

								return dto;
	}

	@Override
	public List transformList(List collection) {
	       return collection;
	}
	});

query.setParameter("uid", uid);

UserFullDto result = (UserFullDto) query.getSingleResult();

Using @NamedNativeQuery

All fields in the entity must be in the select statement which is not efficient. All children objects must have their ids in the select statement like getAllUsers must have address_id for its address child object.

@JsonInclude(value = Include.NON_NULL)
@Entity
@Table(name = "user")
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "getAllUsers",
        query = "SELECT id, name, email, age, address_id, uid FROM user",
        resultClass=User.class
        
    ),
    @NamedNativeQuery(
        name = "getAllUserById",
        query = "SELECT u.id, u.name, u.email " +
                    "FROM user as u " +
                    "WHERE u.id = :id ",
                    resultClass=User.class
    )
})
public class User implements Serializable ...

Source Code on Github

May 1, 2019

Spring Data Custom Select Query with Join

In this tutorial, I am going to show you how to use JOIN in custom spring JPA queries.

The join is always on the id column.

@Query("select distinct user from User user JOIN user.address addr WHERE addr.id = 1;")
	User getUserById(@Param("addressId") Long addressId);

Multiple Joins

@Query("select distinct user from User user JOIN user.account acct JOIN acct.paymentMethod pm WHERE pm.id = 1;")
	User getUserById(@Param("pmId") Long pmId);
April 24, 2019

Spring Data Exclude elements from the Many side of a One To Many relationship.

March 18, 2019