[FIXED] Why addScalar in Hibernate is not Working?

Issue

Im trying to create a query using NativeQuery but when i'm trying to add into a DTO. It's not recognize DTO.

public List<AbsenceResponse> findAllByUserId1(Long id) {
        List<AbsenceResponse> res = null;
        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT  a.absence_id, a.create_date, a.end_date,a.date_off,a.title,a.description,a.status,a.type,a.enable_date, u.full_name as fullNameOfDepartment");
        sql.append(" FROM absence a ");
        sql.append(" INNER JOIN user u ON u.user_id = a.absence_by ");
        sql.append(" WHERE a.user_id = "+id);

        res = entityManager.unwrap(org.hibernate.Session.class).createNativeQuery(sql.toString())
                .addScalar("absenceId", LongType.INSTANCE)
                .addScalar("createDate", DateType.INSTANCE)
                .addScalar("end_date", DateType.INSTANCE)
                .addScalar("dateOff", IntegerType.INSTANCE)
                .addScalar("title", StringType.INSTANCE)
                .addScalar("description", StringType.INSTANCE)
                .addScalar("status", IntegerType.INSTANCE)
                .addScalar("type", IntegerType.INSTANCE)
                .addScalar("enable_date", DateType.INSTANCE)
                .addScalar("fullNameOfDepartment", StringType.INSTANCE)

                .setResultTransformer(Transformers.aliasToBean(AbsenceResponse.class))
                .list();
        return res;
    }

And this is my AbsenceResponse(DTO)

public class AbsenceResponse {

    private Long absenceId;
    private Date startDate;
    private Date endDate;
    private String title;
    private String description;
    private int status;
    private int type;
    private Date enableDate;
    private String fullNameOfDepartment
}

it's turn this error

java.sql.SQLException: Column 'absenceId' not found.

This is the first time i used NativeQuery. If i have something bad or missing, please comment below. Your comment is helped me alot. Have a good time

Solution

The column returned by the select-query is named absence_id (and not absenceId). You will have a similar error for all the other columns not matching - createDate for example (it should be create_date).

It's a native query, so you need to use the named returned by the native SQL query (and not the name of the attribute in the class).

You can also change the query to:

SELECT  a.absence_id as absenceId, a.create_date as createDate, a.end_date as endDate, a.date_off as dateOff, ...

This way the result of the query will match the attributes in the bean and should fix the issue with the result transformer.

Answered By - Davide D'Alto
Answer Checked By - Katrina (JavaFixing Volunteer)