Java Database Programming

Java Database Programming

1. The Relational Database Systems

Readings: Quick read on Sections 34.1 to 34.3.

A database system consists of a database, the software that stores and manages data in the database, and the application programs that present data and enable the user to interact with the database system.

A database is a repository of data that are organized together in some ways. There are a lot of database system such as MySQL, Oracle, MS Access, Sybase etc.

Most of today’s database systems are relational database systems, based on the relational data model. A relational data model has three key components: structure, integrity, and language. Structure defines the representation of the data. Integrity imposes constraints on the data. Language provides the means for accessing and manipulating data.

In a relational database system, the data are organized in tables. Tables are the essential objects in a database.

A table consists of columns which are attributes of a data record. Each row of the table is the data for a record.  

If you never use a database system, I suggest you should try it on the MS Access which is available on your windows system.

Most of database systems mentioned above use SQL to access the data in the database. With SQL a user can insert new data into the database; can update/delete old data records, can make queries to the database etc.

2. Java JDBC

The Java API for developing Java database applications is called JDBC. With JDBC you can get the data from databases into Java programs. JDBC provides Java programmers with a uniform interface for accessing and manipulating a wide range of relational databases.

JDBC API lets you communicate with database using SQL. The JDBC API is a set of Java interfaces and classes used to write Java programs for accessing and manipulating relational databases. Between the JDBC API and the particular database is an special interface called JDBC driver. For example, if you want to access the MySQL’s databases in Java programs, you have to install the JDBC driver for MySQL. Each database vendor provides its own JDBC driver for the JDBC API. However the Java provides the JDBC-ODBC bridge driver for accessing some kind of database, like MS Access database system.

In this topic we are going to only use the JDBC-ODBC bridge driver to connect the MS Access database with the Java JDBC API, because the JDBC-ODBC driver for Access is bundled in Java system.

Remember: to work with database, you must import SQL for Java as follows

import java.sql.*;

2.1  Loading Drivers

An appropriate driver must be loaded using the statement shown below before connecting to a database

Class.forName(“JDBCDriverClass”);

A driver is a concrete class that implements the java.sql.Driver interface. For example, if you are going to connect a MySQL database, you should use

Class.forName(“com.mysql.jdbc.Driver”);

For MS Access database we use

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

As the compiled code for a driver is packed in a jar file, the above statement tells the compiler which jar file should be used in the program. The corresponding jar file must be in the classpath for the Java.

2.2  Establishing Connections

To connect to a database, use the static method

Connection con = DriverManager.getConnection(DatabaseURL);

where the database URL is the unique identifier of the database on the Internet. As the database usually is password-protected, when you connect to a database you should provide a user name and passspord for a database in the following way

Connection con = DriverManager.getConnection(DatabaseURL, username,
password);

As the URL for connecting an Access database is very long, we write it in this way

String accessDBURLPrefix = "jdbc:odbc:Driver={Microsoft
Access Driver (*.mdb)};DBQ=";
String accessDBURLSuffix = ";DriverID=22;READONLY=true}";
String filename = "c:/Access/my.mdb";
String databaseURL;
databaseURL = accessDBURLPrefix + filename.trim() + accessDBURLSuffix;

con = DriverManager.getConnection(databaseURL, "", "");

Note: the data file (my.mdb) should exist before you can run the program. It is very easy for you to create a database file with the Access. If you don’t have Access on your computer, please get a file from the subject website.

2.3 Creating Statements

Once the getConnection method returns a Connection object, you can use it as a channel and create Statements that can delivers SQL statements for execution by the database and brings the result back to the program.

The Statement object is created as follows
Statement stat = con.createStatement();

2.4 Executing Statements

Then a string of SQL commands can be put in the Statement object and delivered for execution. For example, you can create a table in the Access database my.mdb,

