java - 使用 JDBC 执行查询时出现错误 "column not allowed"

Question

这个问题已经有答案了:

"column not allowed here" error in INSERT statement (13 个回答)

已关闭 9 年前。

 import java.util.Scanner;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.Statement;
      public class H {

              public static void main(String[] args) throws Exception {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                   Connection conn = DriverManager.getConnection("jdbc:odbc:dsn1","system","mandriva");
                   Statement stmt = conn.createStatement();
                   Scanner sc = new Scanner(System.in);

                String fName, lName, sql, input;
                 int id, age;

               do {

                  System.out.println("Enter ID");
                  id = sc.nextInt();
                  System.out.println("Enter First Name");
                  fName = sc.next();
                  System.out.println("Enter Last Name");
                  lName = sc.next();
                  System.out.println("Enter Age");
                  age = sc.nextInt();

                  sql = "insert into employee values(" + id + "," +  fName + "," +  lName + "," +  age + ")";
                 stmt.execute(sql);

              System.out.println("Do you want to insert one more(y/n)?");
              input = sc.next();
            } while("y".equals(input));

         System.out.println("done");
     }
 }

当我运行该程序时，输入信息后，显示以下错误:

线程“main”java.sql.SQLException中出现异常:[Oracle][ODBC][Ora]ORA-00984:此处不允许列 在 sun.jdbc.odbc.JdbcOdbc.createSQLException(来源未知) 在 sun.jdbc.odbc.JdbcOdbc.standardError(来源未知) 在 sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(来源未知) 在 sun.jdbc.odbc.JdbcOdbcStatement.execute(来源未知) 在 SQL1.H.main(H.java:30)

谁能解释一下为什么会出现这个异常？

Answer 1

使用Prepare stmt而不是传递内联参数。还要在插入语句中提供列名称:

// Assuming columns are: id, first_name, last_name, age
sql = "insert into employee(id, first_name, last_name, age) values(?, ?, ?, ?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, id);
pstmt.setString(2, fName);
pstmt.setString(3, lName);
pstmt.setInt(4, age);

pstmt.executeUpdate();