使用另一个表的结果将数据插入到多个表中

Question

我正在使用JDBC和MySql。我将数据插入到一个表中并获取主键transactionid。现在使用transactionid插入数据到另一个表中。

我的代码：

if (connection != null) { 
     System.out.println("Now connection is created successfully"); 
     stmt=connection.createStatement(); 
     Date date=new Date(); 
     DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss"); 
     String time=formatter.format(date); 
     System.out.println("time is "+time); 
     //insert transaction id and minimum support.... 
     mySql = "insert into Transaction (minimumsupport) 
values('"+minimumSupport+"')"; 
     stmt.executeUpdate(mySql,Statement.RETURN_GENERATED_KEYS); 
     ResultSet rt=stmt.getGeneratedKeys(); 
     int transactionId; 
     while(rt.next()){ 
      transactionId=rt.getInt(1); 
      System.out.println("the transaction id is: "+transactionId); 
     } 
     rt.close(); 

     int i; 
     Statement stmt1; 
     for(i=0;i<productArray.length;i++) 
     { 
      stmt=connection.createStatement(); 
      System.out.println("first value: "+productArray[i]); 
      mySql = "insert into 
         Producttransaction(transactionid,productId) 
    values('"+transactionId+"','"+productArray[i]+"')"; 
      stmt.executeUpdate(mySql); 
      status="productAdded"; 
      System.out.println("product added"); 
     } 
      //To insert the data into the tables.... 
     connection.close(); 

抛出以下异常：

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 
Cannot add or update a child row: a foreign key constraint fails (`aprioridb`.`Producttransaction`, CONSTRAINT `fk_Productid` FOREIGN KEY (`transactionid`) REFERENCES `Product` (`productId`) ON DELETE NO ACTION ON UPDATE NO ACTION) 

Answer 1

看来你的外键约束是错误的：

FOREIGN KEY (transactionid) REFERENCES Product (productId) 

应该

FOREIGN KEY (productid) REFERENCES Product (productId) 

另外，了解准备好的语句以避免SQL注入攻击，拥有更健壮，更高效的代码。