TABLE OF CONTENTS (HIDE)

Java Tutorial

An Introduction to Java Database (JDBC) Programming by Examples

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".

Setting-up MySQL

In this section, I shall describe the MySQL relational database system. For other database systems, read "Other Databases".

Install MySQL (read "How to Set Up MySQL and Get Started"). I shall assume that the MySQL server is running on the default port number 3306.

On MySQL, instead of using the "mysql" interactive 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.

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 (Read "How to install JDK and Get Started").
  2. A programming text editor, such as TextPad, Notepad++, Sublime, Atom for Windows (Read "Programming Editors for Windows"), or jEdit, gEdit, Sublime, Atom for Mac OS (Read "Programming Editors for Mac OS").

Create a Directory to Keep all your Works

I shall assume that you have created a directory called "c:\myWebProject" (for Windows) or "~/myWebProject" (for Mac OS X) in your earlier exercises. Do it otherwise.

This step is important; otherwise, you will be out-of-sync with this article and will not be able to find your files later.

Install MySQL JDBC Driver (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 ⇒ "MySQL Connectors" ⇒ "Connector/J" ⇒ Connector/J 5.1.{xx} ⇒ select "Platform Independent" ⇒ ZIP Archive (e.g., "mysql-connector-java-5.1.{xx}.zip", where {xx} is the latest release number) ⇒ "No thanks, just start my download".
  2. UNZIP the download file into your project directory "C:\myWebProject".
  3. Open the unzipped folder. Check for JAR file "mysql-connector-java-5.1.{xx}-bin.jar". The absolute filename of the JAR file (which you will need later) is "C:\myWebProject\mysql-connector-java-5.1.{xx}\mysql-connector-java-5.1.{xx}-bin.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 Mac OS X

  1. Download the latest MySQL JDBC driver from http://dev.mysql.com/downloads ⇒ MySQL Connectors ⇒ Connector/J ⇒ Connector/J 5.1.{xx} ⇒ select "Platform Independent" ⇒ Compressed TAR Archive (e.g., mysql-connector-java-5.1.{xx}.tar.gz, where {xx} is the latest release number).
  2. Double-click on the downloaded TAR file to expand into folder "mysql-connector-java-5.1.{xx}".
  3. Move the expanded folder into your project directory "~/myWebProject".
  4. Open the expanded folder. Check for JAR file "mysql-connector-java-5.1.{xx}-bin.jar". The absolute filename of the JAR file (which you will need later) is "~/myWebProject/mysql-connector-java-5.1.{xx}/mysql-connector-java-5.1.{xx}-bin.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 the JDK's extension directory (prior to JDK 9), 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-5.1.{xx}-bin.jar JDBCClassToRun
// For Mac OS / Unixes
java -cp .:/path/to/mysql-connector-java-5.1.{xx}-bin.jar JDBCClassToRun

For JDK 8 and lower, you can copy the JAR file into JDK's extension directory at "<JAVA_HOME>\jre\lib\ext" (Windows) or "/Library/Java/Extensions" (Mac OS X).

JDK 9 remove the extension mechanism.

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
+-------+----------------------------+---------------+---------+-------+
| 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 Mac OS X
// 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 Mac OS X: assume that MySQL is installed in "/usr/local/mysql"
cd /usr/local/mysql/bin
./mysql -u myuser -p

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;

Introduction to JDBC Programming by Examples

A JDBC (Java Database Connectivity) program comprises the following steps:

  1. Allocate a Connection object, for connecting to the database server.
  2. Allocate a Statement object, under the Connection object created, 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 object to free up the resources.

We shall illustrate Java Database programming by the following examples.

IMPORTANT: The following examples require JDK 1.7 and above to run. If you JDK is below 1.7, upgrade your JDK (See JDK How-to)! You can check your JDK version via command "javac -version".

Example 1: SQL SELECT

Try out the following JDBC program (requires JDK 1.7 and above), which issues an SQL SELECT. Take note that the source filename must be the same as the classname, with extension of ".java".

Save the program into your project directory "c:\myWebProject" (Windows) or "~/myWebProject" (Mac OS X), as "JdbcSelectTest.java".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
import java.sql.*;   // Use 'Connection', 'Statement' and 'ResultSet' classes in java.sql package
 
// JDK 1.7 and above
public class JdbcSelectTest {   // Save as "JdbcSelectTest.java"
   public static void main(String[] args) {
      try (
         // Step 1: Allocate a database 'Connection' object
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/ebookshop?useSSL=false", "myuser", "xxxx");
               // MySQL: "jdbc:mysql://hostname:port/databaseName", "username", "password"
 
         // Step 2: Allocate a 'Statement' object in the Connection
         Statement stmt = conn.createStatement();
      ) {
         // Step 3: Execute a SQL SELECT query, the query result
         //  is returned in a 'ResultSet' object.
         String strSelect = "select title, price, qty from books";
         System.out.println("The SQL query is: " + strSelect); // Echo For debugging
         System.out.println();
 
         ResultSet rset = stmt.executeQuery(strSelect);
 
         // Step 4: Process the ResultSet by scrolling the cursor forward via next().
         //  For each row, retrieve the contents of the cells with getXxx(columnName).
         System.out.println("The records selected are:");
         int rowCount = 0;
         while(rset.next()) {   // Move the cursor to the next row, return false if no more row
            String title = rset.getString("title");
            double price = rset.getDouble("price");
            int    qty   = rset.getInt("qty");
            System.out.println(title + ", " + price + ", " + qty);
            ++rowCount;
         }
         System.out.println("Total number of records = " + rowCount);
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - Done automatically by try-with-resources
   }
}

Compile: To compile the program:

// Windows: The Java source directory is "c:\myWebProject"
c:
cd \myWebProject
javac JdbcSelectTest.java

// Mac OS X: 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-5.1.{xx}\mysql-connector-java-5.1.{xx}-bin.jar JdbcSelectTest

// For Mac OS X
cd ~/myWebProject
// Need to use $HOME instead of ~ in the "java" command
java -cp .:$HOME/myWebProject/mysql-connector-java-5.1.{xx}/mysql-connector-java-5.1.{xx}-bin.jar JdbcSelectTest

You should COPY and SAVE this command to your notepad, so that you don't need to type this super-long command again and again. (For Mac User, DO NOT use the default TextEdit to save this command!!! Use a programming text editor.)

Figure out that the part marked in RED is the absolute filename of the JAR file I asked you to take note earlier, when you installed the MySQL JDBC Driver.

This is a little messy here. The "." denotes the current directory to locate the JdbcSelectTest. The ";" or ":" are path separator for Windows and Mac OS X, respectively. In other words, there are 2 items in the classpath: the current directory and the MySQL JDBC Driver.

(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.
  3. In Line 8, we allocate a Connection object (called conn) via static method DriverManager.getConnection(database-url, db-user, password). The Java program uses a so-called database-URL to connect to the server:
    • For MySQL:
      // Syntax
      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:{port}/{db-name}", "{db-user}", "{password}");
      // 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.
    • Others: Read "Other Databases".
  4. In Line 13, we allocate a Statement object (called stmt) within 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). 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.
  6. You may use rset.getString(columnName) to retrieve all types (int, double, etc).
  7. 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.
  8. In this example, we use JDK 1.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).

  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

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 into your project directory "c:\myWebProject" (Windows) or "~/myWebProject" (Mac OS), as "JdbcUpdateTest.java".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import java.sql.*;     // Use classes in java.sql package
 
// JDK 1.7 and above
public class JdbcUpdateTest {    // Save as "JdbcUpdateTest.java"
   public static void main(String[] args) {
      try (
         // Step 1: Allocate a database 'Connection' object
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/ebookshop?useSSL=false", "myuser", "xxxx"); // MySQL
 
         // Step 2: Allocate a 'Statement' object in the Connection
         Statement stmt = conn.createStatement();
      ) {
         // Step 3 & 4: Execute a SQL UPDATE via executeUpdate()
         //   which returns an int indicating the number of rows affected.
         // Increase the price by 7% and qty by 1 for id=1001
         String strUpdate = "update books set price = price*0.7, qty = qty+1 where id = 1001";
         System.out.println("The SQL query is: " + strUpdate);  // Echo for debugging
         int countUpdated = stmt.executeUpdate(strUpdate);
         System.out.println(countUpdated + " records affected.");
 
         // Step 3 & 4: Issue a SELECT to check the UPDATE.
         String strSelect = "select * from books where id = 1001";
         System.out.println("The SQL query is: " + strSelect);  // Echo for debugging
         ResultSet rset = stmt.executeQuery(strSelect);
         while(rset.next()) {   // Move the cursor to the next row
            System.out.println(rset.getInt("id") + ", "
                    + rset.getString("author") + ", "
                    + rset.getString("title") + ", "
                    + rset.getDouble("price") + ", "
                    + rset.getInt("qty"));
         }
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - Done automatically by try-with-resources
   }
}

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:

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

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 into your project directory "c:\myWebProject" (Windows) or "~/myWebProject" (Mac OS), as "JdbcInsertTest.java".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
import java.sql.*;     // Use classes in java.sql package
 
// JDK 1.7 and above
public class JdbcInsertTest {    // Save as "JdbcUpdateTest.java"
   public static void main(String[] args) {
      try (
         // Step 1: Allocate a database 'Connection' object
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/ebookshop?useSSL=false", "myuser", "xxxx"); // MySQL
 
         // Step 2: Allocate a 'Statement' object in the Connection
         Statement stmt = conn.createStatement();
      ) {
         // Step 3 & 4: Execute a SQL INSERT|DELETE statement via executeUpdate(),
         //   which returns an int indicating the number of rows affected.
 
         // DELETE records with id>=3000 and id<4000
         String sqlDelete = "delete from books where id>=3000 and id<4000";
         System.out.println("The SQL query is: " + sqlDelete);  // Echo for debugging
         int countDeleted = stmt.executeUpdate(sqlDelete);
         System.out.println(countDeleted + " records deleted.\n");
 
         // INSERT a record
         String sqlInsert = "insert into books " // need a space
               + "values (3001, 'Gone Fishing', 'Kumar', 11.11, 11)";
         System.out.println("The SQL query is: " + sqlInsert);  // Echo for debugging
         int countInserted = stmt.executeUpdate(sqlInsert);
         System.out.println(countInserted + " records inserted.\n");
 
         // INSERT multiple records
         sqlInsert = "insert into books values "
               + "(3002, 'Gone Fishing 2', 'Kumar', 22.22, 22),"
               + "(3003, 'Gone Fishing 3', 'Kumar', 33.33, 33)";
         System.out.println("The SQL query is: " + sqlInsert);  // Echo for debugging
         countInserted = stmt.executeUpdate(sqlInsert);
         System.out.println(countInserted + " records inserted.\n");
 
         // INSERT a partial record
         sqlInsert = "insert into books (id, title, author) "
               + "values (3004, 'Fishing 101', 'Kumar')";
         System.out.println("The SQL query is: " + sqlInsert);  // Echo for debugging
         countInserted = stmt.executeUpdate(sqlInsert);
         System.out.println(countInserted + " records inserted.\n");
 
         // Issue a SELECT to check the changes
         String strSelect = "select * from books";
         System.out.println("The SQL query is: " + strSelect);  // Echo For debugging
         ResultSet rset = stmt.executeQuery(strSelect);
         while(rset.next()) {   // Move the cursor to the next row
            System.out.println(rset.getInt("id") + ", "
                    + rset.getString("author") + ", "
                    + rset.getString("title") + ", "
                    + rset.getDouble("price") + ", "
                    + rset.getInt("qty"));
         }
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - Done automatically by try-with-resources
   }
}

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

Notes:
  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);

JDBC Cycle

JDBC_Cycle.png

REFERENCES & RESOURCES

  1. JDBC Online Tutorial @ http://download.oracle.com/javase/tutorial/jdbc/index.html.
  2. JDBC Home Page @ http://www.oracle.com/technetwork/java/javase/jdbc/index.html.
  3. White Fisher, et al., "JDBC API Tutorial and Reference", 3rd eds, Addison Wesley, 2003.
  4. MySQL Home Page @ https://dev.mysql.com/, and documentation.
  5. MySQL 5.7 Reference Manual @ https://dev.mysql.com/doc/refman/5.7en/.
  6. Russell Dyer, "MySQL in a Nutshell", O'Reilly, 2008.