Loading the Driver

Running a database application is not quite as straightforward as running the serialization example in the earlier sections of this chapter. The reason for this is that a client/server connection must be created. Connecting to a local file, as was done in the serialization example, is a fairly basic task. However, remember that when using a separate database application such as Microsoft Access, a connection must be made to the database itself.

This connection actually requires that the database driver be loaded first. To load the driver, we need to use the Driver Manager. In Java, the DriverManager class loads the driver into the Java app, and then JDBC is used to make the connection between the app and the database.

To load the Sun driver, you code the following line:

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 

Drivers for Other Databases

You can use drivers for database systems other than Access as well. You would then have to replace the string loaded by the forName() command.

Normally the Class.forName construct is used for this purpose. You could explicitly assign a reference to the driver like this:

 java.sql.Driver d = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 

However, the Driver class is automatically registered within the application, so this is not necessary.

Making the Connection

After the driver has been loaded, the connection to the database can now be loaded using the getConnection method.

 Connection con = DriverManager.getConnection(url, "id", "pwd"); 

The url string format depends on what driver you are using. For example, because we are using the JDBC-ODBC bridge, we can use a url like "jdbc:odbc:myDriver" .

 Connection con = DriverManager.getConnection("jdbc:odbc:myDriver", "id", "pwd"); 

You can also connect to the datasource over the Internet with the following form:

 jdbc:<sub-protocol>:<sub-name> 

The actual code might look like this:

 jdbc:odbc//companyserver.com:500/supplierdata 

Driver Documentation

Remember that you need to consult the documentation for the driver you are using. The syntax may vary depending on the specific driver.

With the driver loaded and a connection made to the database, you are now ready to execute some SQL commands.

The SQL Statements

If you have used Microsoft Access or any other relational database, you have certainly executed SQL statements. This section will provide the basic Java syntax for building and submitting a SQL query to a relational database. It is interesting to note that from now on, everything that we do is not database-specific . Now that the driver has been loaded and the connection made, the rest is basic SQL, which is standard across database platforms.

The first thing to do is create a statement object, which at this point does not yet hold a SQL statement. You can use the createStatement method to execute simple SQL statements that do not contain any parameters. In this case, we are simply creating a statement object, which will obtain its SQL information a bit later.

 Statement statement = connection .createStatement(); 

There are actually two types of SQL statements that we can execute:

• Queries

• Updates

We can use the executeQuery method to execute basically any type of SQL query that we are interested in. We can use the executeUpdate method to execute something like an update or insert operation, or anything that would actually change the database. The executeQuery method only inspects the database, and never physically alters it.

However, before we can actually execute the query, we must build the query. Rather than hard-code it into the executeQuery method, let's build a string that we can pass to the executeQuery method. This way, we can make the code much more configurable. Here is the code to build a query string.

 String sqlQuery= "select PRODUCT from SUPPLIERTABLE where PRODUCT = 'Bolts'"; 

What we want to do here is query the SUPPLIERTABLE for any record that contains a PRODUCT of 'Bolts' .

SQL Strings

Note that SQL uses the single quote to delineate strings. Make sure you remember this, because many programming languages use double quotes to delineate strings. This can get confusing and produce incorrect code.

Now that we have the SQL string built, we can execute the executeQuery method as follows :

 ResultSet rs = statement.executeQuery(sqlQuery); 

You might be wondering what the ResultSet is . Well, remember that the SQL query performs a search of the SUPPLIERTABLE for any record that contains a PRODUCT of 'Bolts' . This implies that there might be more than one supplier that supplies bolts. Thus, we have the potential to need storage for more than one supplier. Many object-oriented languages include the concept of a collection. Collections not only include traditional data structures such as arrays; they also include data structures like vectors, hash tables, and so on.

Arrays and Collections

Collections are a very useful addition to the Java and .NET toolkits. One of the disadvantages of an array is that you must define its length when the array is declared. Vectors, on the other hand, are basically arrays that can grow, and thus make your programming life much easier.

When a SQL query is executed, we hold the results in a ResultSet object, as indicated in the previous line of code. When the executeQuery method is invoked, all records in the SUPPLIERTABLE that contain the string 'Bolts' in the Product field will be returned in the ResultSet . One of the advantages of this is that we can iterate through the ResultSet . For example, suppose we want to iterate through the ResultSet to simply print all the suppliers that supply bolts that were culled from the database.

 if (rs.next()){ System.out.println("rs.getString("SUPPLIERID")); } 

In this case, when the ResultSet is returned, the pointer to the collection is at position 0 (remember that Java and .NET start counting at zero). Each time rs.next() is called, the pointer to the collection is incremented by one, basically pointing to the next row. If there are no more rows available, rs.next() returns a value of false . In this way, you can process the ResultSet in a very logical and efficient manner.

If you know the specific row ahead of time, you can actually use the following code:

 if (rs.next()){ System.out.println("rs.getString(5)); } 

This might be very convenient , but it is obviously not that configurable.

Although the statement and the connection will close by default when the application terminates, proper programming conventions dictate that you should close them yourself. This will ensure the integrity of the database. Closing the database is just as important as closing a file. The code for this is quite simple:

 statement.close(); connection.close(); 

Figure 11.8 illustrates the complete process as detailed in this part of the chapter.

Figure 11.8. The complete process.

The complete code for this example is as follows:

 public void findVendor(String vendorId) throws SQLException{     String returnString = null;     String dbUserid = "userid"; // Your Database user id     String dbPassword = "password" ; // Your Database password     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");     Connection connection =     DriverManager.getConnection("jdbc:odbc:myDriver", dbUserid ,dbPassword);     Statement statement = connection .createStatement();     String sqlQuery=           "select PRODUCT from SUPPLIERTABLE where PRODUCT = 'Bolts'";     ResultSet rs = statement.executeQuery(sqlQuery);     if (rs.next())     {       System.out.println("rs.getString("SUPPLIERID"));     }     statement.close();     connection.close(); } 

Executing the Code

Remember that you will have to customize this code for whichever driver you are using and the name of your database. Thus, some editing is required before this code will run.