CODEWITHSUNDEEP
javahibernate
Vincent
Vincent

Reputation: 199

Hibernate SQLQuery: Database view with multiple entities and aliased column names

I try to select multiple classes at once using a database view. Both tables have similar columns, like "ID". Therefore the view is working with aliases. Unfortunately Hibernate doesn´t map die "ID"-Field properly.

Here a simplified example:

Species                 Cat
| ID | Name         |   | ID | Name         | SpeciesId
+----+--------------+   +----+--------------+---------
| 1  | Tuxedo cat   |   | 1  | Sylvester    | 1
                        | 2  | Sylvester Jr | 1
                        | 3  | Sylth Vester | 1

These tables could result in the following View

CREATE VIEW 'CatsBySpecies' AS
  SELECT
    SPECIES.ID, 
    SPECIES.NAME,
    CAT.ID AS CAT_ID,
    CAT.NAME AS CAT_NAME
  FROM SPECIES 
    LEFT JOIN CAT ON CAT.SPECIESID = SPECIES.ID;

The result of the query is correct:

|ID| Name     | CAT_ID | CAT_NAME     |
+--+----------+--------+--------------+
|1 |Tuxedo cat|    1   | Sylvester    |
|1 |Tuxedo cat|    2   | Sylvester Jr |
|1 |Tuxedo cat|    3   | Sylth Vester |

So far, so good.

Hibernate Query:

String select = "Select * from CatsBySpecies";

catQuery = persistentSession.createSQLQuery(select).addEntity(Species.class).addEntity(Cat.class);
Iterator<?> pairs = catQuery.list().iterator();

But the classes are not mapped correctly, the result-tuples look like this

   Species:               Cat:
      ID = 1               ID = 1
      Name = Tuxedo cat    Name = Tuxedo cat

So hibernate seems to recognize the corresponding columnnames and compares them to the object-attributes.

Is there a way to tell hibernate the way to map the aliases? Unfortunately the tables have much more columns, so I tried to avoid to addScalar for each column of the database-result.

Looking forward to your hints and suggestions.

Upvotes: 2

Views: 852

Answers (1)

Vincent
Vincent

Reputation: 199

Solved the issue through adaption of my selection string. In this case it is important to use the {} form (mentioned also here)

In addition I had to announce the aliased column names. The new selection string looks like this:

String select = "Select id as {spec.id}, name as {spec.name}, cat_id as {cat.id}, cat_name as {cat.name} from CatsBySpecies";

Now we can get the objects as usual:

catQuery = persistentSession.createSQLQuery(select).addEntity("spec", Species.class).addEntity("cat", Cat.class);

I hope this helps anyone else out there, who´s facing the same problem.

Upvotes: 2

Related Questions