Java Database Programming (JDBC) by Examples - Part 2

More JDBC

Example 4: Atomic Transaction - Commit and Rollback

An atomic transaction is a group of SQL statements - either all succeed or none succeeds (atom is indivisible). This is to prevent partial update to the database. To manage transaction in JDBC, we first disable the default auto-commit (which commits every SQL statement), issue a few SQL statements, and then decide whether to issue a commit() to commit all the changes or rollback() to discard all the changes since the last commit. For example:

// Step 3 and 4: Execute queries and process the query results
// Disable auto-commit for the connection, which commits every SQL statement.
conn.setAutoCommit(false);

// Before Changes
ResultSet rset = stmt.executeQuery("select id, qty from books where id in (1001, 1002)");
System.out.println("-- Before UPDATE --");
while(rset.next()) {
   System.out.println(rset.getInt("id") + ", " + rset.getInt("qty"));
}
conn.commit();     // Commit SELECT
 
// Issue two UPDATE statements thru executeUpdate()
stmt.executeUpdate("update books set qty = qty + 1 where id = 1001");
stmt.executeUpdate("update books set qty = qty + 1 where id = 1002");
conn.commit();     // Commit UPDATEs
 
rset = stmt.executeQuery("select id, qty from books where id in (1001, 1002)");
System.out.println("-- After UPDATE and Commit --");
while(rset.next()) {
   System.out.println(rset.getInt("id") + ", " + rset.getInt("qty"));
}
conn.commit();     // Commit SELECT
 
// Issue two UPDATE statements thru executeUpdate()
stmt.executeUpdate("update books set qty = qty - 99 where id = 1001");
stmt.executeUpdate("update books set qty = qty - 99 where id = 1002");
conn.rollback();   // Discard all changes since the last commit
 
rset = stmt.executeQuery("select id, qty from books where id in (1001, 1002)");
System.out.println("-- After UPDATE and Rollback --");
while(rset.next()) {
   System.out.println(rset.getInt("id") + ", " + rset.getInt("qty"));
}
conn.commit();     // Commit SELECT

In some database implementations, you are required to "commit" the SELECT statement as well.

Rolling Back in Catch-Clause

The rollback() method is usually issued whenever there is an error (e.g., SQLException). Hence, it should be placed in the catch-clause. For example,

import java.sql.*;
 
public class JdbcCommitCatchTest {  // JDK 7 and above
   public static void main(String[] args) throws SQLException {
      try (
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/ebookshop", "myuser", "xxxx"); // MySQL
         Statement stmt = conn.createStatement();
      ) {
         try {
            // Disable auto-commit
            conn.setAutoCommit(false);
 
            // Issue two INSERT statements
            stmt.executeUpdate("insert into books values (4001, 'Paul Chan', 'Mahjong 101', 4.4, 4)");
            // Duplicate primary key, which triggers a SQLException
            stmt.executeUpdate("insert into books values (4001, 'Peter Chan', 'Mahjong 102', 4.4, 4)");
            conn.commit();     // Commit changes only if all statements succeed.
 
         } catch(SQLException ex) {
            System.out.println("-- Rolling back changes --");
            conn.rollback();   // Rollback to the last commit.
            ex.printStackTrace();
         }
      }  // auto-close Connection and Statement
   }
}

Notes:

In JDK 7 try-with-resource syntax, you cannot access the declared resources (in try) in the catch-clause, e.g. to do the conn.rollback(). (Test it out yourself!) Hence, we need to nest another try-catch under the try-with-resource to do the conn.rollback(), which is rather messy.

You shall get an exception on "duplicate entry on primary key", e.g., in MySQL:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '4001' for key 'PRIMARY'

Exercise: [TODO]

Example 5: `ResultSetMetaData`

Each ResultSet object is associated with a header (called meta-data), which contains information about the ResultSet object, such as the number of columns, the name and type of the columns etc. The meta-data is stored in a ResultSetMetaData object. You can use method rset.getMetaData() to retrieve the associated meta-data object of a ResultSet rset.

