System design to support database connectivity

Question

I am trying to develop a forum system as a part of a university assignment, which contains a database connectivity part. I am writing the server in java and decided to use hibernate as an ORM tool to save and load from the data base. My question is not about the syntax, but about the design of the entire system regarding the database.

Who should be in charge of creating a session and commit transactions? should I make a singleton which recive an object and save it to the database and use this singelton in every setter/constructor of the different classes?
should each change in a memory object be commited directly or should I commit only every few changes? (and if so what is the best way to do this?)

score 2 · Answer 1 · answered Apr 24 '15 at 11:20

Ideally, a framework should do that for you. Using Spring, for example, you could simply mark methods of Spring beans transactional using an annotation, and the session and transaction handling would be done by Spring:

@Autowired
private SessionFactory sessionFactory;

@Transactional
public void foo() {
    Session session = sessionFactory.getCurrentSession();
    // do some work with the session
}

instead of

private SessionFactory sessionFactory;

public void foo() {
    Session sess = sessionFactory.openSession();
    Transaction tx = null;
    try {
        tx = sess.beginTransaction();

        // do some  with the session

        tx.commit();
    }
    catch (RuntimeException e) {
        if (tx != null) {
            tx.rollback();
        }
        throw e; 
    }
    finally {
        sess.close();
    }
}

You should neither commit after each change, nor commit every few changes. You should commit atomic, coherent changes. That's the whole point of transactions:being able to go from a coherent state of your database to another coherent state of your database.

For example If posting a message to a topic forum consists in

persisting a message instance
incrementing the messageCount field of the forum
creating a notification for the topic poster

then these three changes should be in a single transaction, to make sure you don't end up with the message being persisted, but the messageCount not being incremented.

score 0 · Answer 2 · answered Apr 24 '15 at 11:22

I suggest you leave creating and committing transactions to the container, unless you are sure you want to do it yourself. In this case you can use JTA, and use Container Managed Transactions (CMT). Also remember to avoid entitymanager-per-operation antipattern. To learn more read about Transactions and Concurrency and decide yourself what suit you the best.

score 0 · Answer 3 · edited May 23 '17 at 12:06

Take a look at the DAO (Data Access Object) pattern. It will help to provide a pattern which will help you to encapsulate your data access.

http://en.wikipedia.org/wiki/Data_access_object

Also, this page does a great job: Data access object (DAO) in Java

As for the second question, the answer entirely depends on your requirements. Usually you want to commit at least once per user interaction. That might be the result of several in-memory changes. It is definitely easier to commit after each entity is changed.

System design to support database connectivity

3 Answers3