JDBC MySQL Tutorial

A simple tutorial showing the basics of using JDBC database API.

“A man’s true character comes out when he’s drunk.”
― Charlie Chaplin

1. Introduction

JDBC stands for Java DataBase Connectivity and is the standard API (application programming interface) java provides for working with databases. JDBC, as a standard, is built into the java platform. To connect with and use a database, you need an additional component called a jdbc driver, which is specific to the type of database you are working with, such as MySQL, SQL Server, etc. In other words, the JDBC API consists of a small number of classes and a bunch of interfaces, the implementation provided by the actual database vendor. This is illustrated by the figure below.

In this article, we learn how to connect to a MySQL database from java and use JDBC for some operations.

2. Setting up the MySQL Database

Let us setup a test database for use with our java class. We assume you have root access to a MySQL database on the local machine.

Startup the MySQL Client.

$ mysql -p -u root

Once you have logged into MySQL as root, run the following commands to create a database called testing, with a username called joe and a password of secret.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

mysql> create database testing character set 'utf8';
Query OK, 1 row affected (0.00 sec)

mysql> create user 'joe'@'localhost' identified by 'secret';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on testing.* to 'joe'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Verify that you can now connect to the database and create a table called users.

$ mysql -p -u joe testing
mysql> create table user (id integer primary key auto_increment,loginname varchar(30) not null,firstname varchar(30) not null,lastname varchar(50) not null);
Query OK, 0 rows affected (0.18 sec)

mysql> show tables;
+-------------------+
| Tables_in_testing |
+-------------------+
| user              |
+-------------------+
1 row in set (0.00 sec)

Insert a couple of rows into the table so we have some data to work with.

mysql> insert into user(loginname, firstname, lastname) values('joe', 'Joe', 'Schmoe');
Query OK, 1 row affected (0.05 sec)

mysql> insert into user(loginname, firstname, lastname) values('donald', 'Donald', 'Trump');
Query OK, 1 row affected (0.04 sec)

3. Loading the JDBC Driver

To be able to connect to and use a database, the appropriate JDBC driver must be loaded. This is done dynamically (at run-time) using the following code. (The name of the MySQL driver class is called com.mysql.jdbc.Driver.)

try {
    Class.forName("com.mysql.jdbc.Driver");
} catch(ClassNotFoundException ex) {
    System.err.println("Unable to load MySQL Driver");
}

To load the driver when the program starts, the above code can be installed in the class static block as follows:

static {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch(ClassNotFoundException ex) {
        System.err.println("Unable to load MySQL Driver");
    }
}

4. Connecting to MySQL

A database connection is represented in java with the interface Connection. When a connection is established to the database using DriverManager.getConnection(), the Connection object is returned.

String jdbcUrl = "jdbc:mysql://localhost/testing?user=joe&password=secret";
try (Connection con = DriverManager.getConnection(jdbcUrl)) {
    System.out.println("Connected: " + con);
}

You need to specify the JDBC URL to connect to a database. As shown, for MySQL, the URL includes the database name, username and password. If necessary, you can omit the username and password from the URL and connect using the following method for specifying the username and the password.

Connection con = DriverManager.getConnection(jdbcUrl, username, password);

Note that we open the connection in a try-with-resources block to ensure that the connection is closed when the block is exited. Connection objects are expensive to create and hold onto, so you should plan your code so you hold a connection open for the least possible time.

5. Performing a Query

Let us now lookup the rows in the user table that we created earlier. In the following code, a Statement object is created from the Connection and a query is executed. The result is returned in the form of a ResultSet object which can be used to extract the data as shown.

Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("select loginname, firstname, lastname from user");
int nrow = 0;
while (rset.next()) {
    nrow++;
    String loginname = rset.getString(1);
    String firstname = rset.getString(2);
    String lastname = rset.getString(3);
    System.out.println("Row " + nrow + ": " +
                       loginname + ", " + firstname + ", " + lastname);
}
# prints
Row 1: joe, Joe, Schmoe
Row 2: donald, Donald, Trump

6. Inserting a Row

Now that we know how to query the database, let us learn how to insert a row into the database. For this purpose, we use a PreparedStatement and demonstrate the use of placeholders (the ? characters) in SQL. Using placeholders for user specified input is safer than building an SQL statement with embedded user data in it. It helps prevent vulnerability to a hack called called SQL Injection, so you should use PreparedStatement whenever passing in user input to a database.

PreparedStatement stmt = con.prepareStatement("insert into user(loginname,\n" +
                                              "                 firstname,\n" +
                                              "                 lastname)\n" +
                                              "values(?, ?, ?)");
stmt.setString(1, "jack");
stmt.setString(2, "Jack");
stmt.setString(3, "Reacher");
int nrows = stmt.executeUpdate();
System.out.println("Inserted " + nrows + " row(s)");
# prints
Inserted 1 row(s)

The data in the database shows that the row has been added.

+----+-----------+-----------+----------+
| id | loginname | firstname | lastname |
+----+-----------+-----------+----------+
|  1 | joe       | Joe       | Schmoe   |
|  2 | donald    | Donald    | Trump    |
|  3 | jack      | Jack      | Reacher  |
+----+-----------+-----------+----------+

7. Updating a Row

The code for updating a row is similar but with different SQL. The executeUpdate() method returns the number of rows affected by the update, so for this case it should be 1.

PreparedStatement stmt = con.prepareStatement("update user\n" +
                                              "set lastname = ?\n" +
                                              "where loginname = ?");
stmt.setString(1, "Black");
stmt.setString(2, "jack");
int nrows = stmt.executeUpdate();
System.out.println("Updated " + nrows + " row(s)");
# prints
Updated 1 row(s)

On running the program, we see that the row has been updated.

+----+-----------+-----------+----------+
| id | loginname | firstname | lastname |
+----+-----------+-----------+----------+
|  1 | joe       | Joe       | Schmoe   |
|  2 | donald    | Donald    | Trump    |
|  3 | jack      | Jack      | Black    |
+----+-----------+-----------+----------+

8. Deleting Rows

Deleting rows is performed using the same method: PreparedStatement.executeUpdate(). The method returns the number of rows deleted.

PreparedStatement stmt = con.prepareStatement("delete from user where loginname = ?");
stmt.setString(1, "jack");
int nrows = stmt.executeUpdate();
System.out.println("Deleted " + nrows + " row(s)");

And verifying the update in the database:

+----+-----------+-----------+----------+
| id | loginname | firstname | lastname |
+----+-----------+-----------+----------+
|  1 | joe       | Joe       | Schmoe   |
|  2 | donald    | Donald    | Trump    |
+----+-----------+-----------+----------+

Conclusion

We learned the basics of using JDBC in this tutorial. We started with setting up a MySQL database, to querying and processing the ResultSet objects, and inserting, updating and deleting rows.