ResultSetMetaData is useful in dynamically processing the ResultSet. You could retrieve the number of columns and use rset.getXxx(columnNumber) to retrieve the content of a particular column number in the current row. Note that column number begins at 1 (not 0). For example,

// Step 3 and 4: Execute query and process query result
ResultSet rset = stmt.executeQuery("select * from books");
// Get the metadata of the ResultSet
ResultSetMetaData rsetMD = rset.getMetaData();
// Get the number of column from metadata
int numColumns = rsetMD.getColumnCount();
 
// Print column names - Column Index begins at 1 (instead of 0)
for (int i = 1; i <= numColumns; ++i) {
   System.out.printf("%-30s", rsetMD.getColumnName(i));
}
System.out.println();
 
// Print column class names
for (int i = 1; i <= numColumns; ++i) {
   System.out.printf("%-30s",
           "(" + rsetMD.getColumnClassName(i) + ")");
}
System.out.println();
 
// Print column contents for all the rows
while (rset.next()) {
   for (int i = 1; i <= numColumns; ++i) {
      // getString() can be used for all column types
      System.out.printf("%-30s", rset.getString(i));
   }
   System.out.println();
}

Exercise: [TODO]

Example 6: `DatabaseMetaData`

[TODO]

Example 7: `PreparedStatement`

The Statement object is susceptible to "SQL injection attack". Production systems shall use PreparedStatement instead.

JDBC provides a class called PreparedStatement, which allows you to pass parameters into a SQL statement and execute the same SQL statement multiple times. A PreparedStatement is a pre-compiled SQL statement that is more efficient than using Statement over and over. In a PreparedStatement, '?' denotes a place holder for parameter. A set of setXxx(placeHolderNumber, value) methods can be used to fill in the parameters. For example,

import java.sql.*;             // Use classes in java.sql package
 
public class JdbcPreparedStatementTest {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/ebookshop", "myuser", "xxxx"); // MySQL
 
         // Two PreparedStatements, one for INSERT and one for SELECT
         PreparedStatement pstmt = conn.prepareStatement("insert into books values (?, ?, ?, ?, ?)");
                                                          // Five parameters 1 to 5
         PreparedStatement pstmtSelect = conn.prepareStatement("select * from books");
      ) {
         pstmt.setInt(1, 7001);  // Set values for parameters 1 to 5
         pstmt.setString(2, "Mahjong 101");
         pstmt.setString(3, "Kumar");
         pstmt.setDouble(4, 88.88);
         pstmt.setInt(5, 88);
         int rowsInserted = pstmt.executeUpdate();  // Execute statement
         System.out.println(rowsInserted + "rows affected.");
 
         pstmt.setInt(1, 7002);  // Change values for parameters 1 and 2
         pstmt.setString(2, "Mahjong 102");
         // No change in values for parameters 3 to 5
         rowsInserted = pstmt.executeUpdate();
         System.out.println(rowsInserted + "rows affected.");
 
         // Issue a SELECT to check the changes
         ResultSet rset = pstmtSelect.executeQuery();
         while(rset.next()) {
            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
   }
}

In this example, we used two PreparedStatements: one for INSERT with 5 parameters, denoted as '?'; another one for SELECT without parameter - but you can reuse this SELECT more efficiently.

Once a parameter has been defined for a given PreparedStatement, it can be used for multiple executions, until it is cleared by a call to pstmt.clearParameter(). In the above example, the 3rd, 4th and 5th parameters of the 2nd PreparedStatement are set in the 1st PreparedStatement.

Exercise: [TODO]

Example 8: Batch Processing

JDBC 2.0 supports batch processing of SQL statements, to improve the performance. Each statement is added to the batch via Statement.addBatch() or PreparedStatement.addBatch(). The entire batch of statements is then sent to the database for execution via executeBatch(), which returns an int array keeping the return codes of each of the statement.

