11.1 Overview of JDBC Delete

JDBC Statement and Prepared Statements both provide an API which can be used to delete the records from the database.

 It is important to understand that Statement API does not provide any way to use the place holders in Query like Prepared Statement. You need to use a static query though you can make the query dynamic using String concatenation.

11.2 API to delete Records

For Statement we can use executeUpdate (String SQL) API to fire the delete SQL and this API will return the number of rows deleted.

For PreparedStatement we can use executeUpdate()API to fire the delete SQL and this API will return the number of rows deleted.

11.3 JDBC Delete Examples

We will use the MySQL database . Use the below sql statement to create a schema

Create schema JDBCTutorial;

Create table User with 4 columns using below SQL

CREATE  TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL ,  `age` VARCHAR(45) NULL ,  `email` VARCHAR(45) NULL ,  PRIMARY KEY (`id`) );

11.3.1 – Write an Example to delete records from “User” table using Statement API.

Solution –

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDeleteStatementExample {

     public static void main(String args[])
    {
        try
        {            
            Connection conn= getConnection();
            Statement stmt = conn.createStatement();            
            String sql = "delete from User";
            int deletedRecords  = stmt.executeUpdate(sql);
            System.out.println("Number of Records Deleted=" + deletedRecords);
            stmt.close();
            conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
            String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
            Class.forName(dbDriver);
            con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }

}

Output-

Before Running Program, table status is as below-

On Running above Program, executeUpdate method will delete the records and will return 1 as number of records deleted is 1.

11.3.2 – Write an Example to delete records from “User” table using Prepared Statement API.

Solution –

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCDeletePreparedStatementExample {

    public static void main(String args[])
    {
        try
        {            
            Connection conn= getConnection();
            String sql = "delete from User";
            PreparedStatement stmt = conn.prepareStatement(sql);                    
            int deletedRecords  = stmt.executeUpdate(sql);
            System.out.println("Number of Records Deleted=" + deletedRecords);
            stmt.close();
            conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
            String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
            Class.forName(dbDriver);
            con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }
}

Output-

Before Running Program, table status is as below-

On Running above Program, executeUpdate method will delete the records and will return 1 as number of records deleted is 1.