Update and Delete Operations Using JDBC in Java

The UPDATE operation is used to modify an existing record in a table. It is done using the UPDATE SQL query, typically with a WHERE clause to specify which records to modify. The DELETE operation is used to remove one or more records from a table using the DELETE SQL Query.

Prerequisites

• Download and Install Eclipse
• Install MySQL
• Add JAR file to Classpath in Java
• CREATE DATABASE

Create a simple register table

CREATE TABLE register (

name VARCHAR(100) NOT NULL,

email VARCHAR(100) PRIMARY KEY,

possword VARCHAR(100) NOT NULL,

gender VARCHAR(10),

city VARCHAR(100)

);

Update Operation in JDBC

It involves loading the JDBC driver, establishing a connection, preparing an SQL UPDATE query using PreparedStatement, setting parameters and executing the query with executeUpdate(). The method returns the number of rows affected and based on that, success or failure is determined. Proper exception handling and resource closing are also important.

Syntax:

UPDATE table_name

SET column1 = value1

WHERE condition;

Implementation of the Update operation using JDBC

In JDBC, the Update operation is used to change current entries in a database table using SQL UPDATE statements.

Example

The following example updates the city column for a user in the register table based on their email ID.

App.java:

import java.sql.*;
import javax.sound.midi.Soundbank;
/**
 * Hello world!
 *
 */
public class App {
    public static void main(String[] args) throws Exception
    {
        // update operation
        String city = "noida";
        String email = "gaurav@gmail.com";
        try {
            // load the driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // use try with resource for automatically close
            // jdbc resourse
            try (
                // create connection
                Connection con
                = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jdbc_db",
                    "root", "password");
                // create statement
                PreparedStatement ps = con.prepareStatement(
                    "update register set city=? where email=?")) {
                // set the parameters
                ps.setString(1, city);
                ps.setString(2, email);
                // execute sql query
                int i = ps.executeUpdate();
                if (i > 0) {
                    System.out.println("success");
                }
                else {
                    System.out.println("fail");
                }
            }
        }
        catch (SQLException e) e.printStackTrace();
    }
    catch (ClassNotFoundException e)
    {
        e.printStackTrace();
    }
}
}

Output:

Explanation:

• The above program updates a record in a MySQL table using JDBC.
• A city value ("noida") is updated based on a matching email ("gaurav@gmail.com").
• The JDBC driver is loaded using Class.forName.
• A database connection is established using DriverManager.getConnection.
• An UPDATE SQL query with placeholders is prepared using PreparedStatement.
• Parameters are set using setString() for city and the email, executeUpdate method is used to execute sql query, returning the affected row.
• Print a message of success if the data is updated; otherwise, fail. Use try-with-resources to automatically close JDBC resources.
• Exception handling is included for both SQL and class loading errors.

Delete Operation in JDBC

It follows the same steps as other operations: load the JDBC driver, create a connection, prepare a DELETE SQL query using PreparedStatement, set any required parameters and execute it using executeUpdate(). The method returns how many rows were deleted and resources should be closed properly after the operation.

Syntax:

DELETE FROM table_name
WHERE condition;

Implementation of the Delete operation in JDBC

In JDBC, the Delete operation is used to delete current entries in a database table using SQL DELETE statements.

This example deletes a specific user from the register table based on their email.

App.java:

import javax.sound.midi.Soundbank;
import java.sql.*;
/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws Exception {
       
        // Delete operation
        String email="gaurav@gmail.com";
try{
    // load the driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        
        // use try with resource for automatically close jdbc resourse
        try(
            
            // create connection
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_db","root","password");
        
        // create statement
        PreparedStatement ps= con.prepareStatement("Delete from register  where email=?")){
            
        // set the parameters
               ps.setString(1,email);
               
        //execute sql query
        int i= ps.executeUpdate();
    if(i>0) {
        System.out.println("Success");
    }else {
        System.out.println("Fail");
    }
}
}catch(SQLException e)
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
}
} 

Output:

Explanation:

• The program deletes a record from a MySQL table using JDBC.
• The email value, like gaurav@gmail.com, is used to identify the record to be deleted.
• The JDBC driver is loaded using Class.forName.
• A database connection is established using DriverManager.getConnection.
• A DELETE SQL query is prepared using a PreparedStatement.
• The email value is set using setString() to replace the placeholder and the executeUpdate() method delete query and returns the number of rows affected.
• Print a message of success if the data is deleted; otherwise, fail. Use try-with-resources to automatically close JDBC resources.