FeedbackSearchTopBackwardForward
EDM/2

Accessing Databases Using Java and JDBC

Written by Fernando Lozano

Linkbar

  [Note: The source code for this article is here. Ed.]

Introduction

This article will show how a Java Application, Applet or Servlet can access data stored in relational databases using the JDBC API. Although this article will use mSQL as the example database, you could use any other databases that have JDBC interfaces. These include DB2, Oracle, Sybase, Solid and many others.

First of all, I presume you have read my article in the June issue of EDM/2 and had success installing and running mSQL, so I don't need to give instructions on how to do so.

There are three basic types of JDBC drivers. The first one is simply a bridge between the JDBC API and the operating system dependent ODBC driver manager. Sun provides these bridges for Win32 and Solaris systems, and Dirk Ohme has created one for OS/2 systems. These kind of drivers were created to allow quick development of applications without waiting for the database suppliers to create real JDBC drivers.

The second one uses JNMI (Java Native Method Invocation) to call the functions of the database client library. These are also platform-dependent, as the database supplier will have to provide both the client libaries *and* the JDBC driver specific for the desired platform. IBM and Oracle, among others, provide this kind of drivers (only IBM provides them for OS/2 systems).

The third one is the most interesing, a "100% Pure" Java driver. It may not be certified as such, but the important feature is that the driver is written entirely in standard Java, and then it can be used on any platform that has a Java Virtual Machine. That's perfect if your client platform is not supported by your database supplier, ex: Linux. Oracle, Sybase and IBM, among others, provide this kind of drivers, and some can be obtained from third-parties or as Open Source Software.

Some companies provides a variation of the third kind of driver in which the JDBC driver is just a stub, or a proxy, that forwards the JDBC calls to a server proccess (possibily on another machine) that has the logic to access the desired database. There are many reasons for companies to create these kind of drivers, but the main ones are:

  1. An easy path to a 100% Java driver, without having to rewrite all database client logic in Java;
  2. To provide a unique JDBC driver that can be used with multiple databases, instead of installing one driver on each client for each database;
  3. To work around Applet security restrictions that won't let a Java Applet make TCP/IP connections to any server except the one from which the Applet has been downloaded.
Whatever the driver you use, the programming will be the same. And if you use a JDBC driver of the third kind, you can pack all your .class files together and deploy them on any platform you like. Add to the mix a 100% Java software installer (like the one suplied by InstallShield) and you can advertise your application as "Run Anywhere".

The mSQL JDBC Driver

