java - JDBC 模板 : MS SQL datetime column query

Question

我有一个 MS SQL DN，其中有一个名为“Load”的表，其中有一列“load_starttime”，其类型为 datetime。我的查询试图计算特定日期插入的行数。我按照本教程进行了 mapping a datetime via the jdbc template .

Date d = new Date("2014-02-06");
JdbcTemplate template = new JdbcTemplate(getDataSource());
int count = template.queryForInt(
    "SELECT COUNT(load_starttime) FROM Load WHERE load_starttime=:load_starttime",
        new MapSqlParameterSource().addValue(
            "load_starttime", 
            new java.sql.Date(d.getTime())
        ),
    Types.TIMESTAMP   
);

我当前的异常是

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT COUNT(load_starttime) FROM Load WHERE load_starttime=:load_starttime]; Unable to convert between org.springframework.jdbc.core.namedparam.MapSqlParameterSource and JAVA_OBJECT.; nested exception is java.sql.SQLException: Unable to convert between org.springframework.jdbc.core.namedparam.MapSqlParameterSource and JAVA_OBJECT.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
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.query(JdbcTemplate.java:637)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:674)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:729)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:745)
at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:776)

第二部分

Caused by: java.sql.SQLException: Unable to convert between org.springframework.jdbc.core.namedparam.MapSqlParameterSource and JAVA_OBJECT.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:446)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:370)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:668)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:365)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:145)
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$1.doInPreparedStatement(JdbcTemplate.java:642)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)

Answer 1

我认为是由于 .

JdbcTemplate template = new JdbcTemplate(getDataSource());

尝试用

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource());

JdbcTemplate 只适用于 ?

JdbcTemplate - this is the classic Spring JDBC approach and the most widely used. This is the "lowest level" approach and all other approaches use a JdbcTemplate under the covers. Works well in a JDK 1.4 and higher environment.

NamedParameterJdbcTemplate - wraps a JdbcTemplate to provide more convenient usage with named parameters instead of the traditional JDBC "?" place holders. This provides better documentation and ease of use when you have multiple parameters for an SQL statement. Works with JDK 1.4 and up.

这被认为是命名参数 *load_starttime=:load_starttime*