Spring NamedParameterJdbcTemplate And BeanPropertySqlParameterSource Example


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 Java Bean (that is, an instance of a class that adheres to the Java Bean conventions), and uses the properties of the wrapped Java Bean as the source of named parameter values.


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-beanpropertysqlparametersource.

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

buildscript {
	ext {
		springBootVersion = '2.3.2.RELEASE'
    repositories {
    dependencies {

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

sourceCompatibility = 12
targetCompatibility = 12

repositories {

dependencies {
	implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
	//required for jdk 9 or above

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"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">


		<!--required only if jdk 9 or higher version is used -->

MySQL Table

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

USE `roytuts`;

  `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;

INSERT INTO `user` (`id`, `first_name`, `last_name`) VALUES
	(4, 'First Name', 'Last Name'),
	(3, 'Arup', 'Roy'),
	(1, 'Soumitra', 'Roy'),
	(2, 'Liton', 'Sarkar');

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:


#disable schema generation from Hibernate

The required configuration Java class is given below:

public class Config {

	private Environment environment;

	public DataSource dataSource() {

		DriverManagerDataSource ds = new DriverManagerDataSource();
		return ds;

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

		return jdbcTemplate;



Model Class

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

public class User {

	private Integer id;

	private String firstName;

	private String lastName;

	public User() {

	public User(Integer id, String firstName, String lastName) {
		this.id = id;
		this.firstName = firstName;
		this.lastName = lastName;

	public Integer getId() {
		return id;

	public void setId(Integer id) {
		this.id = id;

	public String getFirstName() {
		return firstName;

	public void setFirstName(String firstName) {
		this.firstName = firstName;

	public String getLastName() {
		return lastName;

	public void setLastName(String lastName) {
		this.lastName = lastName;


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.

public class UserDao {

	private NamedParameterJdbcTemplate jdbcTemplate;

	public void addUser(User user) {
		final String sql = "insert into user(id, first_name, last_name) values(:id, :firstName, :lastName)";

		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);

		jdbcTemplate.update(sql, paramSource);

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

		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);

		return jdbcTemplate.queryForObject(sql, paramSource, Integer.class);


Main Class

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

public class SpringBeanPropertySqlParameterSourceApp implements CommandLineRunner {

	private UserDao dao;

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

	public void run(String... args) throws Exception {
		dao.addUser(new User(1, "Soumitra", "Roy"));

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


Testing BeanPropertySqlParameterSource

Executing the above class will give you the following output:

Number of Users: 1

Source Code


Leave a Reply

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