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);
}