Java JDBC - prepared statement for bulk insertion using merge sort

Question

I am using JDBC prepared statement for bulk insertion. I am calling ps.execute() method. If it fails then i am calling a method where i am passing the list of parameters and the prepared statement. I am using merge sort technique to divide the list and then trying to insert the records, but not successfull.

Here is my code;

from executePrepareStatement method i am calling

this.executeInsertStatement(query, myCollection, 0, myCollection.size());

// executeInsertStatement method

public void executeInsertStatement1(String query, List myCollection, int sIndx, int eIndx) throws DBException,SQLException {

        int startIndx = sIndx, endIndx =  eIndx, mid = 0;

        try {
            try{
                if(conn.isClosed())
                    new DataService(CoreConstants.TARGET);
            } catch (Exception e) { }           
            if(startIndx >= endIndx) {
                return;
            }           
            conn.setAutoCommit(false);
            if (query != null) {
                mid = (startIndx + endIndx) / 2;
                ps = conn.prepareStatement(query);
                executeInsertStatement(query, myCollection, startIndx, mid);
                executeInsertStatement(query, myCollection, mid+1, endIndx);
                //int end_low = mid;
                //int start_high = mid + 1;
                if(mid < endIndx)
                    endIndx = mid;
                for (int i = 0; i < endIndx; i++) {
                    List list = (List) myCollection.get(i);
                    int count = 1;      
                    for (int j = 0; j < list.size(); j++) {
                        if(list.get(j) instanceof Timestamp) {
                            ps.setTimestamp(count,  (Timestamp) list.get(j));       
                        } else if(list.get(j) instanceof java.lang.Character) {
                            ps.setString(count, String.valueOf(list.get(j)));
                        }
                        else {
                            ps.setObject(count, list.get(j));
                        }
                        count++;
                    }
                    try {
                        ps.execute();   
                    } catch (Exception e) {
                        rollback();
                    }                   
                }
            }
        } catch (Exception e) {
            rollback();
        } finally{ 
            try {
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
            } catch (Exception ex) {
            }
        }
    }

Thanks

Answer 1

I don't think you're on the right track by using a merge sort. I understand that you're trying to achive your solution using the concept of divide and conquer but I think you're making the problem harder (and more confusing/complex) than it really needs to be.

If I understand correctly, you have got a data set that you would want to insert into your database. And you would want to do it in bulk. PreparedStatement let's you do that by using a couple of neat methods: addBatch() and executeBatch()

Here's an outline of how I would have tried to implement your requirement:

• I would set a batch limit i.e. the number of statements in my batch when I would like to execute the batch. Unless I reach this limit (which I can very well track by using a counter), I would continue to add to the batch

• Once I hit the limit, I execute the batch, reset the counter, clear the batch and redo Step #1

• This would continue till I'm done with my entire data set. Finally I would end by either committing the data to the database or even performing a rollback, based on my requirement.

Have a look at this answer for an example of how you might go about and implement this.