调用存储过程,函数,selectKey,sql,if,choose标签

xml文件调用过程(调用过程或者函数都要将statementType属性设置CALLABLE):

<select id="query" statementType="CALLABLE" resultType="int" parameterType="map">{call pro_add(<!-- jdbcType:可通过JdbcType该类查看mode:相当于在oracle或则MySQL里面的参数修饰一样p1:参数注意:当我们在通过map来传参的时候,过程或者函数他们的返回值都会被写入到我们传入的map当中-->#{p1,mode=IN,jdbcType=NUMERIC},#{p2,mode=IN,jdbcType=NUMERIC},#{p3,mode=OUT,jdbcType=NUMERIC})}</select>

注解调用过程:

package com.et.TL.Mybatis.lesson02.callProcedures;import java.util.Map;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.mapping.StatementType;public interface TestNodeProcedures {/*** 过程里面要用到的参数请通过map键值对的形式传入，通过OUT修饰的变量就是该过程要返回的结果，存在我们传入的map当中* @param map*/@Select(value="{" +"call pro_add" +"(" +"#{p1,mode=IN,jdbcType=NUMERIC}," +"#{p2,mode=IN,jdbcType=NUMERIC}," +"#{p3,mode=OUT,jdbcType=NUMERIC}" +")" +"}")@Options(statementType=StatementType.CALLABLE)public void qureyList(Map<String,Object> map);}

xml文件调用函数:

<select id="query" statementType="CALLABLE" resultType="int" parameterType="map">{#{p3,mode=OUT,jdbcType=NUMERIC}=call fun_add(#{p1,mode=IN,jdbcType=NUMERIC},#{p2,mode=IN,jdbcType=NUMERIC})}</select>

注解调用函数:

package com.et.TL.Mybatis.lesson02.callFunction;import java.util.Map;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.mapping.StatementType;public interface TestNodeFunction {/** * 函数里面要用到的参数请通过map键值对的形式传入，通过OUT修饰的变量就是该函数要返回的结果，存在我们传入的map当中 * @param map */@Select(value="{" +"#{p3,mode=OUT,jdbcType=NUMERIC}=call fun_add" +"(" +"#{p1,mode=IN,jdbcType=NUMERIC}," +"#{p2,mode=IN,jdbcType=NUMERIC}" +")" +"}")@Options(statementType=StatementType.CALLABLE)public void qureyList(Map<String,Object> map);}

xml文件selectKey标签的使用:

<insert id="insertEmployee" parameterType="map"><!-- order:指定在执行该sql语句执行执行resultType:返回的类型keyColumn:列名(数据查询出来as到一个列名上)keyProperty:结果放入到改修饰的变量里面 --><selectKey order="BEFORE" resultType="int" keyColumn="newEmployeeId" keyProperty="employeeid">select nvl(max(employeeid)+1,1001) as newEmployeeId from employee</selectKey>insert into employee values(#{employeeid},#{deptNo},#{empName},#{empJob},to_date(#{empDate},'yyyy-MM-dd'),#{empSal})</insert>

注解selectKey:

package com.et.TL.Mybatis.lesson02.selectKey;import java.util.Map;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.SelectKey;public interface SelectKeys {@SelectKey(statement="select nvl(max(employeeid)+1,1001) as en from employee",before=true,resultType=String.class,keyColumn="en",keyProperty="empNo")@Insert(value="insert into employee(employeeid,employeename) values(#{empNo},#{empname})")public void addEmp(@Param(value="empname")String empname);}

sql标签:

<sql id="list">EMPLOYEEID,DEPARTMENTID,EMPLOYEENAME,EMPLOYEEPOST,EMPLOYEEPOST,EMPLOYEEMONTHLYSALARY</sql><select id="query" resultType="map"><!-- 通过include标签引用-->select <include refid="list"/> from employee</select>

xml文件if和choose的使用:

<select id="query" resultType="map" parameterType="map"><!-- 当我们在使用if，choose语句的时候我们在下面判断的变量必须是从键值对里面取到，否则会抛错说没有这个变量名.-->select * from employee where 1=1<if test="key!=null">and EMPLOYEENAME like #{key}</if></select><select id="queryChoose" resultType="map" parameterType="map">select * from employee where 1=1<choose><when test="key==null">and employeename like '%%'</when><otherwise>and employeename like #{key}</otherwise></choose></select>

注解if和choose:

package com.et.TL.Mybatis.lesson02.hw;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;public interface HwNodeIfChoose {/** *  * 通过<script>该标签将我们的sql语句和if或者choose语句包含起来就可了 * @param key * @return */@Select(value="<script>select * from employee where 1=1<if test=\"key!=null\">and employeename like #{key}</if></script>")public List<Map<String,Object>> queryEmployee(@Param(value="key")String key);@Select(value="<script>select * from employee where 1=1<choose>" +"<when test=\"key==null\">" +"and employeename like '%%'" +"</when>" +"<otherwise>" +"and employeename like #{key}" +"</otherwise>"+"</choose></script>")public List<Map<String,Object>> queryEmployeeList(@Param(value="key")String key);}

注意：

在使用注解函数或者过程的时候必须要知道@Options这个注解属性statementType=CALLABLE。