Posted in Spring JDBC

Spring NamedParameterJdbcTemplate and MapSqlParameterSource Example

Introduction

In this post I will show you how to use NamedParameterJdbcTemplate and MapSqlParameterSource to execute query for inserting or retrieving results from database table. The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder (?) arguments.

An SqlParameterSource is a source of named parameter values to a NamedParameterJdbcTemplate. The MapSqlParameterSource class is an implementation of SqlParameterSource interface. The MapSqlParameterSource class is a very simple implementation that is simply an adapter around a java.util.Map, where the keys are the parameter names and the values are the parameter values.

spring namedparameterjdbctemplate mapsqlparametersource

Prerequisites

Java 1.8+, Gradle 6.5.1, Maven 3.6.3 – 3.8.2, Spring Boot 2.3.2 – 2.5.5, MySQL 8.0.17 – 8.0.26

Project Setup

You can create either gradle or maven based project in Eclipse or in your favorite IDE or tool. The name of the project is spring-namedparameterjdbctemplate-mapsqlparametersource.

If you are creating gradle based project then you can use below build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.3.2.RELEASE to 2.5.5'
	}
	
    repositories {
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
	implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
    implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
    runtime("mysql:mysql-connector-java:8.0.17")
	
	//required for jdk 9 or above
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project then you can use below pom.xml file:

<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-namedparameterjdbctemplate-mapsqlparametersource</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>8</maven.compiler.source>
		<maven.compiler.target>8</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.5</version>
	</parent>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table

You need to create a table called user under roytuts database in MySQL server.

CREATE TABLE `user` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Database Configuration

I will use annotation based configuration and I need to create appropriate beans for working with database.

I am using application.properties file which is kept under src/main/resources classpath folder.

The content of the properties file is given below:

spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

The required configuration Java class is given below:

package com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@PropertySource("classpath:application.properties")
public class Config {

	@Autowired
	private Environment environment;

	@Bean
	public DataSource dataSource() {

		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
		ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
		ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
		ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
		return ds;
	}

	@Bean
	public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

		return jdbcTemplate;

	}

}

Model Class

You need to create a POJO class that will map database table and Java class together.

package com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.model;

public class User {

	private Integer id;

	private String name;

	private String email;

	private String phone;

	private String address;

	public User() {
	}

	public User(Integer id, String name, String email, String phone, String address) {
		this.id = id;
		this.name = name;
		this.email = email;
		this.phone = phone;
		this.address = address;
	}

	public User(String name, String email, String phone, String address) {
		this.name = name;
		this.email = email;
		this.phone = phone;
		this.address = address;
	}
        
        // getters and setters
}

DAO Class

DAO class is where perform database operations. For my example, here I am going to insert or add a new user record and count the number of users for the given user’s name and return returning the result.

The parameters are specified in the insert SQL statement using names, for examples, :id, :name, etc. and that’s why it is called Named Parameter.

package com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.model.User;

@Component
public class UserDao {

	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate;

	public void addUser(User user) {
		final String sql = "insert into user(id, name, email, phone, address) values(:id, :name, :email, :phone, :address)";

		MapSqlParameterSource mapParameters = new MapSqlParameterSource();
		mapParameters.addValue("id", user.getId());
		mapParameters.addValue("name", user.getName());
		mapParameters.addValue("email", user.getEmail());
		mapParameters.addValue("phone", user.getPhone());
		mapParameters.addValue("address", user.getAddress());

		jdbcTemplate.update(sql, mapParameters);
	}

	public int countByName(User user) {
		final String sql = "select count(*) from user where name = :name";

		MapSqlParameterSource mapParameters = new MapSqlParameterSource();
		mapParameters.addValue("name", user.getName());

		return jdbcTemplate.queryForObject(sql, mapParameters, Integer.class);
	}

}

Main Class

A class having main method with @SpringBootApplication annotation is enough to deploy the Spring Boot application into embedded Tomcat server.

package com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.dao.UserDao;
import com.roytuts.spring.namedparameterjdbctemplate.mapsqlparametersource.model.User;

@SpringBootApplication
public class SpringMapSqlParameterSourceApp implements CommandLineRunner {

	@Autowired
	private UserDao dao;

	public static void main(String[] args) {
		SpringApplication.run(SpringMapSqlParameterSourceApp.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		dao.addUser(new User(1, "Soumitra", "soumitra@roytuts.com", "43256789", "Earth"));

		User user = new User();
		user.setName("Soumitra");
		System.out.println("Number of Users: " + dao.countByName(user));
	}

}

Testing the Application

Executing the above class will give you the following output:

Number of Users: 1

Source Code

Download

Leave a Reply

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