JDBC Notes for CSCI 680E/470


Overview

Java DataBase Connectivity is a set of classes and interfaces that allow Java applications to access relational databases via SQL commands and queries.  You must import java.sql.*; to reference these.

Relational Databases store information in tables.  Each table is made up of rows and columns where a row is like a record and the columns are fields in the records (to use COBOL-like terms).

In the following discussion we will assume a table called "cust" with a

Note: in all SQL statements, the database names (such as the table name "cust" and field names like "ssn") are case sensitive. For this discussion, they will be assumed to be lower case.  SQL reserved/command words may be upper or lower case - for example, SELECT or select is ok.

SQL - Structured Query Language is the standard way to send commands to database systems.  SQL is not case sensitive.  Its English-like syntax is fairly simple:

Other SQL commands to add, update, and delete will be shown later.

The architecture we will assume here is that of a Java client running in a Web browser which communicates via sockets to a Java sever running on a remote machine (normally the one with the database).  The Java server makes the database calls (via a Socket to the database server, which can be on a different machine) and returns results to the Java client for display to the user.

When we write Java server programs with JDBC methods, we access databases indirectly.  


Loading the DriverManager and Creating a Connection to the DataBase

JDBC defines a DriverManager class.  It provides methods for loading specific database drivers and for establishing connections to data sources (databases) via the database drivers.

We will use the mysql database system, so all specifics in the following discussions are for the mysql system as installed and configured on the department's turing unix machine.

We need to load the DriverManager class and use its static methods. To load the DriverManager class we can code:

Class.forName("com.mysql.jdbc.Driver").newInstance();

Now that the DriverManager class is loaded, we can establish a connection to a specific database via a static method:

Connection con = 
  DriverManager.getConnection("jdbc:mysql://courses:3306/JavaCustXX");

Note: in the multithreaded server model we use, these steps should go in the constructor of the Thread-derived class that conducts the conversation with a single client. 


Forming Statments and Executing Queries and Commands

To actually access the database whose Connection was created above (which is named con), we need to create Java statements which will hold SQL queries or commands.  There are three kinds of statements we can create:

After a Java statement of one kind or the other is created, it must be executed.   The executeXXX() methods belong to the Statement and preparedStatement classes.

executeUpdate() can also create new tables (not covered further here).

Note: all executeXXX() calls can throw SQLExceptions.

Here are some samples with explanations:

1. A simple query:

Statement stmnt = con.createStatement();

Then to execute a query:

ResultSet rs = stmnt.executeQuery("SELECT * FROM cust");

Note: this Statement can be reused for any other later hard-coded SQL query or command.   You don't have to create a new one each time.  See Processing Results below for information on ResultSet rs

2. A prepared query:

PreparedStatement pstmnt = con.prepareStatement("SELECT * FROM cust WHERE name = ?");
pstmnt.setString(1, someStringGotFromTheUser);
ResultSet rs = pstmnt.executeQuery();

Notes:


Other Commands

A simple deletion:

Assuming a Statement stmnt exists, then to execute a delete command:

int n = stmnt.executeUpdate("DELETE FROM cust WHERE name='Jones'");

This will delete all the records (rows) where the name is Jones.  See Processing Results below for information on int rc.

A prepared deletion:

PreparedStatement pstmnt = 
    con.prepareStatement("DELETE FROM cust WHERE name = ? || name = ?");
pstmnt.setString(1, oneNameToGetRidOF);
pstmnt.setString(2, anotherNameToDelete);
int n = pstmnt.executeUpdate();

Notes:

Insert:

stmnt.executeUpdate("INSERT INTO cust (name, ssn, code) VALUES ('Smith', '123456789', 10)");

would create a new record with the given values. The address field (which is not specified) would be set to null.

If you supply all the field values in order, there is no need to code the column names:

stmnt.executeUpdate("INSERT INTO cust VALUES ('Smith', '123456789', '6 Fir St.', 10)");

You can use a PreparedStatement to do Inserts with run-time values supplied as ? parameters.  Alternately, the following notation using run-time values will work for a simple Statement.  First create a String to hold the SQL command:

String sqlString = "INSERT INTO cust VALUES ('" +
                           name +            "', '"  +
                           ssn  +            "', '"  +
                           address +         "', " +
                           intCode + ")";

