How to map a database query into an Object [in Java]?

Question

I have a query that joins 5 tables. Then I fill my hand-made object with the column values that I need.

What are solutions here that are wide-common to solve that problem using specific tools ? are there such tools?

I'm only beginning to learn Hibernate, so my question would be: is Hibernate the right decision for this problem?

Hibernate maps a table to a class. So, there's no difference if I would have 5 classes instead of 5 tables. It would still be difficult to join the query result into a class

Could hibernate be used to map THE QUERY into the structure (class) I would define beforehand as we do with table mapping? Or even better, can it map the query result into the meaningful fields [auto-create the class with fields] as it does with reverse-engineering?

I've been thinking about views but.. create a new view everytime we need a complex query.. too verbose.

As S.Lott asked, here is a simple version of a question:

General problem:

select A.field_a, B.field_b, C.field_c 
 from table_a A inner join table_b B inner join table_c C 
 where ...

every table contains 100 fields query returns 3 fields, but every field belongs to the unique table

How do I solve that problem in an OO style? Design a new object with properties corresponding to the returning values of the query.

I want to know if it is the right [and the only one possible] decision and are there any common solutions.

See also my comments.

Answer 1

The point of ORM is to Map Objects to Relations.

The point of ORM is -- explicitly -- not to sweat the details of a specific SQL join.

One fundamental guideline to understanding ORM is this.

SQL joins are a hack because SQL doesn't have proper navigation.

To do ORM design, we to intentionally set the SQL join considerations aside as (largely) irrelevant. Give up the old ways. It's okay, really. The SQL crutches aren't supporting us very well.

Step 1. Define the domain of discourse. The real-world objects.

Step 2. Define implementation classes that are a high-fidelity model of real-world things.

Step 3. Map the objects to relations. Here's where the hack-arounds start. SQL doesn't have a variety of collections -- it only has tables. SQL doesn't have subclasses, it only has tables. So you have to design a "good-enough" mapping between object classes and tables. Ideally, this is one-to-one. But in reality, it doesn't work out that way. Often you will have some denormalization to handle class hierarchies. Other than that, it should work out reasonably well.

Yes you have to add many-to-many association tables that have no object mapping.

Step 4. You're done. Write your application.

"But what about my query that joins 5 (or 3) tables but only takes one attribute from each table?"

What about it? One of those tables is the real object you're dealing with. The other of those 5 (or 3) tables are either part of 1-m nested collections, m-1 containers or m-m associations. That's just navigation among objects.

A 1-m nested collection is the kind of thing that SQL treats as a "result set". In ORM it will become a proper object collection.

A m-1 contain is the typical FK relationship. In ORM it's just a fetch of a related object through ordinary object navigation.

A m-m association is also an object collection. It's a strange collection because two objects are members of each other's collections, but it's just an object collection.

At no time do you design an object that matches a query. You design an object that matches the real world, map that to the database.

"What about performance?" It's only a problem when you subvert the ORM's simple mapping rules. Once in a blue moon you have to create a special-purpose view to handle really big batch-oriented joins among objects. But this is really rare. Often, rethinking your Java program's navigation patterns will improve performance.

Remember, ORM's cache your results. Each "navigation" may not be a complete "round-trip" to the database query. Some queries may be batched by the ORM for you.

Answer 2

I think you could use the Criteria API in Hibernate to map the results of your join into your target class.

Answer 3

There are a few options:

1. Create a single table mapping using <join> elements for the related tables. A join in that way will allow other tables to contribute properties to your class.
2. Use a database view as previously suggested.
3. Use a Hibernate mapping view - instead of <class name=... table=... you can use <class name=... select="select A.field_a, B.field_b, ... from A, B, ...">. It's essentially creating a view on the Hibernate side so the database doesn't have to change. The generated sql will end up looking like "select * from (select A.field_a, B.field_b from A, B, ...) ". I know that works in Oracle, DB2, and MySQL

All that is fine for selecting; if you need to do insert/update, you'll probably need to rethink your data model or your object model.