How to insert List data into database using JDBC?

Question

The current format of my List<String[]> is:

60 52 0 0 1512230400
76 52 1 1 1514044800 
42 52 4 1 1516464000

Whereby each separated value by space is a row in my database table, for example: 60 52 0 0 1512230400. I want to insert the 5 separate values per loop. I want to insert all these lines into my database but am not sure on exactly how. This is also a working connection to my database as of now.

This is my rough idea:

String query = "INSERT INTO games (team1_id, team2_id, score1, score2, created_at) VALUES (? ,?, ?, ?, ? )";
Connection con = DBConnector.connect();
PreparedStatement stmt = con.prepareStatement(query);//prepare the SQL Query

for (String[] s : fixtures) {

}

Any help is amazing.

Many thanks

You can use a JDBC batch insert: https://stackoverflow.com/a/3786127/14955 — Thilo, Feb 19 '19 at 23:52

Kartik · Accepted Answer · 2019-02-20T00:13:04.313

In your for-loop, you can do something like this:

stmt.setString(1, s[0]); //team1_id if it's of string type in db
stmt.setInt(2, Integer.parseInt(s[1])); //team2_id if it's of type integer in db
stmt.setInt(3, Integer.parseInt(s[2])); //score1
stmt.setInt(4, Integer.parseInt(s[3])); //score2
stmt.setLong(5, Long.parseLong(s[4])); //created_at
stmt.executeUpdate();

The above code shows you how to deal with String, Long and Integer, you can use other types similarly.

YoYo · Answer 2 · 2019-02-20T18:50:45.967

List<String[]> fixtures = new ArrayList<>();
fixtures.add(new String [] {"60","52","0","0","1512230400"});
fixtures.add(new String [] {"76","52","1","1","1514044800"});
fixtures.add(new String [] {"42","52","4","1","1516464000"});

String query = 
  "INSERT INTO games (team1_id, team2_id, score1, score2, created_at)\n"
  + " VALUES (? ,?, ?, ?, ? )";
try(
  Connection con = DBConnector.connect();
  PreparedStatement stmt = con.prepareStatement(query);
) {
  for (String[] s : fixtures) {
    stmt.setString(1,s[0]);
    stmt.setString(2,s[1]);
    stmt.setString(3,s[2]);
    stmt.setString(4,s[3]);
    stmt.setString(5,s[4]);
    stmt.execute();
  }
  con.commit();
}

With this approach, we pass the bind variables as strings. If needed, based on the actual type of the columns being inserted to, conversion from string (VARCHAR) to numeric (NUMBER) will happen by the database.

You got basically all of it right, but didn't take the next step of actually setting the bind-variables ...

prasad_ · Answer 3 · 2019-02-20T03:15:57.437

This can work if the input List is already created:

List<String[]> fixtures = ...; // assuming this data is already created
String query = "INSERT INTO games (team1_id, team2_id, score1, score2, created_at) VALUES (? ,?, ?, ?, ? )";

try (Connection con = DBConnector.connect();
      PreparedStatement stmt = con.prepareStatement(query)) {

    for (String [] row : fixtures) {

        // This gets executed for each row insert
        for (int i = 0; i < row.length; i++) {
            stmt.setInt(i+1, Integer.parseInt(row[i]);
        }

        stmt.executeUpdate();
    }
}
catch(SQLException ex) {
    ex.printStackTrace();
    // code that handles exception...
}

How to insert List data into database using JDBC?

3 Answers3