JDBC-Delete from database example

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

JDBC overview

JDBC Drivers

Reading from database example

Inserting record into database example

Updating record example

JDBC PreparedStatement example

Leave a Reply

Your email address will not be published. Required fields are marked *