1.查询总数 count(*)
xml:
<select id="selectCount" parameterClass="string" resultClass="int">
select count(*) from TB_XXX where CODE = #code#
</select>
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
始终提供无参构造函数