[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2220) session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String

[Max Rydahl Andersen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30835 ] 

Max Rydahl Andersen commented on HHH-2220:
------------------------------------------

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(n) 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.

> session.createSQLQuery(sql) translates database type CHAR(n) to Java type char instead of String
> ------------------------------------------------------------------------------------------------
>
>                 Key: HHH-2220
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2220
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.2.0.ga
>            Reporter: Regis Pires Magalhaes
>
> createSQLQuery() method translates database type CHAR(n) to Java type char instead of String when using setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).
> That happens when I do not use addScalar(). And that is the only problem that I have found when not filling return types in advance.
> A workaround I have made is to concatenate the projected field with an empty string (''). See example below:
> ...
> query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
> String sqlQuery = "select s.name state from state s where s.name='PI' ";
> query = session.createSQLQuery(sqlQuery);
> ...
> result: [{STATE=P}]
> name field is CHAR(2) in database definition (PostgreSQL, HSQLDB and Oracle were tested).
> Note that it works when I concatenate the field used in projection with an empty string:
> ...
> String sqlQuery = "select s.name || '' state from state s where s.name='PI' ";
> ...
> result: [{STATE=PI}]

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira