Java Database (JDBC) Programming

by Examples with MySQL

1.  Relational Database and Structure Query Language
(SQL)
I presume that you have some knowledge on Relational Databases and the SQL language. Otherwise,
read "Introduction to Relational Database and SQL".

2.  Setting-up MySQL
Assume that the MySQL server is running on the default TCP port number 3306.

On MySQL, instead of using the "mysql" command-line client program provided, you can write your
own client programs (in Java or other languages) to access the MySQL server. Your client programs
shall connect to the database server at the given IP address and TCP port number, issue the SQL
commands, and process the results received.

2.1  Install JDK and Programming Editor

Before you proceed, I shall assume that you are familiar with Java Programming and have installed
the followings:
1.JDK:_How to install JDK and Get Started
2.A programming text editor, such as Sublime Text, Atom (which runs on Windows, macOS and
Ubuntu).

2.2  Create a Directory to Keep all your Works

Assume that you have created a directory called " c:\myWebProject" (for Windows) or
"~/myWebProject" (for macOS) in your earlier exercises. Do it otherwise.

2.3  Install MySQL JDBC Driver (IMPORTANT - Don't MISS This

Step!)
You need to install an appropriate JDBC (Java Database Connectivity) driver to run your Java
database programs. The MySQL's JDBC driver is called "MySQL Connector/J" and is available at
MySQL mother site.
For Windows
1. Download the "latest" MySQL JDBC driver from http://dev.mysql.com/downloads ⇒
"Connector/J" ⇒ Connector/J 8.0.{xx}, where {xx} is the latest update number ⇒ In
"Select Operating System", choose "Platform Independent" ⇒ ZIP Archive (e.g.,
"mysql-connector-java-8.0.{xx}.zip" ⇒ "No thanks, just start my download".
2. UNZIP (right-click and extract all) the download file into your project directory " C:\
myWebProject".

Biruk A.
 3. Open the unzipped folder. Look for the JAR file " mysql-connector-java-8.0.
{xx}.jar".
The Absolute Full-path Filename for this JAR file is "C:\myWebProject\mysql-
connector-java-8.0.{xx}\mysql-connector-java-8.0.{xx}.jar". Take note of
this super-long filename that you will need later. COPY and SAVE in a scratch pad so
that you can retrieve later.
For macOS
1. Download the latest MySQL JDBC driver from http://dev.mysql.com/downloads ⇒
MySQL Connectors ⇒ Connector/J ⇒ Connector/J 8.0.{xx}, where {xx} is the latest
update number ⇒ In "Select Operating System", choose "Platform Independent" ⇒
Compressed TAR Archive (e.g., mysql-connector-java-8.0.{xx}.tar.gz.
2. Double-click on the downloaded TAR file to expand into folder "mysql-connector-
java-8.0.{xx}".
3. Move the expanded folder into your project directory " ~/myWebProject".
4. Open the expanded folder. Look for the JAR file " mysql-connector-java-8.0.
{xx}.jar".
The Absolute Full-path Filename for this JAR file is "~/myWebProject/mysql-
connector-java-8.0.{xx}/mysql-connector-java-8.0.{xx}.jar". Take note of
this super-long filename that you will need later. COPY and SAVE in a scratch pad so
that you can retrieve later.
(For Advanced User Only)
You can compile Java database programs without the JDBC driver. But to run the JDBC programs, the
JDBC driver's JAR-file must be included in the environment variable CLASSPATH, or in the java's
command-line option -cp.

You can set the -cp option for Java runtime as follows:

// For windows

java -cp .;/path/to/mysql-connector-java-8.0.{xx}.jar JDBCClassToRun

// For macOS / Unixes

java -cp .:/path/to/mysql-connector-java-8.0.{xx}.jar JDBCClassToRun

Prior to JDK 9, you can copy the JAR file into JDK's extension directory at " <JAVA_HOME>\jre\lib\
ext" (Windows) or "/Library/Java/Extensions" (macOS). JDK 9 removes this extension mechanism.

2.4  Setup Database
We have to set up a database before embarking on our database programming. We shall call our
database "ebookshop" which contains a table called "books", with 5 columns, as below:

Database: ebookshop

Table: books

Biruk A.
+-------+----------------------------+---------------+---------+-------+

| id | title | author | price | qty |

| (INT) | (VARCHAR(50)) | (VARCHAR(50)) | (FLOAT) | (INT) |

+-------+----------------------------+---------------+---------+-------+

| 1001 | Java for dummies | Tan Ah Teck | 11.11 | 11 |

| 1002 | More Java for dummies | Tan Ah Teck | 22.22 | 22 |

| 1003 | More Java for more dummies | Mohammad Ali | 33.33 | 33 |

| 1004 | A Cup of Java | Kumar | 44.44 | 44 |

| 1005 | A Teaspoon of Java | Kevin Jones | 55.55 | 55 |

+-------+----------------------------+---------------+---------+-------+

Start MySQL Server : Start the MySQL server and verify the server's TCP port number from the
console messages. I shall assume that MySQL server is running on the default port number of 3306.

// For Windows: assume that MySQL is installed in "c:\myWebProject\mysql"

c:

cd \myWebProject\mysql\bin

mysqld --console

// For macOS

// Use graphical control at "System Preferences" -> MySQL -> Start|Stop

Start a MySQL client : I shall also assume that there is an authorized user called " myuser" with
password "xxxx".

// For Windows: assume that MySQL is installed in "c:\myWebProject\mysql"

c:

cd \myWebProject\mysql\bin

mysql -u myuser -p

// For macOS: assume that MySQL is installed in "/usr/local/mysql"

cd /usr/local/mysql/bin

./mysql -u myuser -p

Biruk A.
Run the following SQL statements to create our test database and table.

create database if not exists ebookshop;

use ebookshop;

drop table if exists books;

create table books (

id int,

title varchar(50),

author varchar(50),

price float,

qty int,

primary key (id));

insert into books values (1001, 'Java for dummies', 'Tan Ah Teck', 11.11, 11);

insert into books values (1002, 'More Java for dummies', 'Tan Ah Teck', 22.22, 22);

insert into books values (1003, 'More Java for more dummies', 'Mohammad Ali', 33.33,
33);

insert into books values (1004, 'A Cup of Java', 'Kumar', 44.44, 44);

insert into books values (1005, 'A Teaspoon of Java', 'Kevin Jones', 55.55, 55);

select * from books;

3.  Introduction to JDBC Programming by Examples

A JDBC program comprises the following steps:
1.Allocate a Connection object, for connecting to the database server.
2.Allocate a Statement object, under the Connection created earlier, for holding a SQL
command.
3.Write a SQL query and execute the query, via the Statement and Connection created.
4.Process the query result.
5.Close the Statement and Connection to free up the resources.

Biruk A.
We shall illustrate Java Database programming by the following examples.

3.1  Example 1: SQL SELECT

Try out the following JDBC program, which issues an SQL SELECT to MySQL.

Save the program as "JdbcSelectTest.java" in your project directory "c:\myWebProject"

(Windows) or "~/myWebProject" (macOS).

1import java.sql.*; // Using 'Connection', 'Statement' and 'ResultSet' classes in

2java.sql package
3
4public class JdbcSelectTest { // Save as "JdbcSelectTest.java" (JDK 7 and above)
5 public static void main(String[] args) {
6 try (
7 // Step 1: Allocate a database 'Connection' object
8 Connection conn = DriverManager.getConnection(
9 "jdbc:mysql://localhost:3306/ebookshop?
1allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC",
0 "myuser", "xxxx"); // For MySQL only
1 // The format is: "jdbc:mysql://hostname:port/databaseName",
1"username", "password"
1
2 // Step 2: Allocate a 'Statement' object in the Connection
1 Statement stmt = conn.createStatement();
3 ) {
1 // Step 3: Execute a SQL SELECT query. The query result is returned in a
4'ResultSet' object.
1 String strSelect = "select title, price, qty from books";
5 System.out.println("The SQL statement is: " + strSelect + "\n"); // Echo
1For debugging
6
1 ResultSet rset = stmt.executeQuery(strSelect);
7
1 // Step 4: Process the ResultSet by scrolling the cursor forward via
8next().
1 // For each row, retrieve the contents of the cells with
9getXxx(columnName).
2 System.out.println("The records selected are:");
0 int rowCount = 0;
2 while(rset.next()) { // Move the cursor to the next row, return false if
1no more row
2

Biruk A.
2
2
3
2
4
2
5
2
6
2
String title = rset.getString("title");
7
double price = rset.getDouble("price");
2
int qty = rset.getInt("qty");
8
System.out.println(title + ", " + price + ", " + qty);
2
++rowCount;
9
}
3
System.out.println("Total number of records = " + rowCount);
0
3
} catch(SQLException ex) {
1
ex.printStackTrace();
3
} // Step 5: Close conn and stmt - Done automatically by try-with-resources
2
(JDK 7)
3
}
3
}
3
4
3
5
3
6
3
7
3
8

Compile : To compile the program:

// Windows: The Java source directory is "c:\myWebProject"

c:

cd \myWebProject

javac JdbcSelectTest.java

Biruk A.
// macOS: The Java source directory is "~/myWebProject"

cd ~/myWebProject

javac JdbcSelectTest.java

Run : It is rather difficult to run the program, as you need to include the MySQL JDBC Driver in
the classpath (via -cp option) as follows:

// For windows

c:

cd \myWebProject

java -cp .;C:\myWebProject\mysql-connector-java-8.0.{xx}\mysql-connector-java-8.0.

{xx}.jar JdbcSelectTest

// For macOS

cd ~/myWebProject

// Need to use $HOME instead of ~ in the "java" command

java -cp .:$HOME/myWebProject/mysql-connector-java-8.0.{xx}/mysql-connector-java-8.0.

{xx}.jar JdbcSelectTest

You should COPY and SAVE this command to a scratch pad, so that you don't need to type this
super-long command again and again.

The -cp includes two paths separated by a " ;" (Windows) or ":" (macOS). The "." denotes the
current directory (to locate the JdbcSelectTest) followed by the full-path filename of the MySQL
JDBC Driver JAR-file (that I asked you to take note earlier).

(Skip Unless...) Read "Common Errors in JDBC Programming on MySQL".

Dissecting the Program
1.The JDBC operations are carried out through the " Connection", "Statement" and
"ResultSet" objects (defined in package java.sql). However, you need not know the
details, but merely the public methods defined in the API (Application Program Interface).
You also need not re-invent the wheels by creating these classes yourself (which will take
you many years?!). "Re-using" software component is a main strength of OOP.
2.Notice that there is little programming involved in using JDBC programming. You only have
to specify the database-URL, write the SQL query, and process the query result. The rest of
the codes are kind of "standard JDBC program template". Again, this is because the wheels
have been invented.

Biruk A.
 3.In Line 7, we allocate a Connection object (called conn) via
DriverManager.getConnection(database-url, db-user, password). The Java program
uses a so-called database-URL to connect to the server:
o For MySQL:

o // Syntax

o Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:

{port}/{db-name}", "{db-user}", "{password}");

o // Example

Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/ebookshop",
"myuser", "xxxx");

The database-url is in the form of "jdbc:mysql://{host}:{port}/{database-

name}", with protocol jdbc and sub-protocol mysql. The port specifies the MySQL
server's TCP port number; db-user/password is an authorized MySQL user. In our
example, "localhost" (with special IP address of 127.0.0.1) is the hostname for local
loop-back; "3306" is the server's TCP port number, and ebookshop is the database
name.
o Others: Read "Other Databases".
4.In Line 13, we allocate a Statement object (called stmt) in the Connection via
conn.createStatement().
5.To execute a SQL SELECT command, we invoke method stmt.executeQuery("SELECT ...").
It returns the query result in a ResultSet object (called rset).
6.The ResultSet models the returned table, which can be access via a row cursor. The cursor
initially positions before the first row in the ResultSet. rset.next() moves the cursor to
the first row. You can then use rset.getXxx(columnName) to retrieve the value of the
column for that row, where Xxx corresponds to the type of the column, such as int, float,
double and String. The rset.next() returns false at the last row, which terminates the
while-loop.
7.You could use rset.getString(columnName) to retrieve all types (int, double, etc).
8.For maximum portability, ResultSet columns within each row should be read in left-to-right
order, and each column should be read only once via the getXxx() methods. Issue
getXxx() to a cell more than once may trigger a strange error.
9.In this example, we use JDK 7's new feature called try-with-resources, which automatically
closes all the opened resources in the try-clause, in our case, the Connection and
Statement objects.

