ORM many-to-one and many-to-many relationships using JDBC DAO

Question

I am writing a small tool for training purposes.
The main scope is to understand how to work with persistent data without using ORM tools.
So, there is a small DB with tables:

users(id, login, password, group_id)
groups(id, name, description)
roles(id, name, description)
// for many-to-many relationship between groups and roles.
groupsroles(group_id, role_id)

So, I have implemented a DAO pattern, with 3 entities : User, Role, Group.
The question is : what is the best way to implement the work with relationships?
How to share the same connection between UserDAO, GroupDAO, RoleDAO to use DB transactions ?

Answer 1

One option is to let the service start a JTA transaction. This will automatically be available to all code running in the same thread. Then use a managed connection pool, so connections automatically join the running JTA transaction.

You can do your usual JDBC work on the connections, just don't call commit() on them, but do call close() on them.

If your goal is only to understand persistence and you don't necessarily want to think about transactions at this moment, you can use stateless session beans (EJB beans). These will automatically start and commit the transaction for you. The Service can be the EJB that your clients will call and this one will automatically start the transaction. Your DAOs can be stateless session beans as well and can be injected with a data source.

e.g.

@Stateless
public class MyService {

   @EJB
   private UserDAO userDAO;

   @EJB
   private GroupDAO groupDAO;

   @EJB
   private RoleDAO roleDAO;

   public void myMethod() {    
        User user = userDAO.getById(...);
        Group group = groupDAO.getByUserId(...);
        // more DAO stuff
   }
}

@Stateless
public class UserDAO {

   @Resource(mappedName="java:/myDS")
   private DataSource dataSource;

   public void getById(Long id) {
      connection = dataSource.getConnection();
      // do JDBC work on connection
   }
}

Answer 2

It's easy to let DAOs share a connection, because it should be created by a service layer, passed into each DAO object, and closed. Just pass the connection around.

Another alternative would be to not share the connection between three DAOs. Rather than mke three queries, you could make one JOIN query, bring all the data back at once, and map it into objects. The penalty is more bytes on the wire, because you bring the parent data back for every child object.