Home » Java programming language

How to delete a particular record using JDBC in Java?

In this article, we are going to learn how to delete a particular record from MYSQL table using JDBC through java program?
Submitted by Manu Jemini, on October 14, 2017

Prerequisite:

1. How to create a table using JDBC in Java?
2. How to insert records through JDBC in Java?
3. How to display all records using JDBC in Java?
4. How to display particular record by a field using JDBC in Java?

Note: To delete a particular record from MYSQL table, you should know at least one field of that record.

Now, we need to create an object of Connection class and connect to the database.

Then, we need to create an object of Statement class and then prepare a MySQL query for displaying data which we want to delete. After this, we display data in console and put up a question to delete above data from MYSQL database.

Then, we prepare a query to delete the data and execute it by using executeUpdate() method, which is a method of Statement class.

Database details:

• Hostname: localhost
• Port number: 3306
• Username: root
• Password: 123
• Database name: demo
• Table name: employees
• Field: empid (employee id)

Java program to delete a particular record using JDBC

import java.io.DataInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DeleteByID {
	public static void main(String[] args) {
	try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			//serverhost = localhost, port=3306, username=root, password=123
			Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","123");

			Statement smt=cn.createStatement();

			DataInputStream KB=new DataInputStream(System.in);

			//input a particular employee id of which we want to delete record
			System.out.print("Enter Employee ID:");
			String eid=KB.readLine();

			//query to take data of a particular record from table employee
			String q="Select * from employees where empid='"+eid+"'";

			//to execute query
			ResultSet rs=smt.executeQuery(q);

			if(rs.next())
			{
				//to show the data
				System.out.println("Employee id:"+rs.getString(1));
				System.out.println("Employee Name:"+rs.getString(2));
				System.out.println("Employee DOB:"+rs.getString(3));
				System.out.println("Employee City:"+rs.getString(4));
				System.out.println("Employee Salary:"+rs.getString(5));
				System.out.println("Sure To Delete Above Record Yes/No?");
				String ch=KB.readLine();
				if(ch.equalsIgnoreCase("yes"))
				{
					//query to delete data of a particular record from table employee
					q="delete from employees where empid='"+eid+"'";
					//to execute query
					smt.executeUpdate(q);
					System.out.println("Record Deleted...");
				}
			}
			else
			{
				System.out.println("Record Not Found...");
			}
			cn.close();
		}
		catch(Exception e){
			System.out.println(e);
		}
	}
}

Output (In console)

Enter Employee ID: 100
Employee id: 100
Employee Name: Aman
Employee DOB: 10/10/1990
Employee City: Delhi
Employee Salary: 35000
Sure To Delete Above Record Yes/No? Yes
Record Deleted...