Client/Server Programming -- Week 6

Introduction

This week we'll cover database programming in Java.

Note that the links from this page are to handouts that will be distributed the night of class.  Only print out those handouts if you could not attend class.

Main topics this week:

Installing Hypersonic
SQL
Using JDBC
Using the Connection
Security Note
A Complete JDBC Example
Assignment 6-1
Next Week

Installing Hypersonic

Hypersonic, otherwise knowsn as hsqldb, is located on your course CD in \resources\software\hsqldb_1_7_1.zip.  You can install this to your home computer by unzipping the file into a directory (typically c:\hsqldb).  Typically this is all you need to do to install it. 

The hsqldb.jar file is installed on einstein in ~shaffsta/public/comp345/hsqldb/hsqldb.jar. 

In the examples that follow, you'll need to adjust the classpath according to where hsqldb.jar is actually installed on the machine you're using. 

You can use Hypersonic in several modes, but we'll use the server mode.  In this mode, you run the database server as a separate program that listens on a port.  Any program that wants to use the database must connect to that port.  The database itself is stored in files.

For example, to run the database server to listen on port 4288, you would type:

java -classpath c:\hsqldb\lib\hsqldb.jar org.hsqldb.Server -port 4288 -database rpsgame

If you were running the server on einstein, you would change the classpath:

java -classpath ~shaffsta/public/comp345/hsqldb/hsqldb.jar org.hsqldb.Server -port 4288 -database rpsgame

This will start up the database server, which will then wait for connections on the port you specified.  The data will be stored in a series of files that start with the name "rpsgame".  As we continue talking about JDBC, we'll see how to write a program to access the data.

Note that a Hypersonic database starts out with a default user named "sa" that has full access to the database. 

Hypersonic includes a graphical user interface for executing commands on the database.   You can run this by typing (note that this will not work on einstein, since it is a graphical user interface):

java -classpath c:\hsqldb\lib\hsqldb.jar org.hsqldb.util.DatabaseManager

The database manager presents a screen which allows you to specify the type of database and the connection URL to it.  You want to fill out the fields so it looks like this:

The database server must be running before you press OK.  The database manager allows you to see the structure of the tables in the database, and to execute commands on the database (using SQL). 

SQL

SQL stands for Structured Query Language, and is the language you use to interact with databases.  You can use SQL to create new tables, modify old tables, insert new rows, modify old rows, etc.  We're primarily concerned with four types of commands:

1. select
2. insert
3. update
4. delete

The examples below will use the Student table from last week:

Select

The select command is how you retrieve data from the database, according to criteria you specify.  The select command can become quite complicated, but we'll stick to the simpler aspects of it.  The general form of the select command is, "select <column list> from <tablename>".  For example, to see all the columns from the STUDENT table, we could type "select * from STUDENT".  To only see the studentid field, we would type "select studentid from STUDENT".  

We can also specify ways to limit the number of rows returned, using the WHERE clause.   For example, to retrieve data from the student table for the student with id number 10337, we would type "select * from STUDENT where studentid=10337". 

You can also sort the data that comes back from the database (by default, the database gives you back rows in its internal order).  For example, to sort by the StudentID, you would use "select * from STUDENT order by StudentID".

You can also combine a WHERE clause with an ORDER BY clause.

Insert

The insert command adds rows to a table.  You must provide values for all the columns in the row.  The syntax is, "insert into <tablename> values (<valuelist>)".  The order of values in the value list must match the order of columns in the table.  For example, to insert a new row into the student table, we would type, "insert into STUDENT values (84328, 'Tim Jenkins')".  Note the single quotes used for delimiting strings. 

Update

The update command changes data in existing rows.  You must provide a WHERE clause to tell it which rows to change.  The general form is, "update <tablename> set <columnname>=<value> where <criteria>".  So to change the student name for the student whose id is 84328, we would type "update STUDENT set studentname='Timothy Jenkins' where studentid=84328".  If the where clause matches more than one row, then all rows will be modified.

Delete

The delete command is used to remove rows from the database.  The general form is, "delete from <tablename> where <clause>".  Each row that matches the where clause will be deleted.  For example, to delete the student whose id is 10337, we would type, "delete from STUDENT where studentid=10337".

Using those four SQL statements, you can manage the data in database tables.

Using JDBC

The way we execute SQL statements on a database from our Java program is by using JDBC classes.

First, you must have an appropriate JDBC driver for the database you want to use.   For example, the JDBC driver for mysql is "org.hsqldb.jdbcDriver".   You must make sure that this class is in your classpath.  This class lives in the hsqldb.jar file mentioned in the above section on the Hypersonic database.

Second, you must load that class into memory using Class.forName.  So, for the Hypersonic driver, you'd do:

try
{
  Class.forName ("org.hsqldb.jdbcDriver");
}
catch (ClassNotFoundException e)
{
  e.printStackTrace();
}

