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