Accessing Data with JDBC

hdd1We’ve talked about databases in previous posts, as well as the Java programming language and associated tools.  Now we can bring those topics together and learn how to access databases from within your Java programs.

Java Database Connectivity

We’ve learned in pass posts how to perform some basic operations against a database like MySQL using simple command line utilities as well as custom graphical tools.  What we need now is a library to perform these same functions from within a Java program.

For this we turn to the Java Database Connectivity (JDBC) API.  Like any API, JDBC provides a standard mechanism for Java programs to interact with databases, regardless of the type of database in question.  Database manufacturers, such as MySQL, create and distribute implementations of the JDBC API for their products, called connectors, for use by anyone building Java applications.

Other database APIs are available, notably Microsoft’s Open Database Connectivity (ODBC).  ODBC is generally available for Microsoft .NET environment, but ‘bridges’ are available to use ODBC drivers on other platforms.  For this post, we’ll stick to the JDBC connector for MySQL.

Setting Up Your Database

We’ll continue with the example we created in the MySQL post using 2 tables: customer and purchase.  As before, we won’t bother setting up MySQL on our local development machine, but instead reach out directly to an experimental partition on our production database.  To review, or tables are formatted as follows:

 

customer

purchase

 

 

 

 

As we can see from displaying the first rows of each table using MySQL Workbench, we have one record in each table, with a relation between the two as a foreign key, linking every purchase to a  customer.  Graphically in Workbench:

schema2

purchase_relation

 

Setting Up Your JDBC Project

As always, we’ll be doing our development and testing in Eclipse, using Maven for project management.  To begin, launch Eclipse, and click File -> New -> Other -> Maven -> Maven Project.  Choose the maven-archetype-quickstart for the archetype on which you’ll base your project.  Give your project a name (Artifact Id), say JdbcProject, and a package (Group Id), say net.proloquor.experimental, and click Finish.

As we’ve seen before, the maven-archetype-quickstart sets up a basic console application with an example class (App.java) with accompanying Junit unit tests.  All we need to do is add the appropriate library from the mysql-connector-java artifact.  Open the pom.xml file in Eclipse (using the Maven POM Editor), click on the Dependencies tab, and then the Add… button.

maven_connector

Use the search bar to find the appropriate library.  Once found, select the latest version (5.1.35 as of this writing) and click OK to add it to your project.

If you can’t find the right library, make sure that Eclipse has a listing of all the available artifacts.  To do this, go to Window -> Preferences -> Maven and make sure ‘Download repository index updates on startup’ is checked.

maven_preferences

This will make Eclipse slower to start up, particularly just after this feature is turned on, but it will make sure you have access to all the available artifacts.

Building A Sample JDBC Application

For the sake of expediency, let’s work in the main() method of the App.java class provided with the archetype.

Setting Up The Database Connection

First, you need to initialize the JDBC driver.  This must be done explicitly for library classes that have static code blocks, like the MySQL connector.

