Using Hibernate to do a query involving two tables
- by Nathan Spears
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.
