在Mybatis中高频率遇到的各种sql语句

在Mybatis中写sql语句

1、查

正常的选择：（这里要注意实际上我们判断条件里面的那个userName是我们传递进来的东西）

vo前端传来的，pojo和dto是处理后传到前端的。

<select id="listUserInfoBy" resultType="com.czx.pojo.UserInfo" parameterType="com.czx.vo.UserInfoVo">
        select * from userinfo
        <where>
            <if test="userName != null and userName !=''">
                and username = #{userName}
            </if>
            <if test="regEmail != null and regEmail !=''">
                and regemail like #{regEmail}
            </if>
            <if test="mobile != null and mobile !=''">
                and mobile like #{mobile}
            </if>
        </where>
    </select>

一对多的选择：column代表的是字段名，而property代表的是我java类里面的属性

<!--下面这种情况是一对多-->
    <resultMap id="userInfoMap" type="com.czx.pojo.UserInfo">
        <id column="USERID" property="userId"></id>
        <result column="USERNAME" property="userName"></result>
        <result column="PASSWORD" property="password"></result>
        <result column="REGEMAIL" property="regEmail"></result>
        <result column="MOBILE" property="mobile"></result>
        <result column="ICON" property="icon"></result>
        <collection property="roleInfoList" ofType="com.czx.pojo.RoleInfo">
            <id column="ROLEID" property="roleId"></id>
            <result column="ROLENAME" property="roleName"></result>
            <result column="SHORTNAME" property="shortName"></result>
        </collection>
    </resultMap>

	<select id="listUserInfoAndRoleInfo" resultMap="userInfoMap">
        SELECT u.*,r.* from userinfo u,user_role ur,roleinfo r
        where u.USERID = ur.USERID and r.ROLEID = ur.ROLEID
    </select>
	

一对一选择：注意，javaType是指一对一的属性类型，而ofType是指映射到List中的pojo类型，所以一对一用javaType，一对多用ofType。一对一用association，一对多用collection

 <resultMap id="getOrderInfoByMap" type="com.czx.pojo.OrderInfo">
        <id column="oid" property="oid"></id>
        <result column="cid" property="cid"></result>
        <result column="orderdesc" property="orderdesc"></result>
        <association property="consumer" javaType="com.qf.pojo.Consumer">
            <id column="cid" property="cid"></id>
            <result column="name" property="name"></result>
        </association>
    </resultMap>


	<select id="selectConsumerOrderOneToOne" resultMap="getOrderInfoByMap">
        select c.* , o.* from consumer c,orderinfo o where c.cid = o.cid
    </select>

模糊查询：注意一下在这里的like后面的东西

<select id="getRoleInfoByLike" resultType="com.czx.dto.RoleInfoDto" parameterType="com.czx.vo.RoleInfoVo">
        select * from roleinfo
        <where>
            <if test="roleName != null and roleName != ''">
                and roleName like "%"#{roleName}"%"
            </if>
            <if test="shortName != null and shortName != ''">
                and shortName like "%"#{shortName}"%"
            </if>
        </where>
    </select>

分页

<select id="getUserByPage" resultMap="getUserByDto" parameterType="Map">
        select * from user limit #{startIndex},#{endIndex}
    </select>

表连接和isnull的运用，isnull判断是否为空

<select id="getRoleInfoByUserId" parameterType="int" resultType="com.czx.dto.RoleInfoDto">
          SELECT r1.*,ISNULL(tab.roleid) isnull from
          (SELECT r.* from user u,user_role ur,roleinfo r where u.userid = ur.userId and ur.roleid = r.roleid and u.userid = #{userId})
           tab RIGHT JOIN roleinfo r1 on r1.roleid = tab.roleid
    </select>

动态sql片段

<select id="selectByTrim" resultType="com.czx.dto.UserInfoLisDto" parameterType="com.czx.vo.UserInfoLisVo">
        select * FROM USER
        <trim prefix="where" prefixOverrides="or">
            <include refid="selectUserByIdAndAgeSql"></include>
        </trim>
    </select>

<sql id="selectUserByIdAndAgeSql">
        <if test="name != '' and name != null">
           or name = #{name}
        </if>
        <if test="id != '' and id != null">
           or id = #{id}
        </if>
        <if test="age != '' and age != null">
           or age = #{age}
        </if>
    </sql>

多选一：

<select id="getUserByChoose" parameterType="com.czx.vo.UserInfoLisVo" resultType="com.czx.dto.UserInfoLisDto">
        select * from user
        <where>
            <choose>
                <when test="name != null and name != ''">
                    name = #{name}
                </when>
                <when test="age != null and age != ''">
                    age = #{age}
                </when>
                <when test="id != null and id != ''">
                    id = #{id}
                </when>
            </choose>
        </where>
    </select>

2、增

正常版本增

<insert id="addUser" parameterType="com.czx.vo.RoleInfoListVo">
        insert into user(id,name,age) values (null,#{name},#{age});
    </insert>

批量增加： collection:指定输入对象中的集合属性 item:每次遍历生成的对象 open:开始遍历时的拼接字符串 close:结束时拼接的字符串 separator:遍历对象之间需要拼接的字符串

<insert id="insertRoleInfoByList" parameterType="com.czx.vo.RoleInfoLisVo">
        insert into user_role values 
        <foreach collection="roleIdList" separator="),(" item="roleId" open="(" close=")">
            default,#{userId},#{roleId},1
        </foreach>
    </insert>

3、改

正常修改

<update id="updateUser" parameterType="com.czx.vo.UserInfoLisVo">
        UPDATE user set name=#{name},age =#{age} where id=#{id}
    </update>

批量修改

<update id="updateByUserIdList" parameterType="com.czx.vo.UserInfoLisVo">
        update userInfo set status = 0
         <where>
             userid in
             <foreach collection="userIdList" item="userId" open="(" separator="," close=")">
                 #{userId}
             </foreach>
         </where>
    </update>

4、删

正常的删

<delete id="deleteRoleInfoByUserId" parameterType="int">
        delete from user_role where userid =#{userId}
    </delete>