第一个Ibatis增删改查例子(sqlserver2005)

1.导入jar包,ibatis-2.3.0.677.jar,sqljdbc.jar

2.定义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>

 <settings
    cacheModelsEnabled="true"     
    enhancementEnabled="true"     
    lazyLoadingEnabled="true"     
    errorTracingEnabled="true"     
    maxRequests="32"         
    maxSessions="10"         
    maxTransactions="5"         
    useStatementNamespaces="true"  
    />
 
  <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:sqlserver://localhost:1433; DatabaseName=DfcpMes"/>
      <property name="JDBC.Username" value="sa"/>
      <property name="JDBC.Password" value="sa"/>
       <property name="Pool.MaximumActiveConnections"
value="10"/>
      <property  name="Pool.MaximumIdleConnections"  value="5"/>
      <property name="Pool.MaximumCheckoutTime"
value="120000"/>
      <property name="Pool.TimeToWait" value="500"/>
      <property name="Pool.PingQuery" value="select 1 from
ACCOUNT"/>
      <property name="Pool.PingEnabled" value="false"/>
      <property name="Pool.PingConnectionsOlderThan"
value="1"/>
      <property name="Pool.PingConnectionsNotUsedFor"
value="1"/>
    </dataSource>
  </transactionManager>

  <!-- List the SQL Map XML files. They can be loaded from the
       classpath, as they are here (com.domain.data...) -->
  <sqlMap resource="com/ibatis/bean/config/Users.xml"/>
  <sqlMap resource="com/ibatis/bean/config/Factory.xml"/>
 

</sqlMapConfig>

3.创建表Users,字段有loginid,username,loginpassword,roleid

4.创建Users表对应的JavaBean,

5.创建Users.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="user_space">

<typeAlias alias="user" type="com.ibatis.bean.Users"/>

  <resultMap id="userResult" class="user">
    <result property="username" column="username"/>
    <result property="loginid" column="loginid"/>
    <result property="loginpassword" column="loginpassword"/>
  
  </resultMap>

  <!-- 返回结果为List -->
  <select id="selectAllUsers" resultMap="userResult" cacheModel="userCache">
    select * from Users
  </select>
 
  <!-- 返回结果为List<HashMap<String,Object>>
  String表示字段名，Object为字段值，即每一行为一个HashMap
   -->
  <select id="selectAllUsersMap" resultClass="java.util.HashMap" >

    select * from Users
  </select>

 <select id="selectUsersByLoginId" parameterClass="String" resultClass="user">
   <![CDATA[
    select
     loginid,
     username,
     loginpassword
    from users
    where loginId = #loginid#
    ]]>
  </select>

  <insert id="insertUsers" parameterClass="user">
  <!-- 生成主键的方式(post表示添加后生成,pre表示添加前生成如Oracle) -->
  <selectKey resultClass="int" type="post" keyProperty="id" >  
        select @@IDENTITY as id
    </selectKey>  
   
    <!--  oracle用序列生成主键的配置
   <selectKey resultClass="int" type="pre" keyProperty="id" >  
        SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL  
    </selectKey>  
  -->
  <![CDATA[
    insert into users(
      username,
      loginid,
      loginpassword,
      roleid )
    values(
      #username#,#loginid#,#loginpassword#,#roleid#
    )
    ]]>
  </insert>

 
  <update id="updateUsers" parameterClass="user">
  <![CDATA[
    update users set
      username=#username#,
      loginid=#loginid#,
      loginpassword=#loginpassword#
    where
      loginId = #loginid#
      ]]>
  </update>

 
  <delete id="deleteUsersByLoginId" parameterClass="String">
    delete from users where loginId = #loginId#
  </delete>

6.定义工厂类，用来生成SqlMapClient

package com.ibatis.dao;

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

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

public class SqlClientFactory {