This makes sure the class is available for use and initialized, and that it registers itself with the JDBC framework.

Third, you must create a connection to the database.  To do this you'll use the java.sql.DriverManager class.  This class has static methods for managing JDBC connections.  The method we're interested in right now is getConnection.  We need to pass getConnection a URL that describes where the database is, and how to log into it.  The URL has a slightly different form depending on the database you're using.   For the Hypersonic database run as a server, the URL is of the form "jdbc:hsqldb:hsql://host:port".   So if you've run the database server on the local machine, and on port 4288, you would use:

Connection conn = null;

try
{
  conn = DriverManager.getConnection ("jdbc:hsqldb:hsql://localhost:4288", "sa", "");
}
catch (SQLException e)
{
  e.printStackTrace ();
}

The second parameter is the name of the user you want to connect to the database as, and the third parameter is the password for that user.  Hypersonic databases by default have a user named "sa" who has no password. 

getConenction will return an instance of java.sql.Connection. 

Using the Connection

The Connection object's primary purpose is to allow you to create SQL statements.   You'll use the createStatement method to create an empty SQL statement, and then use that Statement instance to execute SQL statements on the database.  You also must remember to use the close method to release the connection when you are completely done with it.

For example, to create a statement:

Statement stmt = null;

try
{
  stmt = conn.createStatement ();
}
catch (SQLException e)
{
  e.printStackTrace ();
}

java.sql.Statement

The methods you care about on the Statement class are executeUpdate, and executeQuery.  

The executeUpdate method allows you to execute an SQL statement that is either an INSERT, UPDATE, or DELETE.  If the SQL statement violates any database constraints (such as adding a duplicate student), then an SQLException will be thrown.  You'll need to experiment to discover the errors codes returned in the SQLException for various error conditions in the database. 

The executeQuery method allows you to execute a SELECT statement.  This method returns an instance of ResultSet, which contains the results of the query.

java.sql.ResultSet

A ResultSet instance is like a data structure, in that it contains all the rows that were returned by the SELECT statement.  It also allows you to move forward through the rows, and to get information on each column.  Note that not all rows are actually in the ResultSet to start with.  JDBC may go to the database to get more rows as you step through the ResultSet.  Because of this interactivity with the database, you must call the close method on the ResultSet to release its connection to the database when you're done with the ResultSet.

Let's look at some useful methods on ResultSet:

boolean next () -- This method moves the ResultSet to the next row.  It returns true if there is a next row to go to, false, if the previous row was the last one.   A result set starts out just before the first row, so you must call next () before calling any of the methods to get column values.

String getString (String columnName) -- This method gets a String from the given column.
Date getDate (String columnName) -- This method gets a Date from the given column.
int getInt (String columnName) -- This method gets an integer from the given column.
float getFloat (String columnName) -- This method gets a float from the given column.

There are more get methods in ResultSet, but these are the ones that you'll need for assignment 6-1.

Security Note

There are many times when you will have the user type in information, and then you'll use that information as part of an SQL query by concatenating strings in Java (see the example below for this). 

SQL only requires that strings be surrounded by single quotes, however you will want to use single quotes to surround anything the user has entered.  This ensures that what the user enters will not be taken as part of the SQL query itself, but will simply provide a value.  The danger is that without the single quotes, the user could type in text that would modify the purpose of your original SQL query. 

A Complete JDBC Example

Here's a complete example that uses JDBC, and demonstrates using the Statement and ResultSet classes:

This program creates the database table, and must be run once
This program allows you to add rows to the table, and look up rows, and can be run many times

To run these examples, you must first start the database server.  Then you must run the programs by giving the proper classpath...for example, to run a JDBC program named DatabaseExample on einstein (with the database server running on port 4288), you would type:

java -classpath ~shaffsta/public/comp345/hsqldb/hsqldb.jar:. DatabaseExample 4288

To run the same program on a Windows machine, with the database server running on the same machine:

java -classpath c:\hsqldb\lib\hsqldb.jar;. DatabaseExample 4288

You'll note that, since you're using a database, if you exit the DatabaseExample program and restart it, all your data is still there.  This works even if you run the DatabaseExample program more than once at the same time and make changes in each...each instance of the program will be able to see the changes made by other instances of the program.

Assignment 6-1

You should have already been able to start adding additional features specified in assignment 6-1.  Now you should be able to use the examples from this week to add in database functionality.  Your instructor will provide a Java program that will allow the creation of a database you'll use for this assignment.  You must use the database as specified, without making changes to it.  If changes to the database are needed, your instructor will provide a new Java program.

(The program for creating this term's database is on einstein, ~shaffsta/public/comp345/assignment61/Main.java.  You'll copy it to either your area on einstein, or your home computer.  There are directions for running it in the .java file itself.)

Next Week

Next week we will cover advanced database topics and review for the midterm.