java.sql.SQLException: ORA-00917: 缺少逗号
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/19105939/
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
java.sql.SQLException: ORA-00917: missing comma
提问by Jeff Levine
I'm pulling my hair out trying to find the cause of the SqlExcpetion below. I'm using Oracle Database 11g Express and SqlDeveloper. I've looked through other posts, but so far no help.
我正在努力寻找下面 SqlExcpetion 的原因。我正在使用 Oracle Database 11g Express 和 SqlDeveloper。我已经浏览了其他帖子,但到目前为止没有任何帮助。
java.sql.SQLException: ORA-00917: missing comma
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1029)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at com.ets.hr.dao.EmployeeDAO.createEmployee(EmployeeDAO.java:30)
at Test.main(Test.java:62)
Method below:
方法如下:
public void createEmployee(Employee e){
try{
Class.forName(oracleClass);
Connection conn = DriverManager.getConnection(oracleUrl, "hr", "hr");
Statement s = conn.createStatement();
String query = "INSERT INTO employees VALUES(" +
e.getEmployeeId() + ", '" +
e.getFirstName() + "', '" +
e.getLastName() + "', '" +
e.getEmail() + "', '" +
e.getPhoneNumber() + "', " +
e.getHireDate() + ", '" +
e.getJobId() + "', " +
e.getSalary() + ", " +
e.getCommissionPct() + ", " +
e.getManagerId() + ", " +
e.getDepartmentId() + ")";
s.executeQuery(query);//LINE 30 - SqlException Here
System.out.println("Query Executed: Create Employee");
}
catch(SQLException se){
se.printStackTrace();
}
catch(ClassNotFoundException ce){
ce.printStackTrace();
}
}
Employee Class:
员工等级:
package com.ets.hr.dto;
import java.util.Date;
public class Employee {
//CONSTRUCTORS
public Employee(){}
public Employee(long eId,
String firstName,
String lastName,
String email,
String phoneNumber,
Date hireDate,
String jobId,
double salary,
double commissionPct,
long managerId,
long departmentId){
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.phoneNumber = phoneNumber;
this.hireDate = hireDate;
this.jobId = jobId;
this.salary = salary;
this.commissionPct = commissionPct;
this.managerId = managerId;
this.departmentId = departmentId;
}
//instance variables (from HR.EMPLOYEE table)
private long employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private Date hireDate;
private String jobId;
private double salary;
private double commissionPct;
private long managerId;
private long departmentId;
//GETTERS
public long getEmployeeId(){
return this.employeeId;
}
public String getFirstName(){
return this.firstName;
}
public String getLastName(){
return this.lastName;
}
public String getEmail(){
return this.email;
}
public String getPhoneNumber(){
return this.phoneNumber;
}
public Date getHireDate(){
return this.hireDate;
}
public String getJobId(){
return this.jobId;
}
public double getSalary(){
return this.salary;
}
public double getCommissionPct(){
return this.commissionPct;
}
public long getManagerId(){
return this.managerId;
}
public long getDepartmentId(){
return this.departmentId;
}
//SETTERS
public void setEmployeeId(long employeeId){
this.employeeId = employeeId;
}
public void setFirstName(String firstName){
this.firstName = firstName;
}
public void setLastName(String lastName){
this.lastName = lastName;
}
public void setEmail(String email){
this.email = email;
}
public void setPhoneNumber(String phoneNumber){
this.phoneNumber = phoneNumber;
}
public void setHireDate(Date hireDate){
this.hireDate = hireDate;
}
public void setJobId(String jobId){
this.jobId = jobId;
}
public void setSalary(double salary){
this.salary = salary;
}
public void setCommissionPct(double commissionPct){
this.commissionPct = commissionPct;
}
public void setManagerId(long managerId){
this.managerId = managerId;
}
public void setDepartmentId(long departmentId){
this.departmentId = departmentId;
}
public void printEmployee(){
System.out.println("Employee ID: " + this.getEmployeeId());
System.out.println("Employee Name: " + this.getFirstName() + this.getLastName());
System.out.println("Employee Email: " + this.getEmail());
System.out.println("Employee Phone: " + this.getPhoneNumber());
System.out.println("Employee Hire Date: " + this.getHireDate());
System.out.println("Employee Job ID: " + this.getJobId());
System.out.println("Employee Salary: " + this.getSalary());
System.out.println("Employee Commission Pct: " + this.getCommissionPct());
System.out.println("Employee Manager ID: " + this.getManagerId());
System.out.println("Employee Department ID: " + this.getDepartmentId());
}
}
}
采纳答案by Bohemian
You shouldn't be trying to build your own SQL string, because you have to deal with quoting and escaping (which has bitten you here).
您不应该尝试构建自己的 SQL 字符串,因为您必须处理引用和转义(这已经让您感到厌烦了)。
Instead, use a PreparedStatement
and let the JDBC API escape, quote and format (eg dates) your values foryou:
相反,使用 aPreparedStatement
并让 JDBC API为您转义、引用和格式化(例如日期)您的值:
String query = "INSERT INTO employees VALUES (?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(query);
ps.setInt(e.getEmployeeId(), 1);
ps.setString(e.getFirstName(), 2);
ps.setDate(e.getHireDate(), 6);
// etc - there is a setter for each basic datatype
ps.execute();
ps.close();
The coding is easier, and readability goes way up too.
编码更容易,可读性也提高了。
回答by Josh
It looks like one of the following methods is producing a value that the database expects to be quoted/escaped (maybe it contains a comma itself, or another database special character):
看起来以下方法之一正在生成数据库期望引用/转义的值(可能它包含逗号本身,或另一个数据库特殊字符):
e.getEmployeeId()
e.getHireDate()
e.getSalary()
e.getCommissionPct()
e.getManagerId()
e.getDepartmentId()
I'd recommend looking into the PreparedStatement
class to help with bugs like this (as well as guarding against SQL-based security holes).
我建议查看PreparedStatement
该类以帮助解决此类错误(以及防范基于 SQL 的安全漏洞)。
回答by Java Devil
As OldProgrammer mentions, Instead of embedding the values like this
正如 OldProgrammer 提到的,而不是像这样嵌入值
Statement s = conn.createStatement();
String query = "INSERT INTO employees VALUES(" +
e.getEmployeeId() + ", '" +
e.getFirstName() + "', '" +
e.getLastName() + "', '" +
e.getEmail() + "', '" +
e.getPhoneNumber() + "', " +
e.getHireDate() + ", '" +
e.getJobId() + "', " +
e.getSalary() + ", " +
e.getCommissionPct() + ", " +
e.getManagerId() + ", " +
e.getDepartmentId() + ")";
s.executeQuery(query);
You could use a PreparedStatement
and then any special SQL syntax will be ignored/escaped when the values are put in. Try doing it like this
您可以使用 aPreparedStatement
然后在放入值时将忽略/转义任何特殊的 SQL 语法。尝试这样做
String query = "INSERT INTO employees VALUES(?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(query);
ps.setInt(1, e.getEmployeeId());
ps.setString(2, e.getFirstName());
//.... etc for the rest of your parameters