[b]Hibernate version:3.0.2

[b]Name and version of the database you are using: Tested with MSSQL, ORACLE
Hi all,

Recently in our project we have decided to use Hibernate Native SQL query capabilities. We are facing
a problem with mapping the query results with the java class properties. Basically hibernate is not allowing
us to run the named queries if the sql query is mapping only partial properties (of java object) with the query
results. Let me explain the problem using a simple scenario

In my java object class i have two properties defined

public class AppName {
private String appName;
private String name;
//Here goes the getters and setters for the above properties
}

In the mapping xml file i have
<hibernate-mapping>
<class name="com.example.AppName">
<cache usage="read-only" />
<id name="appName" type="string">
<generator class="assigned" />
</id>
<property name="name" type="string"/>
</class>

<sql-query name="appNameQuery">
<return alias="appNames" class="com.example.AppName" />
SELECT APP_NAME AS {appNames.appName} FROM APP
</sql-query>
</hibernate-mapping>

In the caller java code i am calling the query as
SessionFactory sessionFactoriy = new Configuration().configure().buildSessionFactory();
Session hibernateSession =sessionFactoriy.openSession();
Transaction transaction = hibernateSession.beginTransaction();

List results = hibernateSession.getNamedQuery("appNameQuery").list();

transaction.commit();
hibernateSession.close();

Above setup is working as long as the query return two columns and mapped to the two properties of the java
class. That said the following query (when defined in mapping xml file) is executing fine

SELECT APP_NAME AS {appNames.appName}, NAME as {appNames.name} FROM APP

However following query is not getting executed (Getting an exception that says "Invalid column")

SELECT APP_NAME AS {appNames.appName} FROM APP
Exception: Caused by: java.sql.SQLException: [IBM][SQLServer JDBC Driver]Invalid column name: name0_0_

The only difference in both the queries is that in the second query i am mapping partial number of java class
properties to the query results. After trying out some more tables like this what i found out is that hibernate
expects the query to map all the java class properties with the result columns.

Is there anyway to avoid this restriction so that we can use the same properties and xml
files for various related/similar queries?

Thanks a ton in advance.

Well you should really use


This will allow hibernate to do the mapping itself and only retrieve the columns needed to create your object. By specifying some columns to be fetched lazily you can influence what columns are retrieved from the database initially.

Hi,

Thanks for the quick response.

When i tried with the modification that you have mentioned i get the same error

[4/17/07 18:11:27:250 IST] 00000036 SystemErr R org.hibernate.exception.SQLGrammarException: could not execute query

and then

Caused by: java.sql.SQLException: [IBM][SQLServer JDBC Driver]Invalid column name: name0_0_

Looks like one has to map all the properties defined in the mapping xml file in the query. However, you can define selected properties from the java class in your mapping xml file. What i meant is

No of properties in the xml file CAN be less than the number of properties defined in java class.

If you have any solution to my first post please share. What i am interested to do is: use the same xml mapping file for two different functionalites in my app that needs almost similar columns from the table (but not the same columns).

Thanks in advance.

the short hand version of native queries (where you don't map each property again) is for 3.1+ if i remember correctly. not 3.0.x

Sorry I never used pre-3.2.0 so thay may be...

Anyway, all I can add to this is that the shorthand query for me works, but I'm pretty new so I can't really help more :( sorry

Hi max,

Thanks for the reply.

For the time being i had tested with hibernate 3.2 version. But for my badluck, this is not working with 3.2 also. I am getting the same old error.

[4/17/07 23:49:48:016 IST] 00000027 SystemErr R org.hibernate.exception.SQLGrammarException: could not execute query

and then

Caused by: java.sql.SQLException: [IBM][SQLServer JDBC Driver]Invalid column name: name0_0_

Inside binding xml file, as i said earlier, hibernate is expecting that the query inside sql-query element is fetching and mapping all the properties defined in the class element. For example in the following xml file binding

-------------------------------------------------------------------------------------
<hibernate-mapping>

<class name="com.ibm.hibernatepoc.mappings.AppName">
<cache usage="read-only" />
<id name="appName" type="string">
<generator class="assigned" />
</id>
<property name="name" type="string" />
</class>

<sql-query name="appNameQuery">
<return alias="appNames" class="com.ibm.hibernatepoc.mappings.AppName" />
<![CDATA[ SELECT APP_NAME AS {appNames.appName} FROM APP {appNames}]]>
</sql-query>

</hibernate-mapping>
-------------------------------------------------------------------------------------

if the query fetches and maps both the properties defined in class element (ie. appName and name), hibernate works just fine. However the query in the example is throwing the error because i am not fetching/mapping the 'name' property.

Please let me know a solution to this. If this works fine then we can use the same methodology in order to reuse the code for queries which are querying the same DB.

Thanks a lot in advance.

sorry, i missed your "partial properties" comment.

Loading entities "half-baked" does not make semantically sense; if they are not complete entities they are just values.

solutions/workarounds:
- use resultTransformers to load values as java beans
- map your various "half-baked" entities with separate entity-names (note: these entities are not inter-changable since they represent different state!)
- and eventually try and use lazy properties (I haven't tested if that works well with sql-query)