Exercises: Modify your Java program to issue the following SELECT statements and display all the
columns retrieved. Make sure you modify the ResultSet processing to process only the columns
retrieved (otherwise, you will get a "Column not found" error).

Biruk A.
 1. SELECT * FROM books
2. SELECT title, author, price, qty FROM books WHERE author = 'Tan Ah Teck' OR
price >= 30 ORDER BY price DESC, id ASC

3.2  Example 2: SQL UPDATE

To execute a SQL UPDATE, you have to invoke the method executeUpdate() of the Statement object,
which returns an int indicating the number of records affected. Recall that for SELECT, we use
executeQuery(), which returns a ResultSet object modeling the returned table. SQL UPDATE|
INSERT|DELETE does not return a table, but an int indicating the number of records affected.

Save the program as "JdbcUpdateTest.java" in your project directory "c:\myWebProject"

(Windows) or "~/myWebProject" (macOS).

1import java.sql.*; // Use classes in java.sql package

2
3public class JdbcUpdateTest { // Save as "JdbcUpdateTest.java"
4 public static void main(String[] args) {
5 try (
6 // Step 1: Allocate a database 'Connection' object
7 Connection conn = DriverManager.getConnection(
8 "jdbc:mysql://localhost:3306/ebookshop?
9allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC",
1 "myuser", "xxxx"); // for MySQL only
0
1 // Step 2: Allocate a 'Statement' object in the Connection
1 Statement stmt = conn.createStatement();
1 ) {
2 // Step 3 & 4: Execute a SQL UPDATE via executeUpdate()
1 // which returns an int indicating the number of rows affected.
3 // Increase the price by 7% and qty by 1 for id=1001
1 String strUpdate = "update books set price = price*0.7, qty = qty+1 where
4id = 1001";
1 System.out.println("The SQL statement is: " + strUpdate + "\n"); // Echo
5for debugging
1 int countUpdated = stmt.executeUpdate(strUpdate);
6 System.out.println(countUpdated + " records affected.\n");
1
7 // Step 3 & 4: Issue a SELECT to check the UPDATE.
1 String strSelect = "select * from books where id = 1001";
8 System.out.println("The SQL statement is: " + strSelect + "\n"); // Echo
1for debugging
9 ResultSet rset = stmt.executeQuery(strSelect);

Biruk A.
2
0
2
1
2
2
2
3
2
4
2
5
2 while(rset.next()) { // Move the cursor to the next row
6 System.out.println(rset.getInt("id") + ", "
2 + rset.getString("author") + ", "
7 + rset.getString("title") + ", "
2 + rset.getDouble("price") + ", "
8 + rset.getInt("qty"));
2 }
9 } catch(SQLException ex) {
3 ex.printStackTrace();
0 } // Step 5: Close conn and stmt - Done automatically by try-with-resources
3 }
1}
3
2
3
3
3
4
3
5
3
6
3
7

