項目結構：
依賴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