0

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

Srikanth Sridhar
  • 2,317
  • 7
  • 30
  • 50
  • Can you post an exception stacktrace please? – Mark Bramnik Nov 05 '12 at 06:12
  • 1
    Have you debugged your code? I see a couple catch blocks with no code to handle the exception. This is a really bad practice which you should avoid at all cost. Can you elaborate on what you are trying to do? What exception do you get and where do you get it? Do you get a stackoverflow exception? For me it look like you call your executeInsertStatement1(...) method forever as I don't see a condition where you stop the recursion. – Timo Hahn Nov 05 '12 at 06:58
  • thanks for the reply, the recursion should end when startIndx >= endIndx. What I am trying to implement merge sort technique when the preparestatement fails. The mycollectionlist is a collection of list which contains the values to be inserted. I will not be knowing the position where the prepared statement execution fails, so i am trying to divide the list and trying to insert the records. say i have 1000 records and execution failed at 400. Iam passing the entire list dividing it using merge sort technique and inserting it. – Srikanth Sridhar Nov 05 '12 at 07:12
  • 1
    I would say start over. Don't even write it in code first, start with writing the algorithm in pseudo-code on a piece of paper. Then implement it. What you have there is just god-awful (excuse my language). And stop confusing yourself with merge-sort. This is not about sorting, it's about splitting a range into sub-ranges. Focus on the problem you are trying to solve, not some arbitrary algorithm that really has nothing to do with this. – pap Nov 05 '12 at 08:33

1 Answers1

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.

Community
  • 1
  • 1
Sujay
  • 6,753
  • 2
  • 30
  • 49
  • Thanks for your answer sujay, we are migrating data from the sql to Oracle server. Some of the data in the intermediate txt file which we create contains repetition because some data is hardcoded. My batch execution fails because of constraints. In this scenario I tried to build divide and conquer method. currently if the bulk insert fails i am inserting each record and calling commit(sequential insert). If there are any inputs will be of great help. Thanks – Srikanth Sridhar Nov 05 '12 at 10:24
  • 1
    @user1103504: Well you can extend the above solution. The idea is to execute the batch updates in chunks. Say you've got 100 rows, you divide it in chunks of 10. And you see when you do an `executeUpdate()` with your third chunk, it fails (*because of, say, a constraint violation*). Well in that case, just for that chunk run it sequentially. I don't think that would be hard to code. – Sujay Nov 05 '12 at 10:29
  • hmmm.. thank you,I will drop my divide and conquer and go with this solution for now. But I think it should be possible to with divide and conquer, will try when i have some more time. thanks again – Srikanth Sridhar Nov 05 '12 at 10:39