Java JDBC transactional multi table insert

Question

Let's consider we have a database structured as follows:

table News : contains data about news
table Authors : contains data about authors
between the two tables we have a many-to-many relationship so we need a junction table; let's call it News_Authors_Junction_Table

Suppose we have a DAO for every important table: NewsDAO, AuthorsDAO. Each DAO has an insert method. At the start of the insert method, the connection is acquired, then closed. Also, suppose we have a connection manager class which handles the connection to the database and uses the Singleton pattern for the connection. Basically each time the same connection is returned.

Now comes the problem. Consider that when we have to insert into News we also have to insert into Authors. This means that we need a transaction.

In the NewsDAO insert method, we acquire the connection, set it's auto commit to false, call the AuthorsDAO insert method, set the connection's auto commit to true, then close the connection. But, as I mentioned earlier, in each insert method we acquire the connection and then close it. And because we have an insert method into another insert method, the connection is closed in the inner insert method.

Are there any solutions to this problem?

Johnson Abraham · Accepted Answer · 2015-07-09 17:48:40Z

1

What you can do is, move your transaction management out of your DAO. Write a service layer which will encapsulate all your DAO calls and start your transaction at the beginning of the service method and close it at the end.

So in your case you will have a service class say, NewsService, in which you will have a method createNews(). Below is a skeleton code. I havent worked on the syntax, but put it in code fashion to give you an idea.

public XXReturnXX createNews(){
     Transaction tx = DAOHelper.createTx();

     NewsDao newsDAO.insertNews(tx,..);
     AuthorDAO authorDAO.insertAuthor(tx,..)

     tx.commit()
}

answered Jul 9, 2015 at 17:48

Johnson Abraham

7814 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java JDBC transactional multi table insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related