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();
}
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
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 ...
May 1, 2019 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