Java MySQL – DELETE Row(s)
This tutorial explains how to delete rows in a MySQL table using Java. We will go through different scenarios, including deleting a single row, deleting multiple rows based on a condition, and deleting all rows in a table. Each example includes detailed steps and Java code snippets.
Prerequisites
- MySQL installed and running
- Java Development Kit (JDK) installed
- MySQL Connector/J (JDBC Driver) downloaded
- MySQL Workbench to verify the row deletions
Step 1: Set Up MySQL Database and Table
Before running the Java code, create a sample table in MySQL. We’ll use a table named students with columns for ID, name, age, and class. Run the following SQL commands in MySQL Workbench:
CREATE DATABASE IF NOT EXISTS school;
USE school;
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
class VARCHAR(10)
);
-- Insert sample data
INSERT INTO students (name, age, class) VALUES ('Alice', 19, '10A');
INSERT INTO students (name, age, class) VALUES ('Bob', 20, '10B');
INSERT INTO students (name, age, class) VALUES ('Charlie', 21, '10A');
INSERT INTO students (name, age, class) VALUES ('Daisy', 22, '10B');This setup creates the school database and a students table with sample data to use in our examples.
Step 2: Set Up MySQL Connection in Java
To connect to MySQL from Java, you need the MySQL Connector/J library. Add this library to your Java project to establish the connection.
1. Download MySQL Connector/J
Go to the official MySQL Connector/J download page, download the connector, and add the JAR file to your Java project’s classpath.
Click on the Download button.
You may click No thanks, just start my download.
In the downloaded package, there should be a jar file.
2. Add the Connector to Your Java Project
For the sake of convenience, let us copy the jar file to libs folder in the Java Project.
Right-click on your Java project and go to Properties.
Select Java Build Path > Libraries.
Add the MySQL Connector/J JAR file you downloaded.
Click OK.
Click Apply and Close.
Step 3: Java Code to DELETE Rows
We will now explore different examples for deleting rows from the students table in MySQL.
Example 1: Delete a Single Row
This example demonstrates deleting a specific row based on the id of the student.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DeleteSingleRowExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/school";
String user = "root";
String password = "your_password";
try {
// Establish a connection to MySQL
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
// SQL command to delete a single row
String sql = "DELETE FROM students WHERE id = ?";
// Prepare the statement
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1); // ID of the student to delete (e.g., Alice with id 1)
// Execute the delete command
int rowsDeleted = preparedStatement.executeUpdate();
// Output the result
if (rowsDeleted > 0) {
System.out.println("A row was deleted successfully.");
}
// Close resources
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}This code deletes the row for the student with id = 1 (e.g., Alice).
After running the Java program, open MySQL Workbench and run the following query to verify the deletions:
SELECT * FROM students;
Example 2: Delete Multiple Rows Based on a Condition
In this example, we’ll delete all rows where the class is 10A.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DeleteConditionalRowsExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/school";
String user = "root";
String password = "your_password";
try {
// Establish a connection to MySQL
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
// SQL command to delete multiple rows
String sql = "DELETE FROM students WHERE class = ?";
// Prepare the statement
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "10A"); // Class condition
// Execute the delete command
int rowsDeleted = preparedStatement.executeUpdate();
// Output the result
System.out.println(rowsDeleted + " rows were deleted successfully.");
// Close resources
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}This code deletes all rows for students in class 10A.
After running the Java program, open MySQL Workbench and run the following query to verify the deletions:
SELECT * FROM students;
Example 3: Delete All Rows
This example demonstrates how to delete all rows from the students table. Note: This action cannot be undone, so use caution.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DeleteAllRowsExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/school";
String user = "root";
String password = "your_password";
try {
// Establish a connection to MySQL
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
// SQL command to delete all rows
String sql = "DELETE FROM students";
// Create a statement
Statement statement = connection.createStatement();
// Execute the delete command
int rowsDeleted = statement.executeUpdate(sql);
// Output the result
System.out.println("All rows deleted. Total rows affected: " + rowsDeleted);
// Close resources
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}This code deletes all rows from the students table, leaving it empty.
After running the Java program, open MySQL Workbench and run the following query to verify the deletions:
SELECT * FROM students;
Conclusion
This tutorial covered different ways to delete rows from a MySQL table using Java, including deleting a single row, deleting multiple rows based on a condition, and deleting all rows.