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

---