Error while creating SQL query with bind variables - JDBCTemplate in Spring

Question

I am very beginner to Spring - JDBC . I am trying to retrieve the employee_id from a table using the query having bind variables and also with IN condition in it .

I'm getting SQLException that

" invalid column type" - Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select employee_id from table_employee where age=:varTwo and marks in (:varOne) and name =:varThree]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

Can you please tell , where I'm wrong .

I have tried using the types as Long , Integer , String but still i'm getting "invalid column type"

age is - NUMBER

marks is - NUMBER

name is - VARCHAR

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("varOne", varOne);
parameters.addValue("varTwo", Long.parseLong(varTwo));
parameters.addValue("varThree", varThree);
Long employeeId = jdbcTemplate.queryForObject("select employee_id from table_employee where age=:varTwo and marks in (:varOne) and name =:varThree" , Long.class , parameters);

I should be getting the result of this SQL as the "employee id".

Looks like you are not using a NamedParameterTemplate - please edit your question and add the code that initializes your jdbcTemplate — a_horse_with_no_name
private JdbcTemplate jdbcTemplate; @Autowired public EmployeeImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } — Tarun

shengbang he shengbang he · Accepted Answer · 2019-07-04T10:12:30

I think the type of varOne may be Collection. When you want to use a variable in SQL query especially with IN, you should make sure the variable is a correct type.

Error while creating SQL query with bind variables - JDBCTemplate in Spring

2 Answers