Wednesday, 30 January 2019

Spring JdbcTemplate Example | Spring JDBC tutorial | Spring JDBC

🚀 WANT TO MASTER JAVA? CLICK HERE TO SUBSCRIBE! 🔔

Join the Ram N Java community for weekly coding deep-dives!

Implementing Spring JdbcTemplate: A Beginner's Guide

Database management can often feel overwhelming for beginners. In this tutorial, we break down how to use Spring JdbcTemplate to handle database operations with much less code compared to traditional JDBC. Let's dive in!

1. Why Use Spring JdbcTemplate?

Traditionally, JDBC requires a lot of "boilerplate" code—opening connections, handling exceptions, and closing connections. Spring's JdbcTemplate takes care of all those repetitive tasks for you. This allows you to focus purely on the SQL logic and your application data.

2. Setting Up Your Project

To get started, you need to ensure your environment is ready. You will need:

Java Development Kit (JDK) installed.
Spring Framework libraries in your project.
A Database Driver (like MySQL or Oracle) to connect to your database.

3. The DAO (Data Access Object) Pattern

In this example, we follow the DAO pattern. We create an interface (e.g., EmployeeDAO) and an implementation class (EmployeeDAOImpl). Inside the implementation, we use JdbcTemplate to execute our queries, making the code clean and organized.

4. Configuring the DataSource

Spring needs to know how to find your database. You define a DataSource bean in your configuration file, providing the database URL, username, and password. This DataSource is then "injected" into the JdbcTemplate, giving it the power to talk to your database.

5. Executing an Update

Whether you are inserting a new record or updating an existing one, the update() method is your best friend. In the video, we demonstrate how to pass parameters safely into an SQL query, preventing issues like SQL injection while keeping the logic simple.

More From Ram N Java

If you enjoyed this tutorial, check out these other videos to continue your learning journey:

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

Click the below Image to Enlarge:

Spring JdbcTemplate Example | Spring JDBC tutorial | Spring JDBC

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 javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl implements EmployeeDAO

{

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource)

    {

        this.jdbcTemplate = new JdbcTemplate(dataSource);

    }

    public void insert(Employee employee)

    {

        String sql = "INSERT INTO EMPLOYEE "

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

        jdbcTemplate.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 29, 2019 10:29:43 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

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

Jan 29, 2019 10:29:43 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 29, 2019 10:29:44 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Tue Jan 29 10:29:45 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.