Java

Talking to MySQL: Your First Look at JDBC

    Kevin Yank
    Share

    To build the To-Do List application we will use to explore the myriad Java Web development technologies that are available, we’ll begin with the backbone of the application: a set of classes that connect to a MySQL database to retrieve, model, and update a to-do list.

    Before you go any further, you’ll need a working knowledge of Java as a programming language. At the very least, you should understand the basic concepts of object oriented programming: classes, objects, methods, properties, and constructors. For an overview of these concepts and the necessary basics of Java programming, see my articles: Getting Started with Java, Java Language Basics, and Object Oriented Concepts in Java (part one and two).

    Since we’re connecting to a database, we will be using the Java Database Connectivity (JDBC) classes, which are part of the Java 2 Enterprise Edition (J2EE) platform, supported by all Java-enabled Web servers. These classes provide a common set of classes for connecting to any database server.

    In order to connect to MySQL with JDBC, we need to grab a copy of MySQL Connector/J, the JDBC driver for MySQL. After telling Java to load the driver, we can ask it to connect to a MySQL database and all of the standard JDBC classes will know how to work with that connection.

    Download and extract the MySQL Connector/J archive. Inside you’ll find a mysql-connector-java-version-bin.jar file (version 3.0.15 as of this writing), which is the driver library. Ignore the rest of the fluff and copy the JAR file to the working directory where you plan to build your to-do list Java application.

    First of all, let’s build a class called ToDoList, which will represent the to-do list stored in the database and will allow us to access and modify its entries. We’ll put this class in a Java package (or namespace) called com.sitepoint, so in your working directory create a directory called com with a subdirectory called sitepoint, and in it create a file named ToDoList.java. Start the file out with the required package declaration:

    package com.sitepoint;

    In this class, we’ll use the JDBC classes from the java.sql and the collection classes from java.util, so let’s import those:

    import java.util.*;
