Convert ArrayList to mysql insert parameters in Java

Question

I am stuck. I refuse to stay stuck for long so I am asking for help. What I am trying to do is take a ResultSet that I am retrieving from a select statement from another Database throw it into a ArrayList and then convert the array list to a insert prepared statement in Java.

** UPDATE ** The problem is that I am getting a "java.sql.SQLException: Parameter index out of range (6 > number of parameters, which is 5)." which means that it is receiving 6 parameters and not 5 and I can't see how this is happening. Since in the debugger I am watching as the values update correctly but my logic is now incorrect.

I ran the debugger and saw that I was just dumping all the values into the first parameter. I understand what I need to do. This is to split the ArrayList result into each parameter of the insert statement. My brain is dead. It's the end of the day and this is the last piece of my puzzle so I can move on. I just don't know how to do it. Thank you for any assistance!

This is what my result ArrayList holds.

[800-0022-08, 0025, 2015-09-30, 600040, 115]
[800-0022-08, 0024, 2015-09-30, 600040, 115]
[800-0022-08, 0008, 2015-09-30, 600040, 115]
[800-0022-08, 0036, 2015-09-30, 600040, 115]
[800-0022-08, 0035, 2015-09-30, 600040, 115]
[800-0022-08, 0034, 2015-09-30, 600040, 115]

