Map values returned from Hibernate Native Query to a model

Question

I am quite new to Hibernate. I am using it along-with my Spring Boot project. I want to write native SQL Queries due to the complex joins involved. On execution of the queries the values returned is in the form of a list and there is no simple way to map them to a HashMap.

Right now I am using the below approach which works but is heavily dependent on the sequence of the values returned from the query list.

Is there any simple and efficient way to map this result.

DAO

@Transactional

public List<Object> getAllUsers() {

    Session currentSession = entityManager.unwrap(Session.class);


    String queryString = "select ts.id as id, ts.remarks, concat(s.first_name, ' ',s.last_name) as studentName,\n" + 
            "ts.start_date as startDate, ts.end_date as endDate, ts.status,\n" + 
            "ts.created_at as createdAt, ts.created_by as createdBy, \n" + 
            "ts.updated_at as updatedAt, ts.updated_by as updatedBy\n" + 
            "from TRN_STATUS ts join types lt on ts.type_id = lt.id join users s on s.id = ts.id \n" + 
            "join category ct on ts.cat_id = ct.id\n" + 
            "where ts.tenant_id = 1";
    NativeQuery query = currentSession.createNativeQuery(queryString);

    List<Object> result = (List<Object>) query.getResultList();

    currentSession.close();
    return result;

}

Service

public List<Map<String, Object>> getCount(Optional<String> userId, String userType, String limit, String offset) {

    List<Object> users= userDAO. getAllUsers();
    List<Map<String, Object>> userList = new ArrayList<>();
    Iterator userItr = users.iterator();
    ObjectMapper userMapper = new ObjectMapper();
    Map<String, Object> map;
    UserModel obj = new UserModel();
    while(userItr.hasNext()) {
        Object[] resobj = (Object[]) leaveItr.next();
        obj.setId(String.valueOf(resobj[0]));
        obj.setStartDate(String.valueOf(resobj[1]));
        obj.setEndDate(String.valueOf(resobj[2]));
        obj.setDescription(String.valueOf(resobj[3]));
        obj.setLeaveType(String.valueOf(resobj[4]));
        obj.setCategoryId(String.valueOf(resobj[5]));
        obj.setCategoryName(String.valueOf(resobj[6]));
        obj.setStatus(String.valueOf(resobj[7]));
        obj.setDesignation(String.valueOf(resobj[8]));
        obj.setActionBy(String.valueOf(resobj[9]));
        obj.setStudentName(String.valueOf(resobj[10]));
        obj.setImgUrl(String.valueOf(resobj[11]));

        map = userMapper.convertValue(obj, Map.class);
        userList.add(map);

        logger.info("Value displayed was: "+ map);
    }
return userList;
}

UserModel

import lombok.Data;

public @Data class UserModel {

private String id;
private String startDate;
private String endDate;
private String description;
private String leaveType;
private String categoryId;
private String categoryName;
private String status;
private String designation;
private String actionBy;
private String studentName;
private String imgUrl;
private String createdDate; 
private String hostelName;
private String blockName;
private String roomName;
}

rdas · Accepted Answer · 2020-05-23 17:34:01Z

There is way simple and efficient way to map this result

Instead of creating a method and then mapping the required returned value to a bean class, Just create a Entity class that contains all the fields mapped with the fields which are returned in the query .

Use @NamedNativeQuery for providing the query and @SqlResultSetMapping for mapping the fields which are to be returned after executing the query.

For example

@NamedNativeQuery(
            name = "getTenders",
            query = "SELECT t.id, t.created_at, CONCAT(u.first_name, ' ' , u.last_name) as created_by, t.modified_at, CONCAT(u2.first_name, ' ' , u2.last_name) as modified_by, t.tenders_abbreviation, t.tenders_full_name FROM abc.tenders v\n" + 
                    "LEFT JOIN abc.users u on u.id = v.created_by LEFT JOIN rbac.users u2 on u2.id = v.modified_by",
            resultSetMapping = "tendersMappings"
            )

@SqlResultSetMapping(name = "tendersMappings", entities = {
        @EntityResult(entityClass = TendersEntity.class, fields = {
                @FieldResult(name = "id", column = "id"),
                @FieldResult(name = "created_at", column = "created_at"),
                @FieldResult(name = "modified_at", column = "modified_at"),
                @FieldResult(name = "tenders_abbreviation", column = "tenders_abbreviation"),
                @FieldResult(name = "tenders_full_name", column = "tenders_full_name"),
                @FieldResult(name = "created_by", column = "created_by"),
                @FieldResult(name = "modified_by", column = "modified_by")
                }) 
        })
@Entity
@Table(name = "tenders", schema = "abc")
public class TendersEntity {


    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;

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

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

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

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

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

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


    public TendersEntity() {}

    // setters and getters
}

//DAO class

@Repository
@Transactional
public class TendersDaoImpl implements TendersDao {

    @Autowired
    EntityManager manager;

    @Override
    public List<TendersEntity> getVendors() {
        List<TendersEntity> dataList = new ArrayList<>();
        Query query = manager.createNamedQuery("getTenders");
        try {
            dataList.addAll(query.getResultList());
        } catch (Exception ex) {
            …….// exception code
        }
        return dataList;
    }
}

you have @Table(name = “tenders”, schema = “abc”) i.e there is one table. but the sql is a join of multiple tables. How does the mapping happen?
here tenders is the base table and mapping is done based on the data returned from query

Pratik Joshi · Accepted Answer · 2020-05-23 05:10:47Z

0

You can use SQL Transformers for this:

With native sql returning non-entity beans or Map's is often more useful instead of basic Object[]. With result transformers that is possible.

Example:

List resultWithAliasedBean = s.createSQLQuery(
  "SELECT st.name as studentName, co.description as courseDescription " +
  "FROM Enrolment e " +
  "INNER JOIN Student st on e.studentId=st.studentId " +
  "INNER JOIN Course co on e.courseCode=co.courseCode")
  .addScalar("studentName")
  .addScalar("courseDescription")
  .setResultTransformer( Transformers.aliasToBean(StudentDTO.class))
  .list();

StudentDTO dto =(StudentDTO) resultWithAliasedBean.get(0);

Reference: https://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#d0e13904

edited May 23, 2020 at 5:10

answered May 23, 2020 at 5:03

Pratik Joshi

4395 silver badges14 bronze badges

2 Comments

Anamik Adhikary Over a year ago

What is the contents of StudentDTO?

Pratik Joshi Over a year ago

In your case, It will be UserModel.

Collectives™ on Stack Overflow

Map values returned from Hibernate Native Query to a model

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related