See the previous example on how to compile and run this program! Point to this Java file
"JdbcUpdateTest"!

Exercises: Modify your Java program to issue the following SQL statements:

Biruk A.
 1.Increase the price by 50% for "A Cup of Java".
2.Set the qty to 0 for "A Teaspoon of Java".

3.3  Example 3: SQL INSERT and DELETE

Similarly, use the executeUpdate() to execute 'INSERT INTO' and 'DELETE FROM'. The method
returns an int indicating the number of records affected.

Save the program as "JdbcInsertTest.java" in your project directory "c:\myWebProject"

(Windows) or "~/myWebProject" (macOS).

1import java.sql.*; // Use classes in java.sql package

2
3public class JdbcInsertTest { // Save as "JdbcUpdateTest.java"
4 public static void main(String[] args) {
5 try (
6 // Step 1: Allocate a database 'Connection' object
7 Connection conn = DriverManager.getConnection(
8 "jdbc:mysql://localhost:3306/ebookshop?
9allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC",
1 "myuser", "xxxx"); // for MySQL only
0
1 // Step 2: Allocate a 'Statement' object in the Connection
1 Statement stmt = conn.createStatement();
1 ) {
2 // Step 3 & 4: Execute a SQL INSERT|DELETE statement via executeUpdate(),
1 // which returns an int indicating the number of rows affected.
3
1 // DELETE records with id>=3000 and id<4000
4 String sqlDelete = "delete from books where id >= 3000 and id < 4000";
1 System.out.println("The SQL statement is: " + sqlDelete + "\n"); // Echo
5for debugging
1 int countDeleted = stmt.executeUpdate(sqlDelete);
6 System.out.println(countDeleted + " records deleted.\n");
1
7 // INSERT a record
1 String sqlInsert = "insert into books values (3001, 'Gone Fishing',
8'Kumar', 11.11, 11)";
1 System.out.println("The SQL statement is: " + sqlInsert + "\n"); // Echo
9for debugging
2 int countInserted = stmt.executeUpdate(sqlInsert);
0 System.out.println(countInserted + " records inserted.\n");
2

Biruk A.
1
2 // INSERT multiple records
2 sqlInsert = "insert into books values "
2 + "(3002, 'Gone Fishing 2', 'Kumar', 22.22, 22),"
3 + "(3003, 'Gone Fishing 3', 'Kumar', 33.33, 33)";
2 System.out.println("The SQL statement is: " + sqlInsert + "\n"); // Echo
4for debugging
2 countInserted = stmt.executeUpdate(sqlInsert);
5 System.out.println(countInserted + " records inserted.\n");
2
6 // INSERT a partial record
2 sqlInsert = "insert into books (id, title, author) values (3004, 'Fishing
7101', 'Kumar')";
2 System.out.println("The SQL statement is: " + sqlInsert + "\n"); // Echo
8for debugging
2 countInserted = stmt.executeUpdate(sqlInsert);
9 System.out.println(countInserted + " records inserted.\n");
3
0 // Issue a SELECT to check the changes
3 String strSelect = "select * from books";
1 System.out.println("The SQL statement is: " + strSelect + "\n"); // Echo
3For debugging
2 ResultSet rset = stmt.executeQuery(strSelect);
3 while(rset.next()) { // Move the cursor to the next row
3 System.out.println(rset.getInt("id") + ", "
3 + rset.getString("author") + ", "
4 + rset.getString("title") + ", "
3 + rset.getDouble("price") + ", "
5 + rset.getInt("qty"));
3 }
6 } catch(SQLException ex) {
3 ex.printStackTrace();
7 } // Step 5: Close conn and stmt - Done automatically by try-with-resources
3 }
8}
3
9
4
0
4

