Excerpt

hidden	true

Mapping Java Objects to the results of SQL Queries

Panel

borderStyle	solid
title	Table of contents

Table of Contents

minLevel	1

SideBar: What's iBATIS, or iBATIS vs Hibernate

(This really belongs off in it's own little section, but I can't do side bars in wiki markup!)

...

Code Block
public interface EntryDao { {panel} Entry load(String name); void save(Entry config); void delete(String name); {panel} }

EntryDaoImpl

An implementation of the DAO.
the particular schema this is accessing is a simple Name/Value/LastChanged one, i.e. No distinct id

Code Block

public class EntryDaoImpl extends SqlMapClientDaoSupport implements EntryDao, Serializable {
{panel}
    public Entry load(String name) {
        return (Entry) getSqlMapClientTemplate().queryForObject("Entry.get", name);
    }
{panel}

{panel}
    public void save(Entry entry) {
        if (entry.getLastChanged() == null) {
            getSqlMapClientTemplate().insert("Entry.insert", entry);
        } else {
            getSqlMapClientTemplate().update("Entry.update", entry);
        }
    }
{panel}

{panel}
    public void delete(String name) {
        getSqlMapClientTemplate().delete("Entry.delete", name);
    }
{panel}
}

Entry.map.xml

IBATIS SqlMap definations, one per operation.

Code Block

	xml
	xml



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
{panel}
        PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-2.dtd">
{panel}

<sqlMap namespace="Entry">

{panel}
    <select id="get" parameterClass="string" resultClass="package.name.model.Entry">
{panel}
	SELECT name, value, LAST_CHANGE as lastChange FROM TB_ENTRY WHERE name = #value#
{panel}
    </select>
{panel}

{panel}
    <!-- 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>
{panel}

{panel}
    <update id="update" parameterClass="package.name.model.Entry">
        UPDATE TB_ENTRY
            SET
                VALUE = #value#,
                DESCRIPTION = #description#,
                LAST_CHANGE = SYSDATE
            WHERE NAME = #name#
    </update>
{panel}

{panel}
    <!-- Use primitive properties as parameter for delete. -->
    <delete id="delete" parameterClass="string">
        DELETE FROM TB_ENTRY WHERE NAME = #value#
    </delete>
{panel}
</sqlMap>

A couple of notes:

...

Typical Wicket Spring Initialisation

Code Block

	xml
	xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
{panel}
    <!-- 
        Allow the use of properties from application.properties 
{panel}
	throughout this configuration file
{panel}
    -->
    <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>
{panel}

{panel}
    <!--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>
{panel}

{panel}
    <!--
        <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName"><value>${jndi.name}</value></property>
        </bean>
    -->
{panel}

IBATIS-specific

Code Block

	xml
	xml



{panel}
    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>
{panel}

{panel}
    <!-- setup dao implemations -->
    <bean id="entryDaoImpl" class="package.impl.EntryDaoImpl">
        <property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>
{panel}

{panel}
    <!-- setup transaction manager	-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
{panel}

{panel}
    <!-- 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>
{panel}

{panel}
    <bean id="entryDao" parent="baseTransactionProxy">
        <property name="target" ref="entryDaoImpl" />
    </bean>
    <!-- End transaction management -->
{panel}

Typical Wicket Spring Application

Just for completeness...

Code Block

	xml
	xml



{panel}
    <!-- Wicket Application -->
    <bean id="wicketApplication" class="com.zaryba.topup.web.config.TopUpConfigApplication"/>
{panel}
</beans>

IBATIS's sqlMapConfig.xml

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...

Code Block

	xml
	xml

<!DOCTYPE sqlMapConfig
{panel}
        PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
{panel}

<sqlMapConfig>
{panel}
    <settings useStatementNamespaces="true" />
{panel}

{panel}
    <!-- 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" />
{panel}
</sqlMapConfig>

DAO usage

Note that the Spring TransactionProxyFactoryBean is serializable, so we can do the following in the Wicket component as long as our application extends AnnotSpringWebApplication.

Code Block
public class MyPage extends WebPage { {panel} @SpringBean(name="entryDao") private EntryDao entryDao; {panel} {panel} public MyPage() { ... {panel}

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.

...

Code Block

public EntryDataProvider extends SortableDataProvider {
{panel}
    ...
    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);
    }
    ...
{panel}
}

In the DaoImpl, set up a 'map' data structure to pass various things in.

Code Block

{panel}
    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"));
{panel}

{panel}
        int first = qp.getFirst();
        map.put("min", first + 1);
        map.put("max", first + qp.getCount());
{panel}

{panel}
        return getSqlMapClientTemplate().queryForList("Config.getList", map).listIterator();
    }
{panel}

In the Entry.sql.map, the variables are taken from the Map passed in.

Code Block

	xml
	xml


{panel}
    <select id="getList" parameterClass="map" resultClass="package.model.Entry">
{panel}
		SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT name FROM TB_ENTRY
{panel}
        <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>
{panel}

Some points...

The 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.
If using XML 'reserved' chars, e.g. < or >, you'll need to escape them with CDATA sections.
The variables are used in 3 different ways in this example
1. The 'normal' #name# method
2. As a test, e.g. <isNotNull property="name" ...
3. To create dynamic SQL, e.g. ORDER BY $sort$

...

SqlMap XML Files
- Abator Generated SQL Map Files
Java Classes to match the primary key and fields of the table(s)
Abator Generated Java Model Classes
Java 'Example' classes to help with selecting subsets of the tables(s)
- Using the example Class with the Java2 or Java5 Generator Set
DAO Interfaces & DAOImpl Classes that use the above objects. Various formats (e.g. IBATIS, SPRING, etc)panel

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!

Child pages

Versions Compared

Old Version 1

New Version 2

Key

SideBar: What's iBATIS, or iBATIS vs Hibernate

EntryDaoImpl

Entry.map.xml

Typical Wicket Spring Initialisation

IBATIS-specific

Typical Wicket Spring Application

IBATIS's sqlMapConfig.xml

DAO usage

Child pages

Page History

Versions Compared

Old Version 1

New Version 2

Key

SideBar: What's iBATIS, or iBATIS vs Hibernate

EntryDaoImpl

Entry.map.xml

Typical Wicket Spring Initialisation

IBATIS-specific

Typical Wicket Spring Application

IBATIS's sqlMapConfig.xml

DAO usage