The code is below.

    ResultSet rs = (ResultSet) stmt.executeQuery(query);
                ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
                int numberOfColumns = rsmd.getColumnCount();
                int rowCount = 1

                ArrayList <String[]> result = new ArrayList<>();

                while (rs.next()) {
                    System.out.println("Row " + rowCount + ":  ");
                    String[] row = new String[numberOfColumns];

                    for (i = 1; i < numberOfColumns + 1; i++) {
                        System.out.print("   Column " + i + ":  ");
                        System.out.println(rs.getString(i);
                        row[i - 1] = rs.getString(i);
                    }
                    result.add(row);

                    System.out.println("");
                    rowCount++;             
                }

                String Connection   = "CONNECTIONSTRING";
                String UserName     = "USERNAME";
                String Password     = "PASSWORD";

                String MySQLQuery = "INSERT INTO serial (MTSER_CODE, MTSER_SERIAL, MTSER_RECDATE, MTSER_WO, MTSER_WOSUF)" + "VALUES (?,?,?,?,?)";

    String Driver = "org.gjt.mm.mysql.Driver";
                    Class.forName(Driver);

                    java.sql.Connection MyConnection = (java.sql.Connection) DriverManager.getConnection(Connection, UserName, Password);

                    java.sql.PreparedStatement preparedStmt;
                    preparedStmt = MyConnection.prepareStatement(MySQLQuery);



// ** UPDATED FOR LOOP **
                    for (String[] arr : result) {
                try (java.sql.PreparedStatement preparedStmt = MyConnection.prepareStatement(MySQLQuery)) {
                    for (int q = 0; q < arr.length; q++) {
                        preparedStmt.setString(q + 1, arr[0]);
                        preparedStmt.setString(q + 2, arr[1]);
                        preparedStmt.setString(q + 3, arr[2]);
                        preparedStmt.setString(q + 4, arr[3]);
                        preparedStmt.setString(q + 5, arr[4]);
                    }
                    preparedStmt.execute();

                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

The Insert statement after an iteration looks like this: INSERT INTO serial (MTSER_CODE, MTSER_SERIAL, MTSER_RECDATE, MTSER_WO, MTSER_WOSUF)" + "VALUES ([800-0022-08, 0025, 2015-09-30, 600040, 115],** Not Specified **,** Not Specified **,** Not Specified **,** Not Specified **)

I didn't read everything but for(int p = 1; p < result.size(); p++) looks suspicious: did you mean for(int p = 0 ; p < result.size(); p++) instead (and then using p+1 inside setString)? — Tunaki
– Tunaki, Commented Sep 30, 2015 at 21:56
Try to put the preparedStmt.executeUpdate(); statement outside of the loop, see if that helps. As it is now, you're executing the PreparedStatement before all the parameters are set, hence the exception — Titus
– Titus, Commented Sep 30, 2015 at 21:56
Titus I added your suggestions to the code but now it is throwing this error. "java.sql.SQLException: Parameter index out of range (6 > number of parameters, which is 5)." — ozfive
– ozfive, Commented Sep 30, 2015 at 23:52

Andreas · Accepted Answer · 2015-09-30 23:17:36Z

1

Mass inserts like that should use batching, ensuring that a batch isn't too big.

You should also make sure to clean up your resources.

try (Connection conn = DriverManager.getConnection(url, userName, password);
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    int rowCount = 0;
    for (String[] row : result) {
        for (int i = 0; i < 5; i++) // hardcoded 5 since SQL has 5 markers
            stmt.setString(i + 1, row[i]);
        stmt.addBatch();
        if (++rowCount % 1000 == 0)
            stmt.executeBatch();
    }
    if (rowCount % 1000 != 0)
        stmt.executeBatch(); // execute last batch
}

answered Sep 30, 2015 at 23:17

Andreas

160k13 gold badges164 silver badges262 bronze badges

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

2 Comments

ozfive Over a year ago

Andreas. I will include this in my final code as I am sure this will help a lot!

ozfive Over a year ago

You made quite a compelling argument. I did add the batching in so I changed the accepted answer.

Bohemian · Accepted Answer · 2015-10-01 00:23:27Z

Your problem is here:

for (int q = 0; q < arr.length; q++) {
    preparedStmt.setString(q + 1, arr[0]);
    preparedStmt.setString(q + 2, arr[1]);
    preparedStmt.setString(q + 3, arr[2]);
    preparedStmt.setString(q + 4, arr[3]);
    preparedStmt.setString(q + 5, arr[4]);
}

You are looping - if the array is size 5, in the last iteration q = 4, so you are setting up to q + 5, which would be 9!

Either don't loop:

preparedStmt.setString(1, arr[0]);
preparedStmt.setString(2, arr[1]);
preparedStmt.setString(3, arr[2]);
preparedStmt.setString(4, arr[3]);
preparedStmt.setString(5, arr[4]);

or loop with one call to setString per iteration:

for (int q = 0; q < arr.length; q++) {
    preparedStmt.setString(q + 1, arr[q]);
}

It was very kind of you to point out the logic error. I wish I could give you the correct answer selection as well because you answered the question after I had implemented what Titus suggested. I should have just gone home to sleep. I learned a lot from this problem and will post the final code in my question for others to see.

Titus · Accepted Answer · 2015-10-01 11:38:01Z

1

Try something like this:

for (String[] arr : result) {
    try (PreparedStatement preparedStmt = MyConnection.prepareStatement(MySQLQuery);) {
        for (int i = 0; i < arr.length; i++) {
            preparedStmt.setString(i + 1, arr[i]);
        }
        preparedStmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

What this does is create a new PreparedStatement for each String array in the result list and set the values of this statement using the value in the array.

edited Oct 1, 2015 at 11:38

answered Sep 30, 2015 at 22:27

Titus

22.6k1 gold badge26 silver badges36 bronze badges

3 Comments

Axel Amthor Over a year ago

you should mention that result.get(p)returns an array of strings representing the values per each column in the query and converting that array to one string will cause the above weired SQL query.

Andreas Over a year ago

Preparing the statement inside the loop is entirely wrong, and probably not what you meant since the code won't even compile with the execute() outside the try-with-resources block.

ozfive Over a year ago

So I have added this suggestion to my code above. with the preparedStmt included in the try statement, because I knew this wasn't going to work outside. Thank you Andreas for confirming this. Now I am receiving a "java.sql.SQLException: Parameter index out of range (6 > number of parameters, which is 5)." error. I have a feeling this has to do with my for loop iteration amount.

Collectives™ on Stack Overflow

Convert ArrayList to mysql insert parameters in Java

3 Answers 3

2 Comments

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

3 Comments

Related