Build a Derby calendar, Part 1: Understanding JDBC

Build a calendar and reminder application using the Java™ language and the Apache Derby database in Part 1 of this three-part tutorial series. This tutorial starts by showing you how to install the database and exploring interactions with it through Java Database Connectivity (JDBC). Parts 2 and 3 will examine different options for including Derby in your application and Derby's transactional capabilities as we move from a single-user to a multiuser system. Finally, we'll move to a multimode system that includes a Web interface.

Share:

Nicholas Chase (ibm@nicholaschase.com), Freelance Writer, Backstop Media

Nicholas Chase has been involved in Web site development for companies such as Lucent Technologies, Sun Microsystems, Oracle, and the Tampa Bay Buccaneers. Nick has been a high school physics teacher, a low-level radioactive waste facility manager, an online science fiction magazine editor, a multimedia engineer, an Oracle instructor, and the Chief Technology Officer of an interactive communications company. He is the author of several books, including .



13 September 2005

Before you start

In this tutorial, you'll begin the process of building a calendar and reminder system that uses Apache Derby as its database back end. In Parts 2 and 3 of this series, you'll add two different user interfaces to the application; but for now the tutorial concentrates on accessing the data and on setting appropriate reminders using e-mail.

You'll learn about the following:

  • Basic Derby concepts
  • Installing and interacting with Derby in embedded mode
  • Connecting to Derby using JDBC
  • Adding data to Derby using JDBC
  • Selecting and viewing Derby data using JDBC
  • Updating existing Derby data using JDBC
  • Using the Java language to send e-mail reminders

Prerequisites

This tutorial was created under the assumption that you're familiar with programming in the Java language, but unfamiliar with JDBC and Apache Derby. Familiarity with SQL is helpful, but not required.

System requirements

To follow along with this tutorial, you need:

  • Java 2 Platform, Standard Edition (J2EE) 1.4.2 or higher. You must have the Java Developer Kit, not just the Java Runtime Environment (JRE). Download Java code from the Sun Web site.
  • Apache Derby, which you can download from the Apache Software Foundation.
  • JavaMail. To send e-mail reminders, you need mail.jar, which is now part of the Java package. You also need activation.jar, downloadable as part of the JavaBeans activation framework. Make sure both files are in your class path.
  • A text editor or Integrated Development Environment (IDE). You can easily create Derby applications using an IDE, such as Eclipse, but I'm going to assume you're using a simple text editor.

Calendar overview

Before we dive into building the application, let's look at what we're ultimately trying to accomplish.

The calendar and reminder system

Over the course of this three-part series, you'll build a system designed to let the user enter events (such as meetings and birthdays) and view existing events. The system also e-mails reminders to the address specified on the event.

In this tutorial, you'll build the basic Event, Calendar, and Reminder classes, which take the title, description, date, and e-mail address to receive reminders. You'll look at inserting, updating, and deleting events and at extracting the data from Derby for display. In Parts 2 and 3, you'll incorporate these classes into the application.

Expansion options

The first version of the user interface will be a Java GUI intended to be used by an individual. It will use Derby's embedded mode, a single-user mode in which the database is distributed as part of the application.

Then we'll then move on to a multiuser version in which you still use the Java GUI, but you'll use Derby's network mode in which multiple clients can access the same database. You'll embed the database in a Web application server and create a Web-based interface for the calendar system. Finally, we'll look at some of Derby's advanced features, such as locking and transactions.

How Derby fits in

Apache Derby is the open source center of the IBM Cloudscape database and, as such, shares many of its features, such as a small footprint and the ability to easily embed it in an application. Derby also provides an easy upgrade path to other databases, such as IBM DB2™. Because you interact with it using JDBC, changing database systems can be relatively painless.

In this case, however, we'll use Derby features, such as low-level locking and a network framework.

Derby is implemented as a group of *.jar files, which is why it's relatively straightforward to integrate it with your application classes.

The architecture

The basic application consists of three classes: Event, Calendar, and Reminder (see Figure 1).

Figure 1. The three classes of the basic application
Three classes of the basic application

The Event class receives and stores the data. In addition to getters and setters for each of the attributes, there are methods to create, update, and delete individual records. There are constructors that simplify the creation and management of individual records.

The Calendar class is intended to provide the data displayed on the user interface. For now, it will simply output text, but ultimately it will probably be refactored to return an array of event objects.

The Reminder class generates e-mail reminder messages. For now, we'll explore sending individual messages and sending groups of messages based on the event date.


Setting up the database

Before building the application, you must install Derby.

Install the Derby software

Installing the Derby software is simple. Just download the archive file, and unpack it into the directory in which you want it to live.

Each database you create will have its own directory in this location. There is no installation process, but you will need to set some environment variables.

Preparing the environment

Because Derby is a Java-based system, make sure all of the appropriate classes are available in the class path before you can run it. The easiest way to do that is to use the sysinfo script provided with the Derby distribution. The software comes with two different versions of this script: one for embedded mode and one for network mode. In this case, use the embedded version in the frameworks\embedded\bin directory.

