Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Spring: SQL state [null]; error code [17004]; Invalid column type

Tags:

spring

jdbc

I was trying to insert an employee using JdbcTemplate. But I am getting Invalid Column Type exception. Any idea what could be the issue?

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into employees (employee_id,first_name,last_name,email,hire_date,job_id) values (?,?,?,?,?,?))]; 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
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9168)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8749)
        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9471)
        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9454)
        at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
        at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
        at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:812)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
        ... 5 more

Here are the entire files which I have created.

Employee.java

package com.spring;

import java.sql.Date;

public class Employee {

    private Integer employeeId;
    private String firstName;
    private String lastName;
    private String email;
    private Date hireDate;
    private String jobId;

    public Integer getEmployeeId() {
        return employeeId;
    }
    public void setEmployeeId(Integer employeeId) {
        this.employeeId = employeeId;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getHireDate() {
        return hireDate;
    }
    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }
    public String getJobId() {
        return jobId;
    }
    public void setJobId(String jobId) {
        this.jobId = jobId;
    }

}

EmployeeDAO Interface

package com.spring;

import javax.sql.DataSource;

import org.springframework.jdbc.support.MetaDataAccessException;

public interface EmployeeDAO {

    void setDataSource(DataSource datasource);  
    void addEmployee() throws MetaDataAccessException;

}

EmployeeDAOImpl.java

package com.spring;

import java.util.Date;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;

public class EmployeeDAOImpl implements EmployeeDAO{

    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    @Override
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;   
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void addEmployee() throws MetaDataAccessException{

        String databaseName = (String)JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductName");
        String databaseVersion = (String)JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductVersion");
        String driverName = (String)JdbcUtils.extractDatabaseMetaData(dataSource, "getDriverName");
        String driverVersion = (String)JdbcUtils.extractDatabaseMetaData(dataSource, "getDriverVersion");
        System.out.println("Database Name: " + databaseName);
        System.out.println("Database Version: " + databaseVersion);
        System.out.println("Driver Name: " + driverName);
        System.out.println("Driver Version: " + driverVersion);

        String sql = "insert into employees (employee_id,first_name,last_name,email,hire_date,job_id) values"
                                            + " (?,?,?,?,?,?)";


        Date date = new Date();

        jdbcTemplate.update(sql, new Object[] {Integer.valueOf(2007),"AAAAAAA","BBBBBB","[email protected]",new java.sql.Date(date.getTime()),"ST_MAN"}, new EmployeeMapper());



    }
}

EmployeeMapper.java

package com.spring;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class EmployeeMapper implements RowMapper<Employee>{
    @Override
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee = new Employee();
        employee.setEmployeeId(rs.getInt("employee_id"));
        employee.setFirstName(rs.getString("first_name"));
        employee.setLastName(rs.getString("last_name"));
        employee.setEmail(rs.getString("email"));
        employee.setHireDate(rs.getDate("hire_date"));
        employee.setJobId(rs.getString("job_id"));

        return employee;
    }
}

spring.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:aop="http://www.springframework.org/schema/aop"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/aop 
        http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
            <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>
            <property name="username" value="hr"></property>
            <property name="password" value="welcome"></property>
        </bean>
        <bean id="employeeDAOImpl" class="com.spring.EmployeeDAOImpl">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
</beans>

MainApp.java

package com.spring;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.support.MetaDataAccessException;


public class MainApp {

    public static void main(String[] args) throws MetaDataAccessException{
ApplicationContext context = new FileSystemXmlApplicationContext("spring.xml");
        EmployeeDAOImpl dao = (EmployeeDAOImpl)context.getBean("employeeDAOImpl");
        dao.addEmployee();

    }   

}

Output :

Database Name: Oracle
Database Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Driver Name: Oracle JDBC driver
Driver Version: 10.2.0.1.0
May 19, 2014 7:51:29 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
May 19, 2014 7:51:30 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into employees (employee_id,first_name,last_name,email,hire_date,job_id) values (?,?,?,?,?,?)]; 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:38)
    at com.spring.MainApp.main(MainApp.java:30)
Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9168)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8749)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9471)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9454)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:812)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 5 more
like image 832
user3652785 Avatar asked Mar 07 '26 21:03

user3652785

1 Answers

From the Spring docs, the third parameter must be "SQL types of the arguments (constants from java.sql.Types)" but you have provided new EmployeeMapper().

like image 78
jordan Avatar answered Mar 10 '26 16:03

jordan
Sign in to Comment

Related questions

Spring MVC - using Poincuts to fill Spring MVC partials with dynamic data
Cannot initialize context because there is already a root application context present
How to read List of Strings in spring application.yml file into Immutables object?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!