3

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

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
jayendra bhatt
  • 1,337
  • 2
  • 19
  • 41

1 Answers1

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!

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • this can be done using addentity as well, but what advantage does addscalar provide ? – jayendra bhatt Jun 22 '15 at 18:07
  • There is only the slight advantage of resolving the parameter type. But the advantages is only marginal as in reality setParameter works just as good. – Vlad Mihalcea Jun 22 '15 at 19:21
  • @VladMihalcea after migrate to hibernate 6 .addScalar("properties", JsonNodeBinaryType.INSTANCE) no working anymore, i have also try .addScalar("properties", new JsonType(JsonNode.class)). but i have error message : cannot resolve method addScalar(String, JsonType), i m using hibernate-types-60:2.21.1. Does exist another solution? – Mançaux Pierre-Alexandre Jun 02 '23 at 14:07
  • If i remove all addscalar all work fine, perhaps i don't need this anymore in hibernate6? – Mançaux Pierre-Alexandre Jun 02 '23 at 14:17
  • @MançauxPierre-Alexandre The current library version is 3.4.1 while you are using 2.2.1. As demonstrated by [this test case](https://github.com/vladmihalcea/high-performance-java-persistence/blob/3c4ca8a524be762e32c4ba1bfa947aa756d25a2a/core/src/test/java/com/vladmihalcea/book/hpjp/hibernate/type/json/PostgreSQLJsonTypeRegistryTest.java#L65), the `addScalar` method works just fine. – Vlad Mihalcea Jun 03 '23 at 09:47
  • @VladMihalcea ok you have migrate the project to new artifactId and the version is effectivily 3.4.2 now. old artifcat is 2.21.1, i will plan migration to new artifact soon. Thanks for the tips – Mançaux Pierre-Alexandre Jun 05 '23 at 07:51
  • @MançauxPierre-Alexandre The project name was changed [in December 2022](https://github.com/vladmihalcea/hypersistence-utils/issues/536) because the scope of the project is much larger than just Hibernate Types. – Vlad Mihalcea Jun 05 '23 at 09:41