Monday, 4 March 2019

Spring + JdbcTemplate + How to check employee record exists in the table or not

🚀 Enjoying the tutorials?

Subscribe to Ram N Java for more easy-to-follow coding guides!

Check Record Existence with Spring JdbcTemplate

In this tutorial, we will learn a simple and efficient way to check if a specific record (like an employee) exists in a database table using Spring JDBC Template. This is a common requirement in many Java applications.

1. The SQL Query

We use a SELECT COUNT(*) query to check for the record. This is efficient because it returns a single number instead of all the data in the row.

SELECT count(*) FROM employee WHERE name = ?

2. Using queryForObject

In our Java code, we use the queryForObject method from JdbcTemplate. We pass three things:

The SQL query string.
The parameter (like the employee name).
The return type (Integer.class).

3. The Logic

Once we get the count back from the database, we check if it is greater than 0:

If count > 0: The record exists (returns true).
If count = 0: The record does not exist (returns false).

Watch More Tutorials

Check out these related videos from my channel to master Spring JDBC:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + How to check employee record exists in the table or not

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 boolean isEmployeeExist(String name);

}

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 boolean isEmployeeExist(String name)

    {

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

        int count = getJdbcTemplate().queryForObject(sql,

                new Object[] { name }, Integer.class);

        if (count >= 1)

        {

            return true;

        }

        return false;

    }

}

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");

        boolean isExist = employeeDAO.isEmployeeExist("Peter");

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

    }

}

Output:

Feb 17, 2019 10:29:49 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Sun Feb 17 10:29:49 IST 2019]; root of context hierarchy

Feb 17, 2019 10:29:50 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 17, 2019 10:29:51 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sun Feb 17 10:29: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.

isExist = true