import java.sql.*;

    And finally, we’ll declare our class:

    public class ToDoList {

    To keep the class as database-independent as possible, we’ll design its constructor to accept the name of a JDBC driver and a JDBC connection string, which together give it everything it needs to connect to a database through JDBC. The constructor will load the driver class immediately, and then store the JDBC connection string into a private property, so that the object can use it to connect to the database when it needs to.

      private String jdbcConnectionString;

  public ToDoList(String jdbcDriver, String jdbcConnectionString) {
    this.jdbcConnectionString = jdbcConnectionString;

    // Load the driver
    try {
      Class.forName(jdbcDriver).newInstance();
    }
    catch (Exception ex) {
      System.err.println("Error loading database driver " + jdbcDriver +
                         ":n" + ex.getMessage());
    }
  }
    Class.forName() obtains a reference to the JDBC driver class, and calling its newInstance() method makes sure it is loaded. We catch any exceptions that may be thrown in case the driver class is not available (if you forget to do this, the compiler will remind you).
    


    Now, our first version of this class will allow Java programs (and Web pages) to get a list of the items on the to-do list, get the number of items on the list, add new items to the list, and delete items from the list. Because fetching the list from the database can be a time-consuming operation, we'll make our class keep a copy of the list once it has been fetched, and only update it from the database when necessary.
    


      private List list = new ArrayList();
  private boolean staleList = true;
    

    The list variable will store the list retrieved from the database, and the second will keep track of when that list needs updating (i.e. when it is "stale"). We start off with an empty ArrayList (Java's stock-standard list class, from the java.util package), which is assumed to be in need of updating.
    

    Now, whether the program wants the actual list of to-do items or it simply wants to know how many of them there are, we will need to fetch the list from the database. So let's write a single private method that checks if the stored list (in the list property) is stale and loads the list from the database if necessary:
    

      private void refreshList() {
    if (staleList) {
      // Load list from the database...

      staleList = false;
    }
  }
    

    Loading the list from the database is where JDBC comes in. We first use the JDBC connection string (stored by the constructor in jdbcConnectionString) to connect to the database. Since this is a time-consuming operation, we'll store the connection in another private property called conn, and only create a new connection when it is empty:
    

            if (conn == null) {
          conn = DriverManager.getConnection(jdbcConnectionString);
        }
    

    Next, we create a Statement object from the connection and use it to execute a SELECT query and obtain the resulting ResultSet object:
    

            Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT todoid, todo FROM todo");
    

    With the results in hand, we store a new, empty ArrayList in the list property, and fill it with ToDoItem objects created from the contents of the result set:
    

            list = new ArrayList();
        while (rs.next()) {
          list.add(new ToDoItem(rs.getInt(1), rs.getString(2)));
        }
    

    This may be a confusing bit of code for several reasons. First, we're using a while loop to step through the result set. Calling the next() method of the result set advances it to the next record, or returns false if there are no records remaining--thus ending the while loop.
    

    Within the loop, we're using the add() method of the ArrayList class to add items to the list. But what exactly are we adding? New instances of the ToDoItem class.
    

    ToDoItem is a dead simple little class that keeps track of an ID (the database ID of a to-do list item) and a string (the text for that item). These two values are "read only", in that there are no methods provided for altering them. Objects like this that represent records in the database are called data objects. Create this class by pasting the following code into a file called ToDoItem.java alongside the ToDoList.java file you're already working on:
    


    package com.sitepoint;

public class ToDoItem {
  private int id;
  private String item;

  ToDoItem(int id, String item) {
    this.id = id;
    this.item = item;
  }

  public int getId() {
    return id;
  }

  public String getItem() {
    return item;
  }

  public String toString() {
    return getItem();
  }
}
    

    The only detail worthy of note in this simple class is that the constructor is not declared public, but is instead left package private, so that only classes within the com.sitepoint package can create ToDoItem objects.
    

    Returning to ToDoList, the constructor of ToDoItem expects to be given the ID and text values for the item. We can fetch these values from the current row of the result set, using getInt(1) to fetch an integer value from the first column (todoid) and getString(2) to get a string value from the second column (todo).
    

    That's it for filling up the list, except that to handle database errors from all this we much catch any SQLExceptions that might be thrown (again, the compiler will remind you if you forget). Here's the completed refreshList() method, and the delcaration for the conn property:
    

      private Connection conn;

  private void refreshList() {
    if (staleList) {
      try {
        if (conn == null) {
          conn = DriverManager.getConnection(jdbcConnectionString);
        }
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT todoid, todo FROM todo");

        list = new ArrayList();
        while (rs.next()) {
          list.add(new ToDoItem(rs.getInt(1), rs.getString(2)));
        }
      }
      catch (SQLException ex) {
        System.err.println(
            "Error retrieving to-do list items from the database:n" +
            ex.getMessage());
      }
      staleList = false;
    }
  }
    

    With the hard work done in refreshList(), implementing getToDoItems() and getItemCount(), to obtain the to-do list items and the number of such items respectively, is a cinch:
    

      public Iterator getToDoItems() {
    refreshList();
    return list.iterator();
  }

  public int getItemCount() {
    refreshList();
    return list.size();
  }
    

    The iterator() method of Java's list classes returns an Iterator, an object much like the database result set we just worked with, that lets the requesting object step through the list. size(), meanwhile, is self-explanatory.
    

    The addItem() and deleteItem() methods work much the same way as refreshList(), except they use a PreparedStatement instead of a simple Statement. Consider this code from addItem:
    

          PreparedStatement stmt = conn.prepareStatement(
          "INSERT INTO todo (todo) VALUES (?)");
      stmt.setString(1, item);
      stmt.executeUpdate();
    

    Notice the question mark in the INSERT query. This indicates a spot where some unknown value will be added to the query (the text for the new to-do list item, in this case). The setString(1, item) method call that follows assigns a string value (provided by the item variable in this case) to the first spot in the query. Other methods like setInt() let you stick in values of other types. Sticking values into your database queries in this way neatly sidesteps any character escaping issues that often come into play with other languages.
    

    Here's the complete code for addItem() and deleteItem(), thus completing our ToDoList class:
    

      public void addItem(String item) {
    try {
      if (conn == null) {
        conn = DriverManager.getConnection(jdbcConnectionString);
      }
      PreparedStatement stmt = conn.prepareStatement(
          "INSERT INTO todo (todo) VALUES (?)");
      stmt.setString(1, item);
      stmt.executeUpdate();
    }
    catch (SQLException ex) {
      System.err.println(
          "Error adding a to-do list item to the database:n" +
          ex.getMessage());
    }
    staleList = true;
  }

  public void deleteItem(int id) {
    try {
      if (conn == null) {
        conn = DriverManager.getConnection(jdbcConnectionString);
      }
      PreparedStatement stmt = conn.prepareStatement(
          "DELETE FROM todo WHERE todoid=?");
      stmt.setInt(1, id);
      stmt.executeUpdate();
    }
    catch (SQLException ex) {
      System.err.println(
          "Error deleting a to-do list item from the database:n" +
          ex.getMessage());
    }
    staleList = true;
  }
    

    Notice that each of these methods sets staleList to true once it's done its job, so that the next attept to read the to-do list will cause refreshList to fetch it from the database again.
    

    All we need now is a class to test the work we've done so far. Here's a class called ToDoTest that'll do the trick. Put ToDoTest.java in with your other files:
    

    package com.sitepoint;

import java.util.*;

public class ToDoTest {
  public static void main(String[] args) {
    ToDoList list = new ToDoList("com.mysql.jdbc.Driver",
        "jdbc:mysql://localhost/todo?user=root&password=password");
    
    System.out.println("The to-do list contains " + list.getItemCount() +
                       "items:");
    Iterator it = list.getToDoItems();
    while (it.hasNext()) {
      System.out.println(" - " + it.next());
    }
    
    list.addItem("Another todo item.");
    list.addItem("And yet another.");
    
    System.out.println("The to-do list contains " + list.getItemCount() +
                       " items:");
    it = list.getToDoItems();
    while (it.hasNext()) {
      System.out.println(" - " + it.next());
    }    
  }
}
    

    You'll need to substitute in your MySQL database name, user name, and password into the JDBC connection string near the top of this file.
    

    Compile your three classes from the top-level working directory:
    

    javac com/sitepoint/ToDoList.java com/sitepoint/ToDoItem.java 
com/sitepoint/ToDoTest.java


    Then run ToDoTest, with the MySQL JDBC driver added to the classpath:
    


    

    java -classpath .;mysql-connector-java-3.0.15-ga-bin.jar 
com.sitepoint.ToDoTest
    

    You should see a list of the to-do list items before and after adding two new items to the list, something like this:
    

    The to-do list contains 2 items:
 - Another todo item.
 - And yet another.
The to-do list contains 4 items:
 - Another todo item.
 - And yet another.
 - Another todo item.
 - And yet another.
    

    That's it! We're ready to build a Web application around these classes.
    

    Download the source code (5KB)
    CSS Master, 3rd Edition