Insert ArrayList into MySQL

Question

I trying to insert ArrayList into MySQL, but I only gets the last values in column selected_seats. The variable list holds value 2 and 3. When I check MySQL, only value 3 get inserted, 2 is not inserted.

public void insert( String Name, String NC, String acNum, String title, String day, String time, double totalPrice, ArrayList<Integer> list) throws Exception{
            System.out.println(list);
            DatabaseConnection db=new DatabaseConnection();
            Connection connect=db.getConnection();
            String sql="Insert into user_payment(user_name,ic_number,acc_number, movie_title,movie_day,movie_time, total_price, selected_seats)VALUES (?,?,?,?,?,?,?,?)";
            PreparedStatement ps=connect.prepareStatement(sql);
            ps.setString(1,Name);
            ps.setString(2,NC);
            ps.setString(3,acNum);
            ps.setString(4,title);
            ps.setString(5,day);
            ps.setString(6,time);
            ps.setDouble(7,totalPrice);
            for(Integer seat : list)
            {
                ps.setInt(8,seat);
                ps.executeBatch();
            }
            ps.executeUpdate();

            connect.close();
            ps.close();

        }

Community · Accepted Answer · 2020-06-20 09:12:55Z

What you meant to do is call addBatch() inside the loop, then executeBatch() after the loop, and not call executeUpdate() at all.

addBatch()

Adds a set of parameters to this PreparedStatement object's batch of commands.

executeBatch()

Submits a batch of commands to the database for execution

Also, you have to close the PreparedStatement before closing the Connection. Better yet, use try-with-resources.

public void insert(String Name, String NC, String acNum, String title, String day, String time, double totalPrice, ArrayList<Integer> list) throws Exception {
    System.out.println(list);
    DatabaseConnection db = new DatabaseConnection();
    try (Connection connect = db.getConnection()) {
        String sql = "insert into user_payment (user_name, ic_number, acc_number, movie_title, movie_day, movie_time, total_price, selected_seats) VALUES (?,?,?,?,?,?,?,?)";
        try (PreparedStatement ps = connect.prepareStatement(sql)) {
            ps.setString(1, Name);
            ps.setString(2, NC);
            ps.setString(3, acNum);
            ps.setString(4, title);
            ps.setString(5, day);
            ps.setString(6, time);
            ps.setDouble(7, totalPrice);
            for (Integer seat : list) {
                ps.setInt(8, seat);
                ps.addBatch();
            }
            ps.executeBatch();
         }
    }
}

UPDATE

Is it possible to make it insert only one row, which mean column selected_seats value has 2,3 instead create two rows ?

If the selected_seats column is not a numeric column as the original setInt(8,seat) was indicating, but a VARCHAR which should get a comma-separated list of seat numbers, then a batch is not needed. You then need to convert the List<Integer> into that comma-separated string

StringJoiner buf = new StringJoiner(",");
for (Integer seat : list)
    buf.add(seat.toString());
ps.setString(8, buf.toString());
ps.executeUpdate();

StringJoiner was added in Java 8. If running on earlier version, use StringBuilder:

StringBuilder buf = new StringBuilder();
for (Integer seat : list) {
    if (buf.length() != 0)
        buf.append(',');
    buf.append(seat);
}
ps.setString(8, buf.toString());
ps.executeUpdate();

Is it possible to make it insert only one row, which mean column selected_seats value has 2,3 instead create two rows ?
@JohnJoe Yes, but then it wouldn't be an INTEGER column anymore.

Kaung Yee Hein · Accepted Answer · 2016-05-21 17:06:14Z

You need to insert seat to another table or can be insert with String type as below.

public void insert( String Name, String NC, String acNum, String title, String day, String time, double totalPrice, ArrayList<Integer> list) throws Exception { 
    System.out.println(list);
    DatabaseConnection db=new DatabaseConnection(); 
    Connection connect=db.getConnection(); 
    String sql="Insert into user_payment(user_name,ic_number,acc_number, movie_title,movie_day,movie_time, total_price, selected_seats)VALUES (?,?,?,?,?,?,?,?)"; 
    PreparedStatement ps=connect.prepareStatement(sql); 
    ps.setString(1,Name); 
    ps.setString(2,NC); 
    ps.setString(3,acNum); 
    ps.setString(4,title); 
    ps.setString(5,day); 
    ps.setString(6,time); 
    ps.setDouble(7,totalPrice); 
    String seatList = "";
    for(Integer seat : list) { 
        if(seatList.equal("")) {
            seatList = seat;
        } else {
            seatList += "," + seat;
        }
    } 
    ps.setString(8,seatList); 
    ps.executeBatch(); 
    ps.executeUpdate(); 
    connect.close(); 
    ps.close(); 
}

Don't do string += string in a loop. In Java 8 use a StringJoiner, otherwise use a StringBuilder.

Collectives™ on Stack Overflow

Insert ArrayList into MySQL

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related