Info
Content

java.sql.SQLException：ORA-00917：缺少逗号(java.sql.SQLException: ORA-00917: missing comma)

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.

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());
}

}

 

解决方案

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).

Instead, use a PreparedStatement and let the JDBC API escape, quote and format (eg dates) your values for you:

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.

 

我正在试着找出下面SqlExcpetion的原因。我正在使用Oracle Database 11g Express和SqlDeveloper。我查看了其他帖子，但到目前为止没有任何帮助。



  java.sql.SQLException：ORA-00917：缺少逗号
 
 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。 createEmplo是（EmployeeDAO.java:30）
在Test.main（Test.java:62）



< p>以下方法：

 



  public void createEmployee（Employee e）{
 try {
 Class.forName （oracleClass）; 
连接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这里
 System.out.println（执行的查询：创建员工）; 
} 
 catch（SQLException se）{
 se.printStackTrace（）; 
} 
 catch（ClassNotFoundException ce）{
 ce.printStackTrace（）; 
} 
}



员工类别：



  package com.ets.hr.dto; 
 
 import java.util.Date; 
 
公共类员工{
 
 // CONSTRUCTORS 
公共员工（）{} 
 
公共员工（长期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; 
} 
 
 //实例变量（来自HR.EMPLOYEE表）
 private long employeeId; 
 private String firstName; 
 private String lastName; 
 private String email; 
 private String phoneNumber; 
 private Date hireDate; 
 private String jobId; 
私人双薪; 
 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（员工姓名：+ this.getFirstName（）+ this.getLastName（））; 
 System.out.println（员工电子邮件：+ this.getEmail（））; 
 System.out.println（员工电话：+ this.getPhoneNumber（））; 
 System.out.println（员工雇用日期：+ this.getHireDate（））; 
 System.out.println（员工职位ID：+ this.getJobId（））; 
 System.out.println（Employee Salary：+ this.getSalary（））; 
 System.out.println（Employee Commission Pct：+ this.getCommissionPct（））; 
 System.out.println（员工经理ID：+ this.getManagerId（））; 
 System.out.println（员工部门ID：+ this.getDepartmentId（））; 
}



}

 

您不应该尝试构建自己的SQL字符串，因为您必须处理引用和转义（这里已经咬过你）。



相反，使用 PreparedStatement 并让JDBC API转义，引用和格式化（例如日期）你的 你的值：< / p>

 字符串查询=INSERT INTO员工价值观（？，？，？，？，？，？，？，？，？， ？）？; 
 PreparedStatement ps = conn.prepareStatement（query）; 
 ps.setInt（e.getEmployeeId（），1）; 
 ps.setString（e.getFirstName（），2）; 
 ps.setDate（e.getHireDate（），6）; 
 //等 - 每个基本数据类型都有一个setter 
 ps.execute（）; 
 ps.close（）;



编码更容易，可读性也越来越高。

 

 

https://www.it1352.com/1470289.html

No Comments
Back to top