Inserting into a database using JDBC

Question

I am trying to insert into a database using JDBC and each thread will be inserting into the database. I need to insert around 30-35 columns. I wrote a stored procedure that will UPSERT into those columns.

The problem I am facing is that in the run() method, I have around 30 columns written over there for insertion. Is there any way I can simplify my run method so that it doesn't looks so messy which is looking right now for me? And I have a few more columns as well. So if I keep on adding new columns there, it will be looking so messy at one point in my run method.

Is there any way to clean this, keeping in mind the thread safety issues?

class Task implements Runnable {

    private Connection dbConnection = null;
    private CallableStatement callableStatement = null;

    public Task() {

    }

    @Override
    public void run() {

    dbConnection = getDbConnection();
    callableStatement = dbConnection.prepareCall(Constants.UPSERT_SQL);

    callableStatement.setString(1, String.valueOf(userId));
    callableStatement.setString(2, Constants.getaAccount(userId));
    callableStatement.setString(3, Constants.getaAdvertising(userId));
    callableStatement.setString(4, Constants.getaAvgSellingPriceMainCats(userId));
    callableStatement.setString(5, Constants.getaCatAndKeywordRules(userId));
    callableStatement.setString(6, Constants.getaClvBehavior(userId));
    callableStatement.setString(7, Constants.getaClvChurn(userId));
    callableStatement.setString(8, Constants.getaClvInfo(userId));
    callableStatement.setString(9, Constants.getaClvSegmentation(userId));
    callableStatement.setString(10, Constants.getaCsaCategoriesPurchased(userId));
    callableStatement.setString(11, Constants.getaCustomerService(userId));
    callableStatement.setString(12, Constants.getaDemographic(userId));
    callableStatement.setString(13, Constants.getaFinancial(userId));
    callableStatement.setString(14, Constants.getaGeolocation(userId));
    callableStatement.setString(15, Constants.getaInterests(userId));
    callableStatement.setString(16, Constants.getaLastContributorsPurchased(userId));
    callableStatement.setString(17, Constants.getaLastItemsLost(userId));
    callableStatement.setString(18, Constants.getaLastItemsPurchased(userId));
    callableStatement.setString(19, Constants.getaLastProductsPurchased(userId));
    callableStatement.setString(20, Constants.getaLastSellersPurchasedFrom(userId));
    callableStatement.setString(21, Constants.getaMainCategories(userId));
    callableStatement.setString(22, Constants.getaMessaging(userId));
    callableStatement.setString(23, Constants.getaPositiveSellers(userId));
    callableStatement.setString(24, Constants.getaPromo(userId));
    callableStatement.setString(25, Constants.getaScores(userId));
    callableStatement.setString(26, Constants.getaSegmentation(userId));
    callableStatement.setString(27, Constants.getaSellers(userId));
    callableStatement.setString(28, Constants.getaSrpBuyerUpiCount(userId));
    }
  }



    private Connection getDBConnection() {

    Connection dbConnection = null;

          Class.forName(Constants.DRIVER_NAME);
      dbConnection = DriverManager.getConnection(url,user,password);

      return dbConnection;
    }

Answer 1

Extract a method.

class Task implements Runnable {

    ... 
    private completeStatement (CallableStatement stmt, Strung userId)
    {
        callableStatement.setString(1, String.valueOf(userId));
        ...
    }  

    @Override
    public void run() {
        dbConnection = getDbConnection();
        callableStatement = dbConnection.prepareCall(Constants.UPSERT_SQL);
        completeStatement(callableStatement,userId) 

    }
}

private Connection getDBConnection() {
    ...
}

But find a more suitable name for what your statement is doing.

Answer 2

Some minor notes:

1. dbConnection and callableStatement might be local variables.

2. If more than one thread calls the run method at the same time the shared dbConnection and callableStatement references might lead to resource leaks/race conditions.

3. I'd consider using setString(String parameterName, String x) which uses readable parameter names instead of integer indexes.