// Step 3 and 4: Execute query and Process the query result
conn.setAutoCommit(false);  // Turn off auto-commit for each SQL statement
 
stmt.addBatch("insert into books values (8001, 'Java ABC', 'Kevin Jones', 1.1, 99)");
stmt.addBatch("insert into books values (8002, 'Java XYZ', 'Kevin Jones', 1.1, 99)");
stmt.addBatch("update books set price = 11.11 where id=8001 or id=8002");
int[] returnCodes = stmt.executeBatch();
 
System.out.print("Return codes are: ");
for (int code : returnCodes) {
   System.out.printf(code + ", ");
}
System.out.println();
 
conn.commit();  // Commit SQL statements

You can also use PreparedStatement for batch processing:

// Step 1: Allocate a "Connection"
Connection conn = DriverManager.getConnection(......);
 
// Step 2: Allocate a "PreparedStatement" instead of "Statement"
PreparedStatement pstmt = conn.prepareStatement(
   "insert into books values (?, ?, ?, ?, ?)");  // Five parameters 1 to 5
 
conn.setAutoCommit(false);  // Disable auto-commit for each SQL statement
 
pstmt.setInt(1, 8003);  // Set values for parameters 1 to 5
pstmt.setString(2, "Java 123");
pstmt.setString(3, "Kevin Jones");
pstmt.setDouble(4, 12.34);
pstmt.setInt(5, 88);
pstmt.addBatch();   // add the statement for batch processing
 
pstmt.setInt(1, 8004);  // Change values for parameters 1 and 2
pstmt.setString(2, "Java 456");
// No change in values for parameters 3 to 5
pstmt.addBatch();  // add the statement for batch processing
 
int[] returnCodes = pstmt.executeBatch();
  // executeBatch() returns an int array, keeping the return codes of all statements

System.out.print("Return codes are: ");
for (int code : returnCodes) System.out.printf(code + ", ");
System.out.println();
 
conn.commit();

Exercise: [TODO]

Other Relational Databases

Microsoft Access

Notes: The JDBC-ODBC Bridge driver (sun.jdbc.odbc.JdbcOdbcDriver) was removed from JDK 8! You need to use JDK 7 or find an alternate JDBC driver?!

MS Access is a personal database and is not meant for business production (it is probably sufficient for a small business). Microsoft has it own standard called ODBC, and does not provide a native JDBC drive for Access. A JDBC-ODBC bridge driver provided by Sun (called sun.jdbc.odbc.JdbcOdbcDriver) is used for JDBC programming. Some of the Java's JDBC features do not work on Access - due of the the limitations in the JDBC-ODBC bridge driver.

Access has various versions, such as Access 2010, Access 2007 and Access 2003, which are NOT 100% compatible. The file type for Access 2003 (and earlier versions) is ".mdb". The file type for Access 2007 is ".accdb".

Access 2010

[TODO]

Access 2007

Launch "MS Access 2007".
Create a new database: From the "Access" button ⇒ "New" ⇒ In "Filename" box, select your working directory and enter "ebookshop.accdb" as the name of the database ⇒ "Create".
Delete the auto-generated column ID.
Click "Add New Field" to create the these 5 columns: "id", "title", "author", "price" and "qty".
Type in the above records.

Access 2003

Launch "MS Access 2003".
Create a new database: From Access's "File" menu ⇒ "New" ⇒ "Blank database..." ⇒ Save as "ebookshop.mdb".
Create a new table: Select "Create table by entering data" ⇒ Rename the default column names "field1" , ..., "field5" to "id", "title", "author", "price" and "qty", by clicking on the field names.
Create new records: Enter the above records ⇒ Save the table as "books" ⇒ Answer "NO" to "there is no primary key defined" prompt.

Database Design View

