A Java MySQL INSERT example (using Statement)

Java MySQL FAQ: Can you share an example of a Java MySQL INSERT example using the Java Statement class?

Sure, with one caveat: I haven't used a Java Statement object with a Java SQL INSERT in many, many years. Every developer I know switched to using a Java PreparedStatement many years ago, as the syntax is much easier, and it also gets you away from many "SQL injection" security concerns that you'll have with the Java Statement class.

Given that caveat, let's look at a simple Java MySQL INSERT example using the Statement class.

An example MySQL database table

The first thing we're going to need is an example 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)
);

This table is a little unusual, but I wanted to show several different MySQL data types, and this is what I came up with.

Java MySQL INSERT using Statement - source code

Given that example MySQL database table design, let's assume that we just want to insert one record into 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 INSERT statement.
3. Execute a Java Statement, using our SQL INSERT statement.
4. Close our Java MySQL database connection.
5. Catch any SQL exceptions that may come up during the process.

I've tried to document the following Java MySQL INSERT 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 INSERT example.
 * Demonstrates the use of a SQL INSERT statement against a
 * MySQL database, called from a Java program, using a
 * Java Statement (not a PreparedStatement).
 * 
 * Created by Alvin Alexander, http://devdaily.com
 */
public class JavaMysqlInsertExample
{

  public static void main(String[] args)
  {
    try
    {
      // create a 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", "");
      
      Statement st = conn.createStatement();

      // note that i'm leaving "date_created" out of this insert statement
      st.executeUpdate("INSERT INTO users (first_name, last_name, is_admin, num_points) "
          +"VALUES ('Fred', 'Flinstone', false, 10000)");

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

  }
}

Assuming everything is set up properly on your computer system, this MYSQL INSERT example should succeed, and when you query your MySQL database table like this:

select * from users;

you should see some output like this:

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

Java MySQL INSERT example - summary

I hope this Java MySQL INSERT example using the Java Statement class makes sense as is. In "real world" Java database programs I almost always use Spring 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.