Reducing commits and inserting bulk records at a time in jdbc call?

Question

I have implemented Database Logging to log certain details in my java weblogic portal application to Oracle DB. For this, I am using connection pooling to get connection and using it to make jdbc call to Stored Procedure.

I have a static java method logService which gets connection object and calls the SP. This logservice method is called from different places in my java application with relevant details passed in parameters which inturn is passed to SP call.

So there will be approximately 10-20 logservice method calls for each user accessing the portal. And so, I will be making 10-20 SP calls and I had to commit each time I call the SP.

But the problem is, my DBA has recommended not to commit for each transaction and inturn commit all 10-20 transactions at a time, since there is high commit frequency he says. How do I accomplish this? If its not clear, I can post a sample code.

Yes I am already having Filter for my web application as follows,

public final class RequestFilter implements Filter{

public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) {
 try {
      ((HttpServletResponse) response).sendRedirect(redirectUrl);

  if (chain != null) {
            chain.doFilter(request, response);
        }
     }
 finally { // commit transaction here }
 }
}

After redirecting to redirectURL, I am calling logservice from many places in my application as follows,

DBLog.logService(param1, param2); // static method call

This logservice does prepareCall to SP using connection pooling object. Just I am providing a brief sample code below of how I am doing.

public static void logService(String param1, String param2) {

try {

con=getConnection();
stmt = con.prepareCall("{call DB_LOG_SP (?, ?}");
stmt.setString(1, param1);
stmt.setString(2, param2);
stmt.execute();
stmt.close();
}finally {
           stmt.close();
           con.close();
        } 
 }

But since I am closing the connection after the SP call, how do I commit my transaction in finally {} block inside doFilter method?

Filter Approach --

public class DBLog {

static Connection con = null;
    static PreparedStatement stmt = null;

static {
        try{
        new net.verizon.whatsnext.dblog.JDCConnectionDriver("oracle.jdbc.driver.OracleDriver", 
        "jdbc:oracle:thin:@localhost:7001:xe","user", "password"); 
    con=getConnection(); // Getting connection from Connection pool

    }catch(Exception e){}
    }

 public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:jdc:jdcpool"); 
    }


public static void logService(String param1, String param2) {
    ...
    finally {
               stmt.close();
            } 
     }

 public static void closeTrans() {

    con.commit();
    con.close();
    }
} //End of DBLog class


 public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) {
    ...
     finally { 
          DBLog.closeTrans();
         }
    }

So, I am closing the connection finally after commiting in the finally block in the doFilter method. Am I doing right? Will it work?

Answer 1

Transactional demarcation, as it is the case of security and logging, can be seen as a decoration of your code.

Let's say you have an interface BusinessTask, which is the one that is being implemented by all your current database operations:

public interface BusinessTask {
   public void doWork();
}

For instance, you have a AddSavingsAccountTask and a TransferMoneyTask.

Now, initially you could implement every tasks in such a way that each one do commit (or rollback) individually. But then, if you would like to create a task that consisted in creating an account and transfer money at once, then you would be in trouble, because now you need to change the transaction demarcation.

As such, an approach would be to define a TransactionDecorator, where you can place transaction demarcation.

public class TransactionDecorator implements BusinessTask{

    private final BusinessTask task;

    public TransactionDecorator(BusinessTask task){
        this.task = task;
    }

    @Override
    public void doWork() {
        //beging transaction
        task.doWork();
        //commit or rollback
    }
}

Then you can simply decorate your existing tasks with transaction demarcation:

final AddSavingsAccount addSavingsAccountTask = new AddSavingsAccount();
final TransferFunds transferFundsTask = new TransferFunds();

BusinessTask transaction = new TransactionDecorator(new BusinessTask(){

    @Override
    public void doWork() {
        addSavingsAccountTask.doWork();
        transferFundsTask.doWork();
    }
});

transaction.doWork(); //all the magic happens here

Now, I am just suggesting a pattern here. The implementation of this in your code would vary depending on your design, depending on how your code is structured.

Further references

• Decorator Pattern
• Proxy Pattern

Answer 2

First, do not open a connection on each request, but rather reuse a connection from the connection pool (possibly DataSource), and put in a shared space - in your case the easiest would be to leverage static ThreadLocal field.

In Filter:

public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) {
  Connection conn = obtainConnection(); // obtain connection and store it ThreadLocal
  conn.setAutoCommit(false);
  try {
    chain.doFilter(request, response);
  } finally {
    conn.commit();
  }
}

In your logger class:

public static void logService(String param1, String param2) {
  Connection conn = getConnection(); // obtain thread-local connection
  // your database statements go here
}

In general, avoid using statics. And maybe consider reconsidering your approach to logging.

Answer 3

Consider using a transaction-per-request. You can create a Filter that opens a database transaction before handling the request and commits it afterwards. This would do it.