A pure-java JDBC driver for mSQL was created and is being maintained by George Reese from The Center for Imaginary Environments ( http://www.imaginary.com/). The latest revision available is 1.0B3 (1.0 beta 3), which works with both mSQL 1.0.16 and 2.0.x.

This release does not implement all the MetaData classes and methods, so it may have problems with Wizards and experts from some IDES such as Inprise (ex-Borland) JBuilder. But you can use any of them if you code the database access code yourself.

If you still use JDK 1.0.2 (because of browser compatibility problems), the latest release of the JDBC driver you can use is 0.9.6. and this release works only with mSQL 1.0.16. You will also have to find JDBC 1.22 on javasoft.com and download it, as JDK 1.0.2 does not include JDBC classes, and you will need to make sure your clients have these classes installed also, unless you write your entire application as Java Servlets.

We OS/2 users have the latest Java releases on our web browsers, so let's use the latest JDBC driver available. :-) Anyway, The mSQL PC Home Page (http://www.blnet.com/msqlpc) has handy links to download the JDBC drivers from Imaginary.

Unzip mSQL-JDBC_1_0b3.zip in any directory you like, but make sure you create all the directories contained in the zip file (Info-Unzip does this by default but PkZip 2.50 needs /DIR). I created a directory \JAVA11\JDBC and unzipped all my JDBC drivers there.

Then you need to put the JDBC driver classes in your CLASSPATH. Edit your OS/2 CONFIG.SYS and find the line that looks like: 

  SET CLASSPATH=f:\softos2\NETSCAPE\JAVA11\JEMPCL10.ZIP;
  F:\SOFTOS2\NETSCAPE\JEMPCL10.ZIP;.\.;E:\JAVA11\ICATJAVA\DAEMON\JAVAPROB.ZIP;
And include the Imaginary classes, as in: 
  SET CLASSPATH=E:\JAVA11\JDBC\mSQL-JDBC_1.0b3\imaginary.zip;
  f:\softos2\NETSCAPE\JAVA11\JEMPCL10.ZIP;
  F:\SOFTOS2\NETSCAPE\JEMPCL10.ZIP;.\.;E:\JAVA11\ICATJAVA\DAEMON\JAVAPROB.ZIP;
I recommend you put the Java classes.zip also in your CLASSPATH, as most Java apps won't run if they aren't. So your final CLASSPATH statement looks like: 
  SET CLASSPATH=E:\JAVA11\JDBC\mSQL-JDBC_1.0b3\imaginary.zip;
  f:\softos2\NETSCAPE\JAVA11\JEMPCL10.ZIP;
  F:\SOFTOS2\NETSCAPE\JEMPCL10.ZIP;.\.;E:\JAVA11\ICATJAVA\DAEMON\JAVAPROB.ZIP;
  E:\JAVA11\lib\classes.zip
By the way, E: is my OS/2 boot drive, I have the latest Netscape 2.02 for OS/2 (June 1998) installed and I am working with JDK 1.1.6. But I can tell you I had no problems using JDK 1.0.2 (except for the release restriction stated above) and using JDK 1.1.1 or 1.1.4.

Now, the first thing to do is to test the database. Enter the directory examples (E:\JAVA11\JDBC\mSQL-JDBC_1.0b3\examples in my installation). There you have a sample Java application called Select.java. Open it with your favorite programmer's editor (mine is MED). Look for the line: 

  String url = "jdbc:msql://athens.imaginary.com:1114/db_test";
This line tells which database the application will connect to. Change it to: 
  String url = "jdbc:msql://localhost:1114/db_test";
Now compile the app using the command: 
  javac Select.java
The result will be a file named Select.class. Before we can run it, let's create the database used by the test application. The following command does that: 
  msqladmin create db_test
This isn't enough, we have to provide the test application with some data. The following script creates a table suitable for the test app and inserts some records. 
  create table test (
          test_id int,
          test_val char(20)
  ) \g

  insert into test values (1, 'One') \g
  insert into test values (2, 'Two') \g
  insert into test values (3, 'The third') \g

  \q
Save the script as test.sql and run it this way: 
  msql db_test < test.sql
Finally, we can run our app. Type 
  java Select
And, if everything went right, you should get: 
  Got results:
   key= 1 str= One
   key= 2 str= Two
   key= 3 str= The third
  Table: test

What May Go Wrong?

If anything goes wrong when running the Select application, it may not be evident to the newbie what the error was. This section will try to provide some guidance to get you running your first Java JDBC application accessing mSQL succesfully.

A very common error is forgetting to start the mSQL daemon. Then your app will show the error: 

  Connection failed.
  java.sql.SQLException: Connection failed.
          at com.imaginary.sql.msql.MsqlConnection.connect(Compiled Code)
          at com.imaginary.sql.msql.MsqlConnection.(Compiled Code)
          at com.imaginary.sql.msql.MsqlDriver.connect(Compiled Code)
          at java.sql.DriverManager.getConnection(Compiled Code)
          at java.sql.DriverManager.getConnection(Compiled Code)
          at Select.main(Compiled Code)
Our application was written to dump a stack trace on any error, that's the reason you get this long message. Just look at the first line, where it says "Connection failed".

But let's say you forgot to include the imaginary.zip file in your CLASSPATH. The error you get will be: 

  com.imaginary.sql.msql.MsqlDriver
  java.lang.ClassNotFoundException: com.imaginary.sql.msql.MsqlDriver
          at Select.main(Compiled Code)
The same message will be shown if you put the wrong value in the CLASSPATH environment variable.

Let's say you created the database using the wrong name. You will get the error: 

  Unknown database "db_test"

  com.imaginary.sql.msql.MsqlException: Unknown database "db_test"

          at com.imaginary.sql.msql.MsqlConnection.selectDatabase(Compiled Code)
          at com.imaginary.sql.msql.MsqlConnection.(Compiled Code)
          at com.imaginary.sql.msql.MsqlDriver.connect(Compiled Code)
          at java.sql.DriverManager.getConnection(Compiled Code)
          at java.sql.DriverManager.getConnection(Compiled Code)
          at Select.main(Compiled Code)
Or if you created the database, but forgot to run the test.sql script or made a mistake in the script, creating the table with the wrong name: 
  :Error reading table "test" definition (No such file or directory)

  java.sql.SQLException: :Error reading table "test" definition
  (No such file or directory)

        at com.imaginary.sql.msql.MsqlStatement.sendSQL(Compiled Code)
        at com.imaginary.sql.msql.MsqlStatement.executeQuery(Compiled Code)
        at Select.main(Compiled Code)

JDBC Progamming Concepts

When you write a program that uses a relational database, whatever API you use, may it be embebbed SQL, ODBC, the vendor client libraries or a third-party object interface, there are 4 things you have to know:

  1. How to import the interface functions or commands.
  2. How to connect and disconnect to the database.
  3. How to send SQL statements to be executed.
  4. How to get result sets from the database.
It is no different with JDBC. The only difference you may notice if you already used ODBC or some vendor API is that JDBC defines classes to make these operations while most APIs define function calls or programming language statements. Anyway you'll be surprised how easy is to write Java programs using JDBC.

1. How to import the interface functions or commands

Any JDBC program needs to import the Java classes contained in the packages java.net.URL and java.sql. This is done by the following statements: 

  import java.net.URL;
  import java.sql.*;
Beside the core JDBC classes and its support network classes, you need the vendor specific JDBC driver classes. These are generally only loaded at run-time, so you'll not import them, but instead call the method Class.forName(), as in the following example: 
  Class.forName ("com.imaginary.sql.msql.MsqlDriver");
It's a standard convention that vendor-supplied Java classes always start with "com.vendor_name". The "sql" is standard for JDBC drivers, and "msql" is the package containing the mSQL JDBC driver classes. Inside that package, "MsqlDriver" is the actual JDBC driver.

2. How to connect and disconnect to the database

To establish a connection to any network resource in Java, you need to specify a URL. The URL format may differ from database to database, but it should have at least a protocol designator, a host:port address and a database name.

The protocol designator will allways start with "jdbc:" and be followed by a subprotocol designator specific to the JDBC driver. For mSQL, the complete protocol specificator is "jdbc:msql:". That convention makes sense if you realize that each database has its own OSI layer 7 application protocol.

The host part is the TCP/IP host name of the machine that hosts the mSQL daemon, and the default port number for mSQL-2 is 1114. As our database server is running on the same machine as our Java application, we can use "localhost" as the hostname.

Our URL so far is "jdbc:msql://localhost:1114". For mSQL, we just need to append the database name, "db_test", and our URL is defined by the Select.java application as 

  String url = "jdbc:msql://localhost:1114/db_test";
Now to actually connect to the database, you need to instantiate a Connection object. From now on, all communication to and form the database will be made through this Connection object. You may connect to multiple databases at the same time by instantiating multiple connection objects, and each object may even be using a different JDBC driver.

To create the Connection object, you have to call the getConnection() method of the DriverManager class. This method needs three parameters: the URL, as explained above, the user name and the password. As the mSQL dameon does not enforce passwords, they can be left as empty strings.

Just as a reminder, the valid user names are defined in the msql.acl file. If you followed the previous article (mSQL Revisited), you will have no msql.acl file and so any user name will be considered valid.

Our example application creates its connection as follows: 

  Connection con = DriverManager.getConnection (url, "borg", "");
To end the connection, you use the close() method of the Connection object as follows: 
  con.close ();

3. How to send SQL statements to be executed

Now that we have a connection to the database server, we need to instantiate a Statement object to send SQL statements to be executed by the server.

We first create a Statement object, and then we use this object to execute SQL statements. This way we can execute the same statement many times, without the overhead of parsing and optimizing it multiple times, and we may change the statement parameters between each run.

A Statement object have to be created from a Connection object, as follows: 

  Statement stmt = con.createStatement ();
And our sample application runs it this way: 
  ResultSet rs =
  stmt.executeQuery ("SELECT * from test ORDER BY test_id");
Note that the executeQuery() method returns an object of class ResultSet, that we will explain in the following section.

If your SQL statement does not return any data (that is, it is not a SELECT statement), you should use the executeUpdate() method as follows: 

  stmt.executeUpdate ("INSERT INTO test(test_id, test_val) "
          + "VALUES (4, 'four')");
(Note: the SQL statement was split into two strings so it fits the screen).

A Statement object remains data about the last SQL statement it executed until you invoke its close() method.

4. How to get result sets from the database

When an SQL statement returns data from a relational database, we call this data a result set. As we have seen above, the executeQuery() from the Statement class returns a ResultSet object, that allows us to iterate through the result set.

The ResultSet returned by executeQuery() is positioned just before the first row of the result set. The next() method allows us to get the first row, then the second, and so on, until there are no more rows. Then next() returns False, so we can write a while loop to get all rows: 

  while (rs.next ()) {
          // do whatever you want to the current row
  }
To get any column data from the current row of the result set, you can use the methods named getInt(), getString() and so on. They can have as parameters the column name or the column position, relative to the SELECT statement. So the following code 
  int a = rs.getInt ("test_id");
  String str = rs.getString ("test_val");
Would be equivalent to: 
  int a = rs.getInt (1);
  String str = rs.getString (2);

Putting All the Pieces Together

Now that we have executed the Select.java sample provided with the Imaginary mSQL JDBC driver, and walked through all steps of manipulating mSQL databases using JDBC, let's build a complete application.

Remember the address book database we built in the previous article about mSQL? Let's use it and create a search application using Java JDBC. Our application will have as its inputs just a company name and will list all people from that company.

As a reminder, here's the table "addr_book" we created on the "pim" database in the last article: 

  create table addr_book (
          id              integer not null,
          first_name      char(30),
          last_name       char(30),
          title           char(40),
          department      char(40),
          company         char(40),
          tel             char(20),
          fax             char(20),
          e_mail          char(40)
  ) \g
Insert some data in it and then compile the following application: 
  import java.net.URL;
  import java.sql.*;

  class PimSearch
  {
      public static void main (String args[])
      {
          String where_clause;

          try {
              // connect to mSQL database pim
              Class.forName ("com.imaginary.sql.msql.MsqlDriver");
              String url = "jdbc:msql://localhost:1114/pim";
              Connection con = DriverManager.getConnection (url, "borg", "");

              // execute the query
              Statement stmt = con.createStatement ();
              ResultSet res = stmt.executeQuery (
                  "SELECT first_name, last_name, title, e_mail FROM addr_book "
                  + "WHERE company = '" + args[0] + "'");

              // list the returned rows
              while (res.next ()) {
                  System.out.println ("");
                  System.out.println ("Name:\t" + res.getString (1) + " "
                      + res.getString (2));
                  System.out.println ("Title:\t" + res.getString (3));
                  System.out.println ("E-mail:\t" + res.getString (4));
              }

              // clean-up
              stmt.close ();
              con.close ();
          }
          catch (Exception e) {
              System.out.println (e.getMessage ());
              e.printStackTrace ();
          }
      }
  }
Notice how it:
  1. Connects to the database
  2. Validates user input
  3. Sends a SELECT statement to the database
  4. Loops through the return set
  5. Releases resources held by the connection and the result set
That's a very simple app, but it shows all the concepts we've seen about database access with Java.

An applications that just inserts or updates rows (records) in our database would be much simpler, as it does not have to deal with a result set. Let's see an example. That example will look through all rows that contain a NULL company column (a NULL column is a column that has received no value. This is different from a column that has a blank value!) and change them to 'freelancer'.

The original test data we loaded in our previous article had an entry with a NULL company column, the one of 'Dirk Ohme'. So test if you still have this row, issue the following command from the mSQL monitor: 

  mSQL > select first_name, last_name from addr_book
      -> where company = NULL \g
If there are no such rows, just create one: 
  mSQL > insert into addr_book (id, first_name, last_name)
      -> values (1000, 'John', 'Smith') \g
Remember our CREATE TABLE statement.The only column that has a NOT NULL attribute is the "id" column, so it is the only one that has to get a value. The above statement will set values for three columns of the new row and leave the remaining ones as NULL.

Here's our second example. I agree it is not an interesting application, (although some DBF programmers will find the SQL language capability of performing batch updates to a set of rows interesting. Sometimes it is hard to think up simple but useful examples. 

  import java.net.URL;
  import java.sql.*;

  class PimUpdNull
  {
      public static void main (String args[])
      {
          try {
              // connect to mSQL database pim
              Class.forName ("com.imaginary.sql.msql.MsqlDriver");
              String url = "jdbc:msql://localhost:1114/pim";
              Connection con = DriverManager.getConnection (url, "borg", "");

              // execute the query
              Statement stmt = con.createStatement ();
              int numRows = stmt.executeUpdate (
                  "UPDATE addr_book SET company = 'freelancer'"
                  + "WHERE company = NULL");

              // show how many rows where affected
              System.out.println (numRows + " rows where updated.\n");

              // clean-up
              stmt.close ();
              con.close ();
          }
          catch (Exception e) {
              System.out.println (e.getMessage ());
              e.printStackTrace ();
          }
      }
  }
A real nice example would have some form of user interface so you could enter values to be inserted or updated in the database, but that way I'd write a lot of Java code and this would obfuscate the JDBC code I want to show.

Tips and Tricks

The few classes and methods I've presented here may be sufficient for most DB-aware applications you would write. However, there are a few things that may catch you by surprise, if you have already worked with other relational databases or database APIs (like Windows ODBC).

First of all, JDBC result sets use forward-only cursors. That is, there's no previous() method, just next(). There's no way you can go backwards. Hey, that's not so bad. I've already seen too many RAID applications with poor network performance because they used keysets (forward and backward cursors) when they didn't need to.

Most databases can benefit from Prepared SQL statements and stored procedures. mSQL does not support either. If your database does, you should use the PreparedStatement class instead of the Statement class and call the prepareStatement() method instead of createStatement().

If you use PreparedStatement you should bind parameters to the SQL statement instead of building a string with fixed parameter values (as we did in the previous examples). I won't explain how to do so here, maybe in a future article.

It would also be nice if you put commit() method calls (from Connection class) to ensure your application delimits transactions correctly. Under mSQL, you have no transaction support, but in databases that do, you could end with too large a transaction log and your entire database could stop. If you encounter errors, you should end your transactions by calling the rollback() method.

So, let's see how our examples would look like if they used the PreparedStatement Class and transaction support. Be warned that you won't run these examples with the mSQL JDBC driver, as it does not support these features, because mSQL itself does not support them.

First, the Search example: 

  import java.net.URL;
  import java.sql.*;


  /*
   * How searches would be done if mSQL supported transactions
   * and prepared SQL statements
   */


  class PimSearch2
  {
      public static void main (String args[])
      {
          try {
              // connect to mSQL database pim
              Class.forName ("com.imaginary.sql.msql.MsqlDriver");
              String url = "jdbc:msql://localhost:1114/pim";
              Connection con = DriverManager.getConnection (url, "borg", "");

              // The default behaviour for JDBC is to commit every SQL
              // statement executed. We want to comit ourselves
              con.setAutoCommit (false);

              // prepare the statement
              // the question marks are placeholers for the actual parameters
              PreparedStatement stmt = con.prepareStatement (
                  "SELECT first_name, last_name, title, e_mail FROM addr_book "
                  + "WHERE company = ?");

              // bind parameters to the statement
              stmt.setString (1, args[0]);

              // execute the query
              ResultSet res = stmt.executeQuery ();

              // list the returned rows
              while (res.next ()) {
                  System.out.println ("");
                  System.out.println ("Name:\t" + res.getString (1) + " "
                      + res.getString (2));
                  System.out.println ("Title:\t" + res.getString (3));
                  System.out.println ("E-mail:\t" + res.getString (4));
              }

              // commit writes to the database
              con.commit ();

              // clean-up
              stmt.close ();
              con.close ();
          }
          catch (Exception e) {
              // do not call rollback() on database errors, but
              // just if you detect application errors.
              // rollback() will throw a SQLException.
              //con.rollback ();
              System.out.println (e.getMessage ());
              e.printStackTrace ();
          }
      }
  }
And then the Update example (if you execute them and get errors, this is expected, as mSQL does not support transactions and prepared statements). 
  import java.net.URL;
  import java.sql.*;

  /*
   * How updates would be done if mSQL supported transactions
   * and prepared SQL statements
   */

  class PimUpdNull2
  {
      public static void main (String args[])
      {
          try {
              // connect to some database
              Class.forName ("com.imaginary.sql.msql.MsqlDriver");
              String url = "jdbc:msql://localhost:1114/pim";
              Connection con = DriverManager.getConnection (url, "borg", "");

              // The default behaviour for JDBC is to commit every SQL
              // statement executed. We want to comit ourselves
              con.setAutoCommit (false);

              // prepare the statement
              PreparedStatement stmt = con.prepareStatement (
                  "UPDATE addr_book SET company = ?"
                  + "WHERE company = ?");

              // bind parameters to the statement
              stmt.setString (1, "freelancer");
              stmt.setNull (2, java.sql.Types.CHAR);

              // execute the update
              int numRows = stmt.executeUpdate ();


              // commit writes to the database
              con.commit ();

              // show how many rows where affected
              System.out.println (numRows + " rows where updated.\n");

              // clean-up
              stmt.close ();
              con.close ();
          }
          catch (Exception e) {
              // do not call rollback() on database errors, but
              // just if you detect application errors.
              // rollback() will throw a SQLException.
              //con.rollback ();
              System.out.println (e.getMessage ());
              e.printStackTrace ();
          }
      }
  }
These examples are provided just in case you want to use JDBC to access databases that do support these features, like Oracle, Sybase and DB2.

Compatibility Test

I intented to test and give you the examples from this article adapted for other databases, but there wasn't enough time. Maybe a future article will show how to build transactional applications using JDBC and another database. But, if you want to run these sample apps against other databases, what should you do?

1. Change the JDBC driver name in the Class.forName() method call.

This means I have to recompile my Java application to make it work with different databases? Of course not! You just have to get the JDBC driver class name as an application parameter, not as a hard-coded value.

2. Change the "= NULL" to "IS NULL".

This is a point where mSQL does not conform to the SQL standard. Be careful, other SQL databases will have similar tricks, but fortunately not something so basic.

3. Create the database and tables the way required by your database.

Each database will have its own administration interface (from mSQL you use the commands msql.exe and msqladmin.exe). Some databases have a single daemon managing multiple databases, as mSQL, but others need multiple daemon instances to manage multiple databases, and you need to specify which instance to connect to instead of which database. This the case with Oracle, for example.

4. Change the CREATE TABLE statements.

There are some variations in the data types and constraints supported by each database, so your SQL scripts to create table, indexes and so on will change.

For example, Oracle uses VARCHAR2 for string columns, while mSQL uses CHAR. mSQL defines a primary key by creating a unique index on that key, while Oracle and DB2 let you specify the field as PRIMARY KEY, just like you can specify NOT NULL. Oracle also lets you specify foreign keys, so the database daemon will enforce referential integrity, but mSQL won't.

These differences would be subject for an entire series of articles by themselves, so we won't elaborate any more here.

Last But Not Least

I hope this article helps you get started using JDBC in your applications. I have not provided a complete working application as in the last article, because it would take too much space to code the application, or applet, or servlet, but I hope a future article will give you more details on these, specially Java Servlet programming.

By the time you read this article, a new mSQL for OS/2 should be available. It's a new port, updated to the latest Unix version which is 2.0.4.1. We hope this new port will be more stable than Dirk's 2.0.1B, but we need all the help we can get to debug this port and to build interfaces between mSQL and OS/2 development tools. If you want to help. please send me a message at [email protected]. And visit The mSQL PC Home Page at http://www.blnet.com/msqlpc to get the new port.

And don't miss the next issue of EDM/2, when we will have more articles about mSQL and database programming in general!

 

Linkbar