CODEWITHSUNDEEP
javasqldatabaseeclipserows
user9363615
user9363615

Reputation:

Inserting multipe rows into oracle database from eclipse

I want to insert multiple rows into 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();
        }

Upvotes: 3

Views: 460

Answers (2)

Stefan Winkler
Stefan Winkler

Reputation: 3966

Use batched statements for multiple inserts in JDBC. And since at least Java 7 you should use try-with to get resources closed automatically:

    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 questions.

Upvotes: 2

Andomar
Andomar

Reputation: 238246

You are inserting two rows in one query:

values (?,?,?,?,?,?,?),(?,?,?,?,?,?,?)
                     ^^^

But Oracle doesn't support values with multiple rows. One good solution is to insert only one row per insert command.

An alternative is Oracle's insert all, like:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

Upvotes: 1

Related Questions