JDBC


The vast majority of business-oriented applications access relational databases such as Oracle, MySQL, Sybase, SQL Server, and DB2. Applications require information to be available and dependable from execution to executionto be persistent. A relational database[5] provides an application-independent means of storing and accessing that information.

[5] You may hear the term DBMSdatabase management systemto refer to the software that manages the database. An RDBMS is a relational DBMS.

A relational database is comprised of tables. A table is a matrix of rows and columns. A column is an attribute. A row is a collection of column values. For example, a student table might have columns ID, name, and city. A row exists in the database for each student. A representation of a student table appears in Table 1. In the table, there are two rows and thus two students: Schmoo, living in the city of Pueblo, and Caboose, living in the city of Laurel.

The language SQL (Structured Query Language) allows you to store and retrieve information from any relational database. SQL, for the most part, is standard across database implementations. An SQL statement to access student records from an Oracle database will work for accessing student records from Sybase (with perhaps minor modifications). In Java, you use an API called JDBC (Java DataBase Connectivity) to interact with the database. JDBC allows you to establish connections to the database and execute SQL against it.

Table 1. A Student Table with Two Rows

ID

Name

City

221-44-4400

Schmoo

Pueblo

234-41-0001

Caboose

Laurel


The need for database interaction is so commonplace that many Java products exist to simplify it. Enterprise Java Beans (EJBs), Hibernate, and JDO are three of the more popular attempts to simplify Java persistence. JDBC is the foundation of many of these tools. As is often the case, you are better off learning how the foundation works before considering use of a higher-level tool. Doing so will give you a better understanding of what the tools are doing. You may even find that a custom, highly refactored JDBC implementation is the better solution.

In this brief section, I'll demonstrate how to write simple JDBC tests and code to interact with a database. Just like Swing, entire books and many good web sites on JDBC exist. You will want to consult additional resources for more details on JDBC. You will also want to consult additional resources on SQL. I will provide only minimal explanation about the SQL in this section.

The examples in this section are written to interact with MySQL, a database freely available at http://www.mysql.com. Most of the examples are applicable to any other database. However, some of the code details for connecting to the database will differ. Also, a few of the "getting started" tests make presumptions about the availability of test structures within the database.

