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.
