postgresql 如何使用 Spring JDBC 解决错误的 sql 语法错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25727924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve bad sql grammar error with Spring JDBC?
提问by Dracontis
I'm using PostgreSQL with Spring JDBC. Everythig building with Gradle:
我在 Spring JDBC 中使用 PostgreSQL。使用 Gradle 构建所有内容：
dependencies {
compile("org.springframework.boot:spring-boot-starter-web")
compile("org.springframework:spring-jdbc")
compile("postgresql:postgresql:9.0-801.jdbc4")
compile("com.h2database:h2")
compile("com.googlecode.json-simple:json-simple:1.1")
testCompile("junit:junit")
}
Here is code fragment where I got exception:
这是我遇到异常的代码片段：
String sql = "INSERT INTO " + dbname + " (:fields) VALUES (:values)";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue( "fields", fieldsObj.keySet().toArray() );
parameters.addValue( "values", fieldsObj.values().toArray() );
count = jdbcTemplate.update( sql, parameters );
I get this error:
我收到此错误：
PreparedStatementCallback; bad SQL grammar [INSERT INTO test (?, ?, ?) VALUES (?, ?, ?)]; nested exception is org.postgresql.util.PSQLException
PreparedStatementCallback; 错误的 SQL 语法 [INSERT INTO test (?, ?, ?) VALUES (?, ?, ?)]; 嵌套异常是 org.postgresql.util.PSQLException
I've tried to convert array into string with values, divided by commas, but this gave me same error. What should I do? Or, if you have better ideas what to use, to build custom queries, please, post this idea in comments.
我试图将数组转换为带有值的字符串，以逗号分隔，但这给了我同样的错误。我该怎么办？或者，如果您有更好的想法可以使用什么来构建自定义查询，请在评论中发布此想法。
采纳答案by Richard Huxton
You can't use value placeholders ("?") for things that aren't values (like your field-names).
您不能将值占位符（“？”）用于不是值的内容（例如您的字段名称）。
If you reallywant to do this, just build up the query, mapping the field-names in. Then apply the values with placeholders.
如果您真的想这样做，只需构建查询，将字段名称映射到其中。然后使用占位符应用值。
BUTdo make sure you check the field-names are as you would expect and escape/quote them properly.
但是请确保您检查字段名称是否符合您的预期并正确转义/引用它们。
Oh - Not a java programmer, but do check that keySet() and values() return their results in the same order too.
哦 - 不是 Java 程序员，但要检查 keySet() 和 values() 是否也以相同的顺序返回它们的结果。
Edit:
编辑：
The "?" marks are value placeholders. They are used in database connection libraries of all sorts. They will be mapped to whatever parameterised query syntax the database can use. However, they only work for values - so the database can parse the query with the placeholders.
这 ”？” 标记是值占位符。它们用于各种数据库连接库。它们将被映射到数据库可以使用的任何参数化查询语法。但是，它们仅适用于值 - 因此数据库可以使用占位符解析查询。
