How to remove a record from an existing table in oracle database using JDBC API?

You can remove a particular record from a table in a database using the DELETE query.


DELETE FROM table_name
WHERE [condition];

To delete a record from a table using JDBC API you need to −

Register the Driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

Establish a connection: Connect to the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

Create Statement: Create a Statement object using the createStatement() method of the Connection interface.

Execute the Query: Execute the query using the executeUpdate() method of the Statement interface.

Let us create a table with name dispatches in Oracle database using CREATE statement as shown below −

CREATE TABLE Dispatches(

Now, we will insert 5 records in dispatches table using INSERT statements −

insert into dispatches values('Key-Board', 'Raja', TO_DATE('2019-09-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 7000, 'India');
insert into dispatches values('Earphones', 'Roja', TO_DATE('2019-05-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 2000, 'Vishakhapatnam');
insert into dispatches values('Mouse', 'Puja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:59:59', 'hh:mi:ss'), 3000, 'Vijayawada');
insert into dispatches values('Mobile', 'Vanaja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:10:52', 'hh:mi:ss'), 9000, 'Chennai');
insert into dispatches values('Headset', 'Jalaja', TO_DATE('2019-04-06', 'yyyy/mm/dd'), TO_DATE('11:08:59', 'hh:mi:ss' ), 6000, 'Goa');

Following JDBC program establishes connection with the Oracle database and deletes the record of the customer named Jalaja from the Dispatches table.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecordsOracle {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      //Getting the connection
      String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";
      Connection con = DriverManager.getConnection(oracleUrl, "system", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to delete records
      String query = "Delete from dispatches where CUSTOMERNAME = 'Jalaja'";
      int i = stmt.executeUpdate(query);
      System.out.println("Rows deleted: "+i);
      //Retrieving data
      ResultSet rs = stmt.executeQuery("Select * from dispatches");
      System.out.println("Contents of the table after deleting the records: ");
      while( {
         System.out.print("Name: "+rs.getString("ProductName")+", ");
         System.out.print("Customer Name: "+rs.getString("CustomerName")+", ");
         System.out.print("Dispatch Date: "+rs.getDate("DispatchDate")+", ");
         System.out.print("Delivery Time: "+rs.getTime("DeliveryTime")+", ");
         System.out.print("Price: "+rs.getInt("Price")+", ");
         System.out.print("Location: "+rs.getString("Location"));


Connection established......
Rows deleted: 1
Contents of the table after deleting the records:
Name: Key-Board, Customer Name: Raja, Dispatch Date: 2019-09-01, Delivery Time: 11:00:00, Price: 10001, Location: Hyderabad
Name: Earphones, Customer Name: Roja, Dispatch Date: 2019-05-01, Delivery Time: 11:00:00, Price: 5000, Location: Vishakhapatnam
Name: Mouse, Customer Name: Puja, Dispatch Date: 2019-03-01, Delivery Time: 10:59:59, Price: 6000, Location: Vijayawada
Name: Mobile, Customer Name: Vanaja, Dispatch Date: 2019-03-01, Delivery Time: 10:10:52, Price: 12001, Location: Chennai
Vikyath Ram
Vikyath Ram

A born rival