Monday, 4 March 2019

Spring + JdbcTemplate + How to find a total number of rows in the table

💻 Ready to Level Up Your Java Skills?

Don't miss out on our easy-to-understand coding tutorials. Subscribe to Ram N Java today!

How to Find Total Rows in a Table Using Spring JdbcTemplate

When developing Java applications, you often need to know the total count of records in a database table. Whether it's for pagination or reporting, Spring JDBC makes this incredibly simple. In this guide, we'll walk through the process step-by-step.

1. Understanding the Requirement

To get the total number of rows, we use the standard SQL aggregate function COUNT(*). Instead of writing complex JDBC boilerplate code, Spring's JdbcTemplate allows us to execute this query in just one or two lines of code.

2. The SQL Query

The query is straightforward. It targets the specific table you want to count:

        SELECT COUNT(*) FROM employee
    

3. Implementing with JdbcTemplate

The most common method for this operation is queryForObject. This method is perfect when you expect a single result back from the database.

Parameters used:

SQL: Your count query string.
Required Type: Usually Integer.class or Long.class to hold the count value.

4. Why Use This Approach?

Using queryForObject is efficient because it handles the opening and closing of database connections for you. It also takes care of converting the database result directly into a Java object, making your code much cleaner and easier to read.

"Spring JDBC removes the 'noise' from your data access code, allowing you to focus on the actual logic of your application."

Continue Learning

Want to explore more Spring JDBC features? Check out these related videos from our channel:

📽️ What is Spring JdbcTemplate? | Simplifying JDBC Operations
📽️ Spring JdbcTemplate: Retrieving Auto-Generated Keys
📽️ Spring JdbcTemplate: Creating a Table Example

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + How to find a total number of rows in the table

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;

INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('1','Peter','28','80000');

INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('2','John','50','40000');

INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('3','David','45','20000');

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>

EmployeeDAO.java

package com.ram.employee.dao;

public interface EmployeeDAO

{

    public int findTotalEmployee();

}

EmployeeDAOImpl.java

packagecom.ram.employee.dao.impl;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ram.employee.dao.EmployeeDAO;

public class EmployeeDAOImpl

        extends JdbcDaoSupport implements EmployeeDAO

{

    public int findTotalEmployee()

    {

        String sql = "SELECT COUNT(*) FROM EMPLOYEE";

        int count = getJdbcTemplate().queryForObject(

                sql, Integer.class);

        return count;

    }

}

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;

public class App

{

    public static void main(String[] args)

    {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "applicationContext.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        int count = employeeDAO.findTotalEmployee();

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

    }

}

Output:

Feb 16, 2019 10:50:11 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Sat Feb 16 10:50:11 IST 2019]; root of context hierarchy

Feb 16, 2019 10:50:11 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 16, 2019 10:50:12 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sat Feb 16 10:50:20 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.

count = 3