I’m currently in the process of converting the data layer in a server-side Java service to use hibernate. It has a lot of weird joins in the SQL, so the simplest solution for those cases in the code is to use the native SQL passthrough feature of hibernate. Normally when you run

List data = session.createSQLQuery("select field1, field2 .....")

You get back a java.util.List populated with Object[]’s. You have to reference the individual columns by their index value in order to get data out of them. This has the side-effect that if your database tables every change or columns get re-ordered your code is hosed up and difficult to debug.

A better way I’ve found is to use

List data = session.createSQLQuery("select  field1, field2.....")

This has the effect of returning a List containing Map objects for each row. It then becomes a simple matter to retrieve data based on the column name. If columns ever get added or re-ordered in your table your code will continue working.

for (Object object : data)
Map row = (Map)object;
System.out.println(row.get("field1") + ", " + row.get("field2"));

Post to Twitter

Posted by Andrew, filed under Hibernate. Date: March 7, 2008, 8:34 pm | 5 Comments »

5 Responses

  1. Paulo Luan Says:

    Thanks man!! :D i’m from Brazil and you save my life with this article!

  2. Gustavo Says:

    Thanks man!! :D i’m from Brazil and you save my life with this article!

  3. FRKN Says:

    Thanks, that really helped +1

  4. Sudin Ranjitkar Says:

    org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:87)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:118)
    at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:653)
    at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:93)
    at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:62)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:498)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2073)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:909)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)

    I am facing this error. what may be the reason. according to my output the column names and the number of columns are random. Help please

  5. Kevin Says:

    Very Useful

Leave a Comment

Your comment

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.