John Joe John Joe - 6 months ago 23
MySQL Question

Insert ArrayList into MySQL

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();

}

Answer

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(); 
}