Check out the "Design view". (In Access 2003: Right-click on table "books" ⇒ select "Design view". In Access 2007: Select the view menu from the ribbon.)

Observe the data types of the columns: id and qty are "Number (Long Integer)"; price is "Number (Double)"; title and author are "Text (255)". Access uses its own name for data types (which is NOT conforming to the SQL specification). Access's "Number (Long Integer)", "Number (Double)" and "Text" correspond to Java's int, double and String, respectively. You could change the data type and remove the additional ID column (Access 2007) in the design view.

IMPORTANT: The correct way to create a database in Access is to create all the columns (with proper types and attributes) in the "Design View" before entering or importing the records.

ODBC (Open Database Connectivity) Connection

Close the Access before proceeding to next step to define ODBC connection (otherwise, you will get an error "invalid directory or file path", as the database is currently opened in an exclusive mode).

An so-called ODBC connection is needed to connect to a Microsoft database.

Define an ODBC connection called "ebookshopODBC", which selects the database we have just created, (i.e., "ebookshop.mdb" for Access 2003 or "ebookshop.accdb" for Access 2007), as follows:

Goto "Control Panel" ⇒ Administrator Tools ⇒ Data Source (ODBC),
Choose tab "System DSN" (System Data Source Name) (for all users in the system); or "User DSN" (User Data Source Name) (for the current login user only).
"Add" ⇒ Select "Microsoft Access Driver (*.mdb, *.accdb)" (for Access 2007); or "Microsoft Access Driver (*.mdb)" (for Access 2003) ⇒ Click "Finish".
In "ODBC Microsoft Access Setup" dialog: Enter "ebookshopODBC" in "Data Source Name", and use the "Select" button to navigate and select "ebookshop.accdb" (for Access 2007) or "ebookshop.mdb" (for Access 2003).

Database-URL

The database-url for Access is in the form of "jdbc:odbc:{odbc-name}", with protocol jdbc and sub-protocol odbc.

// Syntax
Connection conn = DriverManager.getConnection("jdbc:odbc:{odbc-name}");
// Example
Connection conn = DriverManager.getConnection("jdbc:odbc:ebookshopODBC");

For JDK prior to 1.6, you need to explicitly load the database drive as follows:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // for MS Access/Excel

A Sample JDBC Program for MS Access

[TODO]

Java DB (Apache Derby)

Java DB is a distribution of the Apache Derby open-source relational database. It is written in pure Java, and supports SQL through the JDBC and Java EE APIs. It can be run either embedded or as client/server.

Read:

"Java DB documentation" @ http://docs.oracle.com/javadb/.
"Apache Derby Tutorial" @ http://db.apache.org/derby/papers/DerbyTut/.

Java DB is included in JDK (since JDK ?), under the directory "<JAVA_HOME>\db". The "bin" sub-directory contains the scripts and utilities; the "lib" sub-directory contains the JAR files. You need to set an environment variable DERBY_HOME pointing to "<JAVA_HOME>\db", and include "<JAVA_HOME>\db\bin" in the PATH. If you installed the JDK demos and samples, you can find the samples and demos in "demo\db".

An interactive scripting tool called ij is provided.

> set DERBY_HOME=%JAVA_HOME%\db
> set PATH=%DERBY_HOME%\bin;%PATH%
> cd %DERBY_HOME%\bin
> sysinfo   // Display the Derby version and Java environment
> ij        // Run scripts or interactive queries
> dblook    // Dump all or part of database

A Sample `ij` Embedded Session

