General JDBC Usage

As mentioned in the beginning of this chapter, JDBC defines a set of interfaces for accessing any given database. The JDBC API facilitates the transfer of data from persistent storage into an application. This section explores loading a database driver, connecting to a database, and accessing the data using JDBC and SQL. To get started with JDBC, all that’s needed is the JDK, 1.2 or later.

Quote from java.sun.com: “All of the JDBC™ 3.0 API, including both the java.sql and javax.sql packages, is bundled with the Java 2 Platform, Standard Edition, version 1.4 (J2SE). If you have downloaded the J2SE platform, you already have all of the JDBC™ API and do not need to download anything . . .

JDBC Drivers

As mentioned earlier, all databases work in their own unique way. JDBC provides the interfaces that each and every JDBC driver must implement for its corresponding database. JDBC drivers essentially translate function calls into database-specific tasks. Most commercial databases ship with drivers. However, third-party drivers are often available as well and are sometimes better. Driver implementations vary in their speed, efficiency, reliability, and so on. There are also different types of drivers:

Type 1: JDBC-ODBC Bridge, which translates JDBC calls into ODBC calls. Microsoft’s Object Database Connectivity (ODBC) is Microsoft’s standard interface for accessing relational databases.

Type 2: Converts JDBC calls into native database calls using .dll files or other native binary code.

Type 3: Communicates through middleware/proxy via network protocols. No drivers needed. Broad support for multiple vendors and protocols. Secure.

Type 4: This driver type uses the network protocols of the RDBMS directly and requires no installation of code to the client.

The differences between the driver types are mostly the communications protocols. Despite the differences, the JDBC API is used in the same uniform way, regardless of the driver type. For example, switching from a type 2 driver to a type 4 driver does not require any modification to the code. In fact, switching driver manufacturers, or even databases (with a few exceptions), requires little or no modification of code. One thing to note is that not every database comes with all four types. In fact, some may not ship with any Java drivers, although these days that’s pretty rare given Java’s incredible popularity and success in so many areas of computing. At any rate, between the database vendor and the third-party driver manufacturers out there, the bases are covered and drivers shouldn’t be hard to hunt down.

Connecting to the Database

Drivers are generally packaged in the form of a .jar or .zip archive. Whatever shape or form the driver comes in, it should be incorporated into the project. When using an IDE, the .jar or .zip file can be added to the project classpath, which will hopefully enable the code-complete features of the IDE to work with the driver. If you are not using an IDE, just be sure to add the driver to the build and runtime classpath. When the project configuration is properly set up, the following code will load the driver at runtime, assuming you are using MySQL:

Class.forName("com.mysql.jdbc.Driver");

If the classpath is not set correctly, the com.mysql.jdbc.Driver class will not be resolved, and the driver will not be loaded. Thus, communication to the database will not be possible. If everything is set up correctly, communication between the client and the database can be established via the java.sql.Connection object. When the driver has been loaded, a connection to the database can be requested from the java.sql.DriverManager class in one of the following ways:

Connection conn =      DriverManager.getConnection(url);   Connection conn =      DriverManager.getConnection(url, username, password);   Connection conn =      DriverManager.getConnection(url, propertiesFile); 

Data Access Using SQL

All database access to standard relational databases is through SQL. Invoking SQL can be done explicitly, such as in the command-line examples mentioned previously or implicitly through a utility or library. A few technologies exist which almost completely abstract developers from the actual SQL used for database access. For example, a certain form of EJB (Enterprise JavaBeans) can be used to hide all of the SQL statements submitted to the database. All the developer must do is request a Java object and supply the primary key. The EJB then uses what is called CMP (Container Managed Persistence) to access the data for you. However, this method is a double-edged sword. Because the SQL is procedurally generated and hidden, there isn’t much flexibility or room for tweaking. Most developers prefer to write the SQL, because this option offers the most functionality and flexibility and also allows the SQL to be tuned for specific purposes. SQL is a powerful and rich language, and the full syntax and usage is beyond the scope of this chapter. Many SQL resources are available to describe the full SQL language and its usage, but at its most simple, SQL statements are formed using one of the following keywords:

SELECT: Read data from the database

INSERT: Write data to the database

