oracle 我的 SQL 语句有什么问题？不能插入 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5691997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is wrong with my SQL statement? Cannot Insert NULL
提问by Mike
What am I doing wrong? I keep getting weird errors from SQL?
我究竟做错了什么？我不断收到来自 SQL 的奇怪错误？
//get parameters from the request
String custID=request.getParameter("cust_ID");
String saleID=request.getParameter("sale_ID");
String firstName=request.getParameter("first_Name");
String mInitial=request.getParameter("mI");
String lastName=request.getParameter("last_Name");
String streetName=request.getParameter("street");
String city=request.getParameter("city");
String state=request.getParameter("state");
String zipCode=request.getParameter("zip_Code");
String DOB=request.getParameter("DOB");
String agentID=request.getParameter("agent_ID");
String homePhone=request.getParameter("home_Phone");
String cellPhone=request.getParameter("cell_Phone");
String profession=request.getParameter("profession");
String employer=request.getParameter("emoployer");
String referrer=request.getParameter("referrer");
preparedStatement = conn.prepareStatement("INSERT INTO customer (cust_ID, sale_ID, first_Name, mI, last_Name, street_Name, city, state, zip_Code, DOB, agent_ID, home_Phone, cell_Phone, profession, employer, referrer)"
+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
preparedStatement.setInt(1, Integer.valueOf(custID));
preparedStatement.setInt(2, Integer.valueOf(saleID));
preparedStatement.setString(3, firstName);
preparedStatement.setString(4, mInitial);
preparedStatement.setString(5, lastName);
preparedStatement.setString(6, streetName);
preparedStatement.setString(7, city);
preparedStatement.setString(8, state);
preparedStatement.setString(9, zipCode);
preparedStatement.setString(10, DOB);
preparedStatement.setInt(11, Integer.valueOf(agentID));
preparedStatement.setString(12, homePhone);
preparedStatement.setString(13, cellPhone);
preparedStatement.setString(14, profession);
preparedStatement.setString(15, employer);
preparedStatement.setString(16, referrer);
preparedStatement.executeUpdate();
SQL DDL
SQL DDL
CREATE TABLE customer
(cust_ID NUMBER NOT NULL,
sale_ID NUMBER NOT NULL,
first_Name VARCHAR2(30) NOT NULL,
mI VARCHAR2(2) ,
last_Name VARCHAR2(50) NOT NULL,
street_Name VARCHAR2(50) NOT NULL,
city VARCHAR2(30) NOT NULL,
state VARCHAR2(2) NOT NULL,
zip_Code VARCHAR2(5) NOT NULL,
DOB DATE ,
agent_ID NUMBER ,
home_Phone VARCHAR2(12) UNIQUE,
cell_Phone VARCHAR2(12) UNIQUE,
profession VARCHAR2(30) ,
employer VARCHAR2(30) ,
referrer VARCHAR2(30)
);
SQL DML
数据库语言
INSERT INTO customer VALUES (primary_ID.nextval,17,'Kito','M','Bradford','123 DeLancy Lane','Wabash','TX','12345','01-JAN-69',1,'222-222-2222','301-555-6874','software engineer','HPL', NULL);
INSERT INTO customer VALUES (primary_ID.nextval,18,'Morpheus',' ','Smith','1289 Matrix Lane','Idaho', 'NE', '45678','06-JUN-72',2,'321-654-9877','258-852-9635','doctor', 'The OC', NULL);
Error message
错误信息
org.apache.jasper.JasperException: An exception occurred processing JSP page
/customerAdd.jsp at line 70
67: <%preparedStatement.setString(15, employer);%>
68: <%preparedStatement.setString(16, referrer);%>
69:
70: <%preparedStatement.executeUpdate();%>
root cause
javax.servlet.ServletException: java.sql.SQLException: [Oracle][ODBC][Ora]ORA-
01400:cannot insert NULL into ("CM485A07"."CUSTOMER"."STREET_NAME")
回答by Eelke
You are tyring to insert a NULL value into the street_Name column which has a NOT NULL constraint.
您正在尝试将 NULL 值插入到具有 NOT NULL 约束的 street_Name 列中。
Probably something is wrong here:
可能这里出了点问题：
String streetName=request.getParameter("street");
Double check the name of the request parameter against the actual request. Maybe there is a mismatch in names or the requesting side forgets to send it.
根据实际请求仔细检查请求参数的名称。也许名称不匹配或请求方忘记发送它。
BTW look like you have a typo here to:
顺便说一句，你在这里有一个错字：
String employer=request.getParameter("emoployer");
I expect your request parameter is called "employer" without the additional o
我希望您的请求参数被称为“雇主”而没有额外的 o
