Java JDBC Delete SQL Query Example

In this example, we will learn how to delete a record from a MySQL database table using JDBC Statement interface.
Learn a complete JDBC tutorial at https://www.javaguides.net/p/jdbc-tutorial.html.

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

  1. Import JDBC Packages
  2. Establishing a connection.
  3. Create a statement.
  4. Execute the query.
  5. Using try-with-resources statements to automatically close JDBC resources

JDBC Statement Delete a Record Example

Here we have a users table in a database and we will delete a record with the following JDBC program.
Check out the below articles:
>> JDBC Statement - Update a Record Example
>> JDBC Statement - Insert Multiple Records Example
>> JDBC Statement Create a Table Example

Note that in the below example, we are using DELETE SQL query to delete the record: 

package com.javaguides.jdbc.statement.examples;

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

/**
 * Delete Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class DeleteStatementExample {

    private static final String DELETE_USERS_SQL = "delete from users where id = 3;";

    public static void main(String[] argv) throws SQLException {
        DeleteStatementExample deleteStatementExample = new DeleteStatementExample();
        deleteStatementExample.deleteRecord();
    }

    public void deleteRecord() throws SQLException {
        System.out.println(DELETE_USERS_SQL);

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            int result = statement.executeUpdate(DELETE_USERS_SQL);
            System.out.println("Number of records affected :: " + result);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
delete from users where id = 3;
Number of records affected :: 1

Key points

  1. From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
  2. We have used try-with-resources statements to automatically close JDBC resources.

References


Comments

Post a Comment