CODEWITHSUNDEEP
javasql-serverspring-jdbcjdbctemplate
emeraldjava
emeraldjava

Reputation: 11202

JDBC Template : MS SQL datetime column query

I have a MS SQL DN with a table called 'Load' which has a column 'load_starttime' which is of type datetime. My query is trying to count the numbers of rows that have been inserted for a specific day. I followed this tutorial for 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   
);

My current exception is 

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)

The second part

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)

Upvotes: 2

Views: 3862

Answers (2)

Wellington Souza
Wellington Souza

Reputation: 2358

The JdbcTemplate class does not support named parameters. You have to use the 'NamedParameterJdbcTemplate' class for this task. See bellow the source code to do what you want:

    final NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
    final Date d = new Date("2014-02-06");
    int count = template.queryForObject(
            "SELECT COUNT(load_starttime) FROM Load WHERE load_starttime=:load_starttime",
            new MapSqlParameterSource().addValue("load_starttime", new java.sql.Date(d.getTime())),
            Integer.class
    );

Upvotes: 0

Koitoer
Koitoer

Reputation: 19533

I think is due to .

JdbcTemplate template = new JdbcTemplate(getDataSource());

Try to do it with 

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource());

JdbcTemplate only works with ?

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.

And this is considered as a named parameter *load_starttime=:load_starttime*

Upvotes: 2

Related Questions