英文:
2-parameter PreparedStatement is throwing syntax error, SQL State 42601, near second argument
问题
我正在创建一个简单的Java应用程序，它使用一个我已经创建好的PostgreSQL数据库（数据库已经创建好了，但应用程序还没有）。
其中一个TABLE被命名为'Employee'。
它有一个名为'eid'的属性，表示员工编号。
我正尝试使用PreparedStatement来实现数据库的通用SELECT查询。我希望有两个参数是通用的。我还想获取结果的长度，因此我创建了第二个PreparedStatement来查找结果长度。
代码看起来很简单，但我一直在收到相同的语法错误。
以下是我试图运行的代码：
public static String[] select(Connection conn, String arguments) {
try {
PreparedStatement preparedSelect = conn.prepareStatement("SELECT ? FROM ?");
PreparedStatement preparedCount = conn.prepareStatement("SELECT COUNT(*) FROM ?");
String[] arrOfStr = arguments.split(" ", 0);
System.out.println(arrOfStr[0].equals("eid"));
System.out.println(arrOfStr[1].equals("Employee"));
preparedSelect.setString(1, arrOfStr[0]);
preparedSelect.setString(2, arrOfStr[1]);
preparedCount.setString(1, arrOfStr[1]);
ResultSet rsSelect = preparedSelect.executeQuery();
ResultSet rsCount = preparedCount.executeQuery();
...
在第一行的参数中，我希望SELECT查询是："SELECT eid FROM Employee"。
在第二行的参数中，我希望SELECT查询是："SELECT COUNT(*) FROM Employee"。
使用此应用程序的用户会在程序的早期输入位于"select"函数参数中的"arguments"参数。"arrOfStr"字符串数组将包含这两个字符串："eid"和"Employee"。
为了检查"arrOfStr"是否具有正确的值，我打印了通过将结果与我想要的字符串（即"eid"和"Employee"）进行比较的.equals()函数产生的布尔值。
在控制台中，结果是（并不令人惊讶）：
true
true
因此，在接下来的3行中，我将字符串值"eid"和"Employee"设置到了PreparedStatement中。
最后，我在接下来的2行中执行了这些查询。这两个查询执行（我尝试过交换它们的顺序）都会给我一个非常相似的错误。对于给定的代码，我得到以下错误：
SQL State: 42601
ERROR: syntax error at or near "$2"
Position: 16
这表示字符串"Employee"的语法存在问题。
当我直接转到我的PostgreSQL数据库，并输入：
SELECT eid FROM Employee;
我得到的输出是：
1663
1983
1357
...
有人能解释一下这个语法错误吗？
<details>
<summary>英文:</summary>
I am creating a simple java application that uses a postgresql database which I have already created (the database has been created, not the application).
One of the TABLEs is called 'Employee'.
It has an attribute called 'eid', short for employee id.
I am trying to use a PreparedStatement to generalize a database SELECT query. I want there to be two parameters which are generalized. I would also like to get the length of the result, so I created a second PreparedStatement to find that.
The code seems quite simple, but I keep getting the same syntax error.
Here is the code I am trying to run:
public static String[] select(Connection conn, String arguments) {
try {
PreparedStatement preparedSelect = conn.prepareStatement("SELECT ? FROM ?");
PreparedStatement preparedCount = conn.prepareStatement("SELECT COUNT(*) FROM ?");
String[] arrOfStr = arguments.split(" ", 0);
System.out.println(arrOfStr[0].equals("eid"));
System.out.println(arrOfStr[1].equals("Employee"));
preparedSelect.setString(1, arrOfStr[0]);
preparedSelect.setString(2, arrOfStr[1]);
preparedCount.setString(1, arrOfStr[1]);
ResultSet rsSelect = preparedSelect.executeQuery();
ResultSet rsCount = preparedCount.executeQuery();
...
In the first line's argument, I would like the SELECT query to be: "SELECT eid FROM Employee".
In the second line's argument, I would like the SELECT query to be: "SELECT COUNT(*) FROM Employee".
The user who uses this application enters (earlier in the program) the "arguments" argument found in the arguments of the "select" function. The "arrOfStr" string array will contain these two strings, "eid", and "Employee".
To check that "arrOfStr" has the correct values, I printed the booleans resulting from the .equals() function comparing the values to the strings I want which, again, are "eid", and "Employee".
In the console the results are, not to my surprise:
true
true
Thus, in the next 3 lines, I set the String values "eid" and "Employee" into the PreparedStatements.
Finally, I execute the queries in the following 2 lines. Both of these query executions (I have tried switching them around) give me a very similar error. For the given code I get the error:
SQL State: 42601
ERROR: syntax error at or near "$2"
Position: 16
This is stating that there is an issue with the syntax of the string "Employee".
When I go directly to my postgresql database, and input:
SELECT eid FROM Employee;
I get the output:
1663
1983
1357
...
Could someone explain this syntax error?
</details>
# 答案1
**得分**: 1
如Elliott所提到的，你不能以这种方式绑定表名。
要实现你的目标，请尝试以下方法：
```java
String[] arrOfStr = arguments.split(" ", 0);
PreparedStatement preparedSelect = conn.prepareStatement("SELECT ? FROM " + arrOfStr[1]);
PreparedStatement preparedCount = conn.prepareStatement("SELECT COUNT(*) FROM " + arrOfStr[1]);
请注意，这是一段Java代码，用于执行SQL查询，不要进行翻译。
英文:
As mentioned by Elliott, you cannot bind the table name that way.
To achieve your goal, try this:
String[] arrOfStr = arguments.split(" ", 0);
PreparedStatement preparedSelect = conn.prepareStatement("SELECT ? FROM " + arrOfStr[1]);
PreparedStatement preparedCount = conn.prepareStatement("SELECT COUNT(*) FROM " + arrOfStr[1]);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论