动态SQL

SMBMS 多条件查询的时候

# if 根据用户角色和用户名模糊查询

where 智能除去多余的and 或 or

trim

set

choose(when otherwise)

foreach 传入参数是集合或数组,map

mapper映射文件

<?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.mybatis.dao.UserMapper">

    <select id="findAllUser" resultType="com.mybatis.pojo.User">
      /*  select * from smbms_user;*/
      <include refid="query"/>
    </select>
   <select id="findUserById" parameterType="Integer" resultType="com.mybatis.pojo.User">
         select id,userCode,userName from smbms_user where id = #{uid}
   </select>

    <insert id="addUser" parameterType="User" >
      insert into smbms_user (id,userCode,userName)
      values (#{id},#{userCode},#{userName})
    </insert>
    <!--<update id=""></update>
    <delete id=""></delete>-->
    <!--可复用的sql语句片段-->
    <sql id="query">
        select * from smbms_user
    </sql>

    <select id="findUserByCondition" resultType="User" >
        <include refid="query"/>
        where userRole=#{userRole} and userName LIKE CONCAT ('%',#{uName},'%')
    </select>

    <!--<select id="findUserByMultiCondition" parameterType="String" resultType="User">
        select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r
        where u.userRole=r.id and u.userName like CONCAT ('%',#{userName},'%')
          and r.roleName=#{userRoleName}
    </select>-->

    <resultMap id="userMap" type="User" >
       <!-- <result property="id" column="id"/>-->
        <!--<result property="userCode" column="userCode"/>-->
        <result property="userName" column="userName"/>
      <!--  <result property="userRole" column="userRole"/>-->
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <resultMap id="userMap2" type="User" >
        <!-- <result property="id" column="id"/>-->
        <id property="id" column="id"></id>
        <!--唯一索引,提升性能,会更快-->
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
          <result property="userRole" column="userRole"/>
        <association property="role" javaType="Role" >
            <result property="id" column="id"/>
            <result property="roleName" column="roleName"/>
        </association>
    </resultMap>
    <select id="findUserByMultiCondition" parameterType="String" resultMap="userMap">
            select u.id,u.userCode,u.userName,u.userRole,r.roleName  from smbms_user u,smbms_role r
        where u.userRole=r.id and u.userName like CONCAT ('%',#{userName},'%')
          and r.roleName=#{userRoleName}
    </select>

    <select id="findUserBycondition2" parameterType="map" resultType="User" resultMap="userMap2" >
        select u.id,u.userCode,u.userName,u.userRole,r.roleName  from smbms_user u,smbms_role r
        where u.userRole=r.id and u.userName like CONCAT ('%',#{userName},'%')
          and r.roleName=#{userRoleName}
    </select>

    <resultMap id="userMap3" type="User">
        <id property="id" column="id"></id>
       <!-- <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="userRole" column="userRole"/>-->
        <collection property="addressList" ofType="Address">
            <id property="id" column="a_id"/>
            <result property="contact" column="contact"/>
            <result property="addressDesc" column="addressDesc"/>
            <result property="postCode" column="postCode"/>
            <result property="tel" column="tel"/>
            <result property="createdBy" column="createdBy"/>
            <result property="userId" column="userId"/>
        </collection>
    </resultMap>
    <select id="findUserByUId" parameterType="Integer" resultMap="userMap3">
        select u.*,a.id a_id,a.contact,a.addressDesc,a.postCode,a.tel,a.createdBy,a.userId from smbms_user u,smbms_address a
        where u.id=a.userId and u.id=#{uid}
    </select>
    <select id="findUserByUserRoleAndUserName" resultType="User">
        select * from smbms_user
     /* where 标签可以智能除掉多余的and 和 or */
        <where>
              <if test="userRole !=null ">
            and  userRole=#{userRole}
        </if>
            <if test="userName !=null and userName !='' ">
                and  userName LIKE CONCAT ('%',#{userName},'%')
            </if>
         </where>
    </select>

    <select id="findUserByUserRoleAndUserName2" resultType="User">
        select * from smbms_user
       <trim prefix="where" prefixOverrides="and|or">
           <if test="userRole !=null ">
               and  userRole=#{userRole}
           </if>
           <if test="userName !=null and userName !='' ">
               and  userName LIKE CONCAT ('%',#{userName},'%')
           </if>
       </trim>

    </select>
    <!--set可以自动去除多余尾部,-->
    <update id="updateUserById" parameterType="User">
          update smbms_user
        <set>
         <if test="userCode != null">
             userCode=#{userCode},
         </if>
         <if test="userName != null">
             userName=#{userName},
         </if>
        <if test="userRole !=null">
            userRole=#{userRole},
        </if>
        </set>
          where id=#{id}
    </update>

    <update id="updateUserById2" parameterType="User">
        update smbms_user
        <trim prefix="set" suffix=" where id=#{id}" suffixOverrides=",">
            <if test="userCode != null">
                userCode=#{userCode},
            </if>
            <if test="userName != null">
                userName=#{userName},
            </if>
            <if test="userRole !=null">
                userRole=#{userRole},
            </if>
        </trim>

    </update>

    <select id="findUser_choose" resultType="User">
        select * from smbms_user
        <where>
            <choose>
                <when test="userCode !=null and userCode !='' ">
                  and  userCode LIKE CONCAT  ('%',#{userCode},'%')
                </when>
                <when test="userRole !=null ">
                    and userRole=#{userRole}
                </when>
                <when test="userName !=null and userName !=''">
                    and userName LIKE CONCAT ('%',#{userName},'%')
                </when>

                <otherwise>
                    and YEAR(creationDate)= YEAR(#{creationDate})
                </otherwise>
            </choose>
        </where>

    </select>
    <select id="findUser_array" parameterType="Integer" resultType="User">
        select * from smbms_user where userRole in
        <foreach collection="array"  open="(" close=")" separator="," item="role">
            #{role}
        </foreach>
   </select>

    <select id="findUser_list" parameterType="Integer" resultType="User">
        select * from smbms_user where userRole in
        <foreach collection="list"  open="(" close=")" separator="," item="role">
            #{role}
        </foreach>
    </select>

    <select id="findUser_map" resultType="User">
        select * from smbms_user where
        userName LIKE CONCAT ('%',#{userName},'%')
         and
        userRole in
        <foreach collection="roles"  open="(" close=")" separator="," item="role">
            #{role}
        </foreach>
    </select>
</mapper>

package com.mybatis.dao;

import com.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    public  List<User> findAllUser();

    public  User findUserById(Integer uid);

    public int addUser(User user);

    public int  delUserById(Integer id);

    public int updateUser(User user);

    //?如果有多个参数怎么办?我就想一次性传多个参数怎么办?
    //查询 用户角色 1 系统管理员中 有没有 姓 邓? userRole userName
    //参数个数大于3个,一般可以封装成对象,否则直接传参
    //传单个参数
    public List<User>  findUserByCondition(@Param("userRole") Integer userRole,@Param("uName") String userName);

    public List<User> findUserByMultiCondition(@Param("userRoleName")String roleName,@Param("userName")String userName);

    public List<User> findUserBycondition2(Map map);

    public User findUserByUId(Integer uid);

    public List<User> findUserByUserRoleAndUserName(@Param("userRole") Integer userRole,@Param("userName") String userName);

    public List<User> findUserByUserRoleAndUserName2(@Param("userRole") Integer userRole,@Param("userName") String userName);

    public int updateUserById(User user);
    public int updateUserById2(User user);

    public List<User> findUser_choose(@Param("userRole") Integer userRole,
                                      @Param("userName")String userName,
                                      @Param("userCode") String userCode,
                                      @Param("creationDate")Date creationDate);

    public List<User> findUser_array(Integer[] roles);

    public List<User> findUser_list(List<Integer> roles);

    public List<User> findUser_map(Map map);



}