Dynamically select field names in a query with Spring JDBCTemplate

Question

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?

Answer 1

I don't think you should be sending anyone column information. That's persistence tier stuff and shouldn't have to leak out.

If you want a locale-specific view, use the Spring facilities to do such a thing. It has nothing at all to do with how you persist data.

Answer 2

I found an answer to my own question.

http://stackoverflow.com/questions/942457/jdbc-preparestatement-doesnt-work