CODEWITHSUNDEEP
javaormjdbcdao
StKiller
StKiller

Reputation: 7951

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

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 ?

Upvotes: 3

Views: 2268

Answers (3)

kiran.kumar M
kiran.kumar M

Reputation: 801

Use a thread local to hold a reference to your connection. The DAO will use connection from the service layer when a connection is passed. When the caller (service layer) does not pass a connection , DAO will use the connection from thread-local. Thus all DAO can share same connection in a given thread.

TX management is as follows.

All transactions are initiated and ended in service layer. The DAO will not have any commit/rollback logic. Since connection is shared across all the DAO, the caller (service layer) is in full control of the transaction.

Upvotes: 1

Arjan Tijms
Arjan Tijms

Reputation: 38163

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
   }
}

Upvotes: 2

duffymo
duffymo

Reputation: 308763

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.

Upvotes: 1

Related Questions