Using Hibernate to do a query involving two tables

Question

I'm inexperienced with sql in general, so using Hibernate is like looking for an answer before I know exactly what the question is. Please feel free to correct any misunderstandings I have. I am on a project where I have to use Hibernate. Most of what I am doing is pretty basic and I could copy and modify.

Now I would like to do something different and I'm not sure how configuration and syntax need to come together. Let's say I have two tables.

Table A has two (relevant) columns, user GUID and manager GUID. Obviously managers can have more than one user under them, so queries on manager can return more than one row. Additionally, a manager can be managing the same user on multiple projects, so the same user can be returned multiple times for the same manager query.

Table B has two columns, user GUID and user full name. One-to-one mapping there.

I want to do a query on manager GUID from Table A, group them by unique User GUID (so the same User isn't in the results twice), then return those users' full names from Table B.

I could do this in sql without too much trouble but I want to use Hibernate so I don't have to parse the sql results by hand. That's one of the points of using Hibernate, isn't it?

Right now I have Hibernate mappings that map each column in Table A to a field (well the get/set methods I guess) in a DAO object that I wrote just to hold that Table's data.

I could also use the Hibernate DAOs I have to access each table separately and do each of the things I mentioned above in separate steps, but that would be less efficient (I assume) that doing one query.

I wrote a Service object to hold the data that gets returned from the query (my example is simplified - I'm going to keep some other data from Table A and get multiple columns from Table B) but I'm at a loss for how to write a DAO that can do the join, or use the DAOs I have to do the join.

FYI, here is a sample of my hibernate config file (simplified to match my example):

<hibernate-mapping package="com.my.dao">
  <class name="TableA" table="table_a">
  <id name="pkIndex" column="pk_index" />
  <property name="userGuid" column="user_guid" />
  <property name="managerGuid" column="manager_guid" />
  </class>
</hibernate-mapping>

So then I have a DAOImplementation class that does queries and returns lists like

public List<TableA> findByHQL(String hql, Map<String, String> params)

etc. I'm not sure how "best practice" that is either.

Answer 1

Ok. First, I noticed that you are mostly talking about tables in your question so I think that you're missing an important point about Hibernate. Hibernate is an ORM (Object-Relational Mapping) tool, it's a tool that helps to map objects to a relational model and to manipulate data using an object paradigm.

So, when using Hibernate, you should actually think in terms of objects that have associations between them, not tables. And by objects, I mean business entities like Project, Employee, not DAOs. DAOs are a way to model the Data Access Layer in a technology agnostic way (JDBC, Hibernate, etc), they do not represent database records. Hibernate mapping files are used to map these entities to tables and to describe their associations.

In other words, instead of "TableA" and "TableB", I'd expect to read something like (this is just an example of course):

I have an Employee class, Employee can work on a Project as Project Members or as Project Manager. I'm using a many-to-many association between Project and Employee for the team members and a many-to-one association between Project and Employee for the project manager. Here are the Hibernate mapping files and the corresponding Java classes...

Then, to query this model, you should again think in terms of objects. Again, this example is fictional (it doesn't really make sense without an object model and the associated mappings) but your HQL query could look like this:

select distinct project.teamMembers 
from Project as project 
where project.projectManager.id=:id 

To summarize, I think that you need to rethink your problem and the way to solve it. Right now, you seem to be on a wrong path.