Dynamically select field names in a query with Spring JDBCTemplate
- by Francesco
Hi, I have a problem with parameters replacing by Spring JdbcTemplate.
I have this query :
<bean id="fixQuery" class="java.lang.String">
<constructor-arg type="java.lang.String"
value="select fa.id, fi.? from fix_ambulation fa
left join fix_i18n fi
on fa.translation_id = fi.id order by name" />
And this method :
public List<FixAmbulation> readFixAmbulation(String locale) throws Exception {
List<FixAmbulation> ambulations = this.getJdbcTemplate().query(
fixQuery, new Object[] {locale.toLowerCase()},
ParameterizedBeanPropertyRowMapper
.newInstance(FixAmbulation.class));
return ambulations;
}
And I'd like to have the ? filled with the string representing the locale the user is using. So if the user is brasilian I'd send him the column pt_br from the table fix_i18n, otherwise if he's american I'd send him the column en_us.
What I get from this method is a PostgreSQL exception org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
If I replace fi.? with just ? (the column name of the locale is unique, so if I run this query in the database it works just fine) what I get is that every object returned from method has the string locale into the field name. I.e. in name field I have "en_us".
The only way to have it working I found was to change the method into :
public List<FixAmbulation> readFixAmbulation(String locale) throws Exception {
String query = "select fa.id, fi." + locale.toLowerCase() + " as name " + fixQuery;
this.log.info("QUERY : " + query);
List<FixAmbulation> ambulations = this.getJdbcTemplate().query(
query,
ParameterizedBeanPropertyRowMapper
.newInstance(FixAmbulation.class));
return ambulations;
}
and setting fixQuery to :
<bean id="fixQuery" class="java.lang.String">
<constructor-arg type="java.lang.String"
value=" from telemedicina.fix_ambulation fa
left join telemedicina.fix_i18n fi
on fa.translation_id = fi.id order by name" />
</bean>
My DAO extends Spring JdbcDaoSupport and works just fine for all other queries. What am I doing wrong?
