In this post I will show you how to use NamedParameterJdbcTemplate and BeanPropertySqlParameterSource to execute query. The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder (‘?’) arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work. An SqlParameterSource is a source of named parameter values to a NamedParameterJdbcTemplate. The BeanPropertySqlParameterSource class is an implementation of SqlParameterSource interface. This class wraps an arbitrary JavaBean (that is, an instance of a class that adheres to the JavaBean conventions), and uses the properties of the wrapped JavaBean as the source of named parameter values.

For this tutorial we will create a standalone maven project in Eclipse. If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.
Prerequisites

The following configurations are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven installed and configured
Spring and MySQL dependencies in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : spring-core

Step 2. Modify the pom.xml file as shown below.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.roytuts</groupId>
    <artifactId>spring-core</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring-core</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <jdk.version>1.8</jdk.version>
        <junit.version>4.11</junit.version>
        <mysql.version>5.1.27</mysql.version>
        <spring.version>4.1.4.RELEASE</spring.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- mysql driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>${jdk.version}</source>
                    <target>${jdk.version}</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Step 3. If you see JRE System Library[J2SE-1.4] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.4], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.

Step 4. Create src/main/resources folder for putting the resource files.

Do right-click on the project and go New -> Source Folder. Give Folder name: as src/main/resources and click on Finish button.

Step 5. Create an XML file under src/main/resources.

Do right-click on src/main/resources in the project and go New -> file. Give File name: as jdbc.properties and click on Finish button.

JDBC.DRIVER=com.mysql.jdbc.Driver
JDBC.URL=jdbc:mysql://localhost:3306/cdcol
JDBC.USERNAME=root
JDBC.PASSWORD=

Step 6. Create spring-datasource.xml file under src/main/resources with the below content.

<?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:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:task="http://www.springframework.org/schema/task"
    xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd">

    <context:annotation-config />
    <context:property-placeholder location="jdbc.properties" />

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${JDBC.DRIVER}" />
        <property name="url" value="${JDBC.URL}" />
        <property name="username" value="${JDBC.USERNAME}" />
        <property name="password" value="${JDBC.PASSWORD}" />
    </bean>
    
    <bean id="namedParameterJdbcTemplate"
        class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>

    <bean id="cdDao" class="com.roytuts.dao.impl.CdDaoImpl" />

</beans>

Step 7. Create a MySQL table

USE `cdcol`;

/*Table structure for table `cds` */

DROP TABLE IF EXISTS `cds`;

CREATE TABLE `cds` (
  `title` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Step 8. Create a POJO class for cds.

package com.roytuts.model;

public class Cd {

    private long id;
    private String title;
    private String interpret;

    public Cd() {
    }

    public Cd(String title, String interpret) {
        this.title = title;
        this.interpret = interpret;
    }

    public Cd(long id, String title, String interpret) {
        this.id = id;
        this.title = title;
        this.interpret = interpret;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getInterpret() {
        return interpret;
    }

    public void setInterpret(String interpret) {
        this.interpret = interpret;
    }

}

Step 9. Create a DAO

package com.roytuts.dao;

import com.roytuts.model.Cd;

public interface CdDao {

    void addCdNamedParameterJdbcTemplate(Cd cd);

    int countOfCds(Cd cd);

}

Step 10. Now create a DAO implementation class

package com.roytuts.dao.impl;

import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.roytuts.dao.CdDao;
import com.roytuts.model.Cd;

public class CdDaoImpl implements CdDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    private final String SQL_INSERT_CD_NAMED_PARAMETER = "insert into cds(title, interpret) values(:title, :interpret)";

    public void addCdNamedParameterJdbcTemplate(final Cd cd) {
        // notice how the named parameters match the properties of the 'Cd'
        // class
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(
                cd);
        namedParameterJdbcTemplate.update(SQL_INSERT_CD_NAMED_PARAMETER,
                namedParameters);
    }

    public int countOfCds(Cd cd) {
        // notice how the named parameters match the properties of the 'Cd'
        // class
        String sql = "select count(*) from cds where title = :title and interpret = :interpret";
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(
                cd);
        return this.namedParameterJdbcTemplate.queryForObject(sql,
                namedParameters, Integer.class);
    }

}

Step 11. Create JUnit test case under src/test/java folder.

package com.roytuts.dao.impl.test;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.roytuts.dao.CdDao;
import com.roytuts.model.Cd;

public class CdDaoImplTest {

    private CdDao cdDao;
    private ApplicationContext applicationContext;

    @Before
    public void setUp() throws Exception {
        applicationContext = new ClassPathXmlApplicationContext(
                "spring-datasource.xml");
        cdDao = applicationContext.getBean("cdDao", CdDao.class);
    }

    @After
    public void tearDown() throws Exception {
    }

    @Test
    public void testAddCdNamedParameterJdbcTemplate() {
        Cd cd = new Cd("Songs", "Soumitra");
        cdDao.addCdNamedParameterJdbcTemplate(cd);
    }
    
    @Test
    public void testCountOfCds() {
        Cd cd = new Cd("Songs", "Soumitra");
        int count = cdDao.countOfCds(cd);
        System.out.println(count);
    }

}

Step 12. Run the above JUnit test case.

Step 13. Now Look at the database table. You will see row value is inserted. Look into the console where the count is displayed.

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *