Spring JdbcTemplate query parameters type error: Invalid column type

Question

I use Spring Jdbc Template that way:

public List<User> getUsersForGrid(int rows, int page, String sidx,
        String sord) {

    int fromRecord = 0;
    int toRecord = 0;
    toRecord = page * rows;
    fromRecord = (page - 1) * rows;

    StringBuilder sqlB = new StringBuilder();
    sqlB.append("SELECT user_id, username ");
    sqlB.append("FROM users ");
    sqlB.append("WHERE :fromRecord <= rownum AND rownum <= :toRecord ");
    sqlB.append("ORDER BY %s %s ");

    String sql = String.format(sqlB.toString(), sidx, sord);

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("fromRecord", fromRecord);
    params.addValue("toRecord", toRecord);

    List<Map<String, Object>> rsRows = this.jdbcTemplate.queryForList(sql
            .toString(),params);

    List<User> users = new ArrayList<User>();
    for (Map<String, Object> row : rsRows) {
        BigDecimal id = (BigDecimal) row.get("user_id");
        String username = (String) row.get("username");

        User user = new User(id.intValue(), username);
        users.add(user);
    }

    return users;
}

and get java.sql.SQLException: Invalid column type

sidx is column nate("user_id" for example) sord is asc/desc

When pass no params(execute only

        sql.append("SELECT user_id, username ");
        sql.append("FROM users ");

) everything is OK.

Update: Works with:

sqlB.append("WHERE ? <= rownum AND rownum <= ? ");

and

this.jdbcTemplate.queryForList(sql.toString(),new Object[]{fromRecord, toRecord});

Seems like problem with Spring MapSqlParameterSource and named parameters. I use Spring 3.1.3

DB is Oracle 11.2

describe users;
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
USER_ID                        NOT NULL NUMBER                                                                                                                                                                                        
USERNAME                       NOT NULL VARCHAR2(40)                                                                                                                                                                                  
PASSWORD                       NOT NULL VARCHAR2(20)                                                                                                                                                                                  
ENABLED                        NOT NULL NUMBER

Sajan Chandran · Accepted Answer · 2012-11-10 19:45:21Z

2

I think the problem is with your order by clause, you are trying to dynamically change your order by clause. Just try

    StringBuilder sql = new StringBuilder();
    sql.append("SELECT user_id, username ");
    sql.append("FROM users ");
    sql.append("WHERE :fromRecord <= rownum AND rownum <= :toRecord ");
    sql.append("ORDER BY user_id asc ");

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("fromRecord", fromRecord);
    params.addValue("toRecord", toRecord);

If the above works, then instead of using the MapSqlParameterSource for changing the order by clause use something like

    StringBuilder sql = new StringBuilder();
    sql.append("SELECT user_id, username ");
    sql.append("FROM users ");
    sql.append("WHERE :fromRecord <= rownum AND rownum <= :toRecord ");
    sql.append("ORDER BY %s %s ");

    //Format the sql string accordingly


MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("fromRecord", fromRecord, Types.INTEGER);
params.addValue("toRecord", toRecord, Types.INTEGER);

Hope it helps.

edited Nov 10, 2012 at 19:45

answered Nov 10, 2012 at 19:18

Sajan Chandran

11.5k3 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evgeni Dimitrov Over a year ago

Just found that mistake.(+1) According to this question stackoverflow.com/questions/10064860/… you cant bound parameters to sql words. So I changed my code, I will update my question. But still got the exception ...

Sajan Chandran Over a year ago

Modified, used the Type parameter in MapSqlParameterSource.

Juan Rada · Accepted Answer · 2012-11-10 18:59:21Z

0

try this

List<Map> rows = getJdbcTemplate().queryForList(sql);
    for (Map row : rows) {
           BigDecimal id = (BigDecimal) row.get("user_id");
            String username = (String) row.get("username");
            User user = new User(id.intValue(), username);
            users.add(user);
}

ok try

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("fromRecord", fromRecord);
namedParameters.addValue("toRecord", toRecord);
namedParameters.addValue("sidx", sidx);
namedParameters.addValue("sord", sord);
return this.getNamedParameterJdbcTemplate().query(query,
            namedParameters, new UserElementMapper());


public class UserMapper implements RowMapper<User> {

public EmailElement mapRow(ResultSet rs, int rowNum) throws SQLException {
    User user = new User();
    emailElement.setID(rs.getInt("user_id"));
    emailElement.setUsernameo(rs.getString("username"));
    return user;
}

}

edited Nov 10, 2012 at 18:59

answered Nov 10, 2012 at 17:46

Juan Rada

3,7961 gold badge32 silver badges29 bronze badges

1 Comment

Evgeni Dimitrov Over a year ago

That will work. When I pass no parameters and execute only SELECT user_id, username FROM users it's OK. But I want to pass parameters. The exception is thrown when queryForList is executed, so the loop section is not important(I assume)

Collectives™ on Stack Overflow

Spring JdbcTemplate query parameters type error: Invalid column type

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related