The final two examples involve a scenario almost every Java applications will encounter. Objects do not exist in their own isolated bubble. Rather, objects have relationships with other objects and these relationships determine behavior. The room_reservations table is linked to conference_room by the room_id column. In the world of Java, however, this might manifest itself in a java.util.List attribute on the ConferenceRoom bean. This List would contain all associated RoomReservation beans. We will modify the ConferenceRoom JavaBean by adding the reservationsList property. Now, if we wish to populate a ConferenceRoom bean, we will want its List of RoomReservations too.
(SQL MAP)
<result-map name="get-conference-room"
class="entities.ConferenceRoom">
<property name="roomId" column="room_id"/>
<property name="roomName" column="room_name"/>
<property name="numberOfSeats" column="number_of_seats"/>
<property name="roomActive" column="room_active"/>
<property name="reservationsList" column="room_id"
mapped-statement="getReservationsByRoomId"/>
</result-map>
<result-map name="get-room-reservation"
class="entities.RoomReservation">
<property name="reservationId" column="reservation_id"/>
<property name="roomId" column="room_id"/>
<property name="reservationStart" column="reservation_start"/>
<property name="reservationEnd" column="reservation_end"/>
<property name="reservationTeam" column="reservation_team"/>
</result-map>
<mapped-statement name="getConferenceRoomById"
result-map="get-conference-room">
SELECT *
FROM conference_rooms
WHERE room_id = #value#;
</mapped-statement>
<mapped-statement name="getReservationsByRoomId"
result-map="get-room-reservation">
SELECT *
FROM room_reservations
WHERE room_id = #value#;
</mapped-statement>
(JAVA)
ConferenceRoom roomWithReservations =
(ConferenceRoom) sqlMap.executeQueryForObject(
"getConferenceRoomById", new Integer(confRoom.getRoomId()));
Okay, so this one is a little more complex. But there really is not that much to it. Two result maps are used, one for ConferenceRoom and one for RoomReservations. Two mapped statements populate the beans by referencing those result maps. Obtaining the reservations is made possible by the following line in the get-conference-room result map:
<property name="reservationsList" column="room_id"
mapped-statement="getReservationsByRoomId"/>
SQL Maps looks to the ConferenceRoom JavaBean for a List by the name of reservationsList. If the List property is there, SQL Maps uses the getReservationsByRoomId statement to populate it.
The SqlMap class also contains some convenience methods that return Java Collections. To retrieve a List of RoomReservations, without the ConferenceRoom bean, we can do this:
(SQL MAP)
<mapped-statement name="getReservationsList"
result-map="get-room-reservation">
SELECT *
FROM room_reservations;
</mapped-statement>
(JAVA)
List reservationsList =
sqlMap.executeQueryForList("getReservationsList", null);
I've demonstrated a number of ways to use SQL Maps, yet I close this section with many great SQL Map features unexamined. These features include transaction support, object caching, support for dynamic querying, and the ability to extend mapped statements for maximum code reusability.
The Many Benefits of SQL Maps
If I've done my job at all, you have a fairly good idea how using SQL Maps can simplify Java data access. However, it may not be amiss at this point to list a few important benefits the SQL Maps framework provides:
- Eliminates low-level JDBC code that is hard to read and prone to bugs
- Provides an efficient way to map database data, once retrieved, to Java objects
- Allows modifications to SQL configuratively, without recompiling code
- Enables an application to switch databases, again without recompilation
- Lets DBAs isolate and test SQL code for efficiency
With SQL Maps you can accomplish almost everything you could using JDBC, while sparing yourself the code complexity that JDBC inevitabely brings with it.
Conclusion
This article has demonstrated the basic features of SQL Maps. If you wish to work with the examples shown in this article, you can download them here. One of the best things about the iBATIS SQL Maps framework is that it is not only easy to use, but also very well documented. I encourage you to visit the iBATIS site and read the Developer's Guide to learn more.
About the Author
Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.
Turbo Screen Sharing
IE 7
Firefox 2.0
