How to solve bad sql grammar error with Spring JDBC?

Question

I'm using PostgreSQL with Spring JDBC. Everythig building with 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

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.

Solution

You can't use value placeholders ("?") for things that aren't values (like your field-names).

If you really want to do this, just build up the query, mapping the field-names in. Then apply the values with placeholders.

BUT do 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.

---

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.