Thursday, 14 February 2019

Spring + JdbcTemplate + Query multiple rows example | Spring JDBC tutorial

🌟 Support the Channel! 🌟

Enjoying these Java tutorials? Don't miss out on new uploads!

Spring JdbcTemplate: How to Query Multiple Rows Easily

If you're working with databases in Spring, one of the most common tasks you'll perform is fetching multiple records at once. Whether you're building a list of users, products, or transactions, understanding how to use JdbcTemplate efficiently is a must-have skill for every Java developer.

The Power of RowMapper

When querying multiple rows, we often use a RowMapper. Think of a RowMapper as a translator: it takes a single row from your database (a ResultSet) and turns it into a Java object (a POJO). Because Spring handles the loop for you, it's incredibly clean and reduces "boilerplate" code.

Why Use JdbcTemplate?

For beginners, JdbcTemplate is the "Goldilocks" of database access. It's not as complex as full Hibernate/JPA, but it's much more powerful than raw JDBC. Here’s why it’s great:

No More Resource Leaks: Spring automatically closes your connections and statements.
Exception Handling: It converts confusing SQL errors into readable Spring Data Access Exceptions.
Clean Code: You focus on the SQL and the mapping, not the plumbing.

Example Overview

In this tutorial, we demonstrate how to use the query() method. By passing your SQL string and a RowMapper implementation, you get back a List of objects ready to be used in your application logic.

More Tutorials from Ram N Java

Keep learning! Check out these other highly-rated videos from our channel:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + Query multiple rows 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 long employeeId;

    private String name;

    private int age;

    private int salary;

    public long getEmployeeId()

    {

        return employeeId;

    }

    public void setEmployeeId(long 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 java.util.List;

import com.ram.employee.model.Employee;

public interface EmployeeDAO 

{

    public List<Employee> findAll();

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

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 List<Employee> findAll()

    {

        String sql = "SELECT * FROM EMPLOYEE";

        List<Map<String, Object>> list = getJdbcTemplate()

                .queryForList(sql);

        List<Employee> employeeList = new ArrayList<Employee>();

        for (Map<String, Object> map : list)

        {

            Employee employee = new Employee();

            employee.setEmployeeId((Long) map.get("EMPLOYEE_ID"));

            employee.setName((String) map.get("NAME"));

            employee.setAge((Integer) map.get("AGE"));

            employee.setSalary((Integer) map.get("SALARY"));

            employeeList.add(employee);

        }

        return employeeList;

    }

}

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 java.util.List;

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");

        List<Employee> employeeList = employeeDAO.findAll();

        System.out.println("employeeList = " + employeeList);

        System.out.println("size = " + employeeList.size());

    }

}

Output:

Feb 03, 2019 8:55:24 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Sun Feb 03 08:55:24 IST 2019]; root of context hierarchy

Feb 03, 2019 8:55:25 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.

Feb 03, 2019 8:55:26 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sun Feb 03 08:55:53 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.

employeeList = [Employee [employeeId=1, name=Peter, age=28, salary=80000], Employee [employeeId=2, name=John, age=50, salary=40000], Employee [employeeId=3, name=David, age=45, salary=20000]]

size = 3