public static void main(String[] args) {
   try {

      /* Initialize the JDBC Driver */
      Class.forName("com.mysql.jdbc.Driver");

   } catch (ClassNotFoundException e) {
      e.printStackTrace();
   }
   .
   .
   .

Next, create a connection to the database from the provided factory class.

   java.sql.Connection connection = null;
   try {

      /* Connect to the database */
      connection = 
         java.sql.DriverManager.getConnection(
         "jdbc:mysql://mydomain.com:3306/mydatabase", 
         "myusername", 
         "mypassword");
   } catch (java.sql.SQLException e) {
      e.printStackTrace();
   } finally {

      /*  
         If the connection to the database is open,
         close it.
      */
      if(connection != null) {
         connection.close();
      }
   }
   .
   .
   .

Now we’re ready to issue commands to the database.

Retrieving Data

SQL is a rich language with many facets.  Creating an API with all those features would be a daunting task, so the JDBC does the next best thing; it provides only a few functions that take raw Structured Query Language (SQL) strings as input.  With JDBC, you are essentially embedding SQL into your java code.  As such, you are responsible for adjusting your SQL statements for the dialect of your database, MySQL in this case.

For example, if you wanted to print the first and last name of all customers, you create a statement and execute it with the given SQL command.

   java.sql.Statement statement = null;
   java.sql.ResultSet results = null;   
   try {

      /* Create a new statement, and execute a SELECT. */
      statement = connection.createStatement();
      results = statement.executeQuery(
         "SELECT * FROM customer");
   } catch (java.sql.SQLException e) {
      e.printStackTrace();
   } finally {
      if(results != null) {
         results.close();
      } 
      if (statement != null) {
         statement.close();
      }
   }
   .
   .
   .

Use java.Sql.Statement.executeQuery() to execute SQL commands that return records.  To retrieve them, you can walk through each record with the next() method of the returned java.sql.ResultSet, then access each field as a hash.

   try {

      /* Walk through the results. */
      while(results.next()) {
         System.out.println( 
            rs.getString("given_name") + " " +
            rs.getString("surname"));
      }
   } catch (java.sql.SQLException e) {
      e.printStackTrace();
   } finally {
      if(results != null) {
         results.close();
      }
   }
   .
   .
   .

Inserting New Records

When you need to insert or delete records from the database, your code will look a lot like that above.  The big difference will be the use of java.sql.executeUpdate() to issue your commands, rather than executeQuery().  executeUpdate() takes the same single string as a parameter, representing the SQL statement you want to run.  It returns the number of records affected.

try {
   /* 
      Create a statement and execute an INSERT a new customer.
   */ 
   connection = DriverManager.getConnection(DB_URL, USR, PASS);   statement = connection.createStatement();
   statement.executeUpdate( 
      "insert into customer (given_name, surname, street_address, city, state, zipcode, phone, email_address) " + 
      "values ('Dave', 'Miller', '302 3rd Ave', 'Burbank', 'CA', '93020', '(921)232-9282', 'Dave@aol.com')"); 

   /*
      Use the statement to retrieve the primary key (id) of 
      the recorded inserted last.
   */
   results = statement.executeQuery(
      "select last_insert_id() as last_id"); 
   rs.next(); 
   int id = rs.getInt("last_id"); 

   /* 
      Finally insert a new purchase, using the id of the
      inserted customer for the customer_id foreign key.
   */
   statement.executeUpdate( 
      "insert into purchase (description, sku, quantity, price, customer_id) " + 
      "values ('Screwdriver', '2039243', '1', '14.99', " + id + ")"); 
} catch (Exception e) {
   e.printStackTrace();
}

Here we added a new purchase, as well as a new customer that made it.  We INSERT the customer record first, retrieve the primary key (id), and use it for the customer_id foreign key when we INSERT the purchase.  Because the MySQL macro last_insert_id() applies only to the current connection, there’s no worry that some other action will ‘sneak in’ an insert before you call it.

CustomerPurchase

Transactions

As we’ve seen, most database operations require several statements to complete.  But if one of those statements fail, you would need to undo the remaining statements as well.  If you couldn’t add the purchase, for example, you probably want to undo the addition of the new customer.

JDBC and the databases it supports can group such statements into transactions.  A transaction is an atomic operation on a database that must be completed in its entirety, or not at all, no matter how many statements it has.

The most basic way to manage transaction in your JDBC application is to control the auto-commit behavior of your statements.  By default, every statement you execute takes effect immediately on the database.  This behavior can be changed, instead delaying execution until your program explicitly commits them.  If there’s an error, your catch block can simply rollback the pending statements, so they never execute.

Updating our example:

try {
   /* 
      Create a statement and execute an INSERT a new customer.
   */ 
   connection = DriverManager.getConnection(DB_URL, USR, PASS);

   connection.setAutoCommit(false); /* turn off auto-commit */

   statement = connection.createStatement();
   statement.executeUpdate( 
      "insert into customer (given_name, surname, street_address, city, state, zipcode, phone, email_address) " + 
      "values ('Dave', 'Miller', '302 3rd Ave', 'Burbank', 'CA', '93020', '(921)232-9282', 'Dave@aol.com')"); 

   /*
      Use the statement to retrieve the primary key (id) of 
      the recorded inserted last.
   */
   results = statement.executeQuery(
      "select last_insert_id() as last_id"); 
   rs.next(); 
   int id = rs.getInt("last_id"); 

   /* 
      Finally insert a new purchase, using the id of the
      inserted customer for the customer_id foreign key.
   */
   statement.executeUpdate( 
      "insert into purchase (description, sku, quantity, price, customer_id) " + 
      "values ('Screwdriver', '2039243', '1', '14.99', " + id + ")"); 

   connection.commit(); /* Commit all changes to the DB */

} catch (Exception e) {
   e.printStackTrace();
  
   connection.rollback(); /* Rollback changes if there's a problem */

}

Finally, you can also set ‘savepoints’ with java.sql.Connection.setSavePoint() to control how far the commit and rollback functions take effect back through the transaction.

What’s Next?

The JDBC driver is a great way to manage storage solutions for small projects, but can get unwieldy as the project grows.  One of its main drawbacks is the fact that the SQL strings cannot be checked at compile time, and therefor must rely on extensive testing to find errors.  We’ll shortly cover more modern Data Transfer Object (DTO) technologies like Hibernate that address these problems.

Leave a Reply

Your email address will not be published. Required fields are marked *