String createString = "create table COFFEES " +
"(COF_NAME VARCHAR(32), " +
"SUP_ID INTEGER, " +
"PRICE FLOAT, " +
"SALES INTEGER, " +
"TOTAL INTEGER)";

stat.execute(createString);

Then the table will be created in my.mdb. 

2.5 Processing ResultSet

When you execute a query, you are interested in the result. The executeQuery method returns an object of type ResultSet that you use to walk through the result one row at a time

ResultSet rs= stat.executeQuery(“SELECT * FROM COFFEES”);
while (rs.next() ) {
//Do something with the data; for example
System.out.println(rs.getDouble(“PRICE”));
}

2.6 Closing the Connection

After you finish your work on the database, you should immediately close the current connection and JDBC resources that it created. Just like closing an opened file, it is easy to close the current connection and the statement by

stat.close();
con.close();

2.7  Putting together

The following is a complete program. Try the program in the following steps:

1. Download the file Access database file my.mdb from the subject website. There are two tables in the database, COFFEES and Mytable. There are two records in Mytable. You can delete the COFFEES at the first try.
     
2. Compile the program and run it. You will see the table COFFEES will be deleted.
      
3. Then comment the statement s.execute(“drop table COFFEES”); decomment the code segment for creating the table COFFEES. Try again

import java.sql.*;

public class TestDataBase {
public static void main(String args[]) {

String accessDBURLPrefix = "jdbc:odbc:Driver={Microsoft Access
Driver (*.mdb)};DBQ=";
String accessDBURLSuffix = ";DriverID=22;READONLY=true}";

// Initialize the JdbcOdbc Bridge Driver

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(ClassNotFoundException e) {
System.err.println("JdbcOdbc Bridge Driver not found!");
}
/** Creates a Connection to a Access Database */
try {
String filename = "my.mdb";
String databaseURL;
databaseURL = accessDBURLPrefix + filename.trim() +
accessDBURLSuffix;

Connection con = DriverManager.getConnection
(databaseURL, "", "");
Statement s = con.createStatement();
/* Uncomment this to create another table
String createString = "create table COFFEES " +
"(COF_NAME VARCHAR(32), " +
"SUP_ID INTEGER, " +
"PRICE FLOAT, " +
"SALES INTEGER, " +
"TOTAL INTEGER)";
s.execute(createString);
*/ 
s.execute("drop table COFFEES");

s.execute("select Name from Mytable"); // select the data
// from the table
ResultSet rs = s.getResultSet(); // get any ResultSet that
//  came from our query
if (rs != null) 
while ( rs.next() ) 
{
System.out.println("Data from column_name: " +
rs.getString(1) );
}

s.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}  

3.  The ResultSet class

You have noted that the executeQuery and getResultSet() methods return an instance of class ResultSet. In terms of the relational database terminology the result of query is also a table of the relations. You can consider the data contained in ResultSet is arranged row by row.

The next() method of the ResultSet class makes the current row in the result move forward by one, returning false after the last row.
    
getXxx(int columnNumber) and getXxx(String columnName) return the value of the column with the given column number or name, converted to the specified type, where Xxx is a type such as int, double, String, Date etc.
    
Each get method makes reasonable type conversions when the type of the method doesn’t match the type of the column. For example, if the first column is type double, the getString(0) converts the double to a string.

4.  Managing Connections, Statements, and Result sets

Every Connection object can create one or more Statement objects. You can use the same Statement object for multiple, unrelated commands and queries as we did in the example program. However, a statement has at most one open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects.

When you are done using a ResultSet, Statement, or Connection, you should call the close method immediately. These objects use large data structures, and you don’t want to wait for the garbage collector to deal with them.

The close method of a Statement object automatically closes the associated result set if the statement has an open result set. Similiarly, the close method of the Connection class closes all the statement of the connection.

It is a good style to write the code in the following template

Connection con = …
try {
Statement stat = con.createStatement();
ResultSet result = stat.executeQuery(queryString);
…
}
finally {
con.close();
}