Numeric value '{userId}' is not recognized-CodePudding

I have a SQL query written in Java that looks like this:

public void getTransactionHistoryTest(String userId) {
     String q = "SELECT * FROM \"TEST\".\"PUBLIC\".\"USER_TABLE\" WHERE \"ID\"='{userId}'";
     return  jdbcTemplate.query(query, rs -> {
    }, userId);
    }

I have a table in Snowflake where UserID is of type NUMBER, and I am getting the next error:

Numeric value '{userId}' is not recognized

To solve this problem, I tried to cast String userId to int like this:

int userId= Integer.parseInt(id);

But I am still getting the same error.

Is it possible to send String value cast it to int and still make it work in Snowflake?

CodePudding user response：

Try to use ? character instead of '{userId}'

public List<TransactionHistory> getTransactionHistoryTest(String userId) {
 String q = "SELECT * FROM \"TEST\".\"PUBLIC\".\"USER_TABLE\" WHERE \"ID\"=?";
 return jdbcTemplate.query(q, ROW_MAPPER, userId);
}

Your issue is caused not by the value of user Id, but by the incorrect escaping of the parameter. In your case you don't need named parameter - so just ? will work. Otherwise use named parameter template. See more examples i.e. here https://javabydeveloper.com/spring-jdbctemplate-query-examples/

Another way to try:

String sql = "SELECT * FROM \"TEST\".\"PUBLIC\".\"USER_TABLE\" WHERE \"ID\" = ?";
jdbcTemplate.query(sql,
        new Object[] { userId },
        new int[] { Types.INTEGER },
        ROW_MAPPER);