Biruk A.
1
4
2
4
3
4
4
4
5
4
6
4
7
4
8
4
9
5
0
5
1
5
2
5
3
5
4
5
5
5
6
5
7
5
8

See the previous example on how to compile and run this program! Point to this Java file
"JdbcInsertTest"!
Notes:

Biruk A.
 1.You cannot insert two records with the same primary key (i.e., id) value. Hence, we issue a
DELETE before INSERT new record. In this way, you can re-run the program.
2.If you insert a partial record, the missing columns will be set to their default values.

Exercise: Modify your Java program to issue the following SQL statements:
1.Delete all books with id > 8000; and insert: (8001, 'Java ABC', 'Kevin Jones', 15.55, 55) and
(8002, 'Java XYZ', 'Kevin Jones', 25.55, 55);

4.  JDBC Cycle

4.1  (Skip Unless...) How to Debug?

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE:

(Windows) No suitable driver found

(macOS/Linux) NullPointerException

PROBABLE CAUSES: MySQL JDBC Driver Connector/J was NOT (properly) installed.

POSSIBLE SOLUTION:

1. Read "2.3 Install MySQL JDBC Driver" again, again and again...

2. You need to include MySQL JDBC driver via "cp" option to run your JDBC program:

> java -cp .;path-to\mysql-connector-java-x.x.xx.jar JdbcClassName

