Spring: JDBC Template Invalid column type

0
votes

Using the following code to get the list of data from table but getting invalid column error.

String sql = "select * from employees  WHERE emp_status = :statusCode";
Map parameters = new HashMap();
parameters.put("statusCode", "Active");
MapSqlParameterSource parametersSourceMap = new MapSqlParameterSource(parameters );

List<Employee> employees rowSet = jdbcTemplate.queryForList(sql, parametersSourceMap);

Exception

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select * from employees  WHERE emp_status = :statusCode SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876)
        at com.spring.EmployeeDAOImpl.addEmployee(EmployeeDAOImpl.java:46)
        at com.spring.MainApp.main(MainApp.java:33)
    Caused by: java.sql.SQLException: Invalid column type

Table have column as Varchar2

Anyone have idea why we can't get data based on String column?

2
javaspringspring-jdbcjdbctemplate
Something tells me that's not the method that raises the exception. Can you please show the code for method EmployeeDAOImpl#addEmployee? - Luiggi Mendoza
The error is occur here EmployeeDAOImpl.java:46 and you are showing code that not related to this error - Yu Jiaao
line 46 is List<Employee> employees rowSet = jdbcTemplate.queryForList(sql, parametersSourceMap) - user2196474
@Luiggi Mendoza am not adding through JDBCTemplate. selectEmployee. Will chnage the methodName - user2196474
You are using JdbcTemplate whereas you probably want to use NamedParameterJdbcTemplate. - M. Deinum

2 Answers

1
votes

I recently faced the same issue. In my case, I was using JdbcTemplate instead of NamedParameterJdbcTemplate. I am not sure if the issue is same for you.

You can define a bean of type NamedParameterJdbcTemplate and then autowire it in your repository class.

Configuration class :

@Bean
public NamedParameterJdbcTemplate yourNamedParameterJdbcTemplate(DataSource yourDataSource) {
    return new NamedParameterJdbcTemplate(yourDataSource);
}

Repository class :

@Repository
public class YourRepositoryImpl implements YourRepository {

@Autowired
NamedParameterJdbcTemplate jdbcTemplate;
0
votes

Try to pass parameters like this :

String sql = "SELECT * FROM employees WHERE emp_status = ?";
List<Employee> employees = jdbcTemplate.queryForList(
        sql,
        new Object[]{"Active"},
        new BeanPropertyRowMapper<Employee>(Employee.class)
);