A Java MySQL DELETE example

Here’s a Java MySQL DELETE example, demonstrating how to issue a SQL DELETE command from your Java source code.

A simple MySQL database table

The first thing we’ll need is an example MySQL database table to work with. To keep it simple — but also show several different data types — I created the following MySQL database table:

create table users (
  id int unsigned auto_increment not null,
  first_name varchar(32) not null,
  last_name varchar(32) not null,
  date_created timestamp default now(),
  is_admin boolean,
  num_points int,
  primary key (id)
);

A few of these fields are a little contrived, but I wanted to show several different data types in one table, so this is what I came up with. (In particular, the field "num_points" is a little unusual. I made it up so I could show an int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.) 

Other than that, this MySQL database table is relatively normal, though it is greatly simplified.

An example MySQL SELECT statement

Before looking at the Java source code, if I now execute this SQL query from the MySQL command prompt:

select * from users;

I currently see this output:

+----+------------+-----------+---------------------+----------+------------+
| id | first_name | last_name | date_created        | is_admin | num_points |
+----+------------+-----------+---------------------+----------+------------+
|  2 | Fred       | Flinstone | 2010-06-23 00:00:00 |        0 |       6000 | 
|  3 | Barney     | Rubble    | 2010-06-23 00:00:00 |        0 |       5000 | 
+----+------------+-----------+---------------------+----------+------------+
2 rows in set (0.00 sec)

This is important to see, because I'm about to delete that "Barney Rubble" data record.

Java MySQL DELETE example - source code

Given that MySQL database table, let's assume that we just want to delete one record in this table. To do so, we just need to follow these steps:

1. Create a Java Connection to our MySQL database.
2. Create a SQL DELETE query statement.
3. Create a Java PreparedStatement for our SQL DELETE query.
4. Set the fields on our Java PreparedStatement object.
5. Execute our Java PreparedStatement.
6. Close our Java MySQL database connection.
7. Catch any SQL exceptions that may come up during the process.

I've tried to document the following Java MySQL DELETE example so you can see these steps. Note that in this example my MySQL database username is "root", my password is blank, and the MySQL database is running on the same computer where this program is run, so the database host name is "localhost".

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * A Java MySQL DELETE example.
 * Demonstrates the use of a SQL DELETE statement against a
 * MySQL database, called from a Java program, using a
 * Java PreparedStatement.
 * 
 * Created by Alvin Alexander, http://devdaily.com
 */
public class JavaMysqlDeleteExample
{

  public static void main(String[] args)
  {
    try
    {
      // create the mysql database connection
      String myDriver = "org.gjt.mm.mysql.Driver";
      String myUrl = "jdbc:mysql://localhost/test";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "root", "");
      
      // create the mysql delete statement.
      // i'm deleting the row where the id is "3", which corresponds to my
      // "Barney Rubble" record.
      String query = "delete from users where id = ?";
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 3);

      // execute the preparedstatement
      preparedStmt.execute();
      
      conn.close();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception! ");
      System.err.println(e.getMessage());
    }

  }
}

Java MySQL DELETE example - discussion

As noted in the Java MySQL DELETE source code, this SQL DELETE query is hard-wired to delete the row in the database where the "id" column has a value of "3". I know from looking at my database that this is the record corresponding to the "Barney Rubble" data. In a real-world program, you'll likely have the id for the user record you want to delete, so your SQL DELETE query will look very similar to the one shown here, except you'll have a variable in the place where I have hard-coded the number three.

After this query runs, you can verify that it worked by looking at the data from the MySQL command prompt, running a SELECT query like this:

select * from users;

where you will see some output like this:

+----+------------+-----------+---------------------+----------+------------+
| id | first_name | last_name | date_created        | is_admin | num_points |
+----+------------+-----------+---------------------+----------+------------+
|  2 | Fred       | Flinstone | 2010-06-23 14:02:00 |        0 |       6000 | 
+----+------------+-----------+---------------------+----------+------------+
1 row in set (0.00 sec)

Java MySQL DELETE example - summary

These days, in "real world" Java database programs I almost always use the Spring JDBC libraries to access a database, but when you're first getting started, I think it's important to see examples like this so you can understand how things work under the covers.

In summary, this example demonstrated:

1. How to connect to a MySQL database.
2. How to write a Java MySQL DELETE query (for use with a Java PreparedStatement).
3. How to set the desired field values for a Java PreparedStatement.
4. How to execute the Java PreparedStatement.
5. How to close the Java MySQL database connection.
6. One way to confirm that our data was successfully deleted in our MySQL database.

I hope this Java MySQL DELETE example (using a Java PreparedStatement) makes sense. As usual, if you have any questions or comments about this example, just use the Comment form below.