添加具有已排序的主键和外键JDBC的列的行 [英] Adding a row with columns that have sequenced primary and foreign key JDBC
问题描述
我的程序有一个添加项和完成交易选项。 FinishTransaction 类要求用户输入客户的信息，付款方式和付款。
My program has an add item and finish transaction option. The FinishTransaction class asks the user to input the customer's information, method of payment, and the payment.
总付款显示在窗口中。当用户单击 checkout 按钮时，数据应从 CUSTOMER 表中传输（插入客户的信息）， ORDERS table（插入商品的信息）和 TRANSACTION 表（插入交易信息）。
The total payment is displayed at the window. When the user click the checkout button, the data should be transferred from the CUSTOMER table (insert the customer's information), ORDERS table (insert the item's information bought), and the TRANSACTION table (insert the transaction information).
事务表有一列 TRANS_CUSTNUM ，这是一个引用的外键。 CUSTOMER 表中的CUST_NUM
The transaction table has a column of TRANS_CUSTNUM that is a foreign key referenced to the CUST_NUM in the CUSTOMER table.
我的程序工作正常，但 TRANSACTION 表。它不会在我的SQL开发人员中添加一行。您认为我的代码有什么问题？
My program works fine except at the TRANSACTION table. It does not add a row in my SQL developer. What do you think is wrong in my code?
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);
}
}
（已编辑）
工作生成的代码：
(EDITED) Working generated code:
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();
推荐答案
我很确定这个sql insert语句是错误。
I'm pretty sure that this sql insert statement is incorrect.
String sql3 = "INSERT INTO TRANSACTION " +
"VALUES(TransNumSeq.NEXTVAL, CustNumSeq.NEXTVAL, ?, ?)";
您正在将记录插入TRANSACTION并尝试在CUSTOMER上推进序列（CustNumSeq.NEXTVAL） 。在插入CUSTOMER时，你应该尝试使用
You're inserting a record into TRANSACTION and trying to advance the sequence on CUSTOMER (CustNumSeq.NEXTVAL) . You should try to use
//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来获取最后生成的密钥。
Note that not every JDBC driver supports returning generated keys so you may have to use one additional select to fetch the last generated key.
最后，您必须更改此部分以反映上面所做的更改
And finally, you will have to change this part to reflect the changes made above
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);
这篇关于添加具有已排序的主键和外键JDBC的列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
