mysql java。sql。SQLException:字段“Cust_LastName”没有默认值

Question

错误表明，如果我的知识正确，我不会给Cust_LastName一个值，但我正试图使用下面的代码

整个集合用于数据库中的一个新行

// Accessing driver from JAR file
try {
    Class.forName("com.mysql.jdbc.Driver");

    // Creating a variable for the connection
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/schbank", "root", "s201431s");

    // SQL statements to insert the information into the table
    PreparedStatement statementFName = con.prepareStatement("insert into tblcustomer(Cust_FirstName)" + "values(?)");
    PreparedStatement statementLName = con.prepareStatement("insert into tblcustomer(Cust_LastName)" + "values(?)");
    PreparedStatement statementAddress = con.prepareStatement("insert into tblcustomer(Cust_Address)" + "values(?)");
    PreparedStatement statementPhone = con.prepareStatement("insert into tblcustomer(Cust_PhoneNumber)" + "values(?)");
    PreparedStatement statementSSN = con.prepareStatement("insert into tblcustomer(Cust_SSN)" + "values(?)");
    PreparedStatement statementEmail = con.prepareStatement("insert into tblcustomer(Cust_Email)" + "values(?)");
    PreparedStatement statementUsername = con.prepareStatement("insert into tblcustomer(Cust_Username)" + "values(?)");
    PreparedStatement statementPassword = con.prepareStatement("insert into tblcustomer(Cust_Password)" + "values(?)");

    // Sets the values
    statementFName.setString(1, strFname);
    statementLName.setString(1, strLname);
    statementAddress.setString(1, strAddress);
    statementPhone.setString(1, strPhone);
    statementSSN.setString(1, strSSN);
    statementEmail.setString(1, strEmail);
    statementUsername.setString(1, strUserName);
    statementPassword.setString(1, strPassword);

    // Other code removed for brevity
} catch (SQLException ex) {
    ex.printStackTrace(System.err);
}

Answer 1

当您编写单个insert语句时，这意味着它尝试为数据库中的每个语句输入单个行。此错误是由于在执行第一个insert语句时，它需要Cust_LastName值，因为此字段没有默认值

您可以对所有列使用单个insert语句，也可以使用参数insert查询

Answer 2

请尝试以下方法：

PreparedStatement stmt = con.prepareStatement(
    "INSERT INTO tblcustomer (Cust_FirstName, Cust_LastName, Cust_Address, Cust_PhoneNumber, Cust_SSN, Cust_Email, Cust_Username, Cust_Password)"
    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

stmt.setString(1, strFname);
stmt.setString(2, strLname);
stmt.setString(3, strAddress);
stmt.setString(4, strPhone);
stmt.setString(5, strSSN);
stmt.setString(6, strEmail);
stmt.setString(7, strUserName);
stmt.setString(8, strPassword);

按照现在的方式，您正在尝试向表中进行8次单独的插入，您需要一次具有8个值的插入

Answer 3

你的每一个陈述都是一个单独的陈述。每个语句在执行时将插入一个单独的行。该行将只有通过语句填充的一列，而所有其他列将由其默认值填充

在第一条语句中，您只给了Cust_Firstname一个值。因此，您没有给Cust_LastName赋值，并且您的数据库告诉您，由于这个原因，它无法执行该语句

您可能希望一行包含所有列值，而不是八行包含单个列集。为此，您必须准备一条语句并填充其中的所有值