Why to use the Hibernate Query addScalar method?

Question

I have a query in native sql , something like :

SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");

the query result can be used to set the employee object . On the similar lines , i have the same query appended with addscalar(entity_colname).

query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee")
        .addScalar("emp_id", new LongType())
        .addScalar("emp_name", new StringType())
        .addScalar("emp_salary", new DoubleType());

here also we have to obtain the result in the similar way , then what is the advantage of using addscalar?

Regards Jay

Answer 1

When you don't need addScalar

In your example, you don't really need to use addScalar.

If you map the response to a DTO, like the following EmployeeDTO:

public class EmployeeDTO {
    private final Long id;
    private final String name;
    private final Double salary;

    public EmployeeDTO(Long id, String name, Double salary) {
        this.id = id;
        this.name = name;
        this.salary = salary;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Double getSalary() {
        return salary;
    }
}

Then, the following SQL query could fetch the EmployeeDTO just fine:

List<EmployeeDTO> employeeDTOs = entityManager.createNativeQuery("""
    SELECT
       emp_id AS id,
       emp_name AS name,
       emp_salary AS salary
    FROM employee
    """)
.getResultList();

Notice that we used column aliases so that Hibernate can match the DTO property we want to set.

When you need addScalar

Assuming you have a Book entity that has a JSON properties attribute:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(typeClass = JsonNodeBinaryType.class, defaultForType = JsonNode.class)
public static class Book {

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    @Column(columnDefinition = "jsonb")
    private JsonNode properties;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public JsonNode getProperties() {
        return properties;
    }

    public void setProperties(JsonNode properties) {
        this.properties = properties;
    }
}

The JsonNodeBinaryType is provided by the Hypersistence Utils open-source project.

Now, when executing a native SQL query that fetched the JSON properties column:

JsonNode properties = (JsonNode) entityManagercreateNativeQuery("""
    SELECT properties
    FROM book
    WHERE isbn = :isbn
    """)
.setParameter("isbn", "978-9730228236")
.getSingleResult();

Hibernate throws the following exception:

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

This is because Hibernate does not know how to transform the jsonb column to a JsonNode Java object since it lacks a Hibernate-native JSON Type.

But if we call addScalar and provide the Hibernate Type:

JsonNode properties = (JsonNode) entityManager.createNativeQuery("""
    SELECT properties
    FROM book
    WHERE isbn = :isbn
    """)
.setParameter("isbn", "978-9730228236")
.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("properties", JsonNodeBinaryType.INSTANCE)
.getSingleResult();

Then the query will run just fine!