A Java MySQL UPDATE example

Java MySQL FAQ: Can you share an example of a Java MySQL UPDATE example (using a Java PreparedStatement object)?

Sure. I just worked up a Java MySQL UPDATE example, using the Java PreparedStatement class, and a sample MySQL database table we can work with.

A simple MySQL database table

The first thing we need for our Java UPDATE example is a sample MySQL database table. To keep it simple — but also show several different data types — I've 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)
);

-- insert some sample records
insert into users (first_name, last_name) values ('Fred', 'Flinstone');
insert into users (first_name, last_name) values ('Barney', 'Rubble');

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, but 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.

A Java MySQL UPDATE PreparedStatement example

Given that MySQL database table design, let's assume that we just want to update 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 UPDATE statement, using the Java PreparedStatement syntax.
3. Set the fields on our Java PreparedStatement object.
4. Execute a Java PreparedStatement.
5. Close our Java database connection.
6. Catch any exceptions that may come up during the process.

I've tried to document the following Java MySQL UPDATE 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.*;

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

  public static void main(String[] args)
  {
    try
    {
      // create a java 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 java mysql update preparedstatement
      String query = "update users set num_points = ? where first_name = ?";
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt   (1, 6000);
      preparedStmt.setString(2, "Fred");

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

Note that this SQL UPDATE query is a little unusual, but not totally uncommon. Typically in a database program like this you'll end up updating rows based on the primary key of the database table. Specifically, in this example, you'd probably already know the "id" for the user Fred, and when you go to update Fred's data, you would normally do it like this:

update users set num_points = 6000 where id = 2;

but because this is a sample program, I decided to show the query this way.

Java MySQL UPDATE example - results

After this Java MySQL UPDATE 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 UPDATE example using PreparedStatement - summary

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, or working on small programs, 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 MySQL UPDATE query for use with a PreparedStatement.
3. How to set the field values for a PreparedStatement.
4. How to execute the Java PreparedStatement.
5. How to close the MySQL database connection.
6. One way to confirm that our data was successfully updated in our MySQL database.

I hope this Java MySQL UPDATE 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.