Monday, 18 February 2019

Spring + JdbcTemplate + Execute delete statement with multiple parameters with object args example

🚀 Master Spring & Java with Us!

Want to simplify complex coding concepts? Join the Ram N Java family today!

Deleting Data with Multiple Arguments in Spring JDBC

In this tutorial, we will explore how to perform a DELETE operation in a database using Spring JdbcTemplate when you have multiple filtering criteria. Using Object arguments is one of the most efficient and cleanest ways to pass multiple values into your SQL queries.

The SQL Query Pattern

When you need to delete a record based on more than one condition (for example, deleting an employee by both their Name and Age), you use ? placeholders for each dynamic value:

        DELETE FROM employee WHERE name = ? AND age = ?
    

Why Use Object Arguments?

Using an Object[] or varargs (variable arguments) in the update() method is perfect for beginners because:

Prevents SQL Injection: Spring handles the parameter binding safely.
Cleaner Code: You don't have to manually build long strings with + signs.
Automatic Typing: Spring automatically identifies if your parameter is a String, Integer, or Date.

Practical Implementation

SQL Statement: Write your DELETE query with ? for values.
Arguments: Prepare your values. If using an Object array, the order must match the ? order in the SQL.
Execute: Call jdbcTemplate.update(sql, "EmployeeName", 30).

⭐ Pro Tip:

The update() method returns an integer. This number tells you exactly how many rows were deleted. If it returns 0, no matching records were found!

Expand Your Knowledge

Check out these other Spring JDBC tutorials from Ram N Java to sharpen your skills:

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

Click the below Image to Enlarge:

Spring + JdbcTemplate + Execute delete statement with multiple parameters with object args example

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 deleteByEmployeeNameAndAge(String name,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 deleteByEmployeeNameAndAge(String name,int age)

    {

        String sql = "DELETE FROM EMPLOYEE WHERE Name=? and AGE=?";

        int numberOfRowsAffected = getJdbcTemplate().update(sql, name,age);

        return numberOfRowsAffected;

    }

}

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 numberOfRowsAffected = employeeDAO.deleteByEmployeeNameAndAge("Peter",28);

        System.out.println(numberOfRowsAffected + " employee row deleted successfully.");

    }

}

Output:

Feb 09, 2019 11:04:51 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

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

Feb 09, 2019 11:04:51 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 09, 2019 11:04:52 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Sat Feb 09 11:04:58 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.

1 employee row deleted successfully.