java jdbc 复合主键_java – 使用已排序的主键和外键JDBC的列添加行

我的程序有一个添加项目和完成交易选项. 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

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

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

解决方法:

我很确定这个sql insert语句不正确.

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驱动程序都支持返回生成的键,因此您可能必须使用一个额外的select来获取最后生成的键.

最后,您将不得不更改此部分以反映上面所做的更改

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

标签：java,jdbc,prepared-statement,oracle-sqldeveloper

来源： https://codeday.me/bug/20191003/1848667.html