JAVA EE: Spring JDBC Insert and find example | Spring JDBC tutorial

Tuesday, 29 January 2019

Spring JDBC Insert and find example | Spring JDBC tutorial | Spring JDBC

🚀 Master Spring Framework & Java!

Subscribe to Ram N Java for hands-on, beginner-friendly tutorials and real-world Java examples!

Introduction to Spring JDBC

Spring JDBC simplifies database interactions in Java applications by managing repetitive tasks like opening and closing connections, handling SQL exceptions, and binding parameters cleanly.

Project Setup & Dependencies

To get started, create the target database table and configure your project dependencies:

Database Table: An EMPLOYEE table containing fields like EMPLOYEE_ID, NAME, AGE, and SALARY.
Maven Dependencies: Include spring-jdbc, spring-context, and the mysql-connector-java driver in your pom.xml.

Step-by-Step Architecture

A clean Spring JDBC architecture is organized across key layers:

Model Class (Employee): A standard POJO representing employee attributes with constructors, getters, setters, and a toString() method.
DAO Interface (EmployeeDAO): Declares essential CRUD methods like insert(Employee employee) and findByEmployeeId(int id).
DAO Implementation (EmployeeDAOImpl): Implements data access methods, utilizing a configured DataSource injected by the Spring container.
Spring XML Configuration: Configures the database connection properties (driver, URL, username, password) and wires the DataSource into the DAO bean.

Key Takeaways

Spring's Dependency Injection manages database connections and data source configurations automatically.
Separating DAO interfaces from implementation classes keeps your data access code maintainable and testable.

Related Videos to Watch Next

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

Click the below Image to Enlarge:

Spring JDBC Insert and find 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);

    public Employee findByEmployeeId(int employeeId);

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl implements EmployeeDAO

{

    private DataSource dataSource;

    public void setDataSource(DataSource dataSource)

    {

        this.dataSource = dataSource;

    }

    public void insert(Employee employee)

    {

        String sql = "INSERT INTO EMPLOYEE "

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

        Connection conn = null;

        try

        {

            conn = dataSource.getConnection();

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, employee.getEmployeeId());

            ps.setString(2, employee.getName());

            ps.setInt(3, employee.getAge());

            ps.setInt(4, employee.getSalary());

            ps.executeUpdate();

            ps.close();

        }

        catch (SQLException e)

        {

            throw new RuntimeException(e);

        }

        finally

        {

            if (conn != null)

            {

                try

                {

                    conn.close();

                }

                catch (SQLException e)

                {

                }

            }

        }

    }

    public Employee findByEmployeeId(int employeeId)

    {

        String sql = "SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID = ?";

        Connection conn = null;

        try

        {

            conn = dataSource.getConnection();

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, employeeId);

            Employee employee = null;

            ResultSet rs = ps.executeQuery();

            if (rs.next())

            {

                employee = new Employee(rs.getInt("EMPLOYEE_ID"),

                        rs.getString("NAME"), rs.getInt("AGE"),

                        rs.getInt("SALARY"));

            }

            rs.close();

            ps.close();

            return employee;

        }

        catch (SQLException e)

        {

            throw new RuntimeException(e);

        }

        finally

        {

            if (conn != null)

            {

                try

                {

                    conn.close();

                }

                catch (SQLException e)

                {

                }

            }

        }

    }

}

Spring-Datasource.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>

</beans>

Spring-Employee.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="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">

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

    </bean>

</beans>

Spring-Module.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">

    <import resource="database/Spring-Datasource.xml"/>

    <import resource="employee/Spring-Employee.xml"/>

</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(

                "Spring-Module.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

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

        employeeDAO.insert(employee1);

        Employee employee2 = employeeDAO.findByEmployeeId(1);

        System.out.println(employee2);

    }

}

Output:

Jan 26, 2019 10:30:05 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Sat Jan 26 10:30:05 IST 2019]; root of context hierarchy

Jan 26, 2019 10:30:05 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [Spring-Module.xml]

Jan 26, 2019 10:30:06 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [database/Spring-Datasource.xml]

Jan 26, 2019 10:30:06 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [employee/Spring-Employee.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 26, 2019 10:30:07 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sat Jan 26 10:30:08 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.

Sat Jan 26 10:30:22 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 [employeeId=1, name=Peter, age=28, salary=80000]