SQL Maps in Action
Now that we have some insight into how SQL Maps works, what follows is a series of code excerpts that highlight additional features of SQL Maps. In each case, a set of parentheses identifies the relevant file.
A reservation system wouldn't be much without a means to create reservations. In this example, a reservation is added for the confRoom bean.
(SQL MAP)
<mapped-statement name="insertReservation">
INSERT INTO room_reservations
(room_id,reservation_start,reservation_end,reservation_team)
VALUES
(#roomId#,#reservationStart#,#reservationEnd#,#reservationTeam#)
</mapped-statement>
(JAVA)
Calendar start = new GregorianCalendar();
start.set(2004,Calendar.MARCH,10,12,30);
Calendar end = new GregorianCalendar();
end.set(2004,Calendar.MARCH,10,14,30);
RoomReservation ourReservation = new RoomReservation();
ourReservation.setRoomId(confRoom.getRoomId());
ourReservation.setReservationStart(new Date(start.getTimeInMillis()));
ourReservation.setReservationEnd(new Date(end.getTimeInMillis()));
ourReservation.setReservationTeam("Development Team");
sqlMap.executeUpdate("insertReservation", ourReservation);
A JavaBean is passed to this SQL Maps statement. The bean's properties are referenced with the syntax #{property}#. The executeUpdate method is used to insert, update, or delete from a database.
There should now be a single reservation for confRoom. Passing in roomId, which happens to be a Java primitive of type int, we obtain confRoom's reservation count.
(SQL MAP)
<mapped-statement name="getReservationCountForRoom"
result-class="java.lang.Integer">
SELECT COUNT(1) as value
FROM room_reservations
WHERE room_id = #value#
</mapped-statement>
(JAVA)
Integer cntReservations = (Integer) sqlMap.executeQueryForObject(
"getReservationCountForRoom", new Integer(confRoom.getRoomId()));
System.out.println("Reservation Cnt for " + confRoom.getRoomName(
+ " is " + cntReservations);
We've already seen the #value# syntax. As you recall, it is used when working with Java primitives. SQL Maps works solely with Java objects so primitives, either passed in or returned from a statement, must be wrapped in their object equivalents. The clause 'as value' is used to select a single, primitive value from the database.
Along with mapped-statement, a SQL Map file may contain the parameter-map and result-map attributes for handling more complex mapping requirements. Earlier, when we persisted a RoomReservation bean to the database, we used an implicit parameter map. SQL Maps used Java Reflection to match each #{property}# entry in the mapped statement with a bean property.
Using an explicit parameter map provides two useful capabilities. First, it allows you to specify a database type for an object or bean property. Second, an explicit parameter map gives you a chance to provide a default value when a parameter is null. Explicit parameter maps can be defined with <parameter-map> </parameter-map> tags, and then referenced in a mapped statement with the "parameter-map" attribute. However, given the limited goals of a parameter map, SQL Maps provides an alternative, 'inline' syntax to simplify things.
To obtain the number of reservations for a particular team while specifying a parameter's database type and providing a null default, use the following:
(SQL MAP)
<!--Get count using an inline parameter map, passing in a String.-- >
<mapped-statement name="getReservationCountByTeam"
parameter-class="java.lang.String"
result-class="java.lang.Integer">
SELECT COUNT(1) as value
FROM room_reservations
WHERE reservation_team = #value:VARCHAR:NONE#;
</mapped-statement>
(JAVA)
//Use an in-line parameter map.
Integer cntTeamReservations = (Integer) sqlMap.executeQueryForObject(
"getReservationCountByTeam", ourReservation.getReservationTeam());
Direct your attention to the WHERE clause. It states that the parameter identifying the team must be compatible with the database type VARCHAR. In addition, if this parameter object happens to be null, 'NONE' is used.
It is far more common to find an explicit result map in a SQL Map file. Explicit result maps allow you to specify a default return value when a query returns null. More importantly, result maps enable a developer to map complex objects into an application.
Previously, mapped statements returned either primitive wrappers or entire JavaBeans. Consider this next example where we want to return a subset of properties for the conference room with the most number of seats.
(SQL MAP)
<result-map name="get-id-name-and-number-of-seats"
class="java.util.HashMap">
<property name="id" column="room_id"/>
<property name="name" column="room_name" null="NO NAME"/>
<property name="seatNum" column="number_of_seats"/>
</result-map>
<mapped-statement name="getLargestConferenceRoomInfo"
result-map="get-id-name-and-number-of-seats"
result-class="entities.ConferenceRoom">
SELECT *
FROM conference_rooms
WHERE number_of_seats = (SELECT MAX(number_of_seats)
FROM conference_rooms);
</mapped-statement>
(JAVA)
Map largestRoomInfo = new HashMap();
largestRoomInfo = (HashMap) sqlMap.executeQueryForObject(
"getLargestConferenceRoomInfo",null);
System.out.println("largest room info: id : "
+ largestRoomInfo.get("id")
+ ",name : "
+ largestRoomInfo.get("name")
+ ",seatNum : "
+ largestRoomInfo.get("seatNum"));
The Map largestRoomInfo returns with map keys for each bean property name. The ability to map results to a Java Collection gives us a good deal of flexibility and power. Also note that this result map is not bound to only one mapped statement. Once defined, a result map can be reused again and again.
IE 7
Firefox 2.0
