Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to remove a record from an existing table in a database using JDBC API?
You can remove a particular record from a table in a database using the DELETE query.
Syntax
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 ot 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.
Example
Assume we have a table named customers in the database named mydatabase in MySQL, with 12 records as:
+----+-----------+------+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+------+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Mumbai | | 5 | Koushik | 30 | 9000.00 | Kota | | 6 | Hardik | 45 | 6400.00 | Bhopal | | 7 | Trupthi | 33 | 4360.00 | Ahmedabad | | 8 | Mithili | 26 | 4100.00 | Vijayawada | | 9 | Maneesh | 39 | 4000.00 | Hyderabad | | 10 | Rajaneesh | 30 | 6400.00 | Delhi | | 11 | Komal | 29 | 8000.00 | Ahmedabad | | 12 | Manyata | 25 | 5000.00 | Vijayawada | +----+-----------+------+---------+----------------+
Following JDBC program establishes a connection with MySQL database and deletes the records of the customers with salary value less than 5000, retrieves and displays the contents of the table after delete operation.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecordsExample {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/MyDatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Creating the Statement
Statement stmt = con.createStatement();
//Query to delete records
String query = "Delete from customers where salary < 5000";
int i = stmt.executeUpdate(query);
System.out.println("Rows deleted: "+i);
//Retrieving data
ResultSet rs = stmt.executeQuery("Select * from customers");
System.out.println("Contents of the table after deleting the records: ");
}
}Output
Connection established...... Rows deleted: 6 Contents of the table after deleting the records: ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada
268 Views
