Inserting an ArrayList of String Arraylists as records into MySQL database

Question

I'm doing a project which will read the contents of every cell of row of an Excel spreadsheet and store all of this information in an ArrayList of String ArrayLists, before inserting all of these rows as records into a MySQL database.

I have a method which extracts this data fine, and returns the full ArrayList of String ArrayLists. The problem I am having is trying to insert these String ArrayLists as records into the SQL database. I'm getting an index out of bounds exception at run time.

Could anyone please try to explain where it is I am going wrong? I think there is some fundemental flaw in my understanding of ArrayLists/looping/JDBC/all of the previous.

public void insertData(ArrayList<ArrayList<String>> l) {
    try {

        String insert = "INSERT INTO 1person (ModuleNumber, ModuleTitle, School, ModuleCoordinator, NumberOfStudents, ExamCW, Courses, Blankety, Blank) VALUES (?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(insert);

        for (ArrayList<String> ar1 : l) {
            //for(int i = 0; i < ar1.size(); i ++){
            //ps.setString(i+1, ar1.get(i)
            ps.setString(1, ar1.get(0).toString());
            ps.setString(2, ar1.get(1).toString());
            ps.setString(3, ar1.get(2).toString());
            ps.setString(4, ar1.get(3).toString());
            ps.setString(5, ar1.get(4).toString());
            ps.setString(6, ar1.get(5).toString());
            ps.setString(7, ar1.get(6).toString());
            ps.setString(8, ar1.get(7).toString());
            ps.setString(9, ar1.get(8).toString());
            //}

        }
        ps.execute();

    } catch (Exception ex) {
        System.out.println(ex);
    }
}

returns: java.lang.IndexOutOfBoundsException: Index: 8, Size: 8

Your list size 8 but you are trying to retrieving 9 item from the list as ar1.get(8). — SatyaTNV
– SatyaTNV, Commented Feb 19, 2016 at 16:01

Jan · Accepted Answer · 2016-02-22 09:19:27Z

1

You seem to have only 8 values where you should have 9 (you try to insert 9 values!)

Just as well you only call execute() once - but you should either call it per line of insert (and call executeUpdate() in case of INSERT or UPDATE) or you could batch your inserts and then call executeBatch().

Try it like this:

for (ArrayList<String> ar1 : l) {
   if(line.size() != 9) {      
     System.out.println("This line only had "+line.size()+" elements - supposed to contain 9");
     for(String item : ar1) {
        //Show for finding the error
        System.out.println("- " + item);
     }
   } else {
      for(int i = 0; i <9; ++i) {
        ps.setString(i+1, ar1.get(i));
      }
      //Mark this line to be added
      ps.addBatch();
   }     
}
int[] result = ps.executeBatch();

edited Feb 22, 2016 at 9:19

answered Feb 19, 2016 at 16:10

Jan

13.9k3 gold badges35 silver badges58 bronze badges

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

1 Comment

Dave Over a year ago

Thanks Jan, that's cleared things up for me. Works perfectly now! Many thnaks

Collectives™ on Stack Overflow

Inserting an ArrayList of String Arraylists as records into MySQL database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related