java将数据插入包含外键的mysql表时出错

Question

我的数据库中有两个表：

员工详细信息表：

• 员工id int主键
• 员工姓名varchar（20）

员工考勤：

• 员工id外键引用的员工id 员工详细信息表
• 出席人数(日期)
• 存在或不存在varchar（6）

以下是将数据插入员工考勤的java代码：

class Attendance
{
 java.util.Date utilDate;
 java.sql.Date sqlDate;

 String attDate;
 int empid;
 String pa;
 Connection con=null;
 PreparedStatement statement=null;
 ResultSet rs=null;

 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
 {                                         
    try
    {
    empid=Integer.parseInt(jTextField1.getText());
    sqlDate=new java.sql.Date(utilDate.getTime());
    pa=jComboBox1.getSelectedItem().toString();
    }

    catch(Exception e)
    {
        JOptionPane.showMessageDialog(null,e.getMessage());
    }

   String query = "Insert into employee_attendance (employee_id,attendance_for,present_or_absent) values (?,?,?)";

        try
        {
           Class.forName("com.mysql.jdbc.Driver");
           con=DriverManager.getConnection("jdbc:mysql:///hrmps?zeroDateTimeBehavior=convertToNull","root","root");
           statement=con.prepareStatement(query); 
           statement.setInt(1,empid);
           //sqlDate=new java.sql.Date(utilDate.getTime());
           statement.setDate(2,sqlDate);
           statement.setString(3, pa);
           statement.executeUpdate();
        }

        catch(Exception e)
        {
             JOptionPane.showMessageDialog(null,e.getMessage());
        }
}                                        
}

每当我尝试在员工考勤中插入数据时，我会得到以下例外情况：

"Cannot add or update a child row: a foreign key constraint fails"

Answer 1

正如评论者已经说过的，错误告诉你问题所在。“外键约束”冲突意味着您试图使用不存在的父行的外键值将行插入子表

因此，您需要检查父表（Employee）中的数据以及试图插入子表（Attention）中的值。你有一个不匹配

下面是一个完整的工作示例，创建数据库&；插入行，全部填充到单个类中。虽然这是不适合生产的代码，但它是正确插入子行（三次）和不正确插入子行（一次是demo方法的最后一行）的有效演示

演示的重点是demo方法中对insertAttendance的四个调用。我们得到的employee_行的标识符为1、2和3。因此，为这三个值中的任何一个插入attendance_行都会成功

