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

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + ResultSetExtractor + 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.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
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()
    {
        return getJdbcTemplate().query("SELECT * FROM EMPLOYEE",
                new ResultSetExtractor<List<Employee>>()
                {
                    public List<Employee> extractData(ResultSet rs)
                            throws SQLException, DataAccessException
                    {

                        List<Employee> employeeList = new ArrayList<Employee>();
                        while (rs.next())
                        {
                            Employee employee = new Employee();
                            employee.setEmployeeId(rs.getInt(1));
                            employee.setName(rs.getString(2));
                            employee.setAge(rs.getInt(3));
                            employee.setSalary(rs.getInt(4));
                            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 04, 2019 9:39:03 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Mon Feb 04 09:39:03 IST 2019]; root of context hierarchy
Feb 04, 2019 9:39:03 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 04, 2019 9:39:04 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Mon Feb 04 09:39: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.
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

Click the below link to download the code:
https://sites.google.com/site/javaspringram2019/java_spring_2019/SpringDemo_ResultSetExtractor_Query_multi_rows.zip?attredirects=0&d=1

Github Link:
https://github.com/ramram43210/Java_Spring_2019/tree/master/Spring_2019/SpringDemo_ResultSetExtractor_Query_multi_rows

Bitbucket Link:
https://bitbucket.org/ramram43210/java_spring_2019/src/82f7e9dec96e6a7b6bf54351b41ee4ef695048bc/Spring_2019/SpringDemo_ResultSetExtractor_Query_multi_rows/?at=master

See also:

All JavaEE Videos Playlist

All JavaEE Videos

All JAVA EE Links

Spring Tutorial

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JAVA Tutorial

Kids Tutorial

Cooking Tutorial