Thursday, 14 February 2019

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

🚀 Boost Your Java Skills!

Join the Ram N Java family for more simplified coding tutorials.

Fetching Multiple Rows Using BeanPropertyRowMapper

Retrieving a list of objects from a database is a staple of Java development. In this guide, we'll explore how Spring JdbcTemplate combined with BeanPropertyRowMapper makes querying multiple rows incredibly simple and efficient.

The Power of BeanPropertyRowMapper

Normally, you would have to manually map each column in a ResultSet to your Java object's fields. BeanPropertyRowMapper automates this process by matching column names (like EMPLOYEE_NAME) to class properties (like employeeName) automatically!

Using the query() Method

To fetch multiple rows, we use the query() method of JdbcTemplate. It typically requires two main components:

SQL String: A standard SELECT * FROM table query.
RowMapper: An instance of BeanPropertyRowMapper initialized with your target class (e.g., Employee.class).

Implementation Steps

Create Your POJO: Define a standard Java class (e.g., Employee) with fields that match your database columns.
The DAO Method: In your DAO, call jdbcTemplate.query(sql, new BeanPropertyRowMapper(Employee.class)).
Result: Spring returns a List<Employee>, with each row automatically converted into an Employee object.

⭐ Pro Tip:

Make sure your Java bean has a default (no-argument) constructor and standard setter methods, as BeanPropertyRowMapper relies on them to populate the data!

Related Tutorials to Explore

Check out these related videos from Ram N Java to further your Spring JDBC expertise:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + BeanPropertyRowMapper + 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.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

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<Employee> employeeList = getJdbcTemplate().query(sql,

                new BeanPropertyRowMapper(Employee.class));

        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 05, 2019 10:14:40 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Tue Feb 05 10:14:40 IST 2019]; root of context hierarchy

Feb 05, 2019 10:14:40 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 05, 2019 10:14:41 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Tue Feb 05 10:14:48 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