Hibernate native query – char(3) column

by

It looks like Hibernate reads value of type CHAR(n) as Character. Try to cast it to VARCHAR(n):

Query q2 = em.createNativeQuery(
    "select cast(sc_cur_code as VARCHAR2(3)), sc_amount from sector_costs");

When using Hibernate via Session interface, you can explcitly set a type of result with addScalar() instead (also accessible via unwrap() in JPA 2.0):

Query q2 = em.createNativeQuery(
    "select sc_cur_code, sc_amount from sector_costs");
q2.unwrap(SQLQuery.class).addScalar("sc_cur_code", StringType.INSTANCE);

There are plenty of unresolved issues related to this problem in Hibernate JIRA, starting from HHH-2220.

Here is an explanation by Max Rydahl Andersen from HHH-2220’s comments:

Currently Hibernate supports a kind of “automagic” mapping from SQL types to Hibernate/Java types – because of the many ambiguities in doing such mapping it will sometime not match what you actually want.

That is why we always recommend to use explicit addScalar OR if you don’t want that all over your code use the subclassing of Dialect to dictate which of the multiple possible mappings do you want.

The issue with CHAR is the most problematic one, but it is not easy to fix – we would need a registerType(type, from, to, typename) to map a range instead of a specific length…but even then you could bump into mapping ambiguities (E.g. sometime you want an array other times string etc.) Hence using .addScalar is recommended for any native sql querying – depending on automatic discovery will always be risky and should only be used to a minimum.

If you have your native query described in Hibernate mappings configuration file, then you need to define <return-scalar ...> for each value returned. Note: You have to enumerate all returned values, as when you define the return types explicitly, autodiscovery is switched off and only declared columns are returned.

<sql-query name="myQuery">
    <query-param name="days" type="int" />
    <return-scalar column="count" type="int" />
    <return-scalar column="section_name" type="string" />
    <![CDATA[select count(id) as count, section_name from document where days <= :days]]>
</sql-query>

More Related Contents:

  1. having an exception when saving oracle type object in some entity
  2. How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA
  3. Force refresh of collection JPA entityManager
  4. PersistentObjectException: detached entity passed to persist thrown by JPA and Hibernate
  5. Difference between FetchType LAZY and EAGER in Java Persistence API?
  6. How to map an entity field whose name is a reserved word in JPA
  7. Why is Hibernate Open Session in View considered a bad practice?
  8. What’s the difference between JPA and Hibernate? [closed]
  9. How to convert a Hibernate proxy to a real entity object
  10. What is the solution for the N+1 issue in JPA and Hibernate?
  11. JPA CascadeType.ALL does not delete orphans
  12. Wrong ordering in generated table in jpa
  13. Solve Hibernate Lazy-Init issue with hibernate.enable_lazy_load_no_trans
  14. Configure hibernate (using JPA) to store Y/N for type Boolean instead of 0/1
  15. what is @JoinColumn and how it is used in Hibernate
  16. JPA eager fetch does not join
  17. In which case do you use the JPA @JoinTable annotation?
  18. How can I get the session object if I have the entity-manager?
  19. No Dialect mapping for JDBC type: 1111
  20. Hibernate one-to-one: getId() without fetching entire object
  21. JPA: Implementing Model Hierarchy – @MappedSuperclass vs. @Inheritance
  22. Hibernate: Where do insertable = false, updatable = false belong in composite primary key constellations involving foreign keys?
  23. Hibernate generates negative id values when using a sequence
  24. How to prevent SQL Injection with JPA and Hibernate?
  25. Specifying an Index (Non-Unique Key) Using JPA
  26. Hibernate: Create Mysql InnoDB tables instead of MyISAM
  27. IN-clause in HQL or Java Persistence Query Language
  28. How to set the timeout period on a JPA EntityManager query
  29. Hibernate throws strange error: Class is not mapped
  30. How to force Hibernate to return dates as java.util.Date instead of Timestamp?

Leave a Comment