org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter

Question

I'm working on Spring JDBC example using the BeanPropertySqlParameterSource and SqlParameterSource. When I run my code I see the following error is comming. After spending couple of hours I dont find its solution. Please guide whats wrong here.

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'employeeId': Invalid property 'employeeId' of bean class [com.spring.jdbc.model.Order]: Bean property 'employeeId' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter?
    at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:342)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:348)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:211)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:226)
    at com.spring.jdbc.dao.OrderDAOImpl.countOfOrders(OrderDAOImpl.java:51)
    at com.spring.batch.OrderTest.testCountByEmployeeIdAndShipperId(OrderTest.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

OrderDAOImpl.java

    @Override
        public Long countOfOrders(Order order) {
            String sql = "SELECT count(*) FROM orders WHERE EmployeeID = :employeeId AND ShipperID = :shipperId ";

            SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(order);

           Long count = namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Long.class);
            return count;
        }

OrderTest.java

@Test
    public void testCountByEmployeeIdAndShipperId() {
        Order order = new Order();
        order.setEmployeeID(3);
        order.setShipperID(2);

        Long countEmployee = orderDAO.countOfOrders(order);
        System.out.println("Count : "+countEmployee);
    }

OrderDAO.java

public interface OrderDAO {
    Long countByEmployeeId(Long employeeId);

    Long countByShipperId(Long shipperId);

    Long countOfOrders(Order order);
}

Order.java

public class Order {
    private int orderId;
    private int customerID;
    private int employeeID;
    private Date orderDate;
    private int shipperID;

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public int getCustomerID() {
        return customerID;
    }

    public void setCustomerID(int customerID) {
        this.customerID = customerID;
    }

    public int getEmployeeID() {
        return employeeID;
    }

    public void setEmployeeID(int employeeID) {
        this.employeeID = employeeID;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    public int getShipperID() {
        return shipperID;
    }

    public void setShipperID(int shipperID) {
        this.shipperID = shipperID;
    }
}

Customer.java

public class Customer {
    private int customerID;
    private String customerName;
    private String contactName;
    private String address;
    private String city;
    private String postalCode;
    private String country;

    public int getCustomerID() {
        return customerID;
    }

    public void setCustomerID(int customerID) {
        this.customerID = customerID;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public String getContactName() {
        return contactName;
    }

    public void setContactName(String contactName) {
        this.contactName = contactName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getPostalCode() {
        return postalCode;
    }

    public void setPostalCode(String postalCode) {
        this.postalCode = postalCode;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }
}

Beans.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"
    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:property-placeholder location="classpath:database.properties" />

    <!-- Initialization for data source -->
    <bean id="dataSource"  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver.class.name}" />
        <property name="url" value="${jdbc.driver.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>


    <bean id="orderTemplate" class="com.spring.jdbc.dao.OrderDAOImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="customerTemplate" class="com.spring.jdbc.dao.CustomerDAOImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

Answer 1

Oh I see the issue. The issue is in query, variable name is not matching at all. Use below query. Done !!

 String sql = "SELECT count(*) FROM orders WHERE EmployeeID = :employeeID AND
 ShipperID = :shipperID ";

You should use shipperID and employeeID and not shipperId and employeeId