Introduction
In my current project I had to retrieve data from Postgre database. The persistence layer was Hibernate and in the current scenario I had to run predefined sql queries. So i was not using hibernate mapping files but direct query instead
Problem – columns database type was different
Everything seemed to work with no problems, because the columns were String types.
Well not exactly. Some of them were “character varying()” and others were “char()”.
So we execute the sql like this:
List loadValues(final String aQueryString) throws DataAccessException {
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session aSession) throws HibernateException, SQLException {
Query query = aSession.createSQLQuery(aQueryString)
return query.list();
}});}
And later when we try to cast the Object to string we receive a cast exception
.. can not cast java.lang.Character to ..”
It seems that hibernate automatically maps “char()” columns to java.lang.Character and as a result everything except the first symbol is lost.
After spending some time searching in google, I ended with the followin solution.
Solution
public List loadValues(final String aQueryString) throws DataAccessException {
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session aSession) throws HibernateException, SQLException {
Query query = aSession.createSQLQuery(aQueryString).addScalar("id", Hibernate.STRING).addScalar("name", Hibernate.STRING);
return query.list();
}});}
There is a method .addScalar() which allos us to set the return type of the query per column name. This solution expects that all the queries return at least two columns and that they have names “id” and “name”.
It works on the columns alias.
So I had to change all the queries to
select … as id, …. as name from …
otherwise it does not work and you get an error.
