Ibatis 一些笔记

1.查询总数 count(*)

xml: 

<select id="selectCount" parameterClass="string" resultClass="int">
	select count(*) from TB_XXX  where CODE = #code#
</select>

java:

Integer count = (Integer) sqlTemplate.queryForObject("selectCount", hotCode);

2. oracle分页查询 及 java.util.HashMap参数

	<!--   分页 -->
	<select id="selectSthByPage" resultMap="someResult" parameterClass="java.util.HashMap">
		<![CDATA[
	              select * from 
		      (
		        select a.*, rownum rn 
		        from (select * from TB_XXX where CODE = #code# order by SOME_DATE) a 
		        where rownum <= #end#
		      )
		      where rn >= #start#
                ]]>
	</select>

java:

sqlTemplate.queryForList("

selectSthByPage

", map); //传入一个HashMap

3.执行多条语句 如多个delete

xml:

	<!-- 删除地区信息及其所有的子地区和热点 -->	
	<delete id="deleteSthById" parameterClass="string">
		BEGIN
			delete from TB_ONE 
			where
			CODE =  #code#;
			
			delete from TB_TWO 
			where
			CODE =  #code#;
		END;
	</delete>

java:

sqlTemplate.delete("deleteSthById",zoneCode);

4.对象类型配置

<result property="registerDate" column="REGISTER_DATE" javaType="java.util.Date" jdbcType="TIMESTAMP"/>

5.复杂结果集

	<resultMap id="nodeResult" class="ExtTreeNode">
		<result property="id" column="ZONE_CODE" />
		<result property="text" column="ZONE_NAME" />
		<result property="leaf" column="CONNECT_BY_ISLEAF" />
		<result property="data" column="ZONE_CODE" select="selectHotZoneById"/>
	</resultMap>
	
	<!-- 这个结果作为上面结果集中的data  -->
	<select id="selectHotZoneById"  parameterClass="string" resultClass="java.util.HashMap">
		select t.*,
		(select count(*) from TB_HOTINFO where ZONE_CODE = #zone_code#) as hotCounts 
		from TB_HOTZONE t
		where ZONE_CODE = #zone_code#
	</select>
	
	<!--  -->
	<select id="selectHotZoneByParentCode" resultMap="nodeResult" parameterClass="string">
		select ZONE_CODE, ZONE_NAME, CONNECT_BY_ISLEAF from TB_HOTZONE
		where PARENT_CODE = #parentCode#
		connect by prior ZONE_CODE=PARENT_CODE start with PARENT_CODE = #parentCode#
		order by ZONE_CODE
	</select>

6. mysql 主键自动生成  建表时指定  auto_increment

insert时 XML配置：

   <selectKey resultClass="int" keyProperty="id">
            SELECT LAST_INSERT_ID() AS ID
   </selectKey>

7.  在使用Map作为结果集返回类型时候，必须这么设置结果集类型resultClass="java.util.HashMap"，

     这时候，需要根据字段的名称来取值，值类型为Object，key类型为String。

     而作为参数时，parameterClass="map"或parameterClass="java.util.HashMap" 

     统一写：java.util.HashMap

8.其他

当ibatis初始化Bean的时候，会调用无参的构造函数，所以如果Bean中有带参的构造函数，一定得多写个无参的构造函数，

否则ibatis会因找不到构造函数而出错，抛出异常如下：JavaBeansDataExchange could not instantiate result class


始终提供无参构造函数