Best way to implement <loader> in 2.1.x?

handly · **Joined:** Wed Dec 17, 2003 10:57 am **Posts:** 7

Hibernate version: 2.1.x

I am reading data from DB2 on a mainframe and need to suppress the schema names in my queries because my access is granted through a synonym, which does not allow queries in the form

Code:

select * from myid.Person

For parent objects (Person below), I can write a native SQL query to load my data without the schema. What is the best way to load the children (Phone below)? The <loader> tag from Hibernate 3 (congratulations on beta 1!) seen at http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/customsql.html would be ideal, but we will be going into production using version 2.1.x.

So far, the best I have come up with is to load the children mappings manually after loading the parent object. This is not so bad for a get/load for a single person, but seems like a lot of work in cases like a find, where I am returning a List of parent objects.

Person Mapping:

Code:

<hibernate-mapping>

   <class name="domain.Person" table="PERSON">

      <id name="id" type="String" column="PRSN_ID">
         <generator class="assigned" />
      </id>

      <property name="firstName" type="String" column="FRST_NM" length="40" />
      <property name="lastName" type="String" column="LAST_NM" length="60" />
      <property name="preferredName" type="String" column="PREF_NM" length="40" />

      <!-- associations -->
      <many-to-one name="phone" class="domain.Phone" column="PRSN_ID"  unique="true"/>
   </class>

   <sql-query name="person.findByName">
      <return alias="person" class="domain.Person"/>
      SELECT {person.*} FROM PERSON {person}
      WHERE {person}.FRST_NM like :firstname AND {person}.LAST_NM like :lastname
      ORDER BY {person}.LAST_NM, {person}.FRST_NM
   </sql-query>

</hibernate-mapping>

Person DAO: (note: we are using Spring)

Code:

public List findPeopleByName(final String firstName, final String lastName) {
    return getHibernateTemplate().executeFind(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException {
            List people = session.getNamedQuery("person.findByName")
                .setString("firstname", firstName.toUpperCase() + "%")
                .setString("lastname", lastName.toUpperCase() + "%")
                .setMaxResults(maxResults)
                .list();
            for (Iterator iter = people.iterator(); iter.hasNext();) {
                Person person = (Person) iter.next();
                if (person != null) {
                    person.setPhone(phoneDao.getPhoneByPersonId(person.getId()));
                }
            }
            return people;
        }
    });
}

Is there a better way to do this?

Thank you.

max · **Posted:** Mon Dec 20, 2004 12:10 pm

you say:

Code:

 need to suppress the schema names in my queries because my access is granted through a synonym

...and then start looking at H3 only feature for a solution ;)

Wouldn't it be better if you just used explict schema's instead ?

simply done by only setting schema were needed ?

Then you wouldn't have your issue, right?

handly · **Joined:** Wed Dec 17, 2003 10:57 am **Posts:** 7

Using explict schemas is the problem - when accessing a table through a synonym, DB2 will not let you specific a schema in your queries at all.

For example,

Code:

select * from person;

works, but

Code:

select * from schemaname.person;

does not.

If I leave out the schema references in my mappings (i.e. hibernate.default_schema ), then Hibernate drops in my user name as the default schema, such as

Code:

select * from username.person;

which causes the same problem as DB2 says that username.person does not exist.

I wasn't looking at H3 for the solution specifically, but it does seem that the <loader> feature would do the trick.

If there is a way to tell Hibernate to suppress the schema names without using native sql, then I could just use regular HQL and lead a much happier life. ;-) Otherwise, I am looking at doing lots of manual iterations.

Thanks!

max · **Posted:** Mon Dec 20, 2004 12:35 pm

don't use hibernate.default_schema and set schema explicit on table related tags.

Look in the dtd for references to schema...

May i ask why your synonyms is not in the same schema as the rest of your tables/views ? (is that by design or a limitation of DB2 on the mainframe ?)

max · **Posted:** Mon Dec 20, 2004 12:37 pm

maybe if DB2 is sane (i assume it is) you should be able to set the correct schema for the synonym on the relevant entries (i guess the schema is for the default logged in user)

handly · **Joined:** Wed Dec 17, 2003 10:57 am **Posts:** 7

max wrote:

maybe if DB2 is sane (i assume it is) you should be able to set the correct schema for the synonym on the relevant entries (i guess the schema is for the default logged in user)

That may not be a safe assumption. ;-)

When you are using a synonym with DB2, you can NOT have a schema name in your sql. The synonym takes the place of both the schema and table name, for example the synonym 'Person' really stands for 'trueschema.Person'. From the logged in user point of view, the table trueschema.Person does not exist, only the 'table' Person can be seen.

Note: I also tried setting hibernate.default.schema and/or the class level schema to "", but that results in sql like

Code:

select * from .person

(note the dot), which still causes problems. Maybe this would require a custom DB2 dialect to completely turn off schema name generation.

max · **Posted:** Mon Dec 20, 2004 1:14 pm

no - just don't set default_schema to anything and set it explicilty to your stuff....

(but if schema="" at the moment puts in the "." then we should be able to remove that "." ... look at Table.qualify which you should be able to fix to handle your case ....patches are welcome ;)