First, set the DERBY_INSTALL variable, as shown in Listing 1.

Listing 1. Setting the DERBY_INSTALL variable
C:\derby>set DERBY_INSTALL=c:\derby

C:\derby>set PATH=%PATH%;

C:\derby>sysinfo

C:\derby>rem set DERBY_INSTALL=

C:\derby>rem set DERBY_INSTALL=

C:\derby>FOR %X in ("c:\derby") DO SET DERBY_INSTALL=%~sX

C:\derby>SET DERBY_INSTALL=c:\derby

C:\derby>set CLASSPATH=c:\derby\lib\derby.jar;c:\derby\lib\derbytools.jar;

C:\derby>java org.apache.derby.tools.sysinfo
------------------ Java Information ------------------
Java Version:    1.5.0_02
Java Vendor:     Sun Microsystems Inc.
Java home:       C:\Program Files\Java\jre1.5.0_02
Java classpath:  c:\derby\lib\derby.jar;c:\derby\lib\derbytools.jar;
OS name:         Windows 2000
OS architecture: x86
OS version:      5.0
Java user name:  Administrator
Java user home:  C:\Documents and Settings\Administrator
Java user dir:   C:\derby
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[C:\derby\lib\derby.jar] 10.1.1.0 - (208786)
[C:\derby\lib\derbytools.jar] 10.1.1.0 - (208786)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

The sysinfo script shows you the .jar files that are used as well as other relevant information.

Running the ij command line client

The Derby distribution also comes with a command line tool you can use to create and interact with your databases. The tool, ij, enables you to connect to your database using JDBC and to execute SQL commands. Because you have already added the bin directory to your path, you can simply type ij to start the tool, as shown in Listing 2.

Listing 2. Running the ij command line client
C:\derby>ij

C:\derby>rem set DERBY_INSTALL=

C:\derby>java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij version 10.1
ij>\

Now you're ready to create the database.

Create the database

Where most database software makes you jump through a number of hoops to create a new database, Derby enables you to do it by simply making a connection to the database you want to create.

