Andy is an engineer for NuMega Technologies and can be contacted at [email protected].
The JDBC allows Java developers to create applications that can attach and interact with a variety of databases. On PCs, a JDBC application must load a "bridge" that communicates between the JDBC code and an ODBC driver. This bridge is a DLL and is the key to making any PC-based JDBC applet work.
The JDBC applet has a few lines of code that load the driver and cast the resulting class to the JDBC's driver class. The driver is then used to establish connections to various databases, send SQL statements, and gather results. The JDBC provides the same level of database functionality you would expect from tools such as Borland's Delphi and Microsoft's Visual Basic.
There are a few significant advantages that applets have over applications. One is that applets can be executed by browsing a web page; thus, you can make modifications without having the user reinstall. In fact, users need never install anything beyond their browser software. This gives you the ability to make code changes without having to redistribute the application or reinstall.
However, many JDBC developers have run into a strange problem. JDBC applets cannot be loaded from browsers like Netscape's Navigator or Microsoft's Internet Explorer because they cannot load the driver -- the method for invoking the DLL is simply incompatible.
The inability to browse JDBC applets on the PC is a significant roadblock, but there is a way around it. Instead of directly loading an applet that attaches to a database, you can develop two separate applets -- a server that establishes the connection to the database, and a client that communicates with the server. This lets you browse the client applet, giving you the same database connectivity without having to install any special drivers on a user's system.
The Model
Both the client and the server use a series of network support classes found in the java.net package. The client will use the Socket class to establish a connection on the server (which is listening by way of the ServerSocket class). However, the client applet has a significant limitation: It can only communicate with the same system that sent the applet. If the client applet was loaded from server Wombat, the applet can only attach back to Wombat. It may not attach to any other system because of applet-security restrictions. This means that you must run the database server on the same system as the web server.
The database server starts by trying to load the JDBC driver and establish a connection. Once the connection has been made, the server spawns a new thread to listen for incoming client connections. When a client attaches, a new thread, which will interact with the user and act as a session manager, is generated.
To make this work, both the client and the server must be able to speak the same language, so you must produce a new class that organizes the requests of the client into something that can be read by the server. Obviously, the class must also be able to take the server's replies and convert them into something the client can understand.
This adds a convenient layer of abstraction. The client never really knows what is passing the information back to it. The server could be a Java, C/C++, or even a Visual Basic application. The client only knows that it is receiving data in the correct format. The server could communicate with a client written in any language: The only important component is the protocol used to communicate (which you customize to fit your needs).
Once the client has attached and the session manager is active, users can make any query or update. The server simply interprets the incoming packet and generates the correct SQL code on the fly. Once the SQL statement has been executed, the server passes back the resulting data in the correct format by way of the protocol manager.
The Implementation
To illustrate this model, I've written a complete Java database server and client applet. They were coded in Microsoft's Visual J++ and recompiled and tested using Javasoft's JDK. The project is called "Call Tracker." It's a simple tracking system that lets users create, update, and view customer calls to a technical-support group. The complete source code and related files are available electronically; see "Programmer Services," page 3.
The server is broken down into four classes. The first is CTRCKRSVR, which is extended from the Applet class and implements the runable interface. This class generates the user interface and establishes the connection with the database. Once the connection has been established, the server starts an instance of ServerThread.
The connection to the database is seen in Listing One It starts by trying to load the JDBC-ODBC bridge. The JdbcOdbcDriver class dynamically loads the JdbcOdbc.dll and creates a new Driver instance. Once you have the driver running, you should determine if the driver follows the JDBC specification. You then confirm that it can attach to the database prior to actually attaching. This is done by specifying the ODBC driver's URL, where m_JDBCUrl is "jdbc:odbc:"+m_DBName. m_DBName can be any string that names a system data source. We finally attach to the database via m_Connection = DriverManager.getConnection ( m_JDBCUrl ); which establishes the connection and gives us full access to the database.
The Connection class is the framework for the generation of SQL statements and the gathering of results. It specifies how and when data is written to the database. You can use it to find the names of various tables in the database, as well as the state of the database. It finally determines how data is written when the connection is closed. Note that a Connection should be closed when it is no longer being used (formally breaking the link to the database).
Listing Two shows the run member of the CTRCKRSVR class. It executes the startServices() function in Listing One. It also kicks off a new instance of ServerThread. It uses three parameters in its constructor. The first is the socket that the server should listen to. The second is an instance of the Connection class (initialized in startServices()). The third is a list class (m_Log) that acts as a list box. The m_Log member is used throughout the server to act as a log of activities. Anytime a user attaches or makes a query or an update, it is displayed in that list. Finally, the class updates a label repeatedly to show the time and date.
ServerThread listens for incoming client connections. The class is an extended Thread class. Thus, at its creation, it begins executing separately from the main application's thread. Listing Three shows the key members. The run member calls InitServer to initialize the ServerSocket class that listens for a connection. m_Server.accept() causes the socket to go into a blocking state until an application tries to connect to the socket. Once a connection occurs, m_Server.accept() returns a new Socket class that is used as a part of the Session class' constructor. Note that you also pass in the log and connection members. Further, the thread literally stops until a connection is made. This construct is fairly important: It makes it possible to have many users attach and interact with the database at the same time.
The Session class is responsible for communication between the client and the server, translating the incoming packets (by way of the TrackerData class), and acting upon them by executing a query, update, or insert, and then passing the results back to the client. The constructor is responsible for establishing the data-stream classes and starting the execution of the thread; see Listing Four.
Note that you are using a stream-based transport (TCP). There are a number of reasons for using this over datagram (UDP) communication. Streams work in the same way a file stream would operate. You simply read from the stream until you get an EOF or an exception occurs. Stream-based communication is typically used for passing files or large blocks of data. The stream is able to manage splitting data across multiple packets as well as reassembly (this is important, since a name or a problem in our call-tracking system can be any length). You don't need to concern yourself with how the network passes the data. Further, TCP adds several checks that confirm data arrival and check for data corruption.
The run() member is really just a while loop that constantly calls manageConnection(). As is shown in Listing Five, manageConnection() does many different things. It starts by getting the current time. When the minute value changes, you send a "heartbeat" packet, testing the socket and confirming that the client is still attached (an exception is thrown if the user dropped the connection, closing the data streams and the socket). manageConnection() also terminates the thread. The client doesn't interpret the heartbeat, it just cleans the incoming stream and waits for new data.
You call m_InStream.available() to see if there is any data in the incoming data stream that can be read. m_InStream.readFully(stream) then reads the contents of the stream into a buffer, which is used to build a TrackerData object. TrackerData parses the string into the various data types or fields used in the database. Then TrackerData.getType() is called to determine the kind of data you received (update, insert, query, and so on).
ID is used to determine if the database function ran correctly. All records in the database are given a unique number. You can use this to determine if the SQL statement was successful. If it is, the record's ID is returned, otherwise you receive a -1. After each transaction, you return the record to the client with the ID.
Now take a look at the code that inserts a new row into the database. Listing Six begins with addDBItem(TrackerData), which first confirms that there were first- and last-name values entered. It then attaches to the database by calling attachDB(), which creates a new instance of the JDBC's Statement class.
The Statement class is used to perform all SQL statement operations (barring prepared statements handled by the Connection class). This includes insert, delete, and query operations. Results gathered by queries are returned in the RecordSet class.
BuildRecordString() is used to get the new entry's fields. The resulting string is prepended with the SQL statement that performs the insert operation. You then pass the SQL statement into statement.executeUpdate(SQLStatement) and close the statement object. Note that statement.executeUpdate(SQLStatement) handles both updates and inserts. An exception is thrown if the operation fails, at which point you try to close the SQL statement again to terminate the object correctly.
Queries are not much more difficult. Listing Seven shows getID(), which does a query to get the ID of a record that was recently added or updated. This function confirms that the record was actually written and returns its ID. A -1 is returned if the function fails. getID() starts by attaching to the database and establishing a statement object. It then executes a SQL statement that returns the affected row. The statement rs = statement.executeQuery(SQLStatement); runs the query and returns a ResultSet object. You then move to the first row of the result set and display the first field in the record. You do this by calling rs.getInt(1), which will return the integer form of the record's row number. Both the ResultSet and Statement objects are closed and the ID is returned. If either the query or the RecordSet failed, an exception is thrown and an error will be added to the log.
The last part of the server is the TrackerData class (Listing Eight). This class is shared by both the server and the client. It is responsible for formatting the various fields into a single string. The class can also take a string and break it down into the fields that make up a record. It includes the type identifier, length of the next data item, and data item itself. Each item is separated by a dash. The length of each data item is critical (since a dash may also be used in the data item).
An incoming string is parsed by creating a new TrackerData with that string. You then call parsePacket(), which breaks the string down based upon the type field. The individual fields are retrieved by calling the getXXX() member, where XXX is the field name. This allows the remainder of the applet, be it client or server, to remain unaware of the stream's construction.
Outgoing strings are built in a similar fashion. First, the TrackerData object is created. Next, the various fields are populated by calling the setXXX(), where XXX is the field name. The buildPacket() is then called, returning the string that is to be sent to the network. (See Listing Nine.)
The client is little more than a user interface. It is downloaded from the web site and executed through the browser. It attempts to attach to the JDBC server, then passes new records or queries by building the appropriate string through the TrackerData class and forwarding it to the server. An incoming string is broken down through the TrackerData class and the fields are displayed in the appropriate user-interface object.
Conclusion
The JDBC is fairly easy to work with. However, writing applications where the JDBC can be used effectively is obviously much more complicated. The client/server design allows users to run database applications by simply browsing the applet without having any special drivers that would normally be needed to attach and interact with a database. The key is that the JDBC server can attach to the database, and the client can attach to the server.
You can now produce robust Internet database applications that can offer customers, telecommuters, and employees access to the information they need without having to set up special dial-in accounts or extra leased-line connections. Further, one application can communicate with the server regardless of platform, virtually eliminating the need to write applications for any particular operating system in the database arena.
DDJ
Listing One
Driver m_JDBCDriver;String m_JDBCUrl;
Connection m_Connection;
</p>
public boolean StartServices ()
{
try
{
m_JDBCDriver =(Driver) Class.forName
("jdbc.odbc.JdbcOdbcDriver").newInstance(); // Loads JDBC driver
if ( !m_JDBCDriver.jdbcCompliant () )
{
m_Log.addItem ( "Driver is not JDBC compliant");
return false;
}
if (m_JDBCDriver.acceptsURL ( m_JDBCUrl ) )
// Confirms that we can attach to this URL
{
m_Log.addItem ( "Trying to establish a
connection with "+m_JDBCUrl);
m_Connection = DriverManager.getConnection ( m_JDBCUrl );
return true;
}
} catch (ClassNotFoundException e)
{
m_Log.addItem ( "Could not load JDBC-ODBC bridge,
check your path env.");
m_Log.addItem ( e.getMessage() );
return false;
}
catch (SQLException db)
{
m_Log.addItem ( "Connection failed: " +db.getMessage());
}
catch (Exception e)
{
m_Log.addItem ( "Critical Fault in Starting DB services");
m_Log.addItem ( e.getMessage() );
}
return false;
}
Listing Two
List m_Log;Label m_TimeLabel;
int m_Port;
</p>
public void run()
{
StartServices();
m_ServerThread = new ServerThread ( m_Port, m_Connection, m_Log );
m_Log.addItem ( "Database driver and socket services are up" );
while (true)
{
m_TimeLabel.setText( new Date().toString() );
}
}
Listing Three
protected ServerSocket m_Server = null;protected Session m_Session = null;
</p>
protected void InitServer ()
{
if (m_Server == null)
{
try
{
m_Server = new ServerSocket ( m_Port, 6 );
m_Log.addItem ( "Socket server is up at port: " + m_Port );
m_Log.makeVisible ( m_Log.countItems () - 1);
} catch ( IOException e)
{
m_Log.addItem ( "Could not create socket:" + m_Port );
m_Log.addItem ( e.getMessage() );
m_Log.makeVisible ( m_Log.countItems () - 1);
Stop ();
}
}
}
public void run ()
{
InitServer ();
while (true)
{
try
{
m_Log.addItem ( "Waiting for Connect" );
m_Session = new Session (m_Server.accept(), m_Connection, m_Log);
m_Log.addItem ( "User has attached" );
m_Log.makeVisible ( m_Log.countItems () - 1);
} catch ( IOException e)
{
m_Log.addItem ( "Could not Accept" + m_Port );
m_Log.addItem ( e.getMessage() );
m_Log.makeVisible ( m_Log.countItems () - 1);
Stop ();
}
}
}
Listing Four
protected Socket m_Socket = null;protected Connection m_Connection;
protected DataInputStream m_InStream;
protected DataOutputStream m_OutStream;
List m_Log;
</p>
public Session ( Socket socket, Connection connection, List Log)
{
m_Log = Log;
if(socket != null)
{
try
{
m_Socket = socket;
m_OutStream = new DataOutputStream ( m_Socket.getOutputStream());
m_InStream = new DataInputStream ( m_Socket.getInputStream());
} catch ( IOException e)
{
addLogItem ( "Could not duplicate connection: "+e.getMessage());
}
m_Connection = connection;
start();
}
else
{
stop();
}
}
Listing Five
public void run (){
addLogItem ( "New Session Thread Started");
while (true)
{
if (manageConnection() == false)
Stop();
}
}
protected boolean manageConnection()
{
int minutes = new Date().getMinutes();
int ID = -1;
</p>
try
{
Date date = new Date();
</p>
if( date.getMinutes() != minutes)
{
minutes = date.getMinutes();
m_OutStream.writeBytes( TrackerData.buildHeartBeat());
}
if ( m_InStream.available() > 0)
{
byte Stream[] = new byte[m_InStream.available()];
m_InStream.readFully(Stream);
String IncomingString = new String(Stream, 0);
</p>
TrackerData TrackerPacket = new TrackerData(IncomingString);
if( TrackerPacket.parsePacket() )
{
switch( TrackerPacket.getType() )
{
case TrackerPacket.QUERY:
ID = getDBItem(TrackerPacket);
TrackerPacket.setID(ID);
</p>
if(ID != -1)
{
addLogItem("User Queried");
}
else
addLogItem("User Query Failed");
</p>
sendReply(TrackerPacket, TrackerPacket.UPDATE);
break;
</p>
case TrackerPacket.SET:
ID = addDBItem(TrackerPacket);
TrackerPacket.setID(ID);
</p>
if(ID != -1)
addLogItem("New DB Entry");
else
addLogItem("New DB Entry Attemp Failed");
sendReply(TrackerPacket, TrackerPacket.UPDATE);
break;
case TrackerPacket.UPDATE:
ID = updateDBItem(TrackerPacket);
TrackerPacket.setID(ID);
</p>
if(ID != -1)
addLogItem("DB Entry Update");
else
addLogItem("DB Entry Update Failed");
sendReply(TrackerPacket, TrackerPacket.UPDATE);
break;
}
}
}
} catch (IOException f)
{
addLogItem ( f.getMessage());
return false;
}
return true;
}
Listing Six
protected int addDBItem(TrackerData TrackerPacket){
if((TrackerPacket.getFName().length() < 1) ||
(TrackerPacket.getLName().length() < 1))
return -1;
Statement statement = attachDB();
</p>
if(statement == null)
return -1;
</p>
String SQLStatement = buildRecordString(TrackerPacket);
</p>
SQLStatement = "INSERT INTO Customers (ContactFirstName,"+
"ContactLastName, CompanyName, PhoneNumber, Problem, Resolution)"+
"VALUES " + SQLStatement;
try
{
statement.executeUpdate(SQLStatement);
statement.close();
} catch (SQLException e)
{
try
{
statement.close();
} catch (SQLException f)
{
addLogItem(f.getMessage());
return -1;
}
addLogItem(e.getMessage());
return -1;
}
int ID = getID(TrackerPacket);
return ID;
}
protected Statement attachDB ()
{
try
{
Statement statement = m_Connection.createStatement();
return statement;
} catch (SQLException e)
{
addLogItem ( "Failed to get DB connection: "+e.getMessage() );
return null;
}
}
protected String buildRecordString(TrackerData TrackerPacket)
{
String recordString = new String();
recordString = "( '"+TrackerPacket.getFName()+"'"+
", '"+TrackerPacket.getLName()+"'"+
", '"+TrackerPacket.getCompany()+"'"+
", '"+TrackerPacket.getPhone()+"'"+
", '"+TrackerPacket.getProblem()+"'"+
", '"+TrackerPacket.getResolution()+"')";
</p>
return recordString;
}
Listing Seven
protected int getID( TrackerData TrackerPacket){
int ID = -1;
</p>
Statement statement = attachDB();
ResultSet rs;
</p>
if(statement == null)
return ID;
try
{
String SQLStatement = new String();
SQLStatement = "SELECT * FROM Customers WHERE ContactFirstName = '"
+ TrackerPacket.getFName() + "' AND ContactLastName = '"
+ TrackerPacket.getLName() + "' AND CompanyName = '"
+ TrackerPacket.getCompany() + "' AND PhoneNumber = '"
+ TrackerPacket.getPhone() + "' AND Problem = '"
+ TrackerPacket.getProblem()+ "' AND Resolution = '"
+ TrackerPacket.getResolution()+"'";
rs = statement.executeQuery(SQLStatement);
rs.next();
ID = rs.getInt(1);
rs.close();
statement.close();
} catch (SQLException e)
{
try
{
statement.close();
} catch (SQLException f)
{
addLogItem(f.getMessage());
}
addLogItem(e.getMessage());
}
return ID;
}
Listing Eight
public boolean parsePacket(){
if(Packet != null)
{
StringTokenizer ParsedPacket = new StringTokenizer(Packet, "-");
int type;
int len;
</p>
while(ParsedPacket.hasMoreElements())
{
Type = type = getType (ParsedPacket.nextToken());
switch(type)
{
case UPDATE:
case SET:
if (type == UPDATE)
{
len = getLength(ParsedPacket.nextToken());
setID(Integer.parseInt(getString(ParsedPacket, len)));
}
len = getLength(ParsedPacket.nextToken());
setFName(getString(ParsedPacket, len));
</p>
len = getLength(ParsedPacket.nextToken());
setLName(getString(ParsedPacket, len));
</p>
len = getLength(ParsedPacket.nextToken());
setCompany(getString(ParsedPacket, len));
</p>
len = getLength(ParsedPacket.nextToken());
setPhone(getString(ParsedPacket, len));
</p>
len = getLength(ParsedPacket.nextToken());
setProblem(getString(ParsedPacket, len));
</p>
len = getLength(ParsedPacket.nextToken());
setResolution(getString(ParsedPacket,len));
return true;
</p>
case QUERY:
len = getLength(ParsedPacket.nextToken());
setID(Integer.parseInt(getString(ParsedPacket, len)));
return true;
case HEARTBEAT:
len = getLength(ParsedPacket.nextToken());
getString(ParsedPacket,len);
break;
}
}
}
return false;
}
Listing Nine
public String buildPacket(int type){
int FNameLen = FName.length();
int LNameLen = LName.length();
int CompanyLen = Company.length();
int PhoneLen = Phone.length();
int ProblemLen = Problem.length();
int ResolutionLen = Resolution.length();
</p>
String newPacket = new String();
String ids = new String(Integer.toString(ID));
</p>
switch (type)
{
case UPDATE:
case SET:
if (ID == -1)
{
type = SET;
newPacket = type + "-"+FNameLen+"-"+FName+"-"
+LNameLen+"-"+LName+"-"
+CompanyLen+"-"+Company+"-"
+PhoneLen+"-"+Phone+"-"
+ProblemLen+"-"+Problem+"-"
+ResolutionLen+"-"+Resolution;
}
else
{
type = UPDATE;
</p>
newPacket = type +"-"+ids.length()+"-"+ids+
"-"+FNameLen+"-"+FName+"-"
+LNameLen+"-"+LName+"-"
+CompanyLen+"-"+Company+"-"
+PhoneLen+"-"+Phone+"-"
+ProblemLen+"-"+Problem+"-"
+ResolutionLen+"-"+Resolution;
}
break;
case QUERY:
newPacket = type +"-"+ids.length()+"-"+ids;
break;
}
return newPacket;
}
</p>
