I was working with a native query running over DB2 that only returns a composite-id. I am OK with using HQL for this but the query has several complex joins and the underlying tables contain several hundred million rows so control over the statement is likely to be necessary. I also wanted to see how native queries work. When I run the query it said I had no columns. I suspect that is because I only select a composite key.

To learn more how this might work I tried to rework the example from one of the Hibernate tests. I modified the LocationTest and ran it with a native SQL query over a MySql database. I can't seem to get that statement to parse. I run:

String sql = "select "
+ "{Location}.streetNumber as {Location.streetNumber}, "
+ "{Location}.streetName as {Location.streetName}, "
+ "{Location}.city as {Location.city}, "
+ "{Location}.countryCode as {Location.countryCode}, "
+ "{Location}.locale as {Location.locale}, "
+ "{Location}.description as {Location.description} "
+ "from location {Location}"

and receive:

in expected: {Location} [select {Location}.streetNumber as {Location.streetNumber}, {Location}.streetName as {Location.streetName}, {Location}.city as {Location.city}, {Location}.countryCode as {Location.countryCode}, {Location}.locale as {Location.locale}, {Location}.description as {Location.description} from location {Location}

or run:

String sql = "select "
+ "{Location}.streetNumber as {Location.streetNumber}, "
+ "{Location}.streetName as {Location.streetName}, "
+ "{Location}.city as {Location.city}, "
+ "{Location}.countryCode as {Location.countryCode}, "
+ "{Location}.locale as {Location.locale}, "
+ "{Location}.description as {Location.description} "
+ "from location as {Location}";


and get:

unexpected token: as [select {Location}.streetNumber as {Location.streetNumber}, {Location}.streetName as {Location.streetName}, {Location}.city as {Location.city}, {Location}.countryCode as {Location.countryCode}, {Location}.locale as {Location.locale}, {Location}.description as {Location.description} from location as {Location}

Here is my mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>

<class name="com.plumcreek.commons.dao.Location" table="location">
<composite-id>
<key-property name="streetNumber" type="string" />
<key-property name="streetName" type="string" length="20" />
<key-property name="city" type="string" length="20" />
<key-property name="countryCode" type="string" length="2" />
</composite-id>
<property name="locale" type="string" />
<property name="description" type="string" />
</class>

</hibernate-mapping>

Any ideas where I went wrong?

Please show the actual code which call createSQLQuery

I found my parse error. When I changed the test from DB2 to MySql, I changed the call to use HQL. Sorry about that confusion.

Here is the actual call:

public List selectNative(Class[] clazz, String query, Session session)
throws DaoException {
List list = null;

try {
String[] name = new String[clazz.length];

for (int i = 0; i < clazz.length; i++) {
name[i] = stripPackage(clazz[i]);
}

Query hibernateQuery = ((net.sf.hibernate.Session) session.getSession()).createSQLQuery(
query, name, clazz);
list = hibernateQuery.list();
}
catch (Throwable t) {
throw new DaoException(t);
}

return list;
}

The parms are:

query= "select {Location}.streetNumber as {Location.streetNumber}, {Location}.streetName as {Location.streetName}, {Location}.city as {Location.city}, {Location}.countryCode as {Location.countryCode}, {Location}.locale as {Location.locale}, {Location}.description as {Location.description} from location {Location}"

names[] {"Location"}

clazz[] {"com.plumcreek.commons.dao.Location"}

Now I am getting an error that is similar to what I received with DB2:

WARNING: No column name found for property [streetNumber] [select {Location}.streetNumber as {Location.streetNumber}, {Location}.streetName as {Location.streetName}, {Location}.city as {Location.city}, {Location}.countryCode as {Location.countryCode}, {Location}.locale as {Location.locale}, {Location}.description as {Location.description} from location {Location}

hmm....you are using a composite-id ...don't know if it that's the problem.

But could you try to do it on a table without a composite id ?

I think composite Id's are causing a problem. The SQLLoader is loading a columnAliases[] with:

currentPersister.getSubclassPropertyColumnAliases(propertyName, suffixes[currentPersisterIndex])

If I run:
currentPersister.getIdentifierColumnNames()

I get back:
(java.lang.String[]) [streetNumber, streetName, city, countryCode]

It looks like the identity columns are stored as a String[] under a key of "id" in the subclassPropertyAliases Map. It is trying to retrieve those using the individual key name. I worked out a patch that I will submit to Jira.

cool-we should probably change the AbstractEntityPersister to accomodate that stuff