Mapping Java Objects to the results of SQL Queries |
|
As of December 2006, iBATIS has been simplified to perform one function, and only one function: data mapping. While iBATIS used to support a DAO layer, this has been deprecated in favour of using Spring and IoC. This article will stick to discussing the data mapper; for more information on the DAO implementation, please refer to the Spring framework documentation (specifically here). The key documentation for iBATIS is the Developer Guide, with the very latest here in the SVN repository. OK, on to Hibernate vs. iBATIS...
Fundamentally, iBATIS maps Java Objects to the results of SQL Queries, whereas Hibernate maps Java Objects directly to database tables, traditional Object-Relational Mapping. The benefits of Hibernate are that it automatically generates all the SQL for your and the cache invalidation can be more fine grained. iBATIS is more flexible especially if you are a strong SQL query writer. You have control over exactly how the SQL queries are written. Another way of looking at it to say that Hibernate works well if you have mostly standard queries(CRUD, Find by Criteria, etc.) and if you are designing your object model first. If you are working with a legacy system or a schema designed by a DBA, iBATIS often makes a better choice. Of course, we're not discussing absolutes here, they're both much better than raw SQL! The thing with iBATIS is that there isn't a whole lot of magic, and you get full control over the SQL, so you can see what's going on and how to change it. Compared to other frameworks, it has a shorter learning curve, but can take more time to develop and maintain, since you have to write all your queries (using Abator can short-circuit this step) and if your object model changes you have to go through all your queries and make sure to make all the necessary changes to reflect the changes in your object model. |
While there's nothing particularly special about the use of iBATIS within a Wicket application, the normal Wicket requirement of ensuring that things that you store in your pages are Serializable mean that you may need to be careful how you store references to DAO-type objects.
The following is intended to suggest one way in which this might be done.
Note: While this example uses Spring and the SpringBean annotation, neither are strictly required, but they do simplify things enough that I felt it was worth using them, even without any previous knowledge of them.
Dramatis Personae
SqlMapClientDaoSupport and make the DB call using a definition in an SqlMap relating to the Item.Another take on the above may be obtained by using the Abator code generator tool - See below.
An interface describing the DAO operations we want, e.g. load, save, delete, etc.
public interface EntryDao {
Entry load(String name);
void save(Entry config);
void delete(String name);
}
|
An implementation of the DAO.
the particular schema this is accessing is a simple Name/Value/LastChanged one, i.e. No distinct id
public class EntryDaoImpl extends SqlMapClientDaoSupport implements EntryDao, Serializable {
public Entry load(String name) {
return (Entry) getSqlMapClientTemplate().queryForObject("Entry.get", name);
}
public void save(Entry entry) {
if (entry.getLastChanged() == null) {
getSqlMapClientTemplate().insert("Entry.insert", entry);
} else {
getSqlMapClientTemplate().update("Entry.update", entry);
}
}
public void delete(String name) {
getSqlMapClientTemplate().delete("Entry.delete", name);
}
}
|
IBATIS SqlMap definitions, one per operation.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Entry">
<select id="get" parameterClass="string" resultClass="package.name.model.Entry">
SELECT name, value, LAST_CHANGE as lastChange FROM TB_ENTRY WHERE name = #value#
</select>
<!-- Use Entry object (JavaBean) properties as parameters. Each of the
parameters in the #hash# symbols is a JavaBeans property. -->
<insert id="insert" parameterClass="package.name.model.Entry">
INSERT INTO TB_CONFIG (NAME, VALUE, DESCRIPTION, LAST_CHANGE) VALUES (#name#, #value#, #description#, SYSDATE)
</insert>
<update id="update" parameterClass="package.name.model.Entry">
UPDATE TB_ENTRY
SET
VALUE = #value#,
DESCRIPTION = #description#,
LAST_CHANGE = SYSDATE
WHERE NAME = #name#
</update>
<!-- Use primitive properties as parameter for delete. -->
<delete id="delete" parameterClass="string">
DELETE FROM TB_ENTRY WHERE NAME = #value#
</delete>
</sqlMap>
|
A couple of notes:
#value# in the delete block is just a placemarker. The actual name, i.e. "value", isn't significant there, unlike the names used in the update/insert sections where they're used to determine the JavaBean accessors to use.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!--
Allow the use of properties from application.properties
throughout this configuration file
-->
<bean id="placeholderConfig"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location"><value>classpath:application.properties</value></property>
<property name="systemPropertiesModeName"><value>SYSTEM_PROPERTIES_MODE_OVERRIDE</value></property>
</bean>
<!--data source definition-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName"><value>${jdbc.driver}</value></property>
<property name="url"><value>${jdbc.url}</value></property>
<property name="username"><value>${jdbc.user}</value></property>
<property name="password"><value>${jdbc.password}</value></property>
</bean>
<!--
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName"><value>${jndi.name}</value></property>
</bean>
-->
|
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- setup dao implemations -->
<bean id="entryDaoImpl" class="package.impl.EntryDaoImpl">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
<!-- setup transaction manager -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- these are transactional proxys for dao which ensure proper transaction handling -->
<!-- Note that they're loaded in via @SpringBean calls. -->
<bean id="baseTransactionProxy"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
abstract="true">
<property name="transactionManager" ref="transactionManager" />
<property name="transactionAttributes">
<props>
<prop key="save">PROPAGATION_REQUIRED</prop>
<prop key="delete">PROPAGATION_REQUIRED</prop>
<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>
<bean id="entryDao" parent="baseTransactionProxy">
<property name="target" ref="entryDaoImpl" />
</bean>
<!-- End transaction management -->
|
Just for completeness...
<!-- Wicket Application -->
<bean id="wicketApplication" class="com.zaryba.topup.web.config.TopUpConfigApplication"/>
</beans>
|
This contains the 'global' settings for iBATIS and the collection of SQL maps in use. When using together with Spring, it can be very concise...
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings useStatementNamespaces="true" />
<!-- Identify all SQL Map XML files to be loaded by this SQL map.
Notice the paths are relative to the classpath. For now, we only have one -->
<sqlMap resource="sqlmap/Entry.map.xml" />
</sqlMapConfig>
|
Note that the Spring TransactionProxyFactoryBean is serializable, so we can do the following in the Wicket component as long as our application extends AnnotSpringWebApplication.
public class MyPage extends WebPage {
@SpringBean(name="entryDao") private EntryDao entryDao;
public MyPage() {
...
|
Note: If we don't want to/can't use @SpringBean then there are other options available, e.g. [ storing the DAO in the Application|Spring#Application_Object_Approach ], etc.
iBATIS SqlMapClientTemplate does have a queryForList(String statementName, Object parameterObject, int skipResults, int maxResults) method, but note that it works by obtaining all the elements(keys) from the DB then just returning the selected part of the list. If wanting to handle this more efficiently, it's down to the (DB-specific) SQL.
Oracle's paging is rather involved due to it's particular implementation of ROWNUM. However, this example shows how it can work. (It also shows a bit more too, e.g. dynamic & sortable SQL, but that's not the main point here!)
public EntryDataProvider extends SortableDataProvider {
...
public Iterator iterator(int first, int count) {
QueryParam qp = null;
SortParam sp = getSort();
qp = new QueryParam(first, count, sp.getProperty(), sp.isAscending());
return entryDao.find(qp, filter);
}
...
}
|
In the DaoImpl, set up a 'map' data structure to pass various things in.
public Iterator find(QueryParam qp, Entry filter) {
Map map = new HashMap();
String name = filter.getName();
map.put("name", name == null ? null : "%" + name.toUpperCase() + "%");
map.put("sort", qp.getSort());
map.put("sortasc", (qp.isSortAsc() ? " asc" : " desc"));
int first = qp.getFirst();
map.put("min", first + 1);
map.put("max", first + qp.getCount());
return getSqlMapClientTemplate().queryForList("Config.getList", map).listIterator();
}
|
In the Entry.sql.map, the variables are taken from the Map passed in.
<select id="getList" parameterClass="map" resultClass="package.model.Entry">
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT name FROM TB_ENTRY
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="name">
UPPER(name) LIKE #name#
</isNotNull>
</dynamic>
ORDER BY $sort$ $sortasc$) a WHERE ROWNUM <![CDATA[ <= ]]> #max#) WHERE rn <![CDATA[ >= ]]> #min#
</select>
|
Some points...
dynamic section is more complex than needed for the example, as it caters for multiple sub-elements. The idea is that you have multiple blocks and the first time one triggers, the prepend from the outer dynamic block overrides the prepend from the inner block.<isNotNull property="name" ...See http://ibatis.apache.org/docs/tools/abator/ for documentation.
Abator is a code generator for iBATIS that will examine a database table/tables and generate iBATIS artefacts that can be used to access the table(s).
Abator seeks to make a major impact on the large percentage of database operations that are simple CRUD (Create, Retrieve, Update, Delete). You will still need to hand code SQL and objects for custom queries, or stored procedures, but it generates code that should cover much of the required usage (See Using the Abator Generated Objects) and provide a good basis for creating the rest.
Abator will generate:
Abator can run as a standalone JAR file, or as an Ant task, or as an Eclipse plugin.
I'd really recommend starting with this, as it proves a good jump-start!