java - 添加一行，其中的列已排序主键和外键 JDBC

Question

我的程序有一个添加项目和完成交易选项。 FinishTransaction 类要求用户输入客户信息、付款方式和付款金额。

支付总额显示在窗口中。当用户单击结帐按钮时，数据应从CUSTOMER表(插入客户信息)、ORDERS表(插入购买的商品信息)和 TRANSACTION 表(插入交易信息)。

交易表有一列 TRANS_CUSTNUM，它是引用 CUSTOMER 表中 CUST_NUM 的外键。

除了 TRANSACTION 表之外，我的程序运行良好。它不会在我的 SQL 开发人员中添加一行。您认为我的代码有什么问题？

import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.logging.*;

public class FinishTransaction extends JFrame implements ActionListener{
    JLabel totalLabel;
    JLabel nameLabel;
    JLabel addressLabel;
    JLabel contactLabel;
    JLabel custPaymentLabel;
    JLabel methodLabel;
    JLabel creditCardLabel;
    JTextField totalTextField;
    JTextField nameTextField;
    JTextField addressTextField;
    JTextField contactTextField;
    JTextField custPaymentTextField;
    JTextField creditCardTextField;
    final JButton mainMenuButton = new JButton("Main Menu");
    final ButtonGroup bGroup = new ButtonGroup();
    final JRadioButton cashRadioButton = new JRadioButton("Cash");
    final JRadioButton creditRadioButton = new JRadioButton("Credit Card");
    final JButton checkoutButton = new JButton("Checkout");

    static FinishTransaction fin = new FinishTransaction();
    static AddItem add = new AddItem();
    static int total = 0;
    static int payment = 0;
    static int change = 0;
    static String payment_desc;
    static int creditCard;

    public FinishTransaction(){
        //ui
    }

    public void actionPerformed(ActionEvent e){
        if(checkoutButton.getName().equals(((Component)e.getSource()).getName())){
            try{
                payment = Integer.parseInt(custPaymentTextField.getText());
                if(payment>=total){
                    change = payment - total;
                    JOptionPane.showMessageDialog(this, "Thank you for shopping! Your change is "+change, "Exiting", JOptionPane.INFORMATION_MESSAGE);
                }
                else
                    JOptionPane.showMessageDialog(this, "Your payment is not enough. Please try again!", "Error!", JOptionPane.ERROR_MESSAGE);
            }
            catch(NumberFormatException a){
                JOptionPane.showMessageDialog(this, "Invalid input", "Error!", JOptionPane.ERROR_MESSAGE);
            }
            Connection conn = null;
            PreparedStatement pstmt = null;
            PreparedStatement pstmt2 = null;
            PreparedStatement pstmt3 = null;
            String URL = "jdbc:oracle:thin:@VAIO:49160:xe";
            String USER = "mariel";
            String PASS = "1234";

            try {
                  Class.forName("oracle.jdbc.driver.OracleDriver");
                try {
                    String name = nameTextField.getText();
                    String address = addressTextField.getText();
                    int contact = Integer.parseInt(contactTextField.getText());
                    conn = DriverManager.getConnection(URL, USER, PASS);
                    String sql = "INSERT INTO CUSTOMER " + 
                            "VALUES(CustNumSeq.NEXTVAL, ?, ?, ?)";
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setString(1, name);
                    pstmt.setString(2, address);
                    pstmt.setInt(3, contact);

                    pstmt.executeUpdate();

                    for(int index=0;index<add.itemNum.length;index++){
                        String sql2 = "INSERT INTO ORDERS "+
                                "VALUES(OrderNumSeq.NEXTVAL, ?, ?)";    
                        pstmt2 = conn.prepareStatement(sql2);
                        pstmt2.setInt(1,add.itemNum[index]);
                        pstmt2.setInt(2, add.quantity[index]);

                        pstmt2.executeUpdate();
                    }

                    creditCard = Integer.parseInt(creditCardTextField.getText());
                    String sql3 = "INSERT INTO TRANSACTION " + 
                            "VALUES(TransNumSeq.NEXTVAL, CustNumSeq.NEXTVAL, ?, ?, ?, ?)";
                    pstmt3 = conn.prepareStatement(sql3);
                    pstmt3.setInt(1, payment);
                    pstmt3.setString(2, payment_desc);
                    pstmt3.setInt(3, creditCard);
                    pstmt3.setInt(4, change);

                    pstmt.executeUpdate();
                } 
                catch (SQLException ex) {
                }
                catch(NumberFormatException a){
                    JOptionPane.showMessageDialog(this, "Invalid input", "Error!", JOptionPane.ERROR_MESSAGE);
                }
            }
            catch(ClassNotFoundException ex) {
                System.out.println("Error: unable to load driver class!");
                System.exit(1);
            }
            catch(NumberFormatException a){
                JOptionPane.showMessageDialog(this, "Invalid input", "Error!", JOptionPane.ERROR_MESSAGE);
            }
            finally{
                try{
                   if(pstmt!=null)
                      pstmt.close();
                }
                catch(SQLException se2){
                }
                try{
                   if(pstmt2!=null)
                      pstmt2.close();
                }
                catch(SQLException se2){
                }
                try{
                   if(pstmt3!=null)
                      pstmt3.close();
                }
                catch(SQLException se2){
                }
                try{
                   if(conn!=null)
                   conn.close();
                }
                catch(SQLException se){
                }
            }
        }
        else if(mainMenuButton.getName().equals(((Component)e.getSource()).getName())){
            EmployeeMode emp = new EmployeeMode();
            emp.setVisible(true);
            emp.setResizable(false);
            emp.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            emp.setSize(400,300);
            this.setVisible(false);
        }
        if(creditRadioButton.isSelected()){
            creditCardLabel.setVisible(true);
            creditCardTextField.setVisible(true);
            payment_desc = "Credit Card";
        }
        else if(cashRadioButton.isSelected()){
            creditCardLabel.setVisible(false);
            creditCardTextField.setVisible(false);
            payment_desc = "Cash";
        }
    }
    public static void main(String args[]){
        fin.setVisible(true);
        fin.setResizable(false);
        fin.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        fin.setSize(500,400);
    }
}

