NamedParameterJdbcTemplate and Collections.singletonMap
In this post I will show you how to use NamedParameterJdbcTemplate
and Collections.singletonMap
to execute query for returning result. 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 Collections.singletonMap
returns an immutable map, mapping only the specified key to the specified value. The returned map is serializable.
Prerequisites
Java 8/19, Gradle 6.5.1, Maven 3.6.3/3.8.5, Spring Boot 2.3.2/3.1.0, MySQL 8.0.17/8.0.31
Project Setup
You can create either gradle or maven based project in your favorite IDE or tool. The name of the project is spring-namedparameterjdbctemplate-collections-singletonmap.
If you are creating gradle based project then you can use below build.gradle script:
buildscript {
ext {
springBootVersion = '2.3.2.RELEASE'
}
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:
<?xml version="1.0" encoding="UTF-8"?>
<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-collections-singletonmap</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>19</maven.compiler.source>
<maven.compiler.target>19</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.0</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version>
</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 IF NOT EXISTS `user` (
`id` int unsigned NOT NULL,
`first_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`last_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Database Configuration
I will use annotation based configuration and you 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:
@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;
}
}
DAO Class
DAO class is where perform database operations. For my example, here I am going to count the number of users for the given user’s name and return returning the result.
@Component
public class UserDao {
@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;
public int countByName(String name) {
final String sql = "select count(*) from user where first_name = :name";
Map<String, String> namedParameters = Collections.singletonMap("name", name);
return jdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}
}
Main Class
A class having main method with @SpringBootApplication
annotation is enough to deploy the Spring Boot application into embedded Tomcat server.
@SpringBootApplication
public class SpringNamedParameterJdbcTemplateSingletonMapApp implements CommandLineRunner {
@Autowired
private UserDao dao;
public static void main(String[] args) {
SpringApplication.run(SpringNamedParameterJdbcTemplateSingletonMapApp.class, args);
}
@Override
public void run(String... args) throws Exception {
System.out.println("Count: " + dao.countByName("Soumitra"));
}
}
Testing the Application
Executing the above class will give you the following output:
Count: 1