Look carefully. Assuming name, ssn, and address are Strings with the displayed value and intCode is an int with value 8, this produces the String:

INSERT INTO cust VALUES ('aName', 'aSSN', 'aAddress', 8)

.  Then

stmnt.executeUpdate(sqlString); 

will do the trick.  This technique can be used for other commands as well.

Alter:

pstmnt = con.prepareStatement("UPDATE cust SET name = ?, address = ?, code = ? WHERE ssn = ?");
pstmnt.setString(1, someNameString);
pstmnt.setString(2, someAddrString);
psmnt.setInt(3, someInt);
psmnt.setString(4, theSSNString);
int n = pstmnt.executeUpdate();

No new ideas here.  Note the SQL syntax for an alter.  It is possible for an alter to affect more 0 to many records if the WHERE clause references a non-unique field.  For example, you could change the address of all the name='Jones' customers at once.  (This might not be wise or correct, but you could do it.)


Processing Results

1. Queries

If you execute a query (via one of the executeQuery() methods), the method call returns an object that implements the ResultSet interface.  From this object, you can retrieve the value of columns by column name or position (starting at 1).  You can also obtain information about the number of columns and the data type of each column via the "metadata" (not covered further here).  A database query can return 0, 1, or many rows of data, depending on the data and the query.  Normally, if there are no returned results, you do get a valid ResultSet object (i.e. it is not null) but the first rs.next() returns false. You can code a loop to go through the returned rows as follows:

ResultSet rs = stmnt.executeQuery(..);
while (rs.next())
  {
  // do something with one row such as:
  System.out.println("Name: " + rs.getString(1) + "SSN: " + rs.getString("ssn"));
  }

Notes:

2. Commands

If you execute a command (via executeUpdate()) for an alter or delete, the method call returns an integer representing the number of rows in the database affected by the call.  So if you try to delete all the "Jones" and there are three in the database, executeUpdate() would return 3. 

However, if you try to add a duplicate record (i.e. if you try to write a record with an existing unique key), the unsuccessful add will raise an SQLException.


Closing Statements and Connections

When you are finished with any kind of a Statement or PreparedStatement object, call

stmnt.close();

When you are finished with the Connection (perhaps because the client has closed the socket), call

con.close();

Other Requirements for Accessing and Using mysql Databases on turing.

Development and Testing

You will need the following Unix commands:

ps -fu yourloginid                       - to see your tasks and task id's
kill 9999                                - to shut down an executing task - i.e. your server
java myserver &                          - the & starts running the task in the background

Development and testing of client-server systems is somewhat awkward. Testing of deployed systems requires Unix access to start and stop the server program and Windows JDK access to work on the client. You can develop (i.e. write and compile) the server program on unix, or you can write and compile it on any machine with a Java 1.6 compiler and then ftp it to unix. You can test the client with a Java-capable browser on a machine connected to the Internet.

To test a new version of the server, you will have to kill the current version and start the new one.  When testing the client applet on a WWW browser, you may have to clear the cache or even exit and restart the browser to test a new version.

CLASSPATH and Other Mechanics

You have to make sure that your CLASSPATH environment variable is set in order to specify locations of Java class files:

1.) to use the classes from Java 1.6
2.) to use the classes from the the driver classes .jar file
3.) to use the classes from your directory where your java application resides

So, edit the .bashrc file that is on your root directory, to include the following single line:

export CLASSPATH=.:/usr/share/java/*

Note: the initial . before the colon is for the current directory (or working directory) where your applet and server are located.

Be very careful that you type this correctly.

The .bashrc file should be located at the root folder of your turing account, so logon (or ftp files) to turing.cs.niu.edu to work on this project.  (You can use a PC to edit source code and also compile them and then ftp the .class files to turing.)

The .bashrc file is hidden so you will have to use ls -a to find if you have it or not.
If the file does not exist, they should use a text editor to create a .bashrc in this directory.  See your instructor if you do not know how to do this.
If the .bashrc CLASSPATH is not configured correctly, the following line will throw a "Class not found" exception:

Class.forName("com.mysql.jdbc.Driver").newInstance();

The server code itself should be compiled in or uploaded to this same root directory to avoid path issues.

The compiled client applet should be uploaded to the your public_html folder located on turing along with an Web page file (html file) with an applet tag for the Client applet. This setup is the same as assignment 3.