< Zurück | Inhalt | Weiter >

15.3.1 Downloading JDBC for MySQL

The JDBC implementation for MySQL is available for free from

http://www.mysql.com/downloads/api-jdbc.php.

The current version at the time of writing was mysql-connector-java- 3.0.9-stable.tar.gz which you can unpack as follows:


$ gunzip mysql-connector-java-3.0.9-stable.tar.gz

$ tar xvf mysql-connector-java-3.0.9-stable.tar


That leaves you with a directory named mysql-connector-java- 3.0.9-stable which contains a JAR file named mysql-connector-java- 3.0.9-stable-bin.jar along with some directories (which are the contents of the JAR, unpacked) and a few miscellaneous files.

From the readme file:


Once you have unarchived the distribution archive, you can install the driver in one of two ways:

• Either copy the com and org subdirectories and all of their contents to anywhere you like, and put the directory holding the com and org subdirectories in your classpath, or

• Put mysql-connector-java-3.0.9-stable-bin.jar in your class- path, either by adding the full path to it to your CLASSPATH environment variable, or putting it in $JAVA_HOME/jre/lib/ext.


Unlike JUnit, it is OK to put this JAR in the ext directory.


15.4 QUERYING DATA


Back to our example. Do you remember the portion that built the query? Here it is again:


// query

String mySQL = "SELECT id, pw FROM Users WHERE name = ?";


PreparedStatement stmt = conn.prepareStatement(mySQL); stmt.setString(1, args[0]);


If you’re at all familiar with SQL then you’ll recognize the SQL syntax within the String mySQL. Whatever you want your query to be, just build it as literal text. The query is “parameterized” by using the “?” character. Wher- ever a “?” appears in the query string, you can substitute a value with the setString() method on the PreparedStatement class.

There are a variety of setXXXX() methods where XXXX stands for different data types. Besides setString(), the most common ones are setInt(), setBigDecimal(), setDouble(), and setTimestamp() which set the parameter from an int, BigDecimal, Double, and Timestamp classes, respec- tively. The java.sql.Timestamp class is basically a java.util.Date aug- mented for compatibility with SQL’s notion of TIMESTAMP. Read more about it on the Javadoc page for java.sql.Timestamp, or read the java.sql.PreparedStatement page for more on the other set methods available.

The two arguments to each of these set methods are the index and the value that you want to substitute. The index is simply the count of which question mark gets substituted, starting with 1 for the first one. Caution: The parameters start at one, even though most other things in Java, such as Arrays, ArrayLists, and so on, are zero-based. So it’s not uncommon in code that uses JDBC to see something like this:


setInt(i+1, args[i]);



NOTE

Building SQL queries out of String literals is made easier in Java by a conve- nient mismatch between the two languages. In Java, Strings are delimited by double quotes (") whereas in SQL literals are bounded by single quotes ('). Thus in Java, you can construct SQL queries that contain literal string references without much trouble, as in:


String clause = "WHERE name != 'Admin'"

image


If this all seems rather simplistic, well, it is. It may not be a very sophisti- cated way of blending SQL with Java, but it is very effective. Notice that you don’t get any syntax checking on the SQL query when you write your program, though it will throw an exception at runtime if you try to execute ungrammat- ical SQL. For this reason it is not uncommon to try out all your SQL before- hand, cutting and pasting the queries out of the SQL program that you use for directly talking with your database. Some developers even like to keep their queries in files, to be read at runtime. This has the added flexibility (and risk) of being able to change the query without recompiling the code. Since the re- compile doesn’t provide any syntax checking on your query string anyway, it seems a reasonable way to go, provided that you properly write-protect the files containing the queries.


15.5 GETTING RESULTS


Returning to our example, we see that we can execute the query on the

Statement object and then get out the results:


ResultSet rs = stmt.executeQuery();


// read the results while(rs.next()) {

int id = rs.getInt("id"); String pw = rs.getString("pw");


System.out.println("id="+id);

}


The results of a query are returned in a ResultSet object. The easiest way

to think of it is to consider it an iterator over the rows that result from the query. In its simple form it is, like an iterator, a one-pass, once-through traversal of the data. Since the result set is iterating over rows, we need to get at the indi- vidual columns of results with a further method call on the ResultSet. You can see that inside the while loop of the example.

The query was built to retrieve the columns id and pw from our table. The getInt() and getString() methods use those column names to retrieve the data from the ResultSet.




TIP

The case (UPPER or lower) of the column name strings is ignored, so you could write ID and pW and it would work fine. Some developers prefer, for example, to use all-uppercase names of columns. We recommend using a consistent case throughout to avoid confusing those who later have to read your code.


image

There is another form for each of those getXXXX() calls that takes as its argument the column number rather than name. Since our query selected "id, pw", the id is column one and pw is column two, so we could have written:


int id = rs.getInt(1); String pw = rs.getString(2);


In addition to the get methods, ResultSet also has some boolean methods that will help your application figure out how far in the result set the iterator has reached: isBeforeFirst(), isFirst(), isLast(), and isAfterLast(). There is, however, no way to tell how big the result set is directly from this simple result set.