For example, to make a JDBC connection to your calendar database after it has been created, you can use the connect string jdbc:derby:calendar. The jdbc:derby: is the protocol (similar to the http: you'd use for a Web link), and calendar, of course, is the name of the database.

To create the calendar database, simply add the create parameter to the connect string jdbc:derby:calendar:create=true.

Derby enables you to send various parameters by adding them to the connect string. For example, in Part 2 of this series, you'll add a database, username, and password.

To create the calendar database, connect to it in the ij window:

ij> connect 'jdbc:derby:calendar;create=true';

Test the installation

Now that you've created the empty database, you can create a table to test it:

ij> create table testtable (
thetext varchar(255));
0 rows inserted/updated/deleted

Notice first that a standard SQL create statement is used to create the table. Also, ij won't act on the command until you enter a semicolon. To test the table creation, insert a sample record, as shown in Listing 3.

Listing 3. Inserting a sample record
ij> insert into testtable (thetext) values ('my text');
1 row inserted/updated/deleted
Finally, select the data you just inserted:
ij> select * from testtable;
THETEXT

----------------------------------------------------------------------------
------------------------------------------------
my text

1 row selected

Create the Event table

Now that everything is up and running, you can create the Event table, as shown in Listing 4.

Listing 4. Creating the Event table
ij> create table Event (
id INT GENERATED ALWAYS AS IDENTITY, 
title VARCHAR(50), 
description VARCHAR(255), 
remindersTo VARCHAR(255),
eventMonth INT, 
eventDay INT, 
eventYear INT);
0 rows inserted/updated/deleted

After you've created the table, exit ij by typing exit. If the window hangs, press Ctrl-C to exit.


Inserting data: events

Now you can start creating the application, starting with the Event class.

The basic event class

First, create the new file, event.java, and add the basic getters and setters (see Listing 5).

Listing 5. Creating the event.java file
public class Event {

   public  Event(){}

   public  Event(int eventId) {
       setId(eventId);
   }

   public Event(String newTitle, String newDescription, 
                String newRemindersTo, int eventMonth, 
                int eventDay, int eventYear){
   }

   private int eventDay;
   public int getEventDay() {
      return this.eventDay;
   }
   public void setEventDay(int value){
      this.eventDay = value;
   }

   private int eventMonth;
   public int getEventMonth() {
      return this.eventMonth;
   }
   public void setEventMonth(int value) {
      this.eventMonth = value;
   }

   private int eventYear;
   public int getEventYear() {
      return this.eventYear;
   }
   public void setEventYear(int value){
      this.eventYear = value;
   }

   private String description;
   public String getDescription() {
      return this.description;
   }
   public void setDescription(String value) {
      this.description = value;
   }

   private int id;
   public int getId() {
      return this.id;
   }
   public void setId(int value){
      this.id = value;
   }

   private String remindersTo;
   public String getRemindersTo() {
      return this.remindersTo;
   }
   public void setRemindersTo(String value) {
      this.remindersTo = value;
   }

   private String title;
   public String getTitle() {
      return this.title;
   }
   public void setTitle(String value) {
      this.title = value;
   }

   public boolean create(String newTitle, String newDescription, 
                         String newRemindersTo, int eventMonth, 
                         int eventDay, int eventYear) {

       return true;
   }


   public boolean delete()
   {
       return true;
   }

   public boolean update(String newTitle, String newDescription, 
                    String newRemindersTo, int newMonth, int newDay, 
                                                         int newYear){
      return true;
   }

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

I've also added the constructors and stubs for the create(), update(), and delete() methods.

If you've opened a separate window to compile this class, you can run sysinfo to set the CLASSPATH.

Accept the event information

Listing 6 shows how to provide a way for the user to enter event information from the command line.

Listing 6. Accepting event information
public class Event {

   public  Event(){}

   public  Event(int eventId) {
       setId(eventId);
   }

   public Event(String newTitle, String newDescription, 
                String newRemindersTo, int eventMonth, 
                int eventDay, int eventYear){
       create(newTitle, newDescription, newRemindersTo, 
                           eventMonth, eventDay, eventYear);
   }

...

   public boolean create(String newTitle, String newDescription, 
                         String newRemindersTo, int eventMonth, 
                         int eventDay, int eventYear) {

       setTitle(newTitle);
       setDescription(newDescription);
       setRemindersTo(newRemindersTo);
       setEventMonth(eventMonth);
       setEventDay(eventDay);
       setEventYear(eventYear);       
       
       System.out.println("Creating event for " + this.getEventMonth()+
                             "/" + this.getEventDay() + "/" + 
                                               this.getEventYear());
       System.out.println(this.getTitle());
       System.out.println(this.getDescription());
       System.out.println("Reminders to: " + this.getRemindersTo());

       return true;
   }


   public boolean delete()
   {
       return true;
   }

   public boolean update(String newTitle, String newDescription, 
                    String newRemindersTo, int newMonth, int newDay, 
                                                         int newYear){
      return true;
   }

   public static void main (String args[]){

        if (args.length < 6){
            System.out.println("Usage:  Event <eventId> <title> "+
                                  "<description> <remindersTo> "+
                                  "<month> <day> <year>");
        } else {
  
            String newTitle = args[0];
            String newDescription = args[1];
            String newReminders = args[2];
            int newMonth = Integer.parseInt(args[3]);
            int newDay = Integer.parseInt(args[4]);
            int newYear = Integer.parseInt(args[5]);
            Event theEvent = newEvent(newtitle, newdescription,
                             newreminders, newmonth, newday, newyear)

        }
   }
}

Let's start at the bottom with the main() method. To keep things as simple as possible (and because ultimately the main() method is only for testing our functionality), you're only accepting the information as arguments. After you've pulled it out of the args array, create a new Event object.

The constructor then calls the create() method, which simply displays the information, as shown in Listing 7.

Listing 7. Information displayed from the create() method
C:\>java Event
Usage:  Event <title> <description> <remindersTo> <month> <day> <year>

C:\>java Event 
"Going to New Jersey" 
"Bring up dad's bubble gum machines in the car." 
"questions@nicholaschase.com" 8 25 2005
Creating event for 8/25/2005
Going to New Jersey
Bring up dad's bubble gum machines in the car.

Now let's look at adding the information to the database.

Connect to the database

The first step is to make a connection to the database, as shown in Listing 8.

Listing 8. Connecting to the database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class Event {

   public String driver = 
                  "org.apache.derby.jdbc.EmbeddedDriver";


   public  Event(){}

...

   public boolean create(String newTitle, String newDescription, 
                         String newRemindersTo, int eventMonth, 
                         int eventDay, int eventYear) {

       setTitle(newTitle);
       setDescription(newDescription);
       setRemindersTo(newRemindersTo);
       setEventMonth(eventMonth);
       setEventDay(eventDay);
       setEventYear(eventYear);       
       
       System.out.println("Creating event for " + this.getEventMonth()+
                             "/" + this.getEventDay() + "/" + 
                                               this.getEventYear());
       System.out.println(this.getTitle());
       System.out.println(this.getDescription());
       System.out.println("Reminders to: " + this.getRemindersTo());

       try {
            Class.forName(driver).newInstance();
            Connection conn = null;
            conn = DriverManager.getConnection(
                                 "jdbc:derby:c:\\derby\\calendar");
            conn.setAutoCommit(true);

            conn.close();

       } catch (Exception e){
            e.printStackTrace();
            return false;
       }

       return true;
   }
...

Start by adding the relevant imports (you'll use the ResultSet and PreparedStatement classes later).

You're also specifying the JDBC DriverManager class, the application you'll use to connect to the database. Notice that you're using the embedded driver rather than the network driver.

To make the connection, start by instantiating the DriverManager class, EmbeddedDriver. With that object in place, you can use it to create the connection. You're using the connect string from the Create the database section. After you've created the connection, set autoCommit to true so commands are finalized immediately (Part 3 of this series will provide more detail).

Finally, make sure to close the connection.

Create the insert statement

Before you can insert the data, you must create the insert statement, shown in Listing 9.

Listing 9. Creating the insert statement
...
   public boolean create(String newTitle, String newDescription, 
                         String newRemindersTo, int eventMonth, 
                         int eventDay, int eventYear) {

...
       try {
            Class.forName(driver).newInstance();
            Connection conn = null;
            conn = DriverManager.getConnection(
                                 "jdbc:derby:c:\\derby\\calendar");
            conn.setAutoCommit(true);

            String sql = "insert into Event (title, description, "+
                         "remindersTo, eventMonth, eventDay, "+
                         "eventYear)" +
                         "values ('"+this.getTitle()+"', '"
                                  +this.getDescription()+"', '"+
                                  this.getRemindersTo()+"', "
                                  +this.getEventMonth()+", "+
                                  this.getEventDay()+", "+
                                  this.getEventYear()+")";
            System.out.println(sql);

            Statement s = conn.createStatement();
            s.close(); 
            conn.close();

       } catch (Exception e){
            e.printStackTrace();
            return false;
       }

       return true;
   }
...

First, create the SQL statement. This is a standard SQL statement in which you list the columns and their values. You don't need to specify the id column because you've already told Derby to generate that as part of the table-creation process.

Next, create the Statement object, which you'll use to execute the SQL statement, and make sure to close it.

Execute the insert statement

Executing the SQL statement is simple; just use the execute() method, shown in Listing 10.

Listing 10. Executing the insert statement
...
       try {
            Class.forName(driver).newInstance();
            Connection conn = null;
            conn = DriverManager.getConnection(
                                 "jdbc:derby:c:\\derby\\calendar");
            conn.setAutoCommit(true);

            String sql = "insert into Event (title, description, "+
                         "remindersTo, eventMonth, eventDay, "+
                         "eventYear)" +
                         "values ('"+this.getTitle()+"', '"
                                  +this.getDescription()+"', '"+
                                  this.getRemindersTo()+"', "
                                  +this.getEventMonth()+", "+
                                  this.getEventDay()+", "+
                                  this.getEventYear()+")";
            System.out.println(sql);

            Statement s = conn.createStatement();
            s.execute(sql);
            s.close(); 
            conn.close();

       } catch (Exception e){
            e.printStackTrace();
            return false;
       }

       return true;
   }


...

Now you can run the application again to insert the data.

It's important to understand that this way of adding data is simple but has serious limitations. Security concerns aside, you can't use this method to enter data with an apostrophe ('), for example. In the Inserting data, revisited section, you'll see a better way to handle this; but it's important to know that you can use this method for simple SQL operations.

Checking the results

To make sure the application is working, use ij to execute a select statement, as shown in Listing 11.

Listing 11. Checking the results
C:\derby>ij

C:\derby>rem set DERBY_INSTALL=

C:\derby>java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:calendar';
ij> select * from Event;
ID         |TITLE                                       |DESCRIPTION

                               |REMINDERSTO

|EVENTMONTH |EVENTDAY   |EVENTYEAR
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
------------------------------------
1          |Going to New Jersey                    |Bring up Dad's bub
blegum machines in the car.
                               |questions@nicholaschase.com

|8          |25         |2005

1 row selected
ij> exit

Before we move on, however, there's still one thing to do.

Shut down the database

By default, Derby shuts down when the Java Virtual Machine (JVM) exits, but you should exclusively shut it down, as shown in Listing 12.

Listing 12. Shut down the database
...
            Statement s = conn.createStatement();
            s.execute(sql);
            s.close(); 
            conn.close();

            try {
               DriverManager.getConnection(
                                  "jdbc:derby:;shutdown=true");
            } catch (SQLException se) { }

       } catch (Exception e){
            e.printStackTrace();
            return false;
       }

       return true;
   }


...

Notice the connection string here. You're connecting to the Derby engine and telling it to shut down. By definition, this operation throws a SQL Exception to show that it has completed correctly. So we'll just catch it and move on.


Selecting data

There's no point in putting data in a database if you can't get it out again. Now let's look at selecting data.

The basic calendar class

First, create the skeleton calendar.java file, as shown in Listing 13.

Listing 13. Create calendar.java
public class Calendar {

   public static void getEvents(int eventMonth, 
                         int eventDay, int eventYear) {
   }

   public static void main (String args[]){

      int month = Integer.parseInt(args[0]);
      int day = Integer.parseInt(args[1]);
      int year = Integer.parseInt(args[2]);
      getEvents(month, day, year);
   }
}

Because you'll be calling getEvents() as a utility method, make it static. Then, once again, test from the main() method.

Create the select statement

The process for selecting data starts out just as the one you used for inserting data, as shown in Listing 14.

Listing 14. Creating the selecting statement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.ResultSet;

public class Calendar {

   public static String driver = 
               "org.apache.derby.jdbc.EmbeddedDriver";

   public static void getEvents(int eventMonth, 
                         int eventDay, int eventYear) {
      try {
         Class.forName(driver).newInstance();
         Connection conn = null;
         conn = DriverManager.getConnection(
                        "jdbc:derby:c:\\derby\\calendar");

         Statement s = conn.createStatement();
 

         s.close();
         conn.close();
         try {
            DriverManager.getConnection(
                              "jdbc:derby:;shutdown=true");
         } catch (SQLException se) { }

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

   public static void main (String args[]){

      int month = Integer.parseInt(args[0]);
      int day = Integer.parseInt(args[1]);
      int year = Integer.parseInt(args[2]);
      getEvents(month, day, year);
   }
}

As before, you're defining the DriverManager, making the connection, and creating a Statement object. Finally, close the statement and connection, and shut down Derby.

Execute the select statement

Executing a select statement (see Listing 15) differs from executing an insert statement. The most notable difference is that executeQuery() returns a ResultSet object.

Listing 15. Executing the select statement
...
   public static void getEvents(int eventMonth, 
                         int eventDay, int eventYear) {
      try {
         Class.forName(driver).newInstance();
         Connection conn = null;
         conn = DriverManager.getConnection(
                        "jdbc:derby:c:\\derby\\calendar");

         Statement s = conn.createStatement();
         ResultSet rs = s.executeQuery("SELECT * FROM Event "+
                            "where eventMonth="+eventMonth+
                            " and eventDay="+eventDay+" and "+
                            "eventYear="+eventYear);
         if (!rs.next()){
            System.out.println("There are no events for "+
                        eventMonth+"/"+eventDay+"/"+eventYear);
         } else {

         }

         rs.close();
         s.close();
         conn.close();
         try {
            DriverManager.getConnection(
                              "jdbc:derby:;shutdown=true");
         } catch (SQLException se) { }

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

You can use the next() method to try to advance to the next record in the result set. If there is no next record, the method returns false. Now let's look at what happens when there is a next record.

Displaying the selected data

If the ResultSet has at least one record, you can access the values for each column, as shown in Listing 16.

Listing 16. Displaying the selected data
...
      try {
         Class.forName(driver).newInstance();
         Connection conn = null;
         conn = DriverManager.getConnection(
                        "jdbc:derby:c:\\derby\\calendar");

         Statement s = conn.createStatement();
         ResultSet rs = s.executeQuery("SELECT * FROM Event "+
                            "where eventMonth="+eventMonth+
                            " and eventDay="+eventDay+" and "+
                            "eventYear="+eventYear);
         if (!rs.next()){
            System.out.println("There are no events for "+
                        eventMonth+"/"+eventDay+"/"+eventYear);
         } else {
            System.out.println(rs.getInt(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
            System.out.println(rs.getString(4));
            System.out.println(rs.getString(5)+"/"
			              +rs.getString(6)+"/"+rs.getString(7));
         }

         rs.close();
         s.close();
         conn.close();
         try {
            DriverManager.getConnection(
                              "jdbc:derby:;shutdown=true");
         } catch (SQLException se) { }

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

Accessing a column value is a matter of using the appropriate getxxx() method, with the column position passed as an argument. For example, because an int can be easily converted to a String, you can retrieve the id column using getString(1), but you could not get the title using getint(2). A getxxx() method exists for most common Java datatypes.

But what happens if you have more than one result?

Looping through multiple records

When you have more than one record, you can use the next() method to loop through them (see Listing 17).

Listing 17. Looping through multiple records
...
         if (!rs.next()){
            System.out.println("There are no events for "+
                        eventMonth+"/"+eventDay+"/"+eventYear);
         } else {
            System.out.println(rs.getInt(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
            System.out.println(rs.getString(4));
            System.out.println(rs.getString(5)+"/"
                     +rs.getString(6)+"/"+rs.getString(7));

            while (rs.next()) {
               System.out.println(rs.getInt(1));
               System.out.println(rs.getString(2));
               System.out.println(rs.getString(3));
               System.out.println(rs.getString(4));
               System.out.println(rs.getString(5)+"/"
                     +rs.getString(6)+"/"+rs.getString(7));
            }
         }

         rs.close();
         s.close();
         conn.close();
         try {
            DriverManager.getConnection(
                              "jdbc:derby:;shutdown=true");
         } catch (SQLException se) { }

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

...

You've already moved to the first record by virtue of the if condition, so you still need to process that record. You can then use a while loop to run through the rest of the records. When you've run out of records, next() returns false, and the while loop exits.


Updating data

Now let's look at making changes to information that's already in the database.

Finding the correct records

In the Event class, use the update() method to alter data already in the database (see Listing 18).

Listing 18. Finding the correct record
...
public class Event {
...
   public boolean update(String newTitle, String newDescription, 
                    String newRemindersTo, int newMonth, int newDay, 
                                                         int newYear){

       try {
          Class.forName(driver).newInstance();
          Connection conn = null;
          conn = DriverManager.getConnection(
                                    "jdbc:derby:c:\\derby\\calendar");
          conn.setAutoCommit(false);


          Statement s = conn.createStatement();

          String sql = "select * from Event where id= "+this.getId(); 
          ResultSet updateRs = s.executeQuery(sql);

          if (updateRs.next()){
               System.out.println("Record updated");
          } else {
               System.out.println("No such event");
          }

          updateRs.close();
          s.close();
          conn.commit();
          conn.close();
          
          try {
             DriverManager.getConnection(
                                   "jdbc:derby:;shutdown=true");
          } catch (SQLException se) { }
     } catch (Exception e){
          e.printStackTrace();
     }

      return true;
   }

   public static void main (String args[]){

        if (args.length < 7){
            System.out.println("Usage:  Event <eventId> <title> "+
                                  "<description> <remindersTo> "+
                                  "<month> <day> <year>");
        } else {
  
            int eventId = Integer.parseInt(args[0]);
            String newTitle = args[1];
            String newDescription = args[2];
            String newReminders = args[3];
            int newMonth = Integer.parseInt(args[4]);
            int newDay = Integer.parseInt(args[5]);
            int newYear = Integer.parseInt(args[6]);

            Event theEventToUpdate = new Event(eventId);
            theEventToUpdate.update(newTitle, newDescription, 
                                    newReminders, newMonth, 
                                    newDay, newYear);

        }
   }
}

Starting with the main() method, add the eventId as the first input parameter. Then create a new event using the constructor that takes the eventId. From there, feed the data to the update() method.

As before, you're connecting to the database; but this time you're setting autoCommit to false. I won't go into too much detail about that here; just know that you have to do this to update the data. Then you can create the Statement just as before, this time selecting the record that corresponds to the event you want to update. Note that you have added a commit() call. This is related to autoCommit being set to false.

Making the ResultSet updateable

Now it's time to perform a little database magic and make the cursor, which points to the current row of data, updateable, as shown in Listing 19.

Listing 19. Making the ResultSet updateable
...
   public boolean update(String newTitle, String newDescription, 
                    String newRemindersTo, int newMonth, int newDay, 
                                                         int newYear){

       try {
          Class.forName(driver).newInstance();
          Connection conn = null;
          conn = DriverManager.getConnection(
                                    "jdbc:derby:c:\\derby\\calendar");
          conn.setAutoCommit(false);


          Statement s = conn.createStatement();
          s.setCursorName("UPDATEABLESTATEMENT");

          String sql = "select * from Event where id= "+this.getId()+
                            " FOR UPDATE of title, description, "+
                               "remindersto, eventmonth, eventday, "+
                               "eventyear"; 
          ResultSet updateRs = s.executeQuery(sql);

          if (updateRs.next()){
               System.out.println("Record updated");
          } else {
               System.out.println("No such event");
          }

          updateRs.close();
          s.close();
          conn.commit();
          conn.close();
          
          try {
             DriverManager.getConnection(
                                   "jdbc:derby:;shutdown=true");
          } catch (SQLException se) { }
     } catch (Exception e){
          e.printStackTrace();
     }

      return true;
   }
...

First, you are explicitly setting a name for the Statement. You'll need to reference this name in a moment. Also, to make the cursor updateable, you need to alter the SQL statement to specify what you're going to update.

Creating the PreparedStatement

To update the database, you need to create a special kind of statement called a PreparedStatement, as shown in Listing 20.

Listing 20. Creating the PreparedStatement
...
          Statement s = conn.createStatement();
          s.setCursorName("UPDATEABLESTATEMENT");

          String sql = "select * from Event where id= "+this.getId()+
                               " FOR UPDATE of title, description, "+
                               "remindersto, eventmonth, eventday, "+
                               "eventyear"; 
          System.out.println(sql);

          ResultSet updateRs = s.executeQuery(sql);

          PreparedStatement ps = conn.prepareStatement(
                                   "update event set title=?, "+
                                       "description=?, "+
                                       "remindersTo=?,"+
                                       "eventMonth=?, eventDay=?, "+
                                       "eventYear=? where current of "+
                                          "UPDATEABLESTATEMENT");
          if (updateRs.next()){
               System.out.println("Record updated");
          } else {
               System.out.println("No such event");
          }

          updateRs.close();
          ps.close();
          s.close();
          conn.commit();
          conn.close();
          
...

A PreparedStatement enables you to specify placeholders in the SQL statement into which you will place data at run time.

Updating individual columns

After you've created the placeholders, you can specifically set data for each of them, as shown in Listing 21.

Listing 21. Updating individual columns
...
          PreparedStatement ps = conn.prepareStatement(
                                   "update event set title=?, "+
                                       "description=?, "+
                                       "remindersTo=?,"+
                                       "eventMonth=?, eventDay=?, "+
                                       "eventYear=? where current of "+
                                          "UPDATEABLESTATEMENT");
          if (updateRs.next()){
               ps.setString(1, newTitle);
               ps.setString(2, newDescription);
               ps.setString(3, newRemindersTo);
               ps.setInt(4, newMonth);
               ps.setInt(5, newDay);
               ps.setInt(6, newYear);
               System.out.println("Record updated");
          } else {
               System.out.println("No such event");
          }

          updateRs.close();
          ps.close();
          s.close();
          conn.commit();
          conn.close();
...

Just as with the getxxx() methods, the setxxx() methods exist for each Java type. Two arguments are passed to these methods. The first is the placeholder position, and the second is the actual data.

Now you just need to execute the PreparedStatement.

Saving the changes

To perform the update, use the executeUpdate() method, as shown in Listing 22.

Listing 22. Using the executeUpdate()
...
          PreparedStatement ps = conn.prepareStatement(
                                   "update event set title=?, "+
                                       "description=?, "+
                                       "remindersTo=?,"+
                                       "eventMonth=?, eventDay=?, "+
                                       "eventYear=? where current of "+
                                          "UPDATEABLESTATEMENT");
          if (updateRs.next()){
               ps.setString(1, newTitle);
               ps.setString(2, newDescription);
               ps.setString(3, newRemindersTo);
               ps.setInt(4, newMonth);
               ps.setInt(5, newDay);
               ps.setInt(6, newYear);
               ps.executeUpdate();
               System.out.println("Record updated");
          } else {
               System.out.println("No such event");
          }

          updateRs.close();
          ps.close();
          s.close();
...

Now you should be able to see the changes by running the application and either selecting the data using ij or using the Calendar class.

Deleting a record

No discussion of altering data would be complete without a look at removing it all together. You can execute a delete statement in much the same way that you originally inserted data (see Listing 23).

Listing 23. Deleting a record
...
public class Event {
...
   public boolean delete()
   {
       try {
          Class.forName(driver).newInstance();
          Connection conn = null;
          conn = DriverManager.getConnection(
                                  "jdbc:derby:c:\\derby\\calendar");

          Statement s = conn.createStatement();
          String sql = "delete from Event where id= "+this.getId();
          System.out.println(sql);

          int rowsDeleted = s.executeUpdate(sql);

          System.out.println(rowsDeleted+" record(s) deleted");
          s.close();
          conn.close();
          
          try {
             DriverManager.getConnection(
                                      "jdbc:derby:;shutdown=true");
          } catch (SQLException se) { }
        } catch (Exception e){
          e.printStackTrace();
       }

        return true;
   }
...

The executeUpdate() method returns the number of rows affected by the given query.


Inserting data, revisited

Now that you've seen the basics of interacting with Derby using JDBC, let's go back and improve the way you insert data in the first place.

Creating the prepared statement

You use much of the technique you used for updating data to insert a new record, as shown in Listing 24.

Listing 24. Creating the prepared statement
...
public class Event {
...
   public boolean create(String newTitle, String newDescription, 
                         String newRemindersTo, int eventMonth, 
                         int eventDay, int eventYear) {

       setTitle(newTitle);
       setDescription(newDescription);
       setRemindersTo(newRemindersTo);
       setEventMonth(eventMonth);
       setEventDay(eventDay);
       setEventYear(eventYear);       
       
       System.out.println("Creating event for " + this.getEventMonth()+
                             "/" + this.getEventDay() + "/" + 
                                               this.getEventYear());
       System.out.println(this.getTitle());
       System.out.println(this.getDescription());
       System.out.println("Reminders to: " + this.getRemindersTo());

       try {
          Class.forName(driver).newInstance();
          Connection conn = null;
          conn = DriverManager.getConnection(
                              "jdbc:derby:c:\\derby\\calendar");
          conn.setAutoCommit(false);

          PreparedStatement ps = conn.prepareStatement(
                         "insert intoEvent (title, description,"+
                         "remindersTo, eventMonth, "+
                         "eventDay, eventYear)" +
                         "values (?,?,?,?,?,?)");

          ps.close();
          conn.commit();
          conn.close();
          
          try {
             DriverManager.getConnection(
                                     "jdbc:derby:;shutdown=true");
          } catch (SQLException se) { }

     } catch (Exception e){
          e.printStackTrace();
     }
      return true;
   }
...

You've replaced the statement with a PreparedStatement that includes placeholders for the actual data.

Populate and execute the prepared statement

Now you can simply use the setxxx() methods to replace the placeholders with the appropriate data, as shown in Listing 25.

Listing 25. Populating and executing the prepared statement
...
       try {
          Class.forName(driver).newInstance();
          Connection conn = null;
          conn = DriverManager.getConnection(
                              "jdbc:derby:c:\\derby\\calendar");
          conn.setAutoCommit(false);

          PreparedStatement ps = conn.prepareStatement(
                         "insert intoEvent (title, description,"+
                         "remindersTo, eventMonth, "+
                         "eventDay, eventYear)" +
                         "values (?,?,?,?,?,?)");

          ps.setString(1, this.getTitle());
          ps.setString(2, this.getDescription());
          ps.setString(3, this.getRemindersTo());
          ps.setInt(4, this.getEventMonth());
          ps.setInt(5, this.getEventDay());
          ps.setInt(6, this.getEventYear());
          ps.executeUpdate();
          System.out.println("Record inserted");

          ps.close();
          conn.commit();
          conn.close();
          
          try {
             DriverManager.getConnection(
                                     "jdbc:derby:;shutdown=true");
          } catch (SQLException se) { }

     } catch (Exception e){
          e.printStackTrace();
     }
      return true;
   }
...

After you've populated the placeholders, execute the PreparedStatement.

The overall algorithm is the same as updating data, except that you don't need to locate the appropriate record first.


Setting reminders

Now that you can create, update, and delete events, you need a way to remind people about them.

The reminder class

Create the reminder.java file, and add the basic class (see Listing 26).

Listing 26. The reminder class
  public class Reminder{

   public void sendMessage(String reminderTo, String title, 
                           String description, String dateString) {
   }

   public void sendAllReminders(int eventMonth, 
                         int eventDay, int eventYear) {
   }

   public static void main (String args[]){
      Reminder rem = new Reminder();
   }

}

Once again, use the main() method to test the code. Before proceeding, make sure that mail.jar and activation.jar are in your class path.

Sending an e-mail

Sending an e-mail using JavaMail is a powerful, yet straightforward process (see Listing 27).

Listing 27. Sending an e-mail
import java.util.Properties;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.mail.Transport;
import javax.mail.Message;

public class Reminder{

   public void sendMessage(String reminderTo, String title, 
                           String description, String dateString) {
      try {
            String smtp = "earthlink.net";
            String from = "calendar@nicholaschase.com";
            Properties props = System.getProperties();
            props.put("mail.smtp.host", smtp);
            Session session = 
                  Session.getDefaultInstance(props, null);
            MimeMessage message = new MimeMessage(session);
            message.setFrom( new InternetAddress(from));
            message.addRecipient(Message.RecipientType.TO,
                             new InternetAddress(reminderTo));
            message.setSubject(title);
            String bodyText = "Just to reminder for you of the "+
                 "following event to take place on "+
                 dateString + ":\n\n"; 
            bodyText = bodyText + description;
            message.setText(bodyText);
            Transport.send(message);
      } catch (Exception e){
            e.printStackTrace();
      }
   }

   public void sendAllReminders(int eventMonth, 
                         int eventDay, int eventYear) {
   }

   public static void main (String args[]){
      Reminder rem = new Reminder();
      rem.sendMessage("questions@nicholaschase.com", "Test event", 
                                "Test description", "8/26/2005");
    }

}

First, define how and from where you're sending the message. The mail.smtp.host Property defines the Simple Mail Transfer Protocol (SMTP) server that sends the mail. Contact your ISP for information on this value, or simply check the settings on your mail client. Next, create a session and assign the Properties to it.

The next step is to create the actual message. After associating it with the session and, thus, with the Properties, you can set information, such as the from, to, subject, and body. Finally, you can send the message.

Setting all reminders

Once you can send a single reminder, you can send multiple reminders based on records in the database (see Listing 28).

Listing 28. Setting all reminders
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.ResultSet;

import java.util.Properties;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.mail.Transport;
import javax.mail.Message;


public class Reminder{

   public static String driver = 
                  "org.apache.derby.jdbc.EmbeddedDriver";

   public void sendMessage(String reminderTo, String title, 
                           String description, String dateString) {
...
   }

   public void sendAllReminders(int eventMonth, 
                         int eventDay, int eventYear) {
      try {
         Class.forName(driver).newInstance();
         Connection conn = null;
         conn = DriverManager.getConnection(
                        "jdbc:derby:c:\\derby\\calendar");

         Statement s = conn.createStatement();
         ResultSet rs = s.executeQuery("SELECT * FROM Event "+
                            "where eventMonth="+eventMonth+
                            " and eventDay="+eventDay+" and "+
                            "eventYear="+eventYear);
         if (!rs.next()){
            System.out.println("There are no events for "+
                        eventMonth+"/"+eventDay+"/"+eventYear);
         } else {


            while (rs.next()) {
               String title = rs.getString(2);
               String description = rs.getString(3);
               String remindersTo = rs.getString(4);
               String dateString = rs.getString(5)+"/"
                     +rs.getString(6)+"/"+rs.getString(7);
               sendMessage(remindersTo, title, description,
                                                 dateString);
            }
         }

         rs.close();
         s.close();
         conn.close();
         try {
            DriverManager.getConnection(
                              "jdbc:derby:;shutdown=true");
         } catch (SQLException se) { }

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

   public static void main (String args[]){
      Reminder rem = new Reminder();
      rem.sendMessage("questions@nicholaschase.com", "Test event", 
                                "Test description", "8/26/2005");
      rem.sendAllReminders(2,27,2006);

There is nothing new here. You're simply creating a connection, creating a query, and looping through the results. In this case, you're sending the data to the sendmessage() method for further processing.


Summary

At this point, you've designed and created the basic classes necessary for the calendar and reminder system. You created an Event class that updates the data, a Calendar class that displays the data, and a Reminder class that sends e-mails using the data.

These are, of course, basic classes that perform bare necessities of data management. In Part 2 of this series, you'll integrate these classes and with several different user interfaces.


Download

DescriptionNameSize
Source codeCalendar.zip4 KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Java technology
ArticleID=133105
ArticleTitle=Build a Derby calendar, Part 1: Understanding JDBC
publish-date=09132005