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!

✨ SUBSCRIBE FOR MORE ✨

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

package com.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

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

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

Bitbucket Link:
https://bitbucket.org/ramram43210/java_spring_2019/src/8f8b5563b7f2921ed260908e4906103995d9c6cc/Spring_2019/SpringDemo_count/?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

JDBC : TYPE_SCROLL_INSENSITIVE Vs. TYPE_SCROLL_SENSITIVE ResultSet

Mastering Java & Database Concepts?

Subscribe to our YouTube channel for simple, practical, and beginner-friendly programming tutorials!

SUBSCRIBE NOW

Introduction to Scrollable ResultSets

In JDBC, a standard ResultSet cursor moves strictly forward, row by row. However, JDBC also allows you to create scrollable ResultSets that let you navigate backwards, jump to specific rows, or move relative to your current position.

When working with scrollable cursors, JDBC offers two distinct types:

• ResultSet.TYPE_SCROLL_INSENSITIVE
• ResultSet.TYPE_SCROLL_SENSITIVE

While both allow bi-directional scrolling, they handle live database updates and performance in fundamentally different ways.

Understanding TYPE_SCROLL_INSENSITIVE

A TYPE_SCROLL_INSENSITIVE ResultSet creates a static snapshot of the query data at the moment it is executed.

• Snapshot of Data: The data returned represents a frozen state of the database rows matching your query condition.
• Changes Not Visible: If another transaction or user modifies or updates the underlying records in the database table while the ResultSet remains open, those changes will not be reflected.
• Better Performance: Whenever you invoke getXXX() methods, data is retrieved directly from local memory/snapshot rather than making round trips to the database.

Understanding TYPE_SCROLL_SENSITIVE

In contrast, a TYPE_SCROLL_SENSITIVE ResultSet does not hold a full snapshot. Instead, it maintains dynamic pointers to the rows matching your query.

• Live Row Pointers: It references active rows that satisfy the SQL criteria.
• Changes Are Visible: If underlying records are modified by another process while your ResultSet is still active, those updates are immediately visible when reading the row again.
• Performance Overhead: Because every read operation may require verification or a round trip to the database server, it involves higher overhead and slower execution compared to insensitive scroll.

Comparison Summary

Feature	TYPE_SCROLL_INSENSITIVE	TYPE_SCROLL_SENSITIVE
Internal Model	Static snapshot of data	Dynamic row pointers
Live Updates	Database changes are NOT reflected	Database changes ARE reflected
Performance	Faster (reads from local snapshot)	Slower (requires round trips to database)
Driver Support	Widely supported across JDBC drivers	Varies; depends on database and driver support

Related Tutorials You Might Like

• JDBC Scrollable ResultSet: Hands-On Demo
• JDBC Updatable ResultSet: Editing Data Dynamically in Java
• JDBC ResultSetMetadata Explained: The Key to Data Insights | JDBC

---

Click here to watch in Youtube : https://www.youtube.com/watch?v=_7aJ77RKTtI

Click the below Image to Enlarge

JDBC : TYPE_SCROLL_INSENSITIVE Vs. TYPE_SCROLL_SENSITIVE ResultSet

See also: