java - JDBC-ORA-00984 : column not allowed here

Question

import java.util.*;
import java.sql.*;

public class Jdbc {

    public static void main(String j[]) {

        Scanner sc = new Scanner(System.in);
        System.out.print("Enter Id:-");
        int id = sc.nextInt();
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "System", "Mohit");
            String sql = "insert into st values(id)";
            Statement stmt = conn.createStatement();
            boolean res = stmt.execute(sql);
            if (!res) {
                System.out.println("Value Inserted");
            } else {
                System.out.println("Value  Not Inserted");
            }
        } catch (Exception k) {
            System.out.println("Exception is:-" + k);
        }
    }
}

在我的代码中，我想在数据库中插入值但它抛出异常，而在语句接口(interface)中我们不能动态传递值但我们可以手动传递值

C:\Users\MOHIT\Desktop\PACK>java Jdbc
Enter Id:-0 0
Exception is:-java.sql.SQLException: ORA-00984: column not allowed here

Answer 1

关于错误

来自链接here

An ORA-00984 will be thrown if a column name (like in the VALUES clause of an INSERT statement), is used in an expression where it is not permitted. You may have used a column name in an expression where it is not permitted. Typically, ORA-00984 occurs while including a column name in the VALUES clause of an INSERT statement.

To correct ORA-00984, you simply need to view the syntax of the SQL statement and only use column names where they are appropriate.

You may also find it appropriate to include a character value, in the INSERT statement, instead of the column name.

---

解决方案

如果你仔细观察你的查询，你会发现语法不正确这里是插入的正确语法:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

如果你想传递所有的列，那么你不需要指定列名。

INSERT INTO table_name VALUES (value1, value2);

---

如果你的表只包含 id 那么你可以使用:

String sql = "insert into st values(" + id + ")";
//---concat your id with your query-^--^-^----

如果您的表包含多列，那么您可以使用:

String sql = "insert into st(id) values(" + id + ")";
//---------------------------^^-------------^^-------

---

注意

语句可能导致语法错误或 SQL Injection你必须使用 PreparedStetement

所以你可以使用:

String sql="insert into st values(?)";
PreparedStatement insert = connection.prepareStatement(sql);
insert.setInt(1, id);
boolean res = insert.execute(sql);
...