Dynamically select field names in a query with Spring JDBCTemplate

Posted by Francesco on Stack Overflow See other posts from Stack Overflow or by Francesco
Published on 2010-05-31T15:57:49Z Indexed on 2010/05/31 16:03 UTC
Read the original article Hit count: 617

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about query

Related posts about spring