Monday, 4 March 2019

Spring + JdbcTemplate + How to find a total number of rows based on specific column value

🔥 Master Java with us!

Join the Ram N Java community for simplified coding tutorials.

Counting Rows with Parameters in Spring JDBC

Finding the total number of records based on a specific condition—like counting employees of a certain age—is a fundamental task in database management. In this guide, we'll use Spring JdbcTemplate to achieve this cleanly and efficiently.

The SQL Logic

To count records with a specific filter, we use the COUNT(*) function combined with a WHERE clause. In Java, we represent the dynamic value with a placeholder (?).

        SELECT COUNT(*) FROM employee WHERE age = ?
    

Step 1: The DAO Implementation

We extend JdbcDaoSupport or use JdbcTemplate directly. The key method is queryForObject. It takes three arguments:

SQL String: The query we defined above.
Required Type: Since a count is a number, we use Integer.class.
Arguments: The actual value (e.g., the age) that replaces the ?.

Step 2: Configuration & Execution

In your Spring configuration (XML or Java-based), ensure your DataSource is correctly linked to your database (MySQL, Oracle, etc.). Once injected, you can call your DAO method from the main application context.

Pro Tip: Using queryForObject for counts is much safer and faster than retrieving the entire list and checking the size in Java!

Explore More from Ram N Java

Enhance your Spring JDBC skills with these related tutorials:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + How to find a total number of rows based on specific column value

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','50','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 findTotalEmployeeByAge(int age);

}

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 findTotalEmployeeByAge(int age)

    {

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

        int count = getJdbcTemplate().queryForObject(

                sql, Integer.class,age);

        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.findTotalEmployeeByAge(50);

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

    }

}

Output:

Feb 16, 2019 11:24:46 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

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

Feb 16, 2019 11:24:47 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 11:24:48 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sat Feb 16 11:24:57 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 = 2