Mybatis增删改查、复杂查询、动态SQL

一：增删查改

注意事项：resultType=" " 要写上完整路径（除非有进行相关的配置，才可以仅仅写类的名称）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ma.dao.UserMapper">

    <!--映射之后的User&#45;&#45;com.ma.pojo.User-->
   <!-- <select id="getUserList" resultType="hello">-->
    <select id="getUserList" resultType="User">
        select * from mybatis.user
     </select>

    <select id="getUserLike" resultType="com.ma.pojo.User">
        select * from mybatis.user where name like "%"#{value}"%";
    </select>

    <select id="getUserById" parameterType="int" resultType="com.ma.pojo.User">
        select * from mybatis.user where id = #{id};
    </select>
    
    <insert id="addUser" parameterType="com.ma.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id},#{name},#{pwd});
    </insert>

   <!-- map用法，可以随意给某赋值，然后其他值不定义-->
    <!--<insert id="addUser2" parameterType="map">
        insert into mybatis.user (id, name, pwd) values (#{userid},#{userName},#{passWord});
    </insert>-->
    
    <update id="updateUser" parameterType="com.ma.pojo.User">
        update mybatis.user set name = #{name}, pwd = #{pwd} where id = #{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id =#{id};
    </delete>
    
    <select id="getUserByLimit" parameterType="map" resultType="user">
        select * from mybatis.user limit #{startIndex},#{PageSize};
    </select>
</mapper>

 二：复杂查询（多对一，一对多)如下：

（1）自我感觉比较容易理解的做法

<select id="getMatch" resultMap="StudentTeacher">
        select student.name sname,teacher.name tname,teacher.tid tid,sid from teacher,student where student.tid=teacher.tid
    </select>
    <resultMap id="StudentTeacher" type="com.ma.pojo.Student">
        <result property="sid" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.ma.pojo.Teacher">
            <result property="tid" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

其中property是数据库中的字段名，column是目前在java中的名字

注意事项：假如两个表中有相同的字段名，要在查询中为其取别名，如上述例子：student.name sname,teacher.name tname

 三：动态SQL

<select id="queryBlogIF" parameterType="map" resultType="com.ma.pojo.Blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>
    <!--when语句-->
    <select id="queryBlogChoose" parameterType="map" resultType="com.ma.pojo.Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>
    <!--set语句，智能处理“，”问题-->
    <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>