UPDATE: Update preexisting data in the database

DELETE: Delete data from the database

Examples:

Fetch some player data:

SELECT name FROM player WHERE name=’Will’

Insert a player:

INSERT INTO player VALUES (..., ..., ...)

Update player information:

UPDATE player SET item=3 WHERE id=2

Delete a player:

DELETE FROM player WHERE name=’Will’

SQL statements are stored in strings and are executed in the context of a java.sql.Statement object. Statements are created, prepared, and executed via the Connection object. The statement, when executed, may return a Boolean, an int, int[], or a ResultSet, depending on the SQL sent to the database. After a SQL query has been formulated and stored in a string, it may be executed like the following lines:

Statement stmt = conn.createStatement(); stmt.execute(query); 

The way in which the execute() method is used varies, depending on the task at hand. The different method signatures today in the JDK 1.4 follow:

boolean execute(String sql) boolean execute(String sql, int autoGeneratedKeys) boolean execute(String sql, int[] columnIndexes) boolean execute(String sql, String[] columnNames) int[] executeBatch() ResultSet executeQuery(String sql) int executeUpdate(String sql) int executeUpdate(String sql, int autoGeneratedKeys) int executeUpdate(String sql, int[] columnIndexes) int executeUpdate(String sql, String[] columnNames)

One form of statement called a PreparedStatement can be prepared in advance and executed much more quickly. A PreparedStatement is typically used when the same SQL statement is expected to be executed many times. More information on PreparedStatements is discussed later.

In the case that data is returned to the client, the results may be accessed via the java.sql.ResultSet interface. The data is represented to the client as a table. A cursor, which is like a temporary pointer into the table, is provided and can be positioned anywhere on the table, and getter methods are used to retrieve data from the cells. The code will look something like this:

Statement stmt = conn.createStatement(); String sql = "SELECT * FROM player"; ResultSet rs = stmt.executeQuery(sql); While(rs.next()) {     rs.getXXX(columnNumber); }

Depending on the data type of the column you’re accessing, the getter methods will vary. A few of these methods follow:

rs.getInt() rs.getString() rs.getBytes() rs.getDate()

If anything goes wrong during execution, a java.sql.SQLException will be thrown. The SQLException provides a few useful methods, which help to locate problem areas producing the error condition. A couple of them are worth mentioning.

int getErrorCode(): Each database vendor has its own set of exception codes. The application will typically log the error code received. The error code helps with debugging, because it can be referenced in the database documentation and will hopefully shed some light on an otherwise dark situation.

String getMessage(): This function returns a verbose (hopefully) message indicating what went wrong, or at least how the driver perceived the error condition. This function is inherited from java.lang.Throwable and can naturally be used with any Exception.

By default, all of the database inserts/updates are committed as soon as the statements finish execution. However, it is possible to invoke SQL commands in a transactional scope, which is configured using the Connection object:

conn.setAutoCommit(false);

Sometimes several tables in the database must be updated at once, because they are all executed in the same logical context. Transactions require that either all of the database tables in question are updated successfully, or none at all. The idea is to perform all of the database updates and then commit those changes when they’ve all been completed, assuming nothing went wrong during the transaction. After all of the appropriate tables have been written to, the data can be finalized simply by calling the commit() function:

conn.commit();

Your saving grace is that if anything does go wrong, there is always an undo option, typically placed in a catch() block:

conn.rollback();

The entire code block will look something like the following:

Connection conn = DriverManager.getConnection(url); conn.setAutoCommit(false); Statement stmt = conn.createStatement(); try {     stmt.execute(query1);     stmt.execute(query2);     stmt.execute(query3);     conn.commit(); } catch(SQLException sqlE) {      conn.rollback(); }

JDBC resources should be released when they are no longer needed. The Connection is automatically closed when it is garbage collected. However, depending on the configuration of the JVM and garbage collection strategy employed, that Connection may hang around a while before it is collected. Thus, avoid tying up database resources by closing the objects manually. It’s best to do this in reverse order:

results.close(); stmt.close(); conn.close();



Practical Java Game Programming
Practical Java Game Programming (Charles River Media Game Development)
ISBN: 1584503262
EAN: 2147483647
Year: 2003
Pages: 171

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