iBatis的基本使用

项目结构：

依赖jar：

数据库依赖：

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  `groupname` varchar(10) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO ssi.user_account (userid, username, password, groupname) VALUES (1, 'LMEADORS', 'PICKLE', 'EMPLOYEE');
INSERT INTO ssi.user_account (userid, username, password, groupname) VALUES (2, 'JDOE', 'TEST', 'EMPLOYEE');

数据库连接信息：db.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssi
username=root
password=123456

主配置文件：sqlMapConfig.xml

<?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>
    <properties resource="db.properties"/>
    <!--iBatis配置信息-->
    <settings enhancementEnabled="true"
            useStatementNamespaces="true"
            cacheModelsEnabled="true"
            lazyLoadingEnabled="true"/>

    <!--事务管理器-->
    <transactionManager type="JDBC">
    <!--数据库连接信息-->
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="${driverClass}"/>
      <property name="JDBC.ConnectionURL" value="${url}"/>
      <property name="JDBC.Username" value="${username}"/>
      <property name="JDBC.Password" value="${password}"/>
    </dataSource>
    </transactionManager>
    <!-- 映射文件位置 -->
    <sqlMap resource="org/zln/ibatis/sqlmaps/Person.xml" />
    <sqlMap resource="org/zln/ibatis/sqlmaps/UserAccount.xml" />
</sqlMapConfig>

初始化配置文件

package org.zln.ibatis.utils;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 * Created by sherry on 000008/6/8 14:36.
 */
public class SqlMapUtils {
    private static final String resource = "sqlMapConfig.xml";
    public static SqlMapClient sqlMapClient = null;

    public static SqlMapClient getSqlMapClient() throws IOException {
        Reader reader = Resources.getResourceAsReader(resource);
        sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
        return sqlMapClient;
    }
}

映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
        PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
        "http://www.ibatis.com/dtd/sql-map-2.dtd">

