Mybatis(dao)实现举例

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.baizhi.ty.dao.CategoryDAO">
	
		<resultMap type="Category" id="CategoryMap">
			<id column="firstId"  property="id"/>
			<result column="firstname" property="categoryname" />
			<result column="category_id" property="category_id"/>
			<result column="goodscounts" property="goodscounts"/>
			<collection property="children" javaType="java.util.List" ofType="category">
				<id column="secondId" property="id"/>
				<result column="secondname" property="categoryname"/>
				<result column="category_id" property="category_id"/>
				<result column="goodscounts2" property="goodscounts"/>
			</collection>
		</resultMap>
	
		<!-- //查询出类别
		List<Category> selectAllCategory(); -->
		<select id="selectAllCategory" resultMap="CategoryMap">
			select d1.id as firstId ,d1.categoryname as firstname,
			d1.goodscounts,
			d2.id as secondId,d2.categoryname as secondname,
			d2.category_id,d2.goodscounts	
			from d_category d1
			left join d_category d2
			on d1.id = d2.category_id
			where d1.category_id is null
		</select>
		
		<!-- //查询一级类别
		Category selectOneCategory(@Param("oneId")Integer oneId); -->
		<select id="selectOneCategory" resultMap="CategoryMap">
			select d1.id as firstId ,d1.categoryname as firstname,
			d1.goodscounts,
			d2.id as secondId,d2.categoryname as secondname,
			d2.category_id,d2.goodscounts as goodscounts2
			from d_category d1
			left join d_category d2
			on d1.id = d2.category_id
			where d1.category_id is null
				<!-- 二级类别 -->
				<if test="oneId !=null ">
				and	d2.category_id = ${oneId}
				</if>
				<!-- 一级类别  -->
				<if test="oneId == null">
				 and d1.id  = ${twoId}
				</if>
		</select>
	
	<!-- //显示位置
	Category selectpositionCategory(@Param("oneId")Integer oneId,@Param("twoId")Integer twoId); -->
	<select id="selectpositionCategory" resultMap="CategoryMap">
			select d1.id as firstId ,d1.categoryname as firstname,
			d1.goodscounts,
			d2.id as secondId,d2.categoryname as secondname,
			d2.category_id,d2.goodscounts as goodscounts2
			from d_category d1
			left join d_category d2
			on d1.id = d2.category_id
			where d1.category_id is null
				<!-- 二级类别 -->
				<if test="oneId !=null ">
				and	d2.id = ${twoId}
				</if>
				<!-- 一级类别  -->
				<if test="oneId == null">
				 and d1.id  = ${twoId}
				</if>
		</select>
		
		<!--（1） 编辑推荐 -->
		<!-- //查询书的信息
			public Good selectById(String twoId); -->
			<select id="selectById" resultType="Good">
				select * from d_good where id=#{id}
			</select>
		<!-- //查询书的信息
			List<Good> selectGood(); -->
			<select id="selectGood" resultType="Good">
				select id,oneid,twoid,goodname,goodprice,dangprice,middleprice,grade,author,fromarea,fromtime,banci,
				printtime,isbn,zishu,yeshu,kaiben,paper,baozhuang,isup,imguri,sellnum
				from d_good
				where isup=1
			</select>
		<!-- 查询图书的总个数集合
			Integer selectAllBook();-->
			<select id="selectAllBook" resultType="Integer">
			select count(*)  from d_good 
		   	 where 
		       	 <if test="oneId==null">
		         	 oneId=${twoId}
	          	  </if>
		     	  <if test="oneId!=null">
		     		 twoId=${twoId}
		     	  </if>
			 </select>
		<!--分页查询图书 --> 
			 <select id="queryPageBook" resultType="Good">
		  		select * from (
		   			 select e.*,rownum rn from
		     			(select * from (
		       				  select  * from d_good
			    		     where 
			     		  <if test="oneId==null">
			         		 oneId=${twoId}
		          	 	  </if>
			     	  	  <if test="oneId!=null">
			     		 	twoId=${twoId}
			     	 	  </if>
		  		 ) order by ${order}) e
				) where rn between ${beginResult} and ${lastResult}
			</select>
	
		<!--(2)新书上架  -->
		<!-- //新销书
			List<Good> selectNewBook();-->
			<select id="selectNewBook" resultType="Good">
				select b.* from (
			select a.*,rownum rn	 from (
			select id,oneid,twoid,goodname,goodprice,dangprice,middleprice,grade,author,fromarea,fromtime,banci,
			printtime,isbn,zishu,yeshu,kaiben,paper,baozhuang,isup,imguri,sellnum	
			from d_good
			order by fromtime desc
			)a)b
			where rn between 1 and 8
			</select>
		
		
		<!--（3）热卖图书 -->
		<!-- //热销书
			List<Good> selectHotBook(); -->
			<select id="selectHotBook" resultType="Good">
				select b.* from (
			select a.*,rownum rn	 from (
			select id,oneid,twoid,goodname,goodprice,dangprice,middleprice,grade,author,fromarea,fromtime,banci,
			printtime,isbn,zishu,yeshu,kaiben,paper,baozhuang,isup,imguri,sellnum	
			from d_good
			order by sellnum desc
			)a)b
			where rn between 1 and 8
			</select>	
		
		<!--（4）新书热卖  -->
		<!-- //新热销书
			List<Good> selectHotAndNewBook();-->
			<select id="selectHotAndNewBook" resultType="Good">
					select b.* from (
				select a.*,rownum rn from (
				select * from d_good
				order by fromtime desc,sellnum desc
				)a)b
				where rn between 1 and 8
			</select>
		
		<!--添加部门sql-->
		<insert id="insertDept">
			<selectKey keyProperty="id" order="BEFORE" resultType="String">
				select seq_dept.nextval from dual
				<!-- select sys_guid() from dual -->
			</selectKey>
			insert into department1(id,name) values(#{id},#{name})
		</insert>				</mapper>