using differnet SQL queries for different dialects

ludeking · **Joined:** Mon Dec 22, 2008 9:41 am **Posts:** 8

Hi all,

I have a need for using unions in native SQL, hence for different dialects I need to use different sql queries (because of some differences in keywords etc).

So I need to be able to get the Configuration object, and from that, the Dialect in use, so I can decide which SQL query to use.

Can anyone help me do that please?

ludeking · **Joined:** Mon Dec 22, 2008 9:41 am **Posts:** 8

Apparently I need to get the Configuration or Environment, then from there can get the properties and the dialect, can someone give me a pointer?

littypreethkr · **Posted:** Thu Feb 19, 2009 3:10 am

Configuration.getProperty("hibernate.dialect");

ludeking · **Joined:** Mon Dec 22, 2008 9:41 am **Posts:** 8

Thanks, but how do I get the configuration object. I need to do this when I am running my query so that I can select the right version (mysql/oracle) for this particular one so I only have a Session available.

So I need to get the Configuration obj from the Session. Any ideas?

The reason for this is in my native SQL query I have a where clause that includes
SOME_COLUMN=true

on a boolean parameter which maps to a BIT field. MySQL doesn't have a issue but Oracle does (needs to be 1 or 0). If there is another way to do this then that would be great. Perhaps passing in the 'true' param as some Hibernate type so it figures out the right one? (true for MySQL, 1 for Oracle)

littypreethkr · **Posted:** Thu Feb 19, 2009 5:29 am

Code:

String HQL = "from SomeClass where boolColumn = :value";
session.createQuery(HQL).setBoolean("value", Boolean.TRUE).list();

This should do the trick.

ludeking · **Joined:** Mon Dec 22, 2008 9:41 am **Posts:** 8

Thanks I'll give that a go.

One question is because I am using Native SQL not HQL (because I need a union which Hibernate doesnt support) then will that still work?

somu_ngm · **Posted:** Thu Feb 19, 2009 7:41 am

Yes, still it does work..

String SQL = "your sql query string goes here... where bool_column = :value";
session.createSQLQuery(SQL).setBoolean("value", Boolean.TRUE).list();

hope this helps you

Genrally using union is not suggestable as it downs the performance, you can do your logic in java (object world ) that is combining two queries result.

ludeking · **Joined:** Mon Dec 22, 2008 9:41 am **Posts:** 8

Thanks so much, this works nicely on both MySQL and Oracle.
cheers