<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN"
"http://iBATIS.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<select id="salesByListOfMapsSQL" resultClass="java.util.HashMap">
SELECT
E.EMPLOYEE_ID "ID",
E.FIRST_NAME "FIRST",
E.LAST_NAME "LAST",
MS.TOTAL_SALES "TOTAL",
MS.LATEST_SALE
FROM
EMPLOYEE E,
MONTHLY_SALES MS
WHERE
E.EMPLOYEE_ID = MS.EMPLOYEE_ID
AND MS.MONTH = #value#
</select>
<resultMap id="searchResultList" class="MonthlySalesBean">
<result property="employeeID" column="ID"/>
<result property="first" column="FIRST"/>
<result property="last" column="LAST"/>
<result property="total" column="TOTAL"/>
<result property="latestSale.amount" column="LATEST_SALE"/>
</resultMap>
<select id="salesByJavaBeansSQL" resultMap="searchResultList">
SELECT
E.EMPLOYEE_ID "ID",
E.FIRST_NAME "FIRST",
E.LAST_NAME "LAST",
MS.TOTAL_SALES "TOTAL",
MS.LATEST_SALE
FROM
EMPLOYEE E,
MONTHLY_SALES MS
WHERE
E.EMPLOYEE_ID = MS.EMPLOYEE_ID
AND MS.MONTH = #value#
</select>
</sqlMap>

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletContext;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
public class ServiceLocatorBean implements ServiceLocatorIF {
private static final long serialVersionUID = -7166271873610635886L;
//the Spring application context
    private ApplicationContext appContext;
DAO dao = null;
public ServiceLocatorBean() {
try {
// get the spring context
            ServletContext context = FacesUtils.getServletContext();
this.appContext = WebApplicationContextUtils.getRequiredWebApplicationContext(context);
// create instance of the business object
            this.dao = (DAO) this.lookupService("dao");
Connection conn = this.dao.getSqlMapClient().getDataSource().getConnection();
conn.setAutoCommit(false);
/*
Creating a statement lets us issue commands against
the connection.
*/
Statement s = conn.createStatement();
// just in case old tables from prior run (after first run which
// will create the USER1 schema)
            try {
s.execute("drop table employee");
s.execute("drop table monthly_sales");
} catch (Exception ex) {
// not to be concerned (at least in this example
            }
/*
We create a table, add a few rows, and update one.
*/
s.execute("create table employee (employee_id int, first_name varchar(40), last_name varchar(40))");
s.execute("insert into employee values (1,'sterning', 'chen')");
s.execute("insert into employee values (2,'yuxuan', 'Wand')");
s.execute("insert into employee values (3,'Mickey', 'Li')");
s.execute("create table monthly_sales (employee_id int, total_sales numeric(16, 2), latest_sale numeric(8, 2), month int)");
s.execute("insert into monthly_sales values (1, 1600.50, 32.50, 1)");
s.execute("insert into monthly_sales values (2, 1544.20, 12.50, 1)");
s.execute("insert into monthly_sales values (3, 18814.80, 78.65, 1)");
s.execute("insert into monthly_sales values (1, 1450.50, 10.65, 2)");
s.execute("insert into monthly_sales values (2, 2004.25, 52.10, 2)");
s.execute("insert into monthly_sales values (3, 9819.00, 40.65, 2)");
s.close();
conn.commit();
} catch (SQLException sqle) {
// just means the tables already exist
            sqle.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public DAO getDao() {
return this.dao;
}
public Object lookupService(String serviceBeanName) {
return appContext.getBean(serviceBeanName);
}
}