How to delete all records from a table in Oracle using JDBC API?

The SQL TRUNCATE statement is used to delete all the records from a table.

Syntax

TRUNCATE TABLE table_name;

To delete all the records from a table from 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 execute() method of the Statement interface.

Following JDBC program establishes connection with oracle database and deletes all the records in the table named dispatches −

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeletingAllRows_Oracle {
   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 all records in a table
      String query = "Truncate table DISPATCHES";
      //Executing the query
      stmt.execute(query);
      System.out.println("Table truncated....");
   }
}

Output

Connection established......
Table truncated....

After deleting, if you verify the contents of the Dispatches table using select statement, you will get the output as −

SQL> select * from dispatches;
no rows selected