iBatis学习

tis学习小结

关键字: iBATIS       

sqlMap文件

代码

<?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="Account">
  <typeAlias alias="Account" type="test.Account"/>
  <!--列表查询,返回Account Object的List-->
  <resultMap id="AccountResult" class="Account">
    <result property="id" column="ID"/>
    <result property="firstName" column="FIRST_NAME"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="emailAddress" column="EMAIL"/>
  </resultMap>
  <select id="selectAllAccounts" resultMap="AccountResult">
    select * from ACCOUNT
  </select>

  <!--列表查询,返回HashMap的List-->
  <resultMap id="mapResult" class="java.util.HashMap">
    <result property="roleid" column="ROLEID"/>
    <result property="rolename" column="ROLENAME"/>
    <result property="id" column="ID"/>
    <result property="firstName" column="FIRST_NAME"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="emailAddress" column="EMAIL"/>
    <result property="dt" column="DT"/>
  </resultMap>
  <select id="selectAllAccountsWithMap" resultMap="mapResult">
        select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL,A.DT
        from ACCOUNT A left outer join ROLE B   on A.ROLEID = B.ROLEID
        ORDER BY A.ID DESC
  </select>

  <!--关联查询,返回Account Object的List-->
  <resultMap id="AccountResultWithRole" class="Account" groupBy="id">
    <result property="id" column="ID"/>
    <result property="firstName" column="FIRST_NAME"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="emailAddress" column="EMAIL"/>
    <result property="role" resultMap="Account.roleResult"/>
  </resultMap>
  <resultMap id="roleResult" class="test.Role">
    <result property="roleid" column="ROLEID"/>
    <result property="rolename" column="ROLENAME"/>
  </resultMap>
  <select id="selectAccountByIdWithRole" parameterClass="int" resultMap="AccountResultWithRole">
        select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL
        from ACCOUNT A
        left outer join ROLE B
        on A.ROLEID = B.ROLEID
        where A.ID = #id#
  </select>

  <!--查询-->
  <select id="selectAccountById" parameterClass="int" resultClass="Account">
    select
      ID as id,
      FIRST_NAME as firstName,
      LAST_NAME as lastName,
      EMAIL as emailAddress
    from ACCOUNT
    where ID = #id#
  </select>

  <!--新增-->
  <insert id="insertAccount" parameterClass="Account">
    insert into ACCOUNT (
      FIRST_NAME,
      LAST_NAME,
      EMAIL,
      PID,
      DT
    )
    values (
      #firstName:VARCHAR#, #lastName:VARCHAR#, #emailAddress:VARCHAR#,#pid:INTEGER:0#,#dt:TIME#
    )
    <selectKey resultClass="int" type="post" keyProperty="id">
        SELECT @@IDENTITY AS ID
    </selectKey>
  </insert>

  <!--更新-->
  <update id="updateAccount" parameterClass="Account">
    update ACCOUNT set
      FIRST_NAME = #firstName:VARCHAR#,
      LAST_NAME = #lastName:VARCHAR#,
      EMAIL = #emailAddress:VARCHAR#
    where
      ID = #id#
  </update>

  <!--删除-->
  <delete id="deleteAccountById" parameterClass="int">
    delete from ACCOUNT where ID = #id#
  </delete>

  <!--存储过程，如果没有返回列表，procTest的resultMap可以省略-->
  <parameterMap id="procParamMap" class="java.util.HashMap" >
    <parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
    <parameter property="outid" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
    <parameter property="errMsg" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
  </parameterMap>
  <resultMap id="procResultMap" class="java.util.HashMap" >
    <result property="a" column="AAA"/>
    <result property="b" column="BBB"/>
    <result property="c" column="CCC"/>
  </resultMap>
  <procedure id="procTest" parameterMap="procParamMap" resultMap="procResultMap">
    {call test_sp_1 (?,?,?)}
  </procedure>
</sqlMap>

<script>render_code();</script>

 

java 代码

代码

public class TestDao extends SqlMapClientDaoSupport {
    public List selectAllAccounts() throws SQLException {
        return getSqlMapClientTemplate().queryForList("selectAllAccounts");
    }

    public List selectAllAccountsWithMap() throws SQLException {
        return getSqlMapClientTemplate().queryForList(
                "selectAllAccountsWithMap");
    }

    public Account selectAccountById(int id) throws SQLException {
        return (Account) getSqlMapClientTemplate().queryForObject(
                "selectAccountById", new Integer(id));
    }
    public List procTest(Map params) throws Exception {
        List ret = (List) getSqlMapClientTemplate().queryForList("procTest",params);
        return ret;
    }
    public Account selectAccountByIdWithRole(int id) throws SQLException {
        return (Account) getSqlMapClientTemplate().queryForObject(
                "selectAccountByIdWithRole", new Integer(id));
    }

    public void insertAccount(Account account) throws SQLException {
        getSqlMapClientTemplate().insert("insertAccount", account);
    }

    public int updateAccount(Account account) throws SQLException {
        return getSqlMapClientTemplate().update("updateAccount", account);
    }

    public int deleteAccount(int id) throws SQLException {
        return getSqlMapClientTemplate().delete("deleteAccountById", new Integer(id));
    }
}

<script>render_code();</script>

 

SqlServer存储过程

代码

CREATE procedure test_sp_1 @id int,@outid int output,@errMsg varchar(255) output

AS

update ACCOUNT set pid=@id
select @outid=isnull(@id,0)+1
select @errMsg='测试'
select AAA='AAAAAAA',BBB='BBBBBBB',CCC='CCCCCCC'
GO

<script>render_code();</script>