Unable to know whats going wrong with JdbcTemplateSupport

Question

I'm writing a program in spring to get the total number of records and to insert a record . my total number of records works fine, but my insert is giving me an exception.

Below is my code

@Component
public class DaoImpl extends JdbcDaoSupport {

    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private JdbcDaoSupport jdbcDaoSupport;


    // getCount of rows

    public int getNumberOfRecords() {
        String sql = "select count(*) from student";
        return this.getJdbcTemplate().queryForObject(sql, Integer.class);
    }

    // insert using named parameters
    public void InsertUsingNamedParameters(StudentBean studentBean) {

        String paramSql = "insert into Student (id, name, age) values (:id, :name,:age)";
        HashMap<String, Object> params = new HashMap<>();
        params.put("id", 10);
        params.put("name", "new userX");
        params.put("age", 35);
        this.getJdbcTemplate().update(paramSql, params);
    }
  }
}

Here is my main method.

package org.Main;

import org.Service.DaoImpl;
import org.model.StudentBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainStudent {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");

        DaoImpl dao = context.getBean("daoImpl", DaoImpl.class);
        dao.InsertUsingNamedParameters(new StudentBean());
        System.out.println(dao.getNumberOfRecords());
      }
    }
}

Here is my 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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"

    xsi:schemaLocation="
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context 
    http://www.springframework.org/schema/context/spring-context.xsd">


    <context:component-scan base-package="org.model, org.Service"></context:component-scan>
    <context:annotation-config />
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
        <property name="url"
            value="My jdbc url"></property>
        <property name="username" value="my user name"></property>
        <property name="password" value="my password"></property>

    </bean>
    <bean id="daoImpl" class="org.Service.DaoImpl">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

The best part is when I tried doing the same using NamedParameterJdbctemplate, this worked fine.

But in my xml I didn't have

<bean id="daoImpl" class="org.Service.DaoImpl">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

Is this bean code causing that error?

This is pretty confusing. please let me know where am I going wrong and how can I fix this.

Below is my Exception

Jul 20, 2016 4:04:07 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1c12fb0: startup date [Wed Jul 20 16:04:07 IST 2016]; root of context hierarchy Jul 20, 2016 4:04:07 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions INFO: Loading XML bean definitions from class path resource [spring.xml] Jul 20, 2016 4:04:07 PM org.springframework.beans.factory.support.DefaultListableBeanFactory registerBeanDefinition INFO: Overriding bean definition for bean 'daoImpl' with a different definition: replacing [Generic bean: class [org.Service.DaoImpl]; scope=singleton; abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null; defined in file [C:\D\SpringMVC\SpringDataBases\bin\org\Service\DaoImpl.class]] with [Generic bean: class [org.Service.DaoImpl]; scope=; abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null; defined in class path resource [spring.xml]] Jul 20, 2016 4:04:07 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName INFO: Loaded JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver Jul 20, 2016 4:04:08 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] Jul 20, 2016 4:04:08 PM org.springframework.jdbc.support.SQLErrorCodesFactory INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana] Exception in thread "main" org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [insert into Student (id, name, age) values (:id, :name,:age)]; The index 1 is out of range.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941) at org.Service.DaoImpl.InsertUsingNamedParameters(DaoImpl.java:62) at org.Main.MainStudent.main(MainStudent.java:14) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:700) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:896) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:921) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:440) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:66) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:47) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:875) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ... 5 more

Hi @Stultuske, thanks for pointing that out. :) I forgot to add the Exception. I've updated my question. Thanks again:) — user2423959, Jul 20 '16 at 10:47
here seems to be your issue: he doesn't find the index The index 1 is out of range.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range. at — Stultuske, Jul 20 '16 at 10:49
@Stultuske, no where I gave the index, When I tried with `NamedParameterJdbctemplate` and same didn't throw any Exception — user2423959, Jul 20 '16 at 10:57

score 1 · Accepted Answer · edited May 23 '17 at 12:08

You are confused with JdbcTemplate and NamedParameterJdbcTemplate, Refer this

Query "insert into Student (id, name, age) values (:id, :name,:age)"; works fine with NamedParameterJdbcTemplate since it has named parameters instead of '?' place holders.

change your query to "insert into Student (id, name, age) values (?, ?, ?)"; and try to use JdbcTemplate#update(String sql, Object... args) throws DataAccessException method, it should work.

Just fyi, you are mixing xml bean configuration and component-scan, it's not recommended.

Unable to know whats going wrong with JdbcTemplateSupport

1 Answers1