Sunday, 10 February 2019

Spring + JdbcTemplate + NamedParameterJdbcDaoSupport example | Spring JDBC tutorial

🚀 Level Up Your Tech Skills!

Want more hands-on Java and Spring tutorials? Subscribe to Ram N Java and never miss an update!

Mastering NamedParameterJdbcDaoSupport in Spring

Writing SQL queries in Java can often get messy, especially when dealing with multiple parameters. In this tutorial, we take a deep dive into NamedParameterJdbcDaoSupport, a powerful utility that makes your database interactions cleaner and much easier to read.

What is NamedParameterJdbcDaoSupport?

Traditional JDBC uses the ? placeholder, which can become confusing if you have 10+ parameters. NamedParameterJdbcDaoSupport allows you to use named parameters (like :userId or :status). This makes your SQL queries self-documenting and less prone to positional errors.

Why Use It Over Standard JdbcTemplate?

For beginners and pros alike, the benefits are clear:

✔ Readability: Named parameters make it obvious what each value represents.
✔ Maintainability: Adding or removing parameters doesn't require re-counting ? marks.
✔ Spring Integration: It inherits all the goodness of the standard DAO support classes.

The Hands-On Example

In the video, we walk through setting up the configuration, extending the class, and writing a concrete DAO implementation. We show you how to map a MapSqlParameterSource to your query, ensuring a seamless flow from your Java code to the database.

More from the Ram N Java Channel

Check out these other trending videos to keep your knowledge sharp:

🔗 MCP Security: Authentication vs Authorization Explained 🔗 MCP Deep Dive: Why Developers Are Switching to This Architecture 🔗 The Secret History of Generative AI: It Didn't Start with ChatGPT

Click here to watch on Youtube:
https://www.youtube.com/watch?v=2NuDIfNu1Is&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge:

Spring + JdbcTemplate + NamedParameterJdbcDaoSupport example | Spring JDBC tutorial

Employee.sql

CREATE DATABASE org_db;

CREATE TABLE `employee` (

  `EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(100) NOT NULL,

  `AGE` INT(10) NOT NULL,

  `SALARY` INT(10) DEFAULT NULL,

  PRIMARY KEY (`EMPLOYEE_ID`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

pom.xml

<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/maven-v4_0_0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.ram.core</groupId>

    <artifactId>SpringDemo</artifactId>

    <packaging>jar</packaging>

    <version>1.0-SNAPSHOT</version>

    <name>SpringDemo</name>

    <url>http://maven.apache.org</url>

    <properties>

        <spring.version>5.0.5.RELEASE</spring.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <!-- Spring 5 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 database driver -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>8.0.11</version>

        </dependency>

    </dependencies>

</project>

Employee.java

package com.ram.employee.model;

public class Employee

{

    private int employeeId;

    private String name;

    private int age;

    private int salary;

    public Employee(int employeeId, String name, int age, int salary)

    {

        super();

        this.employeeId = employeeId;

        this.name = name;

        this.age = age;

        this.salary = salary;

    }

    public int getEmployeeId()

    {

        return employeeId;

    }

    public void setEmployeeId(int employeeId)

    {

        this.employeeId = employeeId;

    }

    public String getName()

    {

        return name;

    }

    public void setName(String name)

    {

        this.name = name;

    }

    public int getAge()

    {

        return age;

    }

    public void setAge(int age)

    {

        this.age = age;

    }

    public int getSalary()

    {

        return salary;

    }

    public void setSalary(int salary)

    {

        this.salary = salary;

    }

    @Override

    public String toString()

    {

        return "Employee [employeeId=" + employeeId + ", name=" + name

                + ", age=" + age + ", salary=" + salary + "]";

    }

}

EmployeeDAO.java

package com.ram.employee.dao;

import com.ram.employee.model.Employee;

public interface EmployeeDAO

{

    public void insert(Employee employee);

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.util.HashMap;

import java.util.Map;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl

        extends NamedParameterJdbcDaoSupport implements EmployeeDAO

{

    public void insert(Employee employee)

    {

        String sql = "INSERT INTO EMPLOYEE "

                + "(EMPLOYEE_ID, NAME, AGE,SALARY) VALUES (:employeeId, :name, :age,:salary)";

        Map<String, Object> parameters = new HashMap<String, Object>();

        parameters.put("employeeId", employee.getEmployeeId());

        parameters.put("name", employee.getName());

        parameters.put("age", employee.getAge());

        parameters.put("salary", employee.getSalary());

        getNamedParameterJdbcTemplate().update(sql, parameters);

    }

}

applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">

    <bean id="dataSource"

        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.mysql.jdbc.Driver" />

        <property name="url" value="jdbc:mysql://localhost:3306/org_db" />

        <property name="username" value="root" />

        <property name="password" value="root" />

    </bean>

    <bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">

        <property name="dataSource" ref="dataSource" />

    </bean>

</beans>

App.java

package com.ram.core;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class App

{

    public static void main(String[] args)

    {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "applicationContext.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        Employee employee = new Employee(1, "Peter", 28, 80000);

        employeeDAO.insert(employee);

        System.out.println("Employee record inserted successfully.");

    }

}

Output:

Jan 31, 2019 10:10:56 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@736e9adb: startup date [Thu Jan 31 10:10:56 IST 2019]; root of context hierarchy

Jan 31, 2019 10:10:56 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [applicationContext.xml]

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Jan 31, 2019 10:10:56 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Thu Jan 31 10:10:57 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Employee record inserted successfully.