Deleting a record from database is an essential operation among the JDBC operations. In this section we are discussing how to delete from database with suitable example.
Delete from database example
Let us start our example Java code from scratch.We are using MySQL server 5.6 . The developer version is available here. If we need to view the database contents , either we can use the default command line client of MySQL or we can use any object browsers like SQLyog.The community version of SQLYog is available here for download.
Before starting to write the Java code, we should create database and database table .
Run the following queries either from command line client or from the object browser.
1)Create database.
CREATE DATABASE sampledb;
2)Create database table.
USE sampledb;
CREATE TABLE sampletable (number INT PRIMARY KEY,DATA VARCHAR(100));
3)Insert few records into the sampletable.
INSERT INTO sampletable VALUES(‘1′,’CAT’);
INSERT INTO sampletable VALUES(‘2′,’DOG’);
INSERT INTO sampletable VALUES(‘3′,’COW’);
Now it is the time to start our Java code. For compiling and running the code, we should have MySQL connector jar in our class path.It is available for download here.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBDeleteSample {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public void getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sampledb", "admin",
"admin@123");
statement = connection.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void readDB() {
try {
String query = "select * from sampletable";
resultset = statement.executeQuery(query);
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doDeleteRecord() {
try {
String query = "Delete from sampletable where number='3'";
int status = statement.executeUpdate(query);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeConnection() {
try {
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
DBDeleteSample sample = new DBDeleteSample();
sample.getConnection();
System.out.println("Existing records:");
sample.readDB();
System.out.println("Deleting record..");
sample.doDeleteRecord();
System.out.println("Specified record deleted..");
System.out.println("Records available in table:");
sample.readDB();
}
}
Output
Existing records:
Number = 1 ; Data = CAT
Number = 2 ; Data = DOG
Number = 3 ; Data = COW
Deleting record..
Specified record deleted..
Records available in table:
Number = 1 ; Data = CAT
Number = 2 ; Data = DOG
See Related topics