(已编辑)工作生成的代码:

                        String name = nameTextField.getText();
                        String address = addressTextField.getText();
                        int contact = Integer.parseInt(contactTextField.getText());
                        conn = DriverManager.getConnection(URL, USER, PASS);

                        for(int index=0;index<add.itemNum.length;index++){
                            String sql = "INSERT INTO ORDERS "+
                                    "VALUES(OrderNumSeq.NEXTVAL, ?, ?)";    
                            pstmt = conn.prepareStatement(sql);
                            pstmt.setInt(1,add.itemNum[index]);
                            pstmt.setInt(2, add.quantity[index]);

                            pstmt.executeUpdate();
                        }

                        String sql2 = "INSERT INTO CUSTOMER " + 
                                "VALUES(CustNumSeq.NEXTVAL, ?, ?, ?)";
                        String generatedColumns[] = {"CUST_NUM"};
                        pstmt2 = conn.prepareStatement(sql2, generatedColumns);
                        pstmt2.setString(1, name);
                        pstmt2.setString(2, address);
                        pstmt2.setInt(3, contact);
                        pstmt2.executeUpdate();
                        ResultSet rs = pstmt2.getGeneratedKeys();
                        custNum = rs.getInt("CUST_NUM");

                        creditCard = Integer.parseInt(creditCardTextField.getText());
                        String sql3 = "INSERT INTO TRANSACTION " + 
                            "VALUES(TransNumSeq.NEXTVAL, ?, ?, ?, ?, ?)";
                        pstmt3 = conn.prepareStatement(sql3);
                        pstmt3.setInt(1, custNum);
                        pstmt3.setInt(2, payment);
                        pstmt3.setString(3, payment_desc);
                        pstmt3.setInt(4, creditCard);
                        pstmt3.setInt(5, change);

                        pstmt3.executeUpdate();

Answer 1

我很确定这个sql插入语句是不正确的。

String sql3 = "INSERT INTO TRANSACTION " + 
                        "VALUES(TransNumSeq.NEXTVAL,  CustNumSeq.NEXTVAL, ?, ?)";

您正在将一条记录插入 TRANSACTION 并尝试推进 CUSTOMER (CustNumSeq.NEXTVAL) 上的序列。您应该尝试使用

//when inserting into CUSTOMER 
pstmt=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pstmt.execute();//make the insert
//and then simply grab the key
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) 
id = rs.getObject(1);

请注意，并非每个 JDBC 驱动程序都支持返回生成的 key ，因此您可能必须使用一个额外的选择来获取最后生成的 key 。

最后，您必须更改此部分以反射(reflect)上面所做的更改

String sql3 = "INSERT INTO TRANSACTION " + 
                        "VALUES(TransNumSeq.NEXTVAL, ?, ?, ?, ?, ?)";
pstmt3 = conn.prepareStatement(sql3);
pstmt3.setInt(1, id);
pstmt3.setInt(2, payment);
pstmt3.setString(3, payment_desc);
pstmt3.setInt(4, creditCard);
pstmt3.setInt(5, change);