MyBatis动态代理多参数传递&动态SQL

MyBatis中Mapper代理方式
1.创建接口，名字应和mapper.xml对应，再创建增删改查等方法
2.mapper.xml的namespace填写接口的全路径，增删改查的ID要和接口方法名称对应
3.sqlSession.getMapper(FlowerMapper.class);获得对应的接口对象
4.使用对象调用方法

MyBatis动态代理多参数传递
接口文件

    //动态代理中三种传入多个参数的方式
//    1.参数类型为基本数据类型
    Flower findOne(int id,String name);
//    2.参数类型为基本数据类型，使用了别名
    Flower findOne2(@Param("aa") int id, @Param("bb")String name );
//    3.参数类型为多个实体类
    Flower findOne3(Flower f1,Flower f2);

xml文件

  <!--1.传入多个参数的第一种方式：（int id,String name）-->
    <select id = "findOne" resultType="flower">
        select * from flower where fid = #{param1} and fname = #{param2}
    </select>
<!--  2.参数类型为基本数据类型，使用了别名-->
    <select id="findOne2" resultType="flower">
        select * from flower where fid = #{aa} and fname = #{bb}
    </select>
<!--  3.参数类型为多个实体类-->
    <select id="findOne3" resultType="flower">
        select * from flower where fid = #{param1.fid} and fname = #{param2.fname}
    </select>

java测试文件

        //动态代理中三种传入多个参数的方式
        //    1.参数类型为基本数据类型
        FlowerMapper flowerMapper = sqlSession.getMapper(FlowerMapper.class);
        Flower mapperOne = flowerMapper.findOne(2, "紫罗兰");
        System.out.println(mapperOne);
        //    2.参数类型为基本数据类型，使用了别名
        Flower mapperOne2 = flowerMapper.findOne2(1, "仙人掌");
        System.out.println(mapperOne2);
        //    3.参数类型为多个实体类
        Flower f1 = new Flower();
        f1.setFid(1);
        Flower f2 = new Flower();
        f2.setFname("仙人掌");
        Flower one3 = flowerMapper.findOne3(f1, f2);
        System.out.println(one3);

MyBatis中动态SQL

<!--    if字段类似
          if()
          if()的使用方式-->
    <select id="selectMore" resultType="flower">
        select * from flower where 1=1
        <if test="param1!=''and param1!=null">
            and fname = #{param1}
        </if>
        <if test="param2!='' and param2!=null ">
            and variety = #{param2}
        </if>
    </select>
    
    <!--    where字段使用方式,会自动加and-->
    <select id = "selectMore2" resultType="flower">
        select * from flower
        <where>
            <if test="param1!='' and param1!=null">
                fname = #{fname}
            </if>
        </where>
    </select>
    
    <!--when字段，类似if()else if()的使用方式-->
    <select id = "selectMore3" resultType="flower">
        select* from flower
        <where>
            <choose>
                <when test="param1!=''and param1!=null">
                    and fname = #{param1}
                </when>
                <when test="param2!='' and param2!=null">
                    and variety = #{param2}
                </when>
            </choose>
        </where>
    </select>
    
    <!--set字段，修改操作-->
    <update id="update">
        update flower
         <set>
             <if test="fname!='' and fname != null"></if>
             fname = #{fname},
             <if test="variety!= '' and fname != null"></if>
             variety = #{variety}
         </set>
         where fid = #{fid}
    </update>
    
    <!--trim字段，修改操作
    -->
    <update id="update2">
        update flower
        <trim prefix="set" suffixOverrides=",">
            <if test="fname !='' and fname != null ">
                fname = #{fname},
            </if>
            <if test="variety != '' and variety!= null">
                variety = #{variety}
            </if>
            where fid = #{fid}
        </trim>
    </update>

    <!--ForEach字段，遍历List或者Array参数,item放遍历出来的参数
    index：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选-->
    <select id="selectMore4" resultType="flower">
        select * from flower where fid in
        <foreach collection="list" open="(" separator="," close=")" item="it" index="index">
            #{it}

        </foreach>
    </select>
    
    <!--Bind字段，SQL语句动态拼接的,适用于模糊查询-->
    <select id="selectMore5" resultType="flower">
        select * from flower
        <where>
            <if test="param1 != null and param1 != ''">
                <bind name="bi" value="'%'+param1+'%'"/>
                fname like #{bi}
            </if>
            /*字符串拼接，不推荐使用*/
            <if test="param2 != null and param2 != ''">
                and variety like '%${param2}%'
            </if>
        </where>
    </select>
    
    <!--sql字段include-->
    <select id="selectMore6" resultType="flower">
        select <include refid="sq1"></include> from flower
        <where>
        <if test="param1 != null and param1 != ''">
            <bind name="bi" value="'%'+param1+'%'"/>
            fname like #{bi}
        </if>
        </where>
    </select>
    <sql id="sq1" >
        fid,fname
    </sql>