Spring :SQL 状态 [null]；错误代码 [17004]；列类型无效

Question

我试图使用 JdbcTemplate 插入一个员工。但是我收到了 Invalid Column Type 异常。知道可能是什么问题吗？

---

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

---

这是我创建的全部文件。

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","abc@gmail.com",new java.sql.Date(date.getTime()),"ST_MAN"}, new 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>

主程序.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();

    }   

}

输出:

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

Answer 1

来自 the Spring docs ，第三个参数必须是“参数的 SQL 类型(来自 java.sql.Types 的常量)”，但您提供了 new EmployeeMapper()。