<!-- 设置命名空间 -->
<sqlMap namespace="org.zln.ibatis.domain.UserAccount">

    <typeAlias alias="UserAccount" type="org.zln.ibatis.domain.UserAccount"/>

    /*外部参数映射*/
    <parameterMap id="fullUserAccountMapExport" class="UserAccount">
        <parameter property="userid" jdbcType="INTEGER"/>
        <parameter property="username" jdbcType="VARCHAR"/>
        <parameter property="password" jdbcType="VARCHAR"/>
        <parameter property="groupname" jdbcType="VARCHAR"/>
    </parameterMap>
    /*结果集映射*/
    <resultMap id="UserAccountMap" class="UserAccount">
        <result property="userid" column="userid"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="groupname" column="groupname"/>
        <!--当查询tt的时候，会查询select-id对应的SQL，然后映射到JavaBean中的listColl，-->
        <!--<result column="tt" property="listColl" select="select-id"/>-->
    </resultMap>

    <select id="getUserAccountList" parameterClass="UserAccount" resultClass="UserAccount">
        SELECT userid,username,password,groupname FROM user_account
        <dynamic prepend="WHERE">
            <isNotEmpty property="username" prepend="AND">
                username LIKE '%'||#username#||'%'
            </isNotEmpty>
            <isNotEmpty property="password" prepend="AND">
                password = #password#
            </isNotEmpty>
            <isNotEmpty property="groupname" prepend="AND">
                groupname LIKE '%'||#groupname#||'%'
            </isNotEmpty>
            <!--循环标签-->
            <iterate property="ids" open="(" close=")" conjunction=",">
                userid = #userid#
            </iterate>
        </dynamic>
    </select>

    <insert id="insertUserAccount" parameterClass="UserAccount">
        INSERT INTO user_account (username,password,groupname) VALUES (#username#,#password#,#groupname#)
    </insert>
    /*使用外部映射*/
    <insert id="insertUserAccount_1" parameterMap="fullUserAccountMapExport">
        INSERT INTO user_account (username,password,groupname) VALUES (?,?,?)
    </insert>
    /*Oracle的序列使用演示*/
    <!--<insert id="insertUserAccount_2" parameterMap="fullUserAccountMapExport">
        <selectKey keyProperty="userid" resultClass="int">
            SELECT nextVal(#序列#) FROM dual
        </selectKey>
        INSERT INTO user_account (userid,username,password,groupname) VALUES (?,?,?,?)
    </insert>-->

    <update id="updateUserAccount" parameterClass="UserAccount">
        UPDATE user_account set userid = #userid#
        <dynamic>
            <isNotEmpty prepend="," property="username">
                username = #username#
            </isNotEmpty>
            <isNotEmpty prepend="," property="password">
                password = #password#
            </isNotEmpty>
            <isNotEmpty prepend="," property="groupname">
                groupname = #groupname#
            </isNotEmpty>
        </dynamic>
        WHERE userid = #userid#
    </update>


    <sql id="select-user_account-list">
        SELECT userid,username,password,groupname FROM user_account
    </sql>
    <sql id="select-user_account-count">
        SELECT COUNT(1) FROM user_account
    </sql>
    <sql id="where-user_account">
        <![CDATA[
          WHERE userid > #userid:INTEGER#
        ]]>
    </sql>
    <select id="selectUserAccountsAfterId" parameterClass="UserAccount" resultClass="UserAccount">
        <include refid="select-user_account-list"/>
        <include refid="where-user_account"/>
    </select>

    /*调用存储过程演示*/
    <!--<procedure id="callProDemo" parameterClass="string" resultClass="int">
        {call product(?)}
    </procedure>-->

</sqlMap>

BaseDao.java

package org.zln.ibatis.dao.base;

import com.ibatis.sqlmap.client.SqlMapClient;
import org.zln.ibatis.utils.SqlMapUtils;

import java.io.IOException;

/**
 * Created by sherry on 000008/6/8 15:16.
 */
public class BaseDao {
    public SqlMapClient sqlMapClient;

    {
        try {
            sqlMapClient = SqlMapUtils.getSqlMapClient();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

UserAccountDaoImpl.java

package org.zln.ibatis.dao.impl;

import org.zln.ibatis.dao.UserAccountDao;
import org.zln.ibatis.dao.base.BaseDao;
import org.zln.ibatis.domain.UserAccount;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by sherry on 000008/6/8 15:15.
 */
public class UserAccountDaoImpl extends BaseDao implements UserAccountDao {

    @Override
    public List<UserAccount> getUserAccountList(UserAccount userAccount){
        List<UserAccount> userAccounts = null;
        try {
            userAccounts = sqlMapClient.queryForList("org.zln.ibatis.domain.UserAccount.getUserAccountList",userAccount);

            //start test queryForMap
            Map<String,UserAccount> map = sqlMapClient.queryForMap("org.zln.ibatis.domain.UserAccount.getUserAccountList",userAccount,"username");
            System.out.println(map);
            //end
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userAccounts;
    }

    @Override
    public List<UserAccount> getUserAccountListAfterId(UserAccount userAccount) {
        List<UserAccount> userAccounts = null;
        try {
            userAccounts = sqlMapClient.queryForList("org.zln.ibatis.domain.UserAccount.selectUserAccountsAfterId",userAccount);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userAccounts;
    }

    @Override
    public void insertUserAccount(List<UserAccount> userAccounts) {
        try {
            /*批处理*/
            sqlMapClient.startTransaction();
            sqlMapClient.startBatch();
            for (UserAccount userAccount:userAccounts){
                sqlMapClient.insert("org.zln.ibatis.domain.UserAccount.insertUserAccount",userAccount);
            }
            sqlMapClient.executeBatch();
            sqlMapClient.commitTransaction();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                sqlMapClient.endTransaction();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateUserAccount(UserAccount userAccount) {
        try {
            sqlMapClient.update("org.zln.ibatis.domain.UserAccount.updateUserAccount",userAccount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

日志配置文件：log4j.properties

## LOGGERS ##
#define a logger 
#log4j.rootLogger=DEBUG,console,file
log4j.rootLogger=INFO,console
## APPENDERS ##

log4j.appender.console=org.apache.log4j.ConsoleAppender
# define an appender named file, which is set to be a RollingFileAppender
log4j.appender.file=org.apache.log4j.RollingFileAppender
#set the log's size
log4j.appender.file.MaxFileSize=1000KB
log4j.appender.file.MaxBackupIndex=20
## LAYOUTS ##
# assign a SimpleLayout to console appender
log4j.appender.console.layout=org.apache.log4j.SimpleLayout
# assign a PatternLayout to file appender
log4j.appender.file.layout=org.apache.log4j.PatternLayout
# For debug
# log4j.appender.file.layout.ConversionPattern=[%-5p][%t][%C][%d{yyyy-MM-dd HH:mm:ss}] %m%n
# For deployment
log4j.appender.file.layout.ConversionPattern=[%-5p][%d{yyyy-MM-dd HH:mm:ss}] %m%n
# show SQL Where ibatis run
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG