Java adding a list to database

Question

So I have a Product.java class:

Public class Product{
private String id;
private String name;
private float price;
}

I have a list of Product that get data from database:

public List<Product> getProductList() {
    List<Product> list = new ArrayList<Product>();
    String sql = "SELECT * FROM Product";
    try {
        Statement st = getConnection().createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            String id = rs.getString(1);
            String name = rs.getString(3);
            float price = rs.getFloat(4);
            Product p = new Product();
            p.setId(id);
            p.setName(name);
            p.setPrice(price);
            list.add(p);
        }
        rs.close();
        st.close();
    } catch (SQLException ex) {
        Logger.getLogger(DataProcess.class.getName()).log(Level.SEVERE, null, ex);
    }
    return list;
}

I add some Product to another list, and now I want to add that list back to database into table Sold, but I only know how to add a Product items to database, not the whole list. Is there a way to add a list to database?

score 0 · Answer 1 · edited May 23 '17 at 10:27

This can be achieved with PreparedStatement's addBatch() and executeBatch() methods as this other answer demonstrates:

This is a mix of the two previous answers:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");

  ps.setString(1, "John");
  ps.setString(2,"Doe");
  ps.addBatch();

  ps.clearParameters();
  ps.setString(1, "Dave");
  ps.setString(2,"Smith");
  ps.addBatch();

  ps.clearParameters();
  int[] results = ps.executeBatch();

SOURCE: https://stackoverflow.com/a/3786127

In your case you would set the necessary parameters and then call addBatch() on each iteration of a loop over the list and then after the loop call executeBatch().

You may want to call executeBatch() inside the loop after a certain number of items if the database places limits on batches and doesnmt handle those limits transaparently in its JDBC driver.

Java adding a list to database

1 Answers1