org.postgresql.util.PSQLException: 错误: 语法错误靠近 ?,? 在 Java 中

org.postgresql.util.PSQLException: ERROR: syntax error near ?,? in Java

提问by Diego Quirós

The below is the query generate by a prepareStatement in Java:

下面是由 Java 中的 prepareStatement 生成的查询：

insert into schema.table(cedula, actividad, mercado, venta_mensual, fortalezas, crecer,
 financiamiento, monto, patente, contador, regimen_tri, problemas, bn_servicios, cursos ) 
values ('val', 'GAM', 'GAM', '0', 'Calidad', 'Sí', 'Sí', '122', 'Sí', 'Sí', 'ddd', 'aaa','ccc', 'bbb'  )

The Java code is:

Java代码是：

try {
    PreparedStatement pstmt = conexion.prepareStatement(query); 
    pstmt.setString(1, n.getCedula()); 
        //the rest of the sets of the statement continue here from 1 to 13
        pstmt.executeUpdate(); 
    conexion.createStatement().execute(query);
        return true
} catch (SQLException e) {
    e.printStackTrace(); // This error 
    return false;
}

The query is executed int the try statement and insert the values properly in the DB, BUT it also throws the below exception, at line 192: here 'val':

查询在 try 语句中执行并在数据库中正确插入值，但它也会在第 192 行抛出以下异常：此处为“val”：

 org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de ?,?
 org.postgresql.util.PSQLException: ERROR: syntax error near ',' java

The error trace relate to postgres is here:

与 postgres 相关的错误跟踪在这里：

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)

By the way, the table has a bigserial value and all the others values showed in the query. Thanks in advance!

顺便说一下，该表有一个 bigserial 值，查询中显示了所有其他值。提前致谢！

采纳答案by krokodilko

If the query contains string constant within the valuesclause, as you have shown in the question:

如果查询在values子句中包含字符串常量，如问题中所示：

query = "insert into table(cedula, actividad, mercado) "
        + " values ('val', 'GAM', 'GAM' )";

then this part of code will work fine:

那么这部分代码将正常工作：

conexion.createStatement().execute(query);

however this part of code won't work:

但是这部分代码不起作用：

pstmt.setString(1, n.getCedula()); 
//the rest of the sets of the statement continue here from 1 to 13

It will throw an PSQLException: The column index is out of range: X, number of columns: 0, because PreparedStatement.setXXXmethods expect placeholders ?in the SQL statement.
On the other hand, when the insert statement contains placeholders (I assume that your INSERT does containplaceholders, because you haven't got the above exception):

它会抛出PSQLException: The column index is out of range: X, number of columns: 0, 因为PreparedStatement.setXXX方法需要?SQL 语句中的占位符。
另一方面，当插入语句包含占位符时（我假设您的 INSERT确实包含占位符，因为您没有得到上述异常）：

query = "insert into tabla(cedula, actividad, mercado) "
    + " values ( ?, ?, ? )";

then pstmt.setString...statements will work fine, however this statement:

thenpstmt.setString...语句可以正常工作，但是这个语句：

   conexion.createStatement().execute(query);

will throw an exception: PSQLException: ERROR: syntax error near ','
If your intent is to execute the INSERT twice, the first one using placeholders, and the second one using string values, you must do it in this way:

将抛出异常：PSQLException: ERROR: syntax error near ','
如果您的意图是执行 INSERT 两次，第一次使用占位符，第二次使用字符串值，则必须以这种方式执行：

query1 = "insert into tabla(cedula, actividad, mercado) "
        + " values ('val', 'GAM', 'GAM' )";
query2 = "insert into tabla(cedula, actividad, mercado) "
        + " values ( ? , ? , ? )";

PreparedStatement pstmt = conexion.prepareStatement(query2); 
pstmt.setString(1, n.getCedula()); 
  //the rest of the sets of the statement continue here from 1 to 13
pstmt.executeUpdate(); 

conexion.createStatement().execute(query1);