This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Dynamically setting schema name for native queries in hbm file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Dynamically setting schema name for native queries in hbm file" Watch "Dynamically setting schema name for native queries in hbm file" New topic
Author

Dynamically setting schema name for native queries in hbm file

Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
Hi ,
inside my hibernate.cfg.xml I am mentioning
hibernate.default_schema as schema name (say "testSchema") and it is working with named queries and HQL as expected.But when I am using a native sql query ,say

String queryString= 'select field1 from table1 where field2=100';
SQLQuery queryObj=session.createSQLQuery(queryString).
addScalar("field1 ",Hibernate.STRING);

In that case the schema name(i.e. testSchema)is not getting appended to the table names.
As a work around I had to add the schema name name before the table names.
So the changed code is ,
String queryString= 'select field1 from testSchema.table1 where field2=100';
.....

But it is not the correct approach I guess.If there are 100 queries
and in future my schema name changes, I have to change the schema name at 100 places.
Can anybody provide me the correct implementation ?
Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
I also tried the same thing by

<sql-query name="myQuery">
<return-scalar column="NAME" type="java.lang.String"/>
<return-scalar column="SURNAME" type="java.lang.String"/>

SELECT NAME,SURNAME FROM ${hibernate.default_schema}.TBEMPLOYEE
</sql-query>

and hibernate.default_schema value is mentioned in configuration file.
I tried this for Hibernate version 3.3.0 and 3.3.2. But it is not working.

Do anyone know correct syntax?
sarmaP lolla
Greenhorn

Joined: Feb 01, 2009
Posts: 18
If you are writing the native SQL then hibernate just executes that sql... it would not bother to look at the schema.

Sarma
Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
Hi Sarma,

Actually I am trying to write SQL in .hbm file and my requirment is to access the table with schema name.
I have mentioned the default schema name in .cfg file of hibernate.
<property name="hibernate.default_schema">TEST_SCHEMA</property>
and now I want to use this default schema in my .hbm file.

<sql-query name="myQuery">
<return-scalar column="NAME" type="java.lang.String"/>
<return-scalar column="SURNAME" type="java.lang.String"/>

SELECT NAME,SURNAME FROM ${hibernate.default_schema}.TBEMPLOYEE
</sql-query>
But this is giving me an error.

I tried following query also
SELECT NAME,SURNAME FROM :{hibernate.default_schema}.TBEMPLOYEE

But it is giving me "Not all named parameters have been set: [{hibernate.default_schema}.TBEMPLOYEE] [SELECT NAME,SURNAME FROM :{hibernate.default_schema}.TBEMPLOYEE]" error.

Now I am not getting how to access .cfg property in my .hbm file.

Niraj
sarmaP lolla
Greenhorn

Joined: Feb 01, 2009
Posts: 18
This is what help says....
DefaultSchema: This option sets a schema or tablespace name used to qualify unqualified tablenames in generated SQL.

so did you define your mapping file like shown below? In that case this overwrites your default....

<hibernate-mapping default-lazy="false">
<class name="mypackage.domain.Mytable" schema="MY_SCHEMA" table="MY_TABLE">

Also looks like you can not access DefaultSchema from the config file...

Sarma

Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
Hi Sarma,

I tried to define schema in hbm file. But still it doesn't work for me.

<class name="hibernate.Contact" schema="TEST" table="TBEMPLOYEE" >
and query is

SELECT NAME,SURNAME FROM TBEMPLOYEE

I am still getting exception like "could not execute query"......
Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
Hi,

It is finally started working for me.

I downloaded Hibernate V3.5.3 and used {h-schema} tag before my table name and it started working.

SELECT * FROM {h-schema}TBEMPLOYEE

This feature is not available in V 3.2.x or 3.3.x. You should use V3.5.3 or higher to get it worked.

Thanks...
Niraj Gadgilwar
Greenhorn

Joined: Jun 23, 2010
Posts: 11
Hi,

It is finally started working for me.

I downloaded Hibernate V3.5.3 and used {h-schema} tag before my table name and it started working.

SELECT * FROM {h-schema}TBEMPLOYEE

This feature is not available in V 3.2.x or 3.3.x. You should use V3.5.3 or higher to get it worked.

Thanks...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dynamically setting schema name for native queries in hbm file
 
Similar Threads
Does the TransactionAttributeType Required has an impact on JDBC-calls?
ResultSet Question
Dynamically setting schema name for native queries
ResultSet multiple line query exception
why my sql in jsp is error?