Mybatis学习日志(二)

学习了动态SQL

where+if；choose+when+otherwise；updata+set+if+where

<select id="getCustomerif"
            resultType="com.swpu.domain.Customer"
            parameterType="com.swpu.domain.Customer">
        select * from customer
        <where>
            <if test=" name!= null and name != '' ">
                name like concat('%',#{name},'%')
            </if>
            <if test=" job != null and job != '' ">
                and job like concat('%',#{job},'%')
            </if>
        </where>
    </select>

 

<select id="getCustomerchoose"
            resultType="com.swpu.domain.Customer"
            parameterType="com.swpu.domain.Customer">
        select * from customer
        <choose>
            <when test="name!= null and name != '' ">
                name like concat('%',#{name},'%')
            </when>
            <when test=" job != null and job != '' ">
                and job like concat('%',#{job},'%')
            </when>
            <otherwise>
                where phone is not null and phone!=''
            </otherwise>
        </choose>
    </select>

需要注意：在select语句末或者otherwise语句头要加where 否则报错SQL语句错误。

<update id="setCustomer" parameterType="com.swpu.dao.CustomerDao">
        update customer
        <set>
            <if test="name!= null and name != '' ">
                name = #{name}
            </if>
            <if test=" job != null and job != '' ">
                , job = #{job}
            </if>
        </set>
        where id=#{id}
    </update>

需要注意：多个if语句后续不能用and，只能用',' ，否则会报错：Truncated incorrect DOUBLE value【and将字符串视为double类型处理了】

foreach示例：

 

<select id="getCustomerforeach" resultType="com.swpu.domain.Customer">
        select * from customer
        <where>
            <foreach collection="list" item="id" open="id in(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

 

    <insert id="insertCustomers" parameterType="com.swpu.domain.Customer">
        insert customer values
        <foreach collection="list" item="Customer" separator=",">
            (null,#{Customer.name},#{Customer.job},#{Customer.phone},#{Customer.age})
        </foreach>
    </insert>

    <delete id="deleteCustomers">
        delete from customer
        <where>
            <foreach collection="list" item="id" open="id in(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </delete>

 

以防万一，这里把普通的CUDR语句也写上了

 

    <!--    1-->
    <select id="getAllUsers" resultType="com.swpu.domain.User">
        select * from user
    </select>
    <!--    2-->
    <select id="getUserById" resultType="com.swpu.domain.User" parameterType="int">
        select * from user where id=#{id}
    </select>
    <!--    3-->
    <select id="getUsersByName" resultType="com.swpu.domain.User">-- parameterType="String">
        select * from user where name like concat("%",#{name},"%")
    </select>
    <!--    4-->
    <insert id="InsertUser" parameterType="com.swpu.domain.User">
        insert into user(name,sex,address) values (#{name}, #{sex},#{address})
    </insert>
    <!--    5-->
    <update id="updateUser" parameterType="com.swpu.domain.User">
        update user set name=#{user.name},sex=#{user.sex},address=#{user.address} where id=#{id}
    </update>
    <!--    6-->
    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>

 

 

 

【Over】

搜索

复制