You will also need a JDBC driver for MySQL (see http://dev.mysql.com/downloads/connector/j/3.0.html). A driver is a Java library that meets Sun's JDBC interface specification. Your code always interacts with the same Sun-supplied JDBC interface methods, regardless of the database or database driver vendor. The database driver implementation adapts JDBC API calls to the specific requirements of the database itself.

Connecting to a Database

Perhaps the most difficult aspect of interacting with databases is establishing a connection to them. Installing MySQL always creates a database named test that you can access. Your first test will ensure that you can access the database test.

Make sure you have installed the MySQL JDBC driver and added it to your classpath. Note that you won't need the driver on the classpath to compile, but you will need it to execute.

 package sis.db; import junit.framework.TestCase; import java.sql.*; public class JdbcAccessTest extends TestCase {    public void testConnection() throws SQLException {       JdbcAccess access = new JdbcAccess("test");       Connection connection = null;       try {          connection = access.getConnection();          assertFalse(connection.isClosed());       }       finally {          connection.close();       }    } } 

The test is simple enough. Create a JdbcAccess instance using the database name test. Request a connection from the access object and verify that the connection is open (not closed). Make sure the connection gets closed by using a finally block.

A couple of comments: First, the test itself throws SQLException. You'll want to quickly encapsulate SQLExceptionyou want as few classes as possible to even know that you are using JDBC.

Isolate knowledge of JDBC in your system to one class.


Second, you will not usually want client code to request its own connection object. A common problem in many systems is that the client code forgets to close the connection. Clients open more and more connections. Ultimately, the system crashes when it becomes unable to supply any more connections. For now, writing a test to prove the ability to establish a connection allows you to proceed incrementally. You probably won't want to publicize getConnection.

 package sis.db; import java.sql.*; public class JdbcAccess {    private String database;    public JdbcAccess(String database) {       this.database = database;    }    Connection getConnection() throws SQLException {       try {          Class.forName("com.mysql.jdbc.Driver");       } catch (Exception cause) {          throw new SQLException(cause.getMessage());       }       String url = "jdbc:mysql://localhost/" + database;       return DriverManager.getConnection(url);    } } 

The implementation of getConnection in JdbcAccess involves two steps: first, load the JDBC driver class. Second, establish a connection by passing a database URL to the DriverManager class method getConnection.

The suggested technique for loading the driver is to use reflection. You pass a String representing the name of the class to the Class forName class method. Some drivers require you to send newInstance to actually create a driver instance.[6] But there's no reason you couldn't directly create a new instance of it:

[6] MySQL does not. Check your driver documentation. Or you can be lazy and always call newInstance.

 Connection getConnection() throws SQLException {    new com.mysql.jdbc.Driver();    String url = "jdbc:mysql://localhost/" + database;    return DriverManager.getConnection(url); } 

The original reason for suggesting the use of reflection is flexibility. Using Class.forName, you could load the driver class name from a property file or pass it in via a system property (see Properties in this chapter). You would then have the flexibility to change the driver at a later time without changing code. In reality, changing drivers doesn't happen that frequently, and you'll probably have to revisit your code anyway if you do.[7]

[7] Of course, the need to support a second driver is reason enough to eliminate the hard-coded class reference.

When you call Class.forName (or when you first reference and instantiate the class), the static initializer in the Driver class is invoked. Code in the static initializer is responsible for registering the driver with the DriverManager. The DriverManager maintains a list of all registered drivers and selects a suitable one based on the URL when getConnection gets called.

Another means of specifying one or more drivers is to supply class names using the system property jdbc.drivers. Refer to the Properties section later in this chapter for information on how to set this property.

The organization of information in the database URL is specific to the driver vendor. It usually follows a few conventions, such as starting with the word jdbc followed by a subprotocol string (mysql in this case; it's often the vendor name). You separate elements in the URL using colons (:). In MySQL, you follow the subprotocol with a reference to the server (localhost hereyour machine) and the database name.

The end result of calling getConnection is a java.sql.Connection object. You'll need a connection in order to execute any SQL statements.

Connections are scarce resources. You will be able to create only a limited number of connections. Further, establishing a new connection is costly from a performance standpoint. Connection pools allow you to maintain a number of always-open connections. A client requests a connection from the pool, uses it, then relinquishes the connection so it can return to the pool. Some JDBC implementations support connection pools directly; others do not. For purposes of these exercises and for simple, single-client access, connection pools are not necessary.

Executing Queries

The second test demonstrates executing SQL statements.

 package sis.db; import junit.framework.TestCase; import java.sql.*; public class JdbcAccessTest extends TestCase {    private JdbcAccess access;    protected void setUp() {       access = new JdbcAccess("test");    }    ...    public void testExecute() throws SQLException {       access.execute("create table testExecute (fieldA char)");       try {          assertEquals("fieldA",              access.getFirstRowFirstColumn("desc testExecute"));       }       finally {          access.execute("drop table testExecute");       }    } } 

You cannot assume the existence of any tables in the test database, so testExecute creates its own. The SQL statement "create table testExecute (fieldA char)" creates a table named testExecute with one char (character) column named fieldA.

The proof that the table was created is via a method named getFirstRowFirstColumn. The query desc testExecute returns a description of the testExecute table. The SQL command desc is short for describe. A desc command returns one row for each column that it describes. The first column in each row is the name of the column.

As a final measure, the test removes the testExecute table by using the SQL command drop table.

A slightly refactored JdbcAccess shows the new methods execute and getFirstRowFirstColumn. The refactoring eliminates some code duplication. It also eliminates processing duplication by ensuring that the driver instance only gets loaded once. Both the execute and getFirstRowFirstColumn methods obtain and relinquish their own connection. This alleviates the client from that responsibility.

 package sis.db; import java.sql.*; public class JdbcAccess {    private String url;    ...    public void execute(String sql) throws SQLException {       Connection connection = getConnection();       try {          Statement statement = connection.createStatement();          statement.execute(sql);       }       finally {          close(connection);       }    }    private void close(Connection connection) throws SQLException {       if (connection != null)          connection.close();    }    Connection getConnection() throws SQLException {       if (url == null) {          loadDriver();          url = "jdbc:mysql://localhost/" + database;       }       return DriverManager.getConnection(url);    }    private void loadDriver() throws SQLException {       try {          Class.forName("com.mysql.jdbc.Driver");       } catch (Exception cause) {          throw new SQLException(cause.getMessage());       }    }    public String getFirstRowFirstColumn(String query)           throws SQLException {       Connection connection = getConnection();       try {          Statement statement = connection.createStatement();          ResultSet results = statement.executeQuery(query);          results.next();          return results.getString(1);       }       finally {          close(connection);       }    } } 

You need a Statement object in order to execute SQL. You obtain a Statement object from a Connection by sending it createStatement. Once you have a Statement, you can either use the execute method to invoke SQL statements where you expect no results to come back or executeQuery to invoke SQL statements and subsequently obtain results. To execute or executeQuery, you pass the SQL string as an argument.

Results return in the form of a ResultSet object. A ResultSet is very similar to an Iterator. It maintains an internal pointer to the current row. You advance this pointer, which initially points to nothing, by sending the message next to the ResultSet. The method next returns TRue until the internal pointer moves past the last row. You access columns from the current row by either column name or column index. Various methods exist for obtaining column values, each differing by the type of data stored in each column. The ResultSet method getString returns character data, for example, and the method getInt returns an int value.

The code in getFirstRowFirstColumn advances the pointer to the first row returned from executeQuery by calling results.next(). The getFirstRowFirstColumn method returns the value of the first column by sending getString to the ResultSet using its index, 1, as an argument. Column indexes start at 1 in JDBC, not 0.

Prepared Statements

Often you will want to execute the same statement numerous times in an application, different only perhaps by key information. For example, the SIS application requires rapid student lookups based on student identification (ID) number.

Since you pass an SQL statement in the form of a String, the statement must be compiled before the database can execute it. Compiling the statement ensures that it follows valid SQL syntax. Compilation takes enough execution time that it can cause performance problems. (Profile, as always, to be sure that you have poor performance.) To speed things up, you can create a PreparedStatement object using an SQL string. A PreparedStatement compiles the SQL once and stores this compiled version for later, more rapid use.

You can insert placeholders in the SQL string in the form of question marks ('?'). Later, you bind values to these placeholders. An appropriate SQL string for the student lookup might be:

 select id, name from testQueryBy where id = ? 

For each student lookup, you bind a value to the question mark by using set methods. The method statement.setString(1, "boo") would bind the value 'boo' to the first (and only) question-mark argument.

 public void testQueryBy() throws SQLException {    drop("testQueryBy");    access.execute(       "create table testQueryBy (id varchar(10), name varchar(30))");    PreparedStatement statement = null;    try {       access.execute("insert into testQueryBy values('123', 'schmoe')");       access.execute(          "insert into testQueryBy values('234', 'patella')");       statement =         access.prepare("select id, name from testQueryBy where id = ?");       List<String> row = access.getUnique(statement, "234");       assertEquals("234", row.get(0));       assertEquals("patella", row.get(1));       row = access.getUnique(statement, "123");       assertEquals("123", row.get(0));       assertEquals("schmoe", row.get(1));    }    finally {       statement.close();       drop("testQueryBy");    } } private void drop(String tableName) {    try {       access.execute("drop table " + tableName);    }    catch (SQLException ignore) {       // exception thrown if table doesn't exist; we don't care    } } 

Since the point of using PreparedStatement is client efficiency, you do not want to close the connection each time you execute a query against the PreparedStatement. In this case, you will have to trust that client code closes out the connection when it is done using the PreparedStatement.

The implementation:

 public PreparedStatement prepare(String sql) throws SQLException {    Connection connection = getConnection();    return connection.prepareStatement(sql); } public List<String> getUnique(       PreparedStatement statement, String... values)        throws SQLException {    int i = 1;    for (String value: values)       statement.setString(i++, value);    ResultSet results = statement.executeQuery();    results.next();    List<String> row = new ArrayList<String>();    ResultSetMetaData metadata = results.getMetaData();    for (int column = 1; column <= metadata.getColumnCount(); column++)       row.add(results.getString(column));    return row; } 

From a ResultSet, you can extract metadata. The ResultSetMetaData object supplies useful information about the results returned: number of columns, data type for each column, value for each column, and so on. The implementation of getUnique obtains the number of columns using getColumnCount and uses this to iterate through all the column values.

Metadata is also available at the database level. You can send the message getMetaData to a Connection object. In return you receive a DatabaseMetaData object populated with a wealth of information about the database and drivers, including tables and columns, versions, driver limitations, and database limitations.

JDBC Application Design

There are dozens of ways to implement persistence using JDBC in an application. As mentioned earlier, you can use a tool that sits atop JDBC or even supplants it. Or, following TDD, you can grow a similar tool using the philosophy of zero duplication. By being vigilant about squashing duplication, you can quickly build a tool to meet your needs. Unlike a third-party tool, yours will be flexible enough to change rapidly based on demand.

You can use the example above as a starting point for your own tool. The JdbcAccess class should end up being the only place in which you interact with the JDBC API. Most of the remainder of the application is blissfully ignorant of the fact that JDBC is the persistence mechanism. If you choose to supplant JDBC with an object-oriented database down the road, replacement work is minimal.

You can create mapping objects to translate between database columns and domain attributes. You can accomplish this using code generation (which is preferable) or reflection. It is even possible to store everything in String format in the database and do type translation a layer up from JdbcAccess. This can help keep JdbcAccess simple: It provides access methods that return generic lists of lists of strings (rows of columns, each of which are strings).

SQL statements contain rampant duplication. It is fairly easy to code a SQL generator class that builds SQL statements using information available from the database (the database metadata).



Agile Java. Crafting Code with Test-Driven Development
Agile Javaв„ў: Crafting Code with Test-Driven Development
ISBN: 0131482394
EAN: 2147483647
Year: 2003
Pages: 391
Authors: Jeff Langr

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net