Java – Inserts multiple rows from Eclipse into the Oracle database

Inserts multiple rows from Eclipse into the Oracle database… here is a solution to the problem.

Inserts multiple rows from Eclipse into the Oracle database

I want to insert multiple rows into the database from Eclipse. I wrote the code, but I think something is missing :

String SQL ="insert into INFO(ORDER_NO,ITEM_NAME,SERIAL_NO,P-CODE,QTY,RATE,TOTAL)values(?,?,?,?,?,?,?),(?,?,?,?,?,?,?)";
        try {
            PreparedStatement pst=con.prepareStatement(SQL);
            pst.setString(1, order.getText());
            pst.setString(2 ,item1.getSelectedItem().toString());
            pst.setString(3,serial1.getSelectedItem().toString());
            pst.setString(4,code1.getSelectedItem().toString());
            pst.setString(5,qty.getText());
            pst.setString(6,rat1.getSelectedItem().toString());
            pst.setString(7,label_1_1.getText());
            pst.setString(8, order.getText());
            pst.setString(9 ,item2.getSelectedItem().toString());
            pst.setString(10,serial2.getSelectedItem().toString());
            pst.setString(11,code2.getSelectedItem().toString());
            pst.setString(12,qty1.getText());
            pst.setString(13,rat2.getSelectedItem().toString());
            pst.setString(14,label_2.getText());
            pst.execute();
            JOptionPane.showMessageDialog(null,"Done");
            pst.close();    
        }catch (SQLException e) {
            e.printStackTrace();
        }

Solution

Use batch statements for multiple inserts in JDBC.
Starting with at least Java 7, you should use try-with to automatically close resources:

    String SQL = "insert into INFO(ORDER_NO,ITEM_NAME,SERIAL_NO,P-CODE,QTY,RATE,TOTAL) values (?,?,?,?,?,?,?)";
    try (PreparedStatement pst=con.prepareStatement(SQL)) {
        pst.setString(1, order.getText());
        pst.setString(2 ,item1.getSelectedItem().toString());
        pst.setString(3,serial1.getSelectedItem().toString());
        pst.setString(4,code1.getSelectedItem().toString());
        pst.setString(5,qty.getText());
        pst.setString(6,rat1.getSelectedItem().toString());
        pst.setString(7,label_1_1.getText());
        pst.addBatch();

pst.setString(1,order.getText());
        pst.setString(2,item2.getSelectedItem().toString());
        pst.setString(3,serial2.getSelectedItem().toString());
        pst.setString(4,code2.getSelectedItem().toString());
        pst.setString(5,qty1.getText());
        pst.setString(6,rat2.getSelectedItem().toString());
        pst.setString(7,label_2.getText());
        pst.addBatch();

pst.executeBatch();

JOptionPane.showMessageDialog(null,"Done");

} catch (SQLException e) {
        e.printStackTrace();
    }

See also this and this problem.

Related Problems and Solutions