Accessing Databases Using Java and JDBC
Written by Fernando Lozano
[Note: The source code for this article is here. Ed.]
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:
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:
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:
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:
But let's say you forgot to include the imaginary.zip file in your
CLASSPATH. The error you get will be:
Let's say you created the database using the wrong name. You will get
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
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
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
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:
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
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:
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:
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:
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:
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.
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:
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 220.127.116.11. 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@example.com. 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!