this.insertAttendance ( conn , 2 …
this.insertAttendance ( conn , 1 …
this.insertAttendance ( conn , 3 …

当我们尝试一个无效的数字时，我们没有这样的employee_，比如4，那么我们失败了。我们得到一个SQLException，它注意到对foreign keyconstraint的违反。数据库正在执行referential integrity的工作。如果我们没有被阻止，我们将创建一个“孤立”行，一个没有匹配的employee_行的attendance_行

this.insertAttendance ( conn , 4 …  // Throws SQLException for violation of the foreign key constraint.

本例使用纯Java数据库H2 Database。必须添加为依赖项才能运行此代码。易于通过Maven等添加

跑步的时候

Tables established and populated.

Table dump: employee_
Employee id_: 1 | name_: Alfred
Employee id_: 2 | name_: Barbara
Employee id_: 3 | name_: Charlie

Table dump: attendance_
Attendance id_: 1 | fkey_employee_id_: 2 | when_expected_: 2016-01-23T10:00:00Z | status_:  present
Attendance id_: 2 | fkey_employee_id_: 1 | when_expected_: 2016-01-23T10:00:00Z | status_:  present
Attendance id_: 3 | fkey_employee_id_: 3 | when_expected_: 2016-01-23T10:00:00Z | status_:  absent

SQLException: Referential integrity constraint violation: "CONSTRAINT_32: PUBLIC.ATTENDANCE_ FOREIGN KEY(FKEY_EMPLOYEE_ID_) REFERENCES PUBLIC.EMPLOYEE_(ID_) (4)"; SQL statement:
INSERT INTO attendance_ ( fkey_employee_id_ , when_expected_ , status_ ) VALUES ( ? , ? , ? ); [23506-191]

示例代码

package com.example.h2example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Instant;

/**
 * Demonstrates how to both properly and improperly insert rows into a child
 * table.
 *
 * Caveat: Not production-worthy code. For demonstration purposes only. Use at
 * your own risk.
 *
 * @author Basil Bourque
 */
public class App {

    public static void main ( String[] args ) {
        App app = new App ();
        app.demo ();
    }

    private void demo () {
        Connection conn = this.fetchConnection ();

        this.insertAttendance ( conn , 2 , Instant.parse ( "2016-01-23T10:00:00Z" ) , "present" );
        this.insertAttendance ( conn , 1 , Instant.parse ( "2016-01-23T10:00:00Z" ) , "present" );
        this.insertAttendance ( conn , 3 , Instant.parse ( "2016-01-23T10:00:00Z" ) , "absent" );

        this.dumpTable_Employee ( conn );
        this.dumpTable_Attendance ( conn );

        // Insert invalid value (BAD).
        this.insertAttendance ( conn , 4 , Instant.parse ( "2016-01-23T10:00:00Z" ) , "absent" );
    }

    private Connection fetchConnection () {
        Connection conn = null;

        try {
            Class.forName ( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            // TODO: Handle exception.
            System.out.println ( "Database failure: " + e );
            return null;
        }

        // Specify a database named 'EmployeeAttendanceBogus.mv.db' in the Unix user’s home folder.
        String dbFolderPath = "~/";
        String dbName = "EmployeeAttendanceBogus";
        String dbUrl = "jdbc:h2:" + dbFolderPath + dbName;
        String dbUserName = "h2";
        String dbPassword = "pw";

        try {
            // If database does not yet exist, it is automatically created.
            conn = DriverManager.getConnection ( dbUrl , dbUserName , dbPassword );
        } catch ( SQLException ex ) {
            System.out.println ( "SQLException on DriverManager.getConnection: " + ex.getMessage () );
            // TODO: Handle exception when no Connection is made.
        }

        if ( null == conn ) {
            System.out.println ( "Database error. No Connection." );
            // TODO: Handle exception when no Connection is made.
        } else {
            // ELSE got database connection. Normal.
            this.recreateTables ( conn );
//            this.dumpTable_Employee ( conn );
//            this.dumpTable_Attendance ( conn );
        }
        return conn;
    }

    private void recreateTables ( Connection conn ) {
        // Update database structure if needed.

        StringBuilder sql = new StringBuilder ();

        // Delete any existing tables.
        sql.append ( "DROP TABLE IF EXISTS attendance_ ; " + " \n" );   // Drop child table first, because of referential integrity.
        sql.append ( "DROP TABLE IF EXISTS employee_ ; " + " \n" );

        // Define tables.
        sql.append ( "CREATE TABLE employee_ " + " \n" );
        sql.append ( "(" + " \n" );
        sql.append ( "id_ IDENTITY PRIMARY KEY , " + " \n" );  // Primary key, Long type.
        sql.append ( "name_ VARCHAR_IGNORECASE NOT NULL " + " \n" );
        sql.append ( ")" + " \n" );
        sql.append ( ";" + " \n" );

        sql.append ( "" );
        sql.append ( "CREATE TABLE attendance_ " + " \n" );
        sql.append ( "(" + " \n" );
        sql.append ( "id_ IDENTITY PRIMARY KEY , " + " \n" );  // Primary key, Long type.
        sql.append ( "fkey_employee_id_ BIGINT , " + " \n" );
        sql.append ( "when_expected_ TIMESTAMP NOT NULL , " + " \n" );
        sql.append ( "status_ VARCHAR_IGNORECASE NOT NULL " + " \n" );  // Domain: "present" | "absent" .
        sql.append ( ")" + " \n" );
        sql.append ( ";" + " \n" );
        sql.append ( "" );

        sql.append ( "ALTER TABLE attendance_ ADD FOREIGN KEY ( fkey_employee_id_ ) REFERENCES employee_( id_ ) ;" );
        sql.append ( "" );

        sql.append ( "INSERT INTO employee_ ( name_ ) VALUES ( 'Alfred' ) ;" );
        sql.append ( "INSERT INTO employee_ ( name_ ) VALUES ( 'Barbara' ) ;" );
        sql.append ( "INSERT INTO employee_ ( name_ ) VALUES ( 'Charlie' ) ;" );

        System.out.println ( "Tables established and populated.\n" );

        try ( Statement stmt = conn.createStatement () ) {
            stmt.executeUpdate ( sql.toString () );

        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage () );
            // TODO: Handle exception.
        }

    }

    private void dumpTable_Employee ( Connection conn ) {

        StringBuilder sql = new StringBuilder ();
        sql.append ( "SELECT * FROM employee_ ;" );

        try ( PreparedStatement pstmt = conn.prepareStatement ( sql.toString () ) ) {
            try ( ResultSet rs = pstmt.executeQuery (); ) {
                System.out.println ( "Table dump: employee_" );
                while ( rs.next () ) {
                    long id = rs.getLong ( "id_" );
                    String name = rs.getString ( "name_" );
                    System.out.println ( "Employee id_: " + id + " | name_: " + name );
                }
                System.out.println ( "" );
            }

        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage () );
            // TODO: Handle exception.
        }
    }

    private void dumpTable_Attendance ( Connection conn ) {

        StringBuilder sql = new StringBuilder ();
        sql.append ( "SELECT * FROM attendance_ ;" );

        try ( PreparedStatement pstmt = conn.prepareStatement ( sql.toString () ) ) {
            try ( ResultSet rs = pstmt.executeQuery (); ) {
                System.out.println ( "Table dump: attendance_" );
                while ( rs.next () ) {
                    long id = rs.getLong ( "id_" );
                    long fkey = rs.getLong ( "fkey_employee_id_" );
                    java.sql.Timestamp whenExpectedTs = rs.getTimestamp ( "when_expected_" );
                    Instant whenExpected = whenExpectedTs.toInstant ();  // Convert as soon as possible from java.sql.Timestamp to java.time.
                    String status = rs.getString ( "status_" );
                    System.out.println ( "Attendance id_: " + id + " | fkey_employee_id_: " + fkey + " | when_expected_: " + whenExpected + " | status_:  " + status );
                }
                System.out.println ( "" );
            }

        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage () );
            // TODO: Handle exception.
        }
    }

    private void insertAttendance ( Connection conn , long employeeId , Instant whenExpected , String status ) {
        StringBuilder sql = new StringBuilder ();
        sql.append ( "INSERT INTO attendance_ ( fkey_employee_id_ , when_expected_ , status_ ) VALUES ( ? , ? , ? );" );

        try ( PreparedStatement pstmt = conn.prepareStatement ( sql.toString () ) ) {
            pstmt.setLong ( 1 , employeeId );
            pstmt.setTimestamp ( 2 , java.sql.Timestamp.from ( whenExpected ) );
            pstmt.setString ( 3 , status );
            int rowsAffected = pstmt.executeUpdate ();

        } catch ( SQLException ex ) {
            System.err.println ( "SQLException: " + ex.getMessage () );
            // TODO: Handle exception.
        }
    }

}

顺便说一下

您的代码显示您可能对java.util.Date和java.sql.Date感到困惑。第一个是日期和时间，而第二个假装只是日期而没有时间

避免使用旧的java。util。Date类（和.Calendar），因为它设计拙劣、混乱且麻烦。使用java。而是将时间内置到Java8和更高版本中

爪哇。sql类型同样糟糕。但我们必须继续使用它们，直到我们的JDBC驱动程序更新为直接使用java。时间类型。在此之前，请使用java。sql类型尽可能简短，以便将数据移入/移出数据库。从java转换。sql转换为java。收到一个值后的时间