3. For Tomcat, you may copy the driver JAR-file into Tomcat's "lib" directory.

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

Communications link failure

Biruk A.
PROBABLE CAUSES:

1. MySQL Server is NOT started, or

2. The program was connecting to a wrong TCP port number or wrong hostname (or IP
address)

in your database-URL jdbc:mysql://localhost:port/databaseName.

POSSIBLE SOLUTION:

1. Make sure that server has been started. Note down the server's port number

from the server's console.

2. Check the database-URL's hostname and port number:

jdbc:mysql://localhost:port/databaseName

3. Run a MySQL client, issue command "status" to confirm the server's TCP port
number.

4. Run a mysql client, use "mysql -u root -p --port=xxxx" to specify the port
number to

confirm the server's port number.

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: java.sql.SQLException: Access denied for user 'username'@'localhost'

(using password: YES)

PROBABLE CAUSES: Wrong username or password in statement:

DriverManager.getConnection(databaseURL, username, password).

POSSIBLE SOLUTION: Obvious!

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is

not allowed

POSSIBLE SOLUTION:

1. Check you userid and password by logging in thru mysql command-line client

2. Add allowPublicKeyRetrieval=true and/or useSSL=false to Connnection's URL, e.g.,

jdbc:mysql://localhost:3306/ebookshop?
allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

