union-subclass with non-standard SQL database

fh · **Posted:** Tue Nov 01, 2005 6:07 am

This is the English translation of this topic

Hibernate version: 3.0.5
Name and version of the database: Pervasive.SQL 7

Dear experts,
we want/have to use Hibernate with a SQL-database, that expects a non-standard syntax for SELECT ... UNION ...
Our Hibernate mappings contain a union-subclass mapping which is translated into SQL by Hibernate as follows (for clarity reasons the statement has been shortened and keywords written in uppercase):

Code:

SELECT abstractsa0_.AuftrNr as AuftrNr0_, abstractsa0_.VK_Stelle as VK2_13_0_, ..., abstractsa0_.clazz_ as clazz_0_
FROM ( SELECT Anrede, KndCode, Zusatz, ..., 1 as clazz_ FROM Auftragskopf UNION SELECT Anrede, KndCode, Zusatz, ..., 2 as clazz_ FROM ImpKopf )
abstractsa0_ WHERE abstractsa0_.AuftrNr=?

Pervasive 7 doesn't allow for UNION-subselects, so we have to repeat the WHERE clause in every UNION select. For Pervasive 7 the above statement would have to look like this:

Code:

SELECT AuftrNr as AuftrNr0_, VK_Stelle as VK2_13_0_, ..., 1 as clazz_
FROM Auftragskopf ak1 WHERE ak1.AuftrNr=?
UNION SELECT AuftrNr, VK_Stelle, ..., 2 as clazz_ FROM ImpKopf ak2 WHERE ak2.AuftrNr=?

In the Hibernate sources we have seen that the SQL statements are composed at several different places in the code. But the approach for building the SQL is always the same: An org.hibernate.sql.Select instance is created and filled with fragments created by the persister (in our case the org.hibernate.persister.entity.UnionSubclassEntityPersister).

To get a hold on the SQL statement structure we would have to modify the Hibernate sources whereever a select-statement is composed and additionally take appropriate actions to copy a single query parameter into every UNION select.

Is there another point where we can influence the statement's structure?

For your information: We have already tried a lot of possibilities to work around the problem (alternative mappings, a special persister, creating a view in the database etc.) but didn't find a solution. Moreover we don't have a chance to replace Pervasive with a standards-compliant database because a legacy application depends on it.

Thank you!