> cd {path-to-derby-work-directory}
// Start interactive client
> ij
// Create a new database (schema) called firstdb
ij> connect 'jdbc:derby:firstdb;create=true';
// Create a new table called firsttable
ij> create table firsttable (id int primary key, name varchar(20));
// Insert records
ij> insert into firstable values (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
// Select records
ij> select * from firsttable;
// Run a script of SQL commands
ij> run 'script-name.sql';
// Exit ij
ij> exit

The "derby.log" contains the message and error log. The database is created under the "firstdb" directory: seg0 (contains the data) and log (contains the transaction log).

A Sample `ij` Client/Server Session

Start the Derby Network Server via:

> cd {path-to-Derby-work-directory}
> java -jar "%DERBY_HOME%\lib\derbyrun.jar" server start
Tue Oct 16 23:08:01 SGT 2012 : Security manager installed using the Basic server security policy.
Tue Oct 16 23:08:02 SGT 2012 : Apache Derby Network Server - 10.8.2.2 - (1181258) started 
and ready to accept connections on port 1527

Start an ij client and connect to the server

> ij
// Connect to the Derby network server and create a new database called seconddb
ij> connect 'jdbc:derby://localhost:1527/seconddb;create=true';
// CREATE TABLE, INSERT, SELECT ...
ij> ......
ij> exit

To shut down the Derby server:

> java -jar "%DERBY_HOME%\lib\derbyrun.jar" server shutdown

A Sample Embedded JDBC Program

Include %DERBY_HOME%\lib\derby.jar in the CLASSPATH.

import java.sql.*;
 
public class DerbyTest {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         // Step 1: Allocate a database "Connection" object
         Connection conn = DriverManager.getConnection(
               "jdbc:derby:test_db;create=true");
 
         // 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
         int returnCode;
         returnCode = stmt.executeUpdate(
               "create table test_table (id int primary key, name varchar(20))");
         System.out.println(returnCode + " records affected.");
 
         returnCode = stmt.executeUpdate(
               "insert into test_table values (1, 'one'), (2, 'two')");
         System.out.println(returnCode + " records affected.");
 
         ResultSet rset = stmt.executeQuery("select * from test_table");
         while (rset.next()) {
            System.out.println(rset.getInt("id") + ", " + rset.getString("name"));
         }
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - Done automatically by try-with-resources
 
      // Shutdown the Derby
      try {
         DriverManager.getConnection("jdbc:derby:;shutdown=true");
      } catch (SQLException ex)  {
         if ( ex.getSQLState().equals("XJ015") ) {
            System.out.println("successfully shutdown!");
         }
      }
   }
}

The database-URL for Derby is as follows:

// Syntax
Connection conn = DriverManager.getConnection("jdbc:derby:{db-name};create=true");
// Example
Connection conn = DriverManager.getConnection("jdbc:derby:test_db;create=true");

For JDK prior to 1.6, you need to explicitly load the JDBC driver:

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");  // For embedded Apache Derby (Java DB)

A Sample Client/Server JDBC Program

Start the Derby Server, as described in the earlier section.

Change the JDBC client program's database-URL to "jdbc:derby://localhost:1527/test_db;create=true".

Microsoft Excel

Surprisingly, you can use SQL commands to manipulate Excel spreadsheet. This is handy to export data into Excel, says from a database.

Setup Excel Spreadsheet

Create a Excel Spreadsheet called "ebookshop.xlsx" (corresponding to database name). Create a sheet called "books" (corresponding to table name). Create 5 column headers: id, title, author, price and qty. Insert a few records.

Setup ODBC

Goto "Control Panel" ⇒ Administrator Tools ⇒ Data Source (ODBC),
Choose tab "System DSN" (System Data Source Name) (for all users in the system); or "User DSN" (User Data Source Name) (for the current login user only).
"Add" ⇒ Select "Microsoft Excel Driver (*.xls, *.xlsx)" (for Excel 2007) ⇒ Click "Finish".
In "ODBC Microsoft Access Setup" dialog: In "Data Source Name", enter "ebookshopODBC". Click "Select Workbook" and select "ebookshop.xlsx" (for Excel 2007) ⇒ OK.

Sample JDBC Program for Excel

I shall show a sample JDBC program for manipulating Excel here, and point out the differences. I believe that all the discussion in the following sections applied to Excel, but have yet to test it out.