Biruk A.
SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: java.sql.SQLException: The server time zone value 'xxx' is

unrecognized or represents more than one time zone.

POSSIBLE SOLUTION:

Add serverTimezone=UTC to Connnection's URL, e.g.,

jdbc:mysql://localhost:3306/ebookshop?
allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

Unknown database 'xxxx'

PROBABLE CAUSES: DriverManager.getConnection("jdbc:mysql://localhost:8888/xxxx",

userid, password)

specifies a database that does not exist in the server.

POSSIBLE SOLUTION: Create the database using a client, before running the Java
program.

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

Table 'xxxx.xxxx' doesn't exist

PROBABLE CAUSES: The SQL statement references a non-existence table.

POSSIBLE SOLUTION: Check your SQL statement and the database tables.

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: java.sql.SQLException: Column 'xxx' not found.

PROBABLE CAUSES: The method ResultSet.getXxx(columnName) cannot locate

the requested columnName in the ResultSet.

POSSIBLE SOLUTION: Make sure that the column 'xxx' is included in the SELECT
statement,

Biruk A.
 so that it is included in the ResultSet.

SYMPTOM: Can compile the JDBC program but Runtime Error

ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

You have an error in your SQL syntax; check the manual that corresponds to

your MySQL server version for the right syntax to use near .... at line x

PROBABLE CAUSES: Syntax error in your SQL statement.

POSSIBLE SOLUTION: Obvious!

Biruk A.