 public static SqlMapClient getClientMap() throws IOException
 {
  
  String resource ="SqlMapConfig.xml";
  Reader reader = Resources.getResourceAsReader(resource);
  
  
  
  SqlMapClient sqlMap =  SqlMapClientBuilder.buildSqlMapClient(reader);

  return sqlMap;
 }
}

7.创建测试类，运行

/**
  * 获取resultClass="java.util.HashMap"的结果
  * @return
  * @throws IOException
  * @throws SQLException
  */
 List<HashMap<String,Object>> GetAllUserMapList() throws IOException, SQLException
 {
  SqlMapClient client=SqlClientFactory.getClientMap();
  client.startTransaction();
  List<HashMap<String,Object>> mpList=(List<HashMap<String,Object>>)client.queryForList("user_space.selectAllUsersMap");
  client.commitTransaction();
  return mpList;
 }
 
 /**
  * 获取全部用户
  * @return
  * @throws IOException
  * @throws SQLException
  */
 List<Users> GetAllUserList() throws IOException, SQLException
 {
  SqlMapClient client=SqlClientFactory.getClientMap();
  client.startTransaction();
  List<Users> list=(List<Users>)client.queryForList("user_space.selectAllUsers");
  client.commitTransaction();
  return list;
 }
 
 /**
  *  根据登陆账号查询一个用户
  * @param loginId
  * @return
  * @throws IOException
  * @throws SQLException
  */
 Users GetUserByLoginId(String loginId) throws IOException, SQLException
 {
  SqlMapClient client = client=SqlClientFactory.getClientMap();
  client.startTransaction();
  Users user=(Users) client.queryForObject("user_space.selectUsersByLoginId", loginId);
  client.commitTransaction();
  return user;
 }
 
 
 /**
  * 更新一条记录
  * @param user
  * @throws IOException
  * @throws SQLException
  */
 private void UpdateUsers(Users user) throws IOException, SQLException
 {
  SqlMapClient client = client=SqlClientFactory.getClientMap();
  try
  {
   client.startTransaction();
   client.update("user_space.updateUsers", user);
   client.commitTransaction();
  }
  finally
  {
   if(client!=null)
   {
    client.endTransaction();
   }
  }
 }
 
 /**
  * 删除一条记录
  * @param loginId
  * @throws IOException
  * @throws SQLException
  */
 private void deleteUser(String loginId) throws IOException, SQLException
 {
  SqlMapClient client = client=SqlClientFactory.getClientMap();
  try
  {
   client.startTransaction();
   client.delete("user_space.deleteUsersByLoginId", loginId);
   client.commitTransaction();
  }
  finally
  {
   if(client!=null)
   {
    client.endTransaction();
   }
  }
 }
 
 
 /**
  * 添加一个用户insertUsers
  * @param user
  * @throws IOException
  * @throws SQLException
  */
 private void addUser(Users user) throws IOException, SQLException
 {
  SqlMapClient client = client=SqlClientFactory.getClientMap();
  try
  {
   client.startTransaction();
   client.insert("user_space.insertUsers", user);
   client.commitTransaction();
  }
  finally
  {
   if(client!=null)
   {
    client.endTransaction();
   }
  }
 }
 
 private void Show(List<Users> list)
 {
  for(Users user:list)
  {
   System.out.println("loginId:"+user.getLoginid()+" username:"+user.getUsername()+" pwd:"+user.getLoginpassword());
  }
 }
 
 private void ShowMapList(List<HashMap<String,Object>> maplist)
 {
  for(HashMap<String,Object> mp:maplist)
  {
   String loginId=(String) mp.get("LoginId");
   String username=(String) mp.get("UserName");
   String loginpasswords=(String) mp.get("LoginPassword");

   System.out.println("loginId:"+loginId+" username:"+username+" pwd:"+loginpasswords);
  }
 }
 
 private void Show(Users user)
 {
  if(user!=null)
  {
   System.out.println("loginId:"+user.getLoginid()+" username:"+user.getUsername()+" pwd:"+user.getLoginpassword());
  }
 }