MyBatis数据处理

1. <where></where>标签可以去掉前and

    <sql id="selectall" >
         SELECT * FROM USER
    </sql>
    <select id="selectUserByUsernameAndSex" parameterType="com.company.combine.model.User"
            resultType="com.company.combine.model.User">
       <include refid="selectall"/>
        <where>
            <if test="sex!=null and sex!=''">
                and sex = #{sex}
            </if>
            <if test="username!=null and username!=''">
                and username = #{username}
            </if>
        </where>
    </select>

2. foreach 在Javabean中传递List

    <!--bean中传List ,在User中有一个名叫idList的List-->
    <select id="selectByPrimaryKeys" parameterType="com.company.combine.model.User"
            resultType="com.company.combine.model.User">
        SELECT *
        FROM USER
        <where>
            <foreach collection="idList" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>
    </select>

3. foreach 直接传递数组

  <!--直接传数组-->
    <select id="selectByPrimaryKeys"
            resultType="com.company.combine.model.User">
        SELECT *
        FROM USER
        <where>
            <foreach collection="array" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>

    </select>

4. foreach 直接传递List

 <!--直接传list-->
    <select id="selectByPrimaryKeys"
            resultType="com.company.combine.model.User">
        SELECT *
        FROM USER
        <where>
            <foreach collection="list" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>

    </select>

5. 模糊查询

    <!--#{}  占位符-->
    <!--${}  拼接符-->
    <select id="selectUserByUsername" parameterType="String"
            resultType="com.company.combine.model.User">
        SELECT * FROM USER WHERE username LIKE "%${value}%"
    </select>

6. 一对一关联

<resultMap id="result2" type="com.company.combine.model.Orders">
        <id property="id" column="id"/>
        <result property="userId" column="userId"/>
        <result property="number" column="number"/>
        <result property="createtime" column="createtime"/>

        <association property="user" javaType="com.company.combine.model.User">
            <result property="username" column="username"/>
        </association>
    </resultMap>
    <!--一对一-->
    <select id="selectOrdersList"
            resultMap="result2">
        SELECT
        o.id,
        o.user_id as userId,
        o.number,
        o.createtime,
        u.username
        FROM orders o LEFT JOIN user u ON o.user_id=u.id

    </select>

7. 一对多关联

    <!--一对多-->
    <resultMap id="result3" type="com.company.combine.model.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <collection property="ordersList" ofType="com.company.combine.model.Orders">
            <id property="id" column="id"/>
            <result property="userId" column="userId"/>
            <result property="number" column="number"/>
            <result property="createtime" column="createtime"/>
        </collection>
    </resultMap>
    <select id="selectUserList"
            resultMap="result3">
        SELECT
        o.id,
        o.user_id as userId,
        o.number,
        o.createtime,
        u.username
        FROM user u LEFT JOIN orders o ON u.id=o.user_id
    </select>