jdbc example

An Introduction to
Java Database (JDBC) Programming

by Examples

1.  Relational Database and and Structure Query Language (SQL)

I presume that you have some knowledge on Relational Database and SQL. Otherwise, read "Introduction to Relational Database and SQL",

2.  Setting-up MySQL

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

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

On MySQL, instead of using the "mysql" interactive client program provided, you can write your own client program (in Java or other languages) to access the MySQL server. Your client program 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 (Read "How to install JDK and Get Started").
2. A programming text editor, such as TextPad, Notepad++ for Windows (Read "Programming Editor"), or jEdit, gEdit for Mac (Read "Programming Editor for Mac"); or a Java IDE such as Eclipse or NetBeans (Read "How to Install Eclipse" or "How to Install NetBeans").

2.2  Install MySQL JDBC Driver

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. MySQL has a native JDBC driver, supporting many of the Java's JDBC features.

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).
2. UNZIP the download file into any temporary folder.
3. Copy the JAR file "mysql-connector-java-5.1.{xx}-bin.jar" to your JDK's Extension Directory at "<JAVA_HOME>\jre\lib\ext" (where <JAVA_HOME> is the JDK installed directory, e.g.,"c:\program files\java\jdk1.8.0_{xx}\jre\lib\ext").

For Mac OS X

1. Download the latest MySQL JDBC driver from http://www.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. Open the expanded folder. Copy the JAR file "mysql-connector-java-5.1.{xx}-bin.jar" to JDK's extension directory at "/Library/Java/Extension".

(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, or in the java's command-line option -cp <paths>. For example,

// For windows
> java -cp .;/path/to/mysql-connector-java-5.1.{xx}-bin.jar JDBCClassToBeRun
// For Macs/Unixes
> java -cp .:/path/to/mysql-connector-java-5.1.{xx}-bin.jar JDBCClassToBeRun

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

// For Windows
> cd {path-to-mysql-bin}   // Check your MySQL installed directory
> mysqld --console

// For Mac OS X
$ cd /usr/local/mysql/bin
$ sudo ./mysqld_safe --console

Start a MySQL client: I shall also assume that there is an authorized user called "myuser" with password "xxxx" (otherwise, use "root" user).

// For Windows
> cd {path-to-mysql-bin}   // Check your MySQL installed directory
> mysql -u myuser -p
 
// For Mac OS X
$ 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;

3.  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.
2. Allocate a Statement object, under the Connection object created.
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 7), 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 in any directory of your choice (e.g., d:/myproject).

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

import java.sql.*; // Use classes in java.sql package // JDK 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:8888/ebookshop", "myuser", "xxxx"); // MySQL // 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 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 } }

(For Windows) I strongly suggest that you use TextPad. With TextPad, you can compile a Java program via menu "Tools" ⇒ "External Tools" ⇒ "Compile Java"; you can run the Java program via menu "Tools" ⇒ "External Tools" ⇒ "Run Java Applications".

Compile: To compile the program:

> cd {path-to-the-java-source-file}
> javac JdbcSelectTest.java

Run: To run the program:

> java JdbcSelectTest

(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 7, 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-URLto 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:8888/ebooksop", "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; "8888" 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, 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 a 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 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.

Exercises: Try 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 FROM books WHERE price < 30
3. SELECT title, author, price, qty FROM books WHERE author = 'Tan Ah Teck' OR price >= 30 ORDER BY price DESC, id ASC
4. SELECT title FROM books WHERE author LIKE 'T_n%' OR author LIKE '%Ali'

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 useexecuteQuery(), which returns a ResultSet object modeling the returned table. UPDATE|INSERT|DELETE does not return a table, but an int indicating the number of records affected.

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 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:8888/ebookshop", "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 } }

Exercise: [TODO]

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.

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 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:8888/ebookshop", "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); // 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 } }

Notes:

1. You cannot insert two records with the same primary key (i.e., id) value. Hence, we issue a DELETE before INSERT new records. In this way, you can re-run the program.
2. If you insert a partial record, the missing columns will receive their default values.
3. (MS Access) Access reports a "General Error" for duplicate records?! For Access 2007, you need to remove the auto-generated ID column for the first INSERT statement (you get a "Too few parameters" error, otherwise).

Exercise: [TODO]

4.  JDBC Cycle