IBatis and Spring Integration
IBatis framework is a lightweight data mapping framework and persistence API that can be used to generate a database persistence layer for your Java application. A set of XML encoded SQL Map files–one for each database table–holds SQL templates that are executed as prepared statements and map the expected results to Java domain classes. From application code, a layer of iBatis Data Access Objects (DAO) acts as the API that executes the SQL Map templates and assigns the results to the corresponding Java domain classes.
The following code snippets will focus on integrating iBatis with Spring as well as getting data from database (using users_info table). We will use org.springframework.orm.ibatis.SqlMapClientFactoryBean class to integrate iBatis with spring.
Database Objects
USERS_INFO Table:
CREATE TABLE USERS_INFO
(USER_ID NUMBER,
USERNAME VARCHAR2(100) unique,
STATE VARCHAR2(100),
COUNTRY VARCHAR2(100),
IS_ACTIVE CHAR(1))
USER_ID_SEQ Sequence:
CREATE SEQUENCE USER_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;
SqlMap file related to USERS_INFO table
USERS_INFO_SqlMap.xml
<?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="USERS_INFO">
<resultMap id="usersInfoMap" class="data.beans.UserInfo">
<result column="USER_ID" property="userId" jdbcType="NUMBER" />
<result column="USERNAME" property="userName" jdbcType="VARCHAR" />
<result column="STATE" property="state" jdbcType="VARCHAR" />
<result column="COUNTRY" property="country" jdbcType="VARCHAR" />
<result column="IS_ACTIVE" property="isActive" jdbcType="CHAR" />
</resultMap>
<select id="USERS_PROFILE_userInfoByUserName" resultMap="usersInfoMap"
parameterClass="java.lang.String">
SELECT UI.USER_ID, UI.USERNAME, UI.STATE, UI.COUNTRY,
UI.IS_ACTIVE FROM USERS_INFO UI WHERE UI.USERNAME = #userName#
</select>
<insert id="USERS_PROFILE_insertUserInfo" parameterClass="data.beans.UserInfo">
INSERT
INTO USERS_INFO(USER_ID, USERNAME, STATE, COUNTRY, IS_ACTIVE)
VALUES(USER_ID_SEQ.NEXTVAL, #userName#,#state#,#country#,#isActive#)
</insert>
<delete id="USERS_PROFILE_deleteUserInfo" parameterClass="java.lang.String">
DELETE
FROM USERS_INFO WHERE USERNAME = #userName:VARCHAR#
</delete>
</sqlMap>
sqlMapConfig.xml – contains the references to multiple sqlMap files related to project.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<sqlMap resource="USERS_INFO_SqlMap.xml" />
</sqlMapConfig>
applicationContext.xml – assuming that project is standalone java program.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url"
value="jdbc:oracle:thin:@sbkj2kdbd02.wsjdev.dowjones.net:1521:COMMERCE" />
<property name="username" value="provision" />
<property name="password" value="provision123" />
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>sqlMapConfig.xml</value>
</property>
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="userInfoDAO" class="data.dao.UserInfoDAOImpl">
<property name="sqlMapClient" ref="sqlMapClient" />
</bean>
<bean id="userInfoBO" class="data.bo.UserInfoBO">
<property name="userInfoDAO" ref="userInfoDAO" />
</bean>
</beans>
** If it is deployed in any server, please use the jndiName to get the reference to data source. Please refer the following block of code.
<bean id="dataSource"
class="org.springframework.jndi.JndiObjectFactoryBean" lazy-init="true">
<property name="jndiName" value="java:jdbc/userInfoDS"/>
</bean>
UserInfoDAO.java
package data.dao;
import data.beans.UserInfo;
public interface UserInfoDAO {
void create(UserInfo userName);
UserInfo select(String userId);
void delete(String userName);
}
UserInfoDAOImpl.java
package data.dao;
import java.sql.SQLException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import data.beans.UserInfo;
public class UserInfoDAOImpl extends SqlMapClientDaoSupport implements UserInfoDAO {
public void create(UserInfo userInfo) {
getSqlMapClientTemplate().insert("USERS_PROFILE_insertUserInfo", userInfo);
}
public UserInfo select(String userName) {
return (UserInfo) getSqlMapClientTemplate().queryForObject("USERS_PROFILE_userInfoByUserName", userName);
}
public void delete(String userName) {
try {
getSqlMapClient().delete("USERS_PROFILE_deleteUserInfo", userName);
} catch (SQLException sq) {
}
}
}
ApplicationContextUtil.java
package data.util;
import java.io.IOException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class ApplicationContextUtil {
private static final String CONTEXT_CONFIG = "applicationContext.xml";
private static ApplicationContext applicationContext = null;
private ApplicationContextUtil() {
}
public static ApplicationContext getApplicationContextInstance() {
if (applicationContext == null) {
synchronized (ApplicationContextUtil.class) {
if (applicationContext == null) {
try {
applicationContext = createApplicationContext();
} catch (IOException io) {
io.printStackTrace();
}
}
}
}
return applicationContext;
}
private static ApplicationContext createApplicationContext() throws IOException {
return new ClassPathXmlApplicationContext(CONTEXT_CONFIG);
}
}
Now you can perform the database operations.
INSERT:
UserInfo userInfo = new UserInfo();
userInfo.setUserName("testuser");
userInfo.setState("CT");
userInfo.setCountry("USA
userInfo.setIsActive("Y");
ApplicationContext ctx = ApplicationContextUtil.getApplicationContextInstance();
UserInfoDAO userInfoDAO = (UserInfoDAO) ctx.getBean("userInfoDAO");
userInfoDAO.create(userInfo);
The above code execute the following sql snippet from USERS_INFO_SqlMap.xml
<insert id="USERS_PROFILE_insertUserInfo" parameterClass="data.beans.UserInfo">
INSERT
INTO USERS_INFO(USER_ID, USERNAME, STATE, COUNTRY, IS_ACTIVE)
VALUES(USER_ID_SEQ.NEXTVAL, #userName#,#state#,#country#,#isActive#)
</insert>
SELECT
ApplicationContext ctx = ApplicationContextUtil.getApplicationContextInstance();
UserInfoDAO userInfoDAO = (UserInfoDAO) ctx.getBean("userInfoDAO");
UserInfo userInfo = userInfoDAO.select(“testuser”);
The above code execute the following sql snippet from USERS_INFO_SqlMap.xml
<select id="USERS_PROFILE_userInfoByUserName" resultMap="usersInfoMap"
parameterClass="java.lang.String">
SELECT UI.USER_ID, UI.USERNAME, UI.STATE, UI.COUNTRY,
UI.IS_ACTIVE FROM USERS_INFO UI WHERE UI.USERNAME = #userName#
</select>
DELETE
ApplicationContext ctx = ApplicationContextUtil.getApplicationContextInstance();
UserInfoDAO userInfoDAO = (UserInfoDAO) ctx.getBean("userInfoDAO");
userInfoDAO.delete("testuser");
The above code execute the following sql snippet from USERS_INFO_SqlMap.xml
<delete id="USERS_PROFILE_deleteUserInfo" parameterClass="java.lang.String">
DELETE
FROM USERS_INFO WHERE USERNAME = #userName:VARCHAR#
</delete>
