
- JDBC - Home
- JDBC - Introduction
- JDBC - SQL Syntax
- JDBC - Environment
- JDBC - Sample Code
- JDBC - Driver Types
- JDBC - Connections
- JDBC - Statements
- JDBC - Result Sets
- JDBC - Data Types
- JDBC - Transactions
- JDBC - Exceptions
- JDBC - Batch Processing
- JDBC - Stored Procedure
- JDBC - Streaming Data
- JDBC - RowSet
- JDBC - Copy Database
- JDBC - ACID Properties
- JDBC - Connection Pooling
- JDBC Examples
- JDBC - Create Database
- JDBC - Select Database
- JDBC - Drop Database
- JDBC - Create Tables
- JDBC - Drop Tables
- JDBC - Insert Records
- JDBC - Select Records
- JDBC - Update Records
- JDBC - Delete Records
- JDBC - WHERE Clause
- JDBC - Like Clause
- JDBC - Sorting Data
- JDBC Useful Resources
- JDBC - Questions and Answers
- JDBC - Quick Guide
- JDBC - Useful Resources
- JDBC - Discussion
- Useful - Java Tutorials
JDBC - Delete Records
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:\>