oracle查询无效的列类型,JdbcTemplate类的查询参数类型错误：无效列类型

public List 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> rsRows = this.jdbcTemplate.queryForList(sql

.toString(),params);

List users = new ArrayList();

for (Map 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;

}

，并得到java.sql.SQLException: Invalid column type

SIDX是列内特( “USER_ID” 为例)SORD是ASC /降序

当通过没有PARAMS(仅执行

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

sql.append("FROM users ");

)EV一切都好。

更新：兼容于：

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

和

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

好像与Spring MapSqlParameterSource和命名参数的问题。我使用Spring 3.1.3

DB是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