Saturday, 9 February 2019

Spring + JdbcTemplate + JdbcDaoSupport example | Spring JDBC tutorial

🚀 JOIN THE COMMUNITY: Subscribe to Ram N Java for More Tutorials! 🔔

Mastering Spring JdbcTemplate and JdbcDaoSupport

In this guide, we explore how to simplify database operations in Spring using JdbcTemplate and JdbcDaoSupport. If you're a beginner looking to understand how Spring manages database connections efficiently, you're in the right place!

1. What is JdbcDaoSupport?

The JdbcDaoSupport class is a superclass provided by Spring. When your DAO class extends it, you don't need to manually define a DataSource or JdbcTemplate inside your class. It provides built-in methods to handle these components automatically.

2. Creating Your DAO Implementation

In the tutorial, we create an EmployeeDaoImpl class that extends JdbcDaoSupport. By doing this, we can easily access the JdbcTemplate using the getJdbcTemplate() method to perform database operations like inserting data.

3. Setting Up Configuration (XML)

Spring needs to know how to connect to your database. We define a DataSource bean in the configuration file, providing details like:

Driver Class Name
Database URL
Username and Password

This DataSource is then "injected" into our DAO class, making everything work together seamlessly.

4. Performing the Database Insert

Once everything is configured, we use the update() method of the JdbcTemplate. We pass our SQL query along with the employee details (ID, Name, Age, Salary). The Spring container handles the connection opening and closing for us!

Explore More Tutorials

Check out these related videos from my channel to master Spring JDBC:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + JdbcDaoSupport 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 org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl

        extends JdbcDaoSupport implements EmployeeDAO

{

    public void insert(Employee employee)

    {

        String sql = "INSERT INTO EMPLOYEE "

                + "(EMPLOYEE_ID, NAME, AGE,SALARY) VALUES (?, ?, ?,?)";

        getJdbcTemplate().update(sql,

                new Object[] { employee.getEmployeeId(),

                        employee.getName(), employee.getAge(),

                        employee.getSalary() });

    }

}

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 30, 2019 10:26:52 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@736e9adb: startup date [Wed Jan 30 10:26:52 IST 2019]; root of context hierarchy

Jan 30, 2019 10:26:53 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 30, 2019 10:26:53 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Wed Jan 30 10:26:54 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.