iBatis的基本使用

项目结构：

依赖jar：

数据库依赖：

 1 CREATE TABLE `person` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `name` varchar(45) NOT NULL,
 4   PRIMARY KEY (`id`)
 5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 6 
 7 CREATE TABLE `user_account` (
 8   `userid` int(11) NOT NULL AUTO_INCREMENT,
 9   `username` varchar(30) NOT NULL,
10   `password` varchar(30) NOT NULL,
11   `groupname` varchar(10) NOT NULL,
12   PRIMARY KEY (`userid`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 INSERT INTO ssi.user_account (userid, username, password, groupname) VALUES (1, 'LMEADORS', 'PICKLE', 'EMPLOYEE');
2 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

 1 <?xml version="1.0" encoding="utf-8"?>
 2 <!DOCTYPE sqlMapConfig
 3     PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
 4     "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
 5 <sqlMapConfig>
 6     <properties resource="db.properties"/>
 7     <!--iBatis配置信息-->
 8     <settings enhancementEnabled="true"
 9             useStatementNamespaces="true"
10             cacheModelsEnabled="true"
11             lazyLoadingEnabled="true"/>
12 
13     <!--事务管理器-->
14     <transactionManager type="JDBC">
15     <!--数据库连接信息-->
16     <dataSource type="SIMPLE">
17       <property name="JDBC.Driver" value="${driverClass}"/>
18       <property name="JDBC.ConnectionURL" value="${url}"/>
19       <property name="JDBC.Username" value="${username}"/>
20       <property name="JDBC.Password" value="${password}"/>
21     </dataSource>
22     </transactionManager>
23     <!-- 映射文件位置 -->
24     <sqlMap resource="org/zln/ibatis/sqlmaps/Person.xml" />
25     <sqlMap resource="org/zln/ibatis/sqlmaps/UserAccount.xml" />
26 </sqlMapConfig>

初始化配置文件

 1 package org.zln.ibatis.utils;
 2 
 3 import com.ibatis.common.resources.Resources;
 4 import com.ibatis.sqlmap.client.SqlMapClient;
 5 import com.ibatis.sqlmap.client.SqlMapClientBuilder;
 6 
 7 import java.io.IOException;
 8 import java.io.Reader;
 9 
10 /**
11  * Created by sherry on 000008/6/8 14:36.
12  */
13 public class SqlMapUtils {
14     private static final String resource = "sqlMapConfig.xml";
15     public static SqlMapClient sqlMapClient = null;
16 
17     public static SqlMapClient getSqlMapClient() throws IOException {
18         Reader reader = Resources.getResourceAsReader(resource);
19         sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
20         return sqlMapClient;
21     }
22 }

映射文件

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

 1 package org.zln.ibatis.dao.base;
 2 
 3 import com.ibatis.sqlmap.client.SqlMapClient;
 4 import org.zln.ibatis.utils.SqlMapUtils;
 5 
 6 import java.io.IOException;
 7 
 8 /**
 9  * Created by sherry on 000008/6/8 15:16.
10  */
11 public class BaseDao {
12     public SqlMapClient sqlMapClient;
13 
14     {
15         try {
16             sqlMapClient = SqlMapUtils.getSqlMapClient();
17         } catch (IOException e) {
18             e.printStackTrace();
19         }
20     }
21 }

UserAccountDaoImpl.java

 1 package org.zln.ibatis.dao.impl;
 2 
 3 import org.zln.ibatis.dao.UserAccountDao;
 4 import org.zln.ibatis.dao.base.BaseDao;
 5 import org.zln.ibatis.domain.UserAccount;
 6 
 7 import java.sql.SQLException;
 8 import java.util.List;
 9 import java.util.Map;
10 
11 /**
12  * Created by sherry on 000008/6/8 15:15.
13  */
14 public class UserAccountDaoImpl extends BaseDao implements UserAccountDao {
15 
16     @Override
17     public List<UserAccount> getUserAccountList(UserAccount userAccount){
18         List<UserAccount> userAccounts = null;
19         try {
20             userAccounts = sqlMapClient.queryForList("org.zln.ibatis.domain.UserAccount.getUserAccountList",userAccount);
21 
22             //start test queryForMap
23             Map<String,UserAccount> map = sqlMapClient.queryForMap("org.zln.ibatis.domain.UserAccount.getUserAccountList",userAccount,"username");
24             System.out.println(map);
25             //end
26         } catch (SQLException e) {
27             e.printStackTrace();
28         }
29         return userAccounts;
30     }
31 
32     @Override
33     public List<UserAccount> getUserAccountListAfterId(UserAccount userAccount) {
34         List<UserAccount> userAccounts = null;
35         try {
36             userAccounts = sqlMapClient.queryForList("org.zln.ibatis.domain.UserAccount.selectUserAccountsAfterId",userAccount);
37 
38         } catch (SQLException e) {
39             e.printStackTrace();
40         }
41         return userAccounts;
42     }
43 
44     @Override
45     public void insertUserAccount(List<UserAccount> userAccounts) {
46         try {
47             /*批处理*/
48             sqlMapClient.startTransaction();
49             sqlMapClient.startBatch();
50             for (UserAccount userAccount:userAccounts){
51                 sqlMapClient.insert("org.zln.ibatis.domain.UserAccount.insertUserAccount",userAccount);
52             }
53             sqlMapClient.executeBatch();
54             sqlMapClient.commitTransaction();
55         } catch (SQLException e) {
56             e.printStackTrace();
57         }finally {
58             try {
59                 sqlMapClient.endTransaction();
60             } catch (SQLException e) {
61                 e.printStackTrace();
62             }
63         }
64     }
65 
66     @Override
67     public void updateUserAccount(UserAccount userAccount) {
68         try {
69             sqlMapClient.update("org.zln.ibatis.domain.UserAccount.updateUserAccount",userAccount);
70         } catch (SQLException e) {
71             e.printStackTrace();
72         }
73     }
74 
75 
76 }

日志配置文件：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

posted @ 2015-06-11 23:37 csnmd 阅读(1046) 评论(0) 编辑收藏举报

刷新页面返回顶部

KoKo

iBatis的基本使用

公告