java - 插入sql默认值错误

Question

我试图通过 java 将值插入数据库，但我不断收到“没有默认值”错误消息。我在网上寻找一些帮助，但他们并没有真正解决我的问题。这是我收到的错误“java.sql.SQLException:字段“firstName”没有默认值”。

import java.sql.*;

class MysqlCon {

public static void main(String[] args) {
    String personalID = null;
    String firstname = null;
    String lastname = null;
    String addressone = null;
    String addresstwo = null;
    String city = null;
    String state = null;
    String zipcode = null;
    String phone = null;
    String email = null;

    try
    {

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Address_Book","root","");
Statement stmt = con.createStatement();

personalID ="3";
firstname = "John";
lastname = "Doe";
addressone = "4000 s.c.r. 222";
addresstwo = "5000 n.c.r. 333";
city = "place";
state = "Texas";
zipcode = "43523";
phone = "43523524";
email = "weqtatw@aadf.com";

stmt.executeUpdate("insert into Names(personID) values("+personalID+")");
stmt.executeUpdate("insert into Names(firstname) values("+firstname+")");
stmt.executeUpdate("insert into Names(lastname) values("+lastname+")");
stmt.executeUpdate("insert into addresses(address1) values("+addressone+")");
stmt.executeUpdate("insert into addresses(address2) values("+addresstwo+")");
stmt.executeUpdate("insert into addresses(city) values("+city+")");
stmt.executeUpdate("insert into addresses(state) values("+state+")");
stmt.executeUpdate("insert into addresses(zipcode) values("+zipcode+")");
stmt.executeUpdate("insert into phoneNumbers(phoneNumber) values("+phone+")");
stmt.executeUpdate("insert into emailAddresses(emailAddress) values("+email+")");
con.close();
    }catch(Exception e){System.out.println(e);}
}

}

Answer 1

您当前正在一个表中运行三个单独的插入语句，例如:

INSERT INTO Names (personID) values (3);
INSERT INTO Names (firstname) values ("John");
INSERT INTO Names (lastname) values ("Doe");

您最终将得到 3 行独立的行，而不是一行。如果任何列需要一个值(即，如果您有一个没有 auto_increment 的主键)，则插入将失败，因为它将需要该列的值(编辑:在这种情况下，您的 firstName 字段需要一个值) .

您还会有一个非常无用的表，其中包含诸如一行仅包含名称“John”、一行仅包含姓氏“Doe”以及一行 personID 为 3 等条目。

相反，您应该运行单个 INSERT 语句，例如:

stmt.executeUpdate("insert into Names(personID, firstname, lastname) values("+personalID+", "+firstname+", "+lastname+")");

这还应该填充一行中的所有必需单元格。