import java.sql.*;             // Use classes in java.sql package
 
public class ExcelSelectTest {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         // Step 1: Allocate a database "Connection" object
         Connection conn = DriverManager.getConnection(
               "jdbc:odbc:ebookshopODBC");  // Access/Excel
 
         // Step 2: Allocate a "Statement" object in the Connection
         Statement stmt = conn.createStatement();
      ) {
         // Excel connection, by default, is read-only.
         // Need to turn it off to issue INSERT, UPDATE, ...
         conn.setReadOnly(false);
 
         // Step 3: Execute a SQL SELECT query, the query result
         //   is returned in a "ResultSet" object.
         // Table name is the sheet's name in the form of [sheet-name$]
         String strSelect = "select title, price, qty from [books$]";
         System.out.println("The SQL query is: " + strSelect); // Echo For debugging
 
         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);
 
         // Try INSERT
         int returnCode = stmt.executeUpdate(
            "insert into [books$] values (1002, 'Java 101', 'Tan Ah Teck', 2.2, 2)");
         System.out.println(returnCode + " record(s) inserted.");
 
         // Try UPDATE
         returnCode = stmt.executeUpdate(
            "update [books$] set qty = qty+1 where id = 1002");
         System.out.println(returnCode + " record(s) updated.");
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      }  // Step 5: Close the resources - Done automatically by try-with-resources
   }
}

Notes:

The database-URL (Line 8) is in the form of jdbc:odbc:{odbc-name} - the same as Access.
The Excel's connection, by default, is read-only. To issue INSERT|UPDATE|DELETE commands, you need to disable read-only via conn.setReadOnly(false) (Line 15). Otherwise, you will get an error "java.sql.SQLException: [Microsoft][ODBC Excel Driver] Operation must use an updateable query".
In the SQL command, table name corresponds to sheet name, in the form of [sheet-name$].

OpenOffice's Base

[TODO]

Microsoft SQL Server

[TODO]

REFERENCES & RESOURCES

JDBC Online Tutorial @ http://download.oracle.com/javase/tutorial/jdbc/index.html.
JDBC Home Page @ http://java.sun.com/products/jdbc/overview.html.
JDBC API Specifications 1.2, 2.1, 3.0, and 4.0 @ http://java.sun.com/products/jdbc.
White Fisher, et al., "JDBC API Tutorial and Reference", 3rd eds, Addison Wesley, 2003.
MySQL Home Page @ http://www.mysql.org, and documentation.
MySQL 5.5 Reference Manual @ http://dev.mysql.com/doc/refman/5.5/en/index.html.
SQL.org @ http://www.sql.org.
Russell Dyer, "MySQL in a Nutshell", O'Reilly, 2008.

Latest version tested: Latest version tested: JDK 1.8.0_31/JDBC 4.0, MySQL 5.6.23
Last modified: March, 2015

Java Database Programming (JDBC) by Examples - Part 2

More JDBC

Example 4: Atomic Transaction - Commit and Rollback

Rolling Back in Catch-Clause

Notes:

Example 5: ResultSetMetaData

Example 6: DatabaseMetaData

Example 7: PreparedStatement

Example 8: Batch Processing

Other Relational Databases

Microsoft Access

Access 2010

Access 2007

Access 2003

Database Design View

ODBC (Open Database Connectivity) Connection

Database-URL

A Sample JDBC Program for MS Access

Java DB (Apache Derby)

A Sample ij Embedded Session

A Sample ij Client/Server Session

A Sample Embedded JDBC Program

A Sample Client/Server JDBC Program

Microsoft Excel

Setup Excel Spreadsheet

Setup ODBC

Sample JDBC Program for Excel

OpenOffice's Base

Microsoft SQL Server

Example 5: `ResultSetMetaData`

Example 6: `DatabaseMetaData`

Example 7: `PreparedStatement`

A Sample `ij` Embedded Session

A Sample `ij` Client/Server Session