JDBC Primer

If you're not using an ORM framework to do BMP, then you must understand at least the basics of JDBC to implement your entity beans. Of course, you ought to understand JDBC even if you're not interfacing with it directly. No matter which BMP approach you take, it's always JDBC that allows the required database access to take place in the end.

This section describes the fundamental interfaces and a key exception that you need to understand to follow the explanation of BMP given in this chapter. These interfaces and classes are all defined in the java.sql package. If you've worked with JDBC before, this will simply be a refresher because EJB doesn't place any new requirements on JDBC. If you're new to JDBC but you've worked with relational databases before, don't get anxious, because it's not an overwhelming topic by any means. If you're new to writing programs that access a database, there are entire books dedicated to the subject of JDBC. However, the coverage offered by most introductory Java books is adequate for what you need to understand for BMP. Many of the complicated aspects of working with a database, such as connection pooling, are handled for you by the container even when you use BMP.

The Connection Interface

A Connection object provides the interface you need to execute SQL statements using JDBC and retrieve the result sets that are returned. Obtaining a Connection is the first step in accessing the database from within a callback method. This is also the step that's the most different when you compare EJB to other applications that use JDBC. In a typical Java application, you call a static method on the DriverManager class and pass a database URL and login information to get a connection. As you'll see a little later, you use a different procedure to do this from an EJB.

For a simple BMP implementation, you really only need to be concerned with the following two methods of Connection :

 public PreparedStatement prepareStatement(String sql) throws SQLException;  public void close() throws SQLException; 

The prepareStatement method creates and precompiles a SQL statement based on a string you supply. You can include question marks in the string to serve as placeholders for any parameters required by the statement. The close method simply releases the resources associated with the Connection when you're finished with it.

The PreparedStatement Interface

The PreparedStatement interface allows you to supply values for a statement's parameters and then execute it. Parameter values are accessed using a number of setXXXX methods that accept an integer parameter index and a value. Some of the more common methods consist of

 public void setBigDecimal(int paramIndex, BigDecimal x) throws SQLException;  public void setBoolean(int paramIndex, boolean x) throws SQLException;  public void setDouble(int paramIndex, double x) throws SQLException;  public void setFloat(int paramIndex, float x) throws SQLException;  public void setInt(int paramIndex, int x) throws SQLException;  public void setLong(int paramIndex, long x) throws SQLException;  public void setNull(int paramIndex, int sqlType) throws SQLException;  public void setObject(int paramIndex, Object x) throws SQLException;  public void setString(int paramIndex, String x) throws SQLException;  public void setTimestamp(int paramIndex, Timestamp x) throws SQLException; 

Note

Unlike what you normally see with Java, the parameter indexes for a PreparedStatement start with 1 and not 0.

The setXXXX method you use to assign a parameter value determines the SQL type that the value is converted to before being sent to the database. Notice that the signature for setNull requires you to specify the SQL type of the column to which you're writing a null value. These types are defined as constants by the java.sql.Types class. To write non-null values, you must make sure you call the correct set method. Table 6.1 defines the mappings between the Java and SQL types. You can use this to determine which set method to call based on the type of column you're accessing.

Table 6.1. Mapping Java Types to SQL Types

Java Type	SQL Type
BigDecimal	NUMERIC
boolean	BIT
byte	TINYINT
Date	DATE
double	DOUBLE
float	FLOAT
int	INTEGER
long	BIGINT
short	SMALLINT
String	VARCHAR (or LONGVARCHAR if necessary)
Time	TIME
TimeStamp	TIMESTAMP

After you've created a PreparedStatement and assigned its parameter values, it's ready to be executed. You can do this using one of the following methods:

 public boolean execute() throws SQLException;  public ResultSet executeQuery() throws SQLException;  public int executeUpdate() throws SQLException; 

You use executeQuery to execute a select statement and retrieve its results. The executeUpdate method executes an insert, update, or delete statement and returns the number of rows that were affected by the statement. You can use the execute method in place of either of the other two, but it's intended to support statements that return multiple result sets or row counts. If you don't need this behavior, it's easier to work with executeQuery and executeUpdate .

The ResultSet Interface

The ResultSet interface gives you access to the data returned by a select statement. This interface includes a number of methods used to navigate through a result set and extract values for a particular column of a selected row. For simple forward navigation, the next method can be used to move through the ResultSet one row at a time until it's exhausted. A ResultSet cursor is initially positioned before the first row when it's returned, so you must call next before attempting to access any data. After the cursor is positioned on a valid row, you can call a getXXXX method and pass it either a column index or a column name to access the value for that field. The following declarations are examples of these methods:

 public int getInt(int columnIndex) throws SQLException;  public int getInt(String columnName) throws SQLException; 

The SQLException Class

Virtually every JDBC method is declared to throw a SQLException in case any failure occurs. If a SQLException is thrown, you can access a text description of the error and a vendor-specific error code. It's also possible for this type of exception to include other exceptions chained to it to provide more information about the underlying error.