Page Operations
Browse Space
Here is one solution.
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="Calendar"> <resultMap id="quarterMap" class="calendarQuarter" groupBy="quarter"> <result property="quarter" column="quarter"/> <result property="name" column="name"/> <result property="description" column="description"/> <result property="months" resultMap="Calendar.monthMap"/> </resultMap> <resultMap id="monthMap" class="calendarMonth"> <result property="name" column="monthName"/> <result property="description" column="monthDescription"/> <result property="broadcastStartDate" column="broadcastStartDate"/> <result property="broadcastEndDate" column="broadcastEndDate"/> </resultMap> <select id="getQuartersForServiceYear" resultMap="quarterMap"> select distinct QuarterNumber as quarter, QuarterName as name, QuarterDesc as description, SeasonYear as year, MonthName as monthName, MonthDesc as monthDescription, min(broadcastDate) as broadcastStartDate, max(broadcastDate) as broadcastEndDate from BroadcastDate where SeasonYear = #year# and MonthName is not null group by QuarterDesc, QuarterNumber, QuarterName, SeasonYear, MonthName, MonthDesc order by broadcastStartDate </select> </sqlMap>
When you call
List myList = executeQueryForList("Calendar.getQuartersForServiceYear", 2005);
The important items here are the
groupBy
<result property="months" resultMap="Calendar.monthMap"/>
Summary: You have a single query that will return results such as
parent1, child1 parent1, child2 parent2, child1 parent3, child1 parent3, child2 parent3, child3 ....
The groupby will take care of figuring out that you really want a list of parent objects with their matching child objects as a list under them.
I think this example is far too complicated. It confuses the reader because there is strange detail in there to understand which is irrelevent to the example. I remember struggling long and hard with the manual with this example before calling for some help from the mailing list.
Also, I think before showing the result map, it would be helpful to show both the key elements of the database tables and the objects which which you are going to map.
So here is my attempt at a simple example where the purpose of the groupBy attribute is to prevent each row in the results where the cat-id is the same from creating a separate Java Bean:
Database tables:
product_category with fields cat_id (integer primary key), cat_name varchar
product with fields prod_id (integer primary key), prod_name varchar, cat (integer foreign key to cat_id)
Java Beans:
CategorisedProductList with properties
Category cat;
List<Product> prodList;
Category with properties
Integer catid;
String catname;
Product with properties
Integer prodid;
String prodname;
SQLMap xml file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<sqlMap namespace="Products">
<resultMap id="prodict-list" class="CategorisedProductList" groupBy="cat.catid" >
<result property="cat.catid" column="cat_id"/>
<result property="cat.catname" column="cat_name" />
<result property="prodList" resultMap="Products.product-detail" />
</resultMap>
<resultMap id="product-detail" class="Product" >
<result property="prodid" column="prod_id" />
<result property="prodname" column="prod_name" />
</resultMap>
<select id="getProductListByCategory" ResultClass="CategorisedProductList" >
SELECT * FROM product_category JOIN product ON cat_id=cat ORDER BY cat_name ASC
</select>
</sqlMap>
Posted by Two points
1) At one point in the preamble I refer to cat-id. That should be cat_id
2) If instead of a JOIN, I did a RIGHT JOIN and there were records in the product table that did not have a matching category id, an execute of something like this
List<CategorisedProductList> list = sqlmap.mapQueryForList("getProductListByCategory");
would populate the list object with some CategorisedProductList objects in which the cat property was null
Alan, thank you for your example. It looks like more familier.
I tried it and it works! But I'd like to correct some small things.
SQLMap:
<resultMap id="prodict-list"..
=> id="product-list"
<select id="getProductListByCategory" ResultClass="CategorisedProductList">
=> resultMap="product-list"
Client Class:
sqlmap.mapQueryForList("getProductListByCategory")
=> queryForList("getProductListByCategory", null)
<select id="getQuartersForServiceYear" resultMap="quarterMap" parameterClass="int">
where SeasonYear = #value#