I'm trying to figure out how to use JOOQ with NamedParameterJdbcTemplate. I have successfully created other queries, but I'm stuck at creating a query containing the WHERE clause. When I try to run the method below, I get the error org.springframework.dao.InvalidDataAccessApiUsageException: SQL [select "first_name" from "customer" where "id" = cast(? as integer)]: given 1 parameters but expected 0
I get the same error when I try to use only integers in equals(), like... .where(fieldByName("id").equal(1001), same error and .where(fieldByName("id").equal(id), same error.
If I remove the WHERE clause, the query itself seems to work fine.
What am I doing wrong here? It seems to me that the SQL syntax is correct. It's probably me being stupid, but I really can't find what's wrong here. Please help!
public String getCustomerFirstName(int id) {
Query query = create.select(fieldByName("first_name"))
.from(tableByName("customer"))
.where(fieldByName("id").equal(param("id", id)));
Param param = query.getParam("id");
SqlParameterSource namedParameters = new MapSqlParameterSource(param.getName(), id);
return this.getNamedParameterJdbcTemplate().queryForObject(query.getSQL(), namedParameters, String.class);
}
In order to have jOOQ generate named parameters, you have to explicitly tell it to do so.
In your case, this would make
String sql = create.renderNamedParams(query);
The above would replace your call to
query.getSQL();
Note, this is documented here:
http://www.jooq.org/doc/3.0/manual/sql-building/bind-values/named-parameters
A future version of jOOQ (probably 3.1) will probably support initialising your DSLContext with a setting to always render named parameters as such. This is registered as a feature request on the roadmap: