How to insert each value separately

Question

I need your help in inserting each value in the list

private List<String> selectedCertificate = new ArrayList<String>();

The selected certificate will have the values ["A","B","C"] and I want to insert each item in a separate row for the same id (ex.50)

Record No.1  50  A
Record No.1  50  B
Record No.1  50  C

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;

    String insertTableSQL = "INSERT INTO temp"
            + "(USER_ID, Certificate_Code) VALUES"
            + "(?,?)";

    try {
        dbConnection = getDBConnection();
        preparedStatement = dbConnection.prepareStatement(insertTableSQL);

        preparedStatement.setInt(1, 50);
        preparedStatement.setString(2, selectedCertificate);

        // execute insert SQL stetement
        preparedStatement.executeUpdate();

        System.out.println("Record is inserted");

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

Will this will help:

 (String certificate: certificates) 
{   
preparedStatement.setInt(1, 50);
preparedStatement.setString(2, selectedCertificate);
}

So what are you having trouble with? Do you know how to write a loop? — Peter Lawrey, Mar 06 '15 at 13:30
You could use a `while` loop however a `for` loop might be easier to use. like `for (String certificate: certificates) { /* do something with certificate */ }` — Peter Lawrey, Mar 06 '15 at 13:36
possible duplicate of [Java: Insert multiple rows into MySQL with PreparedStatement](http://stackoverflow.com/questions/4355046/java-insert-multiple-rows-into-mysql-with-preparedstatement) — CoronA, Mar 06 '15 at 13:42
or this link could help http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/ — CoronA, Mar 06 '15 at 13:47

score 0 · Accepted Answer · answered Mar 06 '15 at 13:50

maybe something like that should be fine

String insertTableSQL = "INSERT INTO temp"
        + "(USER_ID, Certificate_Code) VALUES"
        + "(?,?)";

try {
    dbConnection = getDBConnection();
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    for(String certif : selectedCertificate) {
        preparedStatement.setInt(1, 50);
        preparedStatement.setString(2, certif);
        preparedStatement.addBatch();
    }

    // execute insert SQL stetement
    preparedStatement.executeUpdate();

    dbConnection.commit();
    System.out.println("Record is inserted");

} catch (SQLException e) {

    System.out.println(e.getMessage());

}

How to insert each value separately

1 Answers1