mybatis的聯合查詢及動态sql

mybatis聯合查詢

實作類的配置

<?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.qs.lianxi0623.mapper.UsermapperNews">
<!--    <resultMap id="userMap" type="Q_users">-->
<!--        <id column="userid" property="Q_userid"></id>-->
<!--        <result column="username" property="Q_username"></result>-->
<!--        <result column="pwd" property="Q_pwd"></result>-->
<!--        <result column="status" property="Q_status"></result>-->
<!--    </resultMap>-->
<!--    <select id="findpage" parameterType="int" resultMap="userMap">-->
<!--        select * from tb_users limit #{page},3;-->
<!--   </select>-->
<!--    <select id="findpageStatus"  resultMap="userMap">-->
<!--        select * from tb_users where status=#{status} limit #{page},3;-->
<!--    </select>-->
<!--&lt;!&ndash;    模糊查詢&ndash;&gt;-->
<!--    <select id="findlike" parameterType="String" resultMap="userMap">-->
<!--        select * from tb_users where status like '%${key}%'-->
<!--    </select>-->
    <resultMap id="userMap" type="Users">
        <id column="userid" property="userid"></id>
        <result column="username" property="username"></result>
        <result column="pwd" property="pwd"></result>
        <result column="status" property="status"></result>
        <collection property="news" ofType="News">
           <id column="newsid" property="newsid"></id>
            <result column="newstitle" property="newstitle"></result>
            <result column="newscontent" property="newscontent"></result>
            <result column="newstime" property="newstime"></result>
        </collection>

    </resultMap>
<select id="findByid" parameterType="Users" resultMap="userMap">
        select u.*,n.* from tb_users u,news n where u.userid=n.userid and u.userid=#{userid};
</select>
<!--    <resultMap id="newsMap" type="News">-->

<!--    </resultMap>-->
<!--    <resultMap id="userssmap" type="Users">-->
<!--        <id column="userid" property="userid"></id>-->
<!--        <result column="username" property="username"></result>-->
<!--        <result column="pwd" property="pwd"></result>-->
<!--        <result column="status" property="status"></result>-->
<!--    </resultMap>-->
    <resultMap id="newsMap" type="News">
        <id column="newsid" property="newsid"></id>
        <result column="newstitle" property="newstitle"></result>
        <result column="newscontent" property="newscontent"></result>
        <result column="newstime" property="newstime"></result>
        <association property="users" javaType="Users">
            <id column="userid" property="userid"></id>
            <result column="username" property="username"></result>
            <result column="pwd" property="pwd"></result>
            <result column="status" property="status"></result>
        </association>
    </resultMap>
    <select id="findByNewsid" parameterType="News" resultMap="newsMap">
        select u.*,n.* from tb_users u,news n where u.userid=n.userid and newsid=#{newsid};
    </select>
</mapper>
           

動态sql he 自帶去除字首和字尾的效果

<?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.qs.lianxi0625.mapper.Usermapper">
    <insert id="save" parameterType="Users">
        insert into tb_users
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="userid!=null">
                    userid,
                </if>
                <if test="username!=null">
                    username,
                </if>
                <if test="pwd!=null">
                    pwd,
                </if>
                <if test="status!=null">
                    status,
                </if>
            </trim>
         values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userid!=null">
                #{userid},
            </if>
            <if test="username!=null">
                #{username},
            </if>
            <if test="pwd!=null">
                #{pwd},
            </if>
            <if test="status!=null">
                #{status},
            </if>
        </trim>

    </insert>
    <update id="update" parameterType="Users">
        update tb_users
            <set>
                <if test="username!=null">
                    username=#{username},
                </if>
                <if test="pwd!=null">
                    pwd=#{pwd},
                </if>
                <if test="status!=null">
                    status=#{status},
                </if>
            </set>
        where userid=#{userid}
    </update>
    <delete id="del" parameterType="Users">
        delete from tb_users
            <where>
               <if test="userid!=null">
                   userid=#{userid}
               </if>
                <if test="username!=null">
                    and username=#{username}
                </if>
                <if test="pwd!=null">
                    and pwd=#{pwd}
                </if>
                <if test="status!=null">
                    and status=#{status}
                </if>
            </where>
    </delete>
</mapper>