JDBC - Delete Records

Quiz

This chapter provides examples on how to delete records from a table using JDBC application. Before executing following example, make sure you have the following in place −

To execute the following example you can replace the username and password with your actual user name and password.
Your MySQL or whatever database you are using is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
Register the JDBC driver − Requires that you initialize a driver so you can open a communications channel with the database.
Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.
Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to delete records from a table. This Query makes use of the WHERE clause to delete conditional records.
Clean up the environment − try with resources automatically closes the resources.

Example: Deleting Record from a Table

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed an update query on table REGISTRATION by calling statement.executeUpdate() method where we've deleted a record whose registration id is 101. Then using executeQuery(), all records are fetched and stored in a ResultSet object. ResultSet is iterated and all records are printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

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

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {		      
         String sql = "DELETE FROM Registration " +
            "WHERE id = 101";
         stmt.executeUpdate(sql);
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>

Example: Deleting Records using Limit from a Table

We can delete limited records using LIMIT clause.

DELETE FROM employees ORDER BY age LIMIT 3

Number of rows deleted is set by the LIMIT clause. In the above SQL, 3 rows will be deleted.

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed a select query on table employees by calling statement.executeQuery() method where we've retrieved all the records and then using showResults() method, all records are printed. showResults() method iterates all records of resultset to print them.

Using executeUpdate(), the delete query with Limit clause is executed and then again using select query on employees table, all records are printed to show the result of deleting records.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

// This class demonstrates DELETE command with LIMIT
public class DeleteWithLimit {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         String sel_qry = "select * from employees ";
         String del_qry = "DELETE FROM employees ORDER BY age LIMIT 3 ";
         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery(sel_qry);
         System.out.println(" Displaying records before deletion ");
         System.out.println(" ----------------------------------" );
         showResults(rs);

         stmt.executeUpdate(del_qry);
         System.out.println("Displaying records after deletion..");
         System.out.println(" ----------------------------------" );
         rs = stmt.executeQuery(sel_qry);
         showResults(rs);
         rs.close();
         stmt.close();
         conn.close();
      }catch(SQLException e){
         e.printStackTrace();
      }   
   }

   public static void showResults(ResultSet res) {
      try{
         while(res.next()){
            System.out.print("ID: " + res.getInt(1));
            System.out.print(", AGE: " + res.getInt(2));
            System.out.print(", FirstName: " + res.getString(3));
            System.out.println(", LastName: " + res.getString(4));
         }
         System.out.println(" ----------------------------------" );
      }catch(SQLException sqle){
         sqle.printStackTrace();
      }
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Displaying records before deletion 
 ----------------------------------
ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali
ID: 2, AGE: 25, FirstName: Mahnaz, LastName: Fatma
ID: 3, AGE: 20, FirstName: Zaid, LastName: Khan
ID: 4, AGE: 28, FirstName: Sumit, LastName: Mittal
ID: 7, AGE: 20, FirstName: Rita, LastName: Tez
ID: 8, AGE: 20, FirstName: Sita, LastName: Singh
ID: 21, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 22, AGE: 40, FirstName: Aditya, LastName: Chaube
ID: 25, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 26, AGE: 35, FirstName: Aditya, LastName: Chaube
ID: 34, AGE: 45, FirstName: Ahmed, LastName: Ali
ID: 35, AGE: 51, FirstName: Raksha, LastName: Agarwal
 ----------------------------------
Displaying records after deletion..
 ----------------------------------
ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali
ID: 2, AGE: 25, FirstName: Mahnaz, LastName: Fatma
ID: 4, AGE: 28, FirstName: Sumit, LastName: Mittal
ID: 21, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 22, AGE: 40, FirstName: Aditya, LastName: Chaube
ID: 25, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 26, AGE: 35, FirstName: Aditya, LastName: Chaube
ID: 34, AGE: 45, FirstName: Ahmed, LastName: Ali
ID: 35, AGE: 51, FirstName: Raksha, LastName: Agarwal
 ----------------------------------

C:\>

Example: Deleting Records using JOIN

We will show how deletion from two tables will occur with a JOIN. Before delete, the tables STUDENTS and SAMPLEDB4 are as follows. Note some StudentID's (in STUDENTS table) are same as ID's in Sampledb4. The FirstName is also similar.

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed a delete query on table students and sampledb4 by calling statement.executeUpdate() method where we've deleted a student record whose id is present in sampledb4 table as well. Then using executeQuery(), all records are fetched and stored in a ResultSet object. ResultSet is iterated and the records are printed.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

public  class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";
   
   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         String del_qry = "delete sampledb4 from students INNER JOIN"+
         " sampledb4 where students.studentID = sampledb4.id";
         Statement stmt = conn.createStatement();
         stmt.executeUpdate(del_qry);

         System.out.println("Displaying records after deletion..");
         System.out.println(" ----------------------------------" );
         ResultSet rs = stmt.executeQuery("select * from sampledb4");

         while(rs.next()){
            System.out.print("ID: " + rs.getInt(1));
            System.out.println(", Name: " + rs.getString(2));
         }

         System.out.println("-----------------------------------");

         rs.close();
         stmt.close();
         conn.close();

      }catch(SQLException e){
         e.printStackTrace();
      }
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Displaying records after deletion..
 ----------------------------------
ID: 3, Name: Sachin
ID: 4, Name: Kishore
-----------------------------------

C:\>

Print Page