Finding the best approach when accessing a database from Java can be a daunting task. The most common solution is to program directly to the JDBC (Java Database Connectivity) APIs. The result is hard-to-read source files, bloated with complex code that has nothing to do with business logic. To make matters worse, JDBC does nothing to address the natural differences that usually exist between a system's object model and its relational data model. This necessitates still more code to glue these disparate views together. There is clearly a need for tools to assist us in this area.
The iBATIS Database Layer, written by Clinton Begin, is a collection of open-source components that assist Java developers with data access. The most important of these components is the SQL Maps framework. SQL Maps provides an efficient way to graph database values to Java objects through the use of XML configuration files. This article will use the latest production release of SQL Maps, 1.3.1, to demonstrate how SQL Maps work using a series of examples. It will then conclude with a discussion of the benefits of using iBatis SQL Maps.
A First Look
To view SQL Maps in action, I will use a simple demo application that allows employees to make conference room reservations. It will not be possible to cover SQL Maps in its entirety, but hopefully there will be enough here to entice you to visit the iBATIS site and explore the project in greater detail.
There are two tables to consider in our application, conference_rooms and room_reservations. The conference_rooms table identifies conference_rooms by id and name. The field number_of_seats can be used to guage the size of a room. The room_reservations table, linked to conference_rooms by room_id, stores reservation times and the team ('development team', 'accounting team') making the reservation. For simplicity's sake, there is a one-to-one mapping between these tables, conference_rooms and room_reservations, and the JavaBeans ConferenceRoom and RoomReservation.
Given this setup, let's first consider a code snippet that you might use if populating a ConferenceRoom bean with JDBC:
String roomToUse = "Conf Room North";
ConferenceRoom confRoom = new ConferenceRoom();
try{
Connection conn = dSrc.getConnection();
PreparedStatement pstmt =
conn.prepareStatement("SELECT room_id,room_name,number_of_seats,
room_active " +
"FROM conference_rooms WHERE room_name = ?";
pstmt.setString(1,roomToUse);
rstst = ps.executeQuery();
rstst.next();
confRoom.roomId = rstst.getInt(1);
confRoom.roomName = rstst.getString(2);
confRoom.numberOfSeats = rstst.getInt(3);
confRoom.roomActive = rstst.getBoolean(4);
rstst.close();
pstmt.close();
}
catch(SQLException e) {
throw newRuntimeException(e);
}
finally {
conn.close();
}
As simple as the code's objective may be, it is complicated by JDBC 'plumbing.' Let's try this once again, the SQL Maps way:
String roomToUse = "Conf Room North";
ConferenceRoom confRoom = (ConferenceRoom)
sqlMap.executeQueryForObject("getConferenceRoomByName", roomToUse);
The SqlMap method, getQueryForObject, accepts two String parameters. The first parameter identifies the name of the mapped SQL statement we use to access the database. The second specifies a room name used in the query. The bean is instantiated, populated, and then returned, leaving us to focus our coding efforts on application logic.
Now, I want to step backwards through this example and examine what makes such clean code possible. To do this, we need to look at two additional items. The first is the SQL Map file. This file is an XML descriptor that contains our mapped SQL statements. At runtime, these statements are read into an application. When "getConferenceRoomByName" is referenced in Java, a PreparedStatement object executes the query and a ResultSet populates the bean. The demo application uses a single SQL Map file called Conferences.xml. An application may use several such SQL Map files.
<!Conferences.xml SQL Map file-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN"
"http://www.ibatis.com/dtd/sql-map.dtd">
<sql-map name="Conferences">
<mapped-statement name="getConferenceRoomByName"
result-class="entities.ConferenceRoom">
SELECT room_id as roomId,
room_name as roomName,
number_of_seats as numberOfSeats,
room_active as roomActive
FROM conference_rooms
WHERE room_name = #value#
</mapped-statement>
</sql-map>
The bulk of the "getConferenceRoomByName" mapped statement consists of SQL. The result-class attribute specifies the fully qualified Java object returned from a mapped statement. The #value# keyword denotes that a statement's parameter is either a String or a Java primitive, such as int.
The final piece of the puzzle is the SQL Map Configuration file:
<!SqlMapConfig.xml SQL Map configuration file-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map-config PUBLIC "-//iBATIS.com
//DTD SQL Map Config 1.0//EN"
"http://www.ibatis.com/dtd/sql-map-config.dtd">
<sql-map-config>
<datasource name = "hsql"
factory-class="com.ibatis.db.sqlmap.datasource.SimpleDataSourceFactory"
default="true">
<property name="JDBC.Driver" value="org.hsqldb.jdbcDriver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:hsqldb:hsql://localhost/conferences"/>
<property name="JDBC.Username" value="sa"/>
<property name="JDBC.Password" value=""/>
</datasource>
<sql-map resource="maps/Conferences.xml"/>
</sql-map-config>
The purpose of this file is two-fold. First, it configures a JDBC DataSource. In this case, it uses iBATIS' own DataSource implementation, SimpleDataSource. Secondly, the file declares each SQL Map file that an application uses. Our examples use the hsqldb database and a single Map file, Conferences.xml.
Upon application startup, we search the classpath for this configuration file and process it.
String resource = "maps/SqlMapConfig.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMap sqlMap = XmlSqlMapBuilder.buildSqlMap(reader);
The configuration needs to happen but one time, prior to the calling of any mapped statements. The sqlMap instance could be a Singleton, or possibly a ServletContext object in a web application, that we retrieve when we need to call a mapped statement. The Resource class we are using is provided by iBATIS to read files from the classpath.