More complex manipulation of the ResultSet object is possible if we create the PreparedStatement with a different method call, one that lets us provide additional parameters to specify this more complex behavior. We could use:


conn.prepareStatement(mySQL,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);


which lets us specify a type of scrollable behavior and whether (CONCUR_UPDATEABLE) or not (CONCUR_READ_ONLY) the results set can be updated.

Once we’ve built the prepared statement this way, we can move the itera- tor forward or backward, to absolute (e.g., row 7) or relative (e.g., 3 rows back) positions. For a good discussion of this topic, see page 257 and the following pages in the Gallardo book.

If you’re still hung up on the fact that you can’t get the size, in rows, of the result set from our first example, notice that you can now do that with this more flexible, “scrollable” result set. To find its size before reading any data,


position it afterLast(), then getRow() to get the size, then position it back to beforeFirst() to be ready to read.


15.6 UPDATES, INSERTS, DELETES


Not every action on a database returns a ResultSet. Operations that create the tables in a database, or those that modify, insert, or delete rows of data don’t return rows of values. For those sorts of SQL statements, we don’t call executeQuery()—we call executeUpdate() instead. It returns an int giving the number of rows affected by the execution. In the case of a CREATE TABLE operation, it simply returns 0.


15.7 REVIEW


Connecting a Java application to a database is a key step in real applications. The mechanisms for doing that are varied and can be complicated. We’ve picked a single approach for connecting, to keep it simple and to highlight the similarities and differences between two different database implementations.

The rest of the conversation with a database depends as much on your SQL skills as on Java skills. Java will take strings of SQL, which can be param- eterized, and, via JDBC calls, send them to be executed by the database. The results are like iterators; they can be retrieved, row after row, for further process- ing. Updates, inserts, and deletes are also easily done, with a few simple calls to process the SQL.


15.8 WHAT YOU STILL DONT KNOW


We’ve skipped over lots of topics to keep this simple. There are a variety of ways to connect to a database that we haven’t covered; the most important one may be the use of a DataSource instead of a DriverManager. As of Java 1.4.1, the DataSource is the preferred means of making connections to your database. While it makes the code more portable (e.g., if you’re planning on moving around, changing databases and/or database servers), it is more compli- cated to set up—there are more “moving parts” to get right. If you already have a Java Naming and Directory Interface (JNDI) service running, thought, it’s very straightforward (see page 254 and the following pages of Gallardo).

15.9 Resources 337

image


We haven’t covered the basic Statement class suitable for fixed queries with no parameters (instead we used PreparedStatement which will work with or without parameters), nor have we discussed the CallableStatement class for calling stored procedures in the database. With what you know now, though, you should be able to glean enough information from the Javadoc pages on these classes to do what you need, as they are similar to the PreparedStatment class.

RowSets extend ResultSet to include mechanisms for listening for data changes and for JavaBeans functionality. But again, Javadoc information or a comprehensive book on the subject would be a good next step, now that you have the basics in hand.

We haven’t covered the ResultSetMetaData class, which provides a way for you to get the names of the columns that come back from the query. Again, check the Javadoc for details on its use.

We also haven’t said anything about transactions, a key element in many database applications. We’ll say more about that as we get into the enterprise- scale applications.

What we have covered should enable you to connect to a database, make real queries, and process the results. There are more advanced techniques to learn for special cases, but what we’ve covered here, accompanied by the Javadoc pages for java.sql.* classes, should be enough to create useful, real applications.

And of course there is always that other good teacher, experience. So go try some of what you now know.


15.9 RESOURCES


If you are going to use an Oracle database, we highly recommend Java Oracle Database Development by David Gallardo (ISBN 0130462187, Prentice Hall PTR), part of their Oracle Series. It includes several introductory chapters on database design, SQL, and even PL/SQL. It then has a much more thorough coverage of JDBC topics, with examples specific to Oracle, than we can cover in our limited space.

A JDBC implementation for MySQL is available at

http://www.mysql.com/downloads/api-jdbc.php.

JDBC tutorial information, as well as lots of other JDBC information, is available from Sun at http://www.java.sun.com/products/jdbc/.


If you are working with a database other than mySQL or Oracle, you might want to check out http://servlet.java.sun.com/products/jdbc/ drivers for a list of approximately two hundred JDBC implementations for various databases.


15.10 EXERCISES


1. Write a program that connects to a database and, for each of the tables specified on the command line, prints out the table name and the number of rows in that table.

2. Using what has been covered in this chapter, write a simple non-GUI SQL program, allowing the user to enter SQL statements, executing them, and showing the results (like a simplified SQL/Plus program). Can you provide some simple editing of the SQL? Or can you implement escaping to an external editor, for example, vi?

3. Make it possible for the user of your SQL program to set and change the connection URL—via an environment variable, command-line parameter, or even from within the program. Your SQL program will then be able to query a variety of databases from various vendors.