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="****.UserMapper"><!--****包名，UserMapper绑定Mybatis java接口文件名--><resultMap id="UserResultMap" type="****.User">    <!--****包名，User绑定对应java类对象文件名--><id column="id" property="id" jdbcType="INTEGER" /><result column="username" property="username" jdbcType="VARCHAR" /><result column="name" property="name" jdbcType="VARCHAR" /><result column="phone" property="phone" jdbcType="VARCHAR" /><result column="email" property="email" jdbcType="VARCHAR" /><result column="address" property="address" jdbcType="VARCHAR" /><result column="sex" property="sex" jdbcType="VARCHAR" /></resultMap><resultMap id="UserDepartResultMap" type="****.User"  extends="UserResultMap">          <collection property="roles" ofType="****.Depart">              <id property="id" column="roleId" />              <result property="name" column="name" />              <result property="remark" column="remark" />          </collection>      </resultMap>    <sql id="User_Column">id, username,name, phone, email, address,sex</sql><sql id="User_Column_U">u.id, u.username,u.name,u.phone, u.email,u.address, u.sex</sql><sql id="Depart_Column_D">d.id,d.name,d.remark</sql><select id="selectAll" resultMap="UserResultMap">select <include refid="User_Column" /> from t_user;</select><select id="selectUserDepart" parameterType="java.util.Map" resultMap="UserResultMap">select <include refid="User_Column_U" /> ,<include refid="Depart_Column_D" />from t_user u left join t_user_depart ud on u.id=ud.userId left join t_depart d on ud.departId=d.idwhere concat(u.username, u.name) like #{text,jdbcType=VARCHAR}<if test="orderType != null">order by convert(${orderType} using gbk) ${orderBy}</if>limit #{startRow}, #{pageSize};</select><!-- 用户的分页查询 --><select id="selectUser" resultMap="UserResultMap" parameterType="java.util.Map">select <include refid="User_Column" /> from t_user where concat(u.username, u.name) like #{text,jdbcType=VARCHAR}<if test="orderType != null">order by convert(${orderType} using gbk) ${orderBy}</if>limit #{startRow}, #{pageSize};</select>    <select id="selectByUserName" resultMap="UserResultMap" parameterType="java.lang.String">select <include refid="User_Column" /> from t_user where user_name = #{username,jdbcType=VARCHAR}</select><select id="selectById" resultMap="UserResultMap" parameterType="java.lang.Integer">select <include refid="User_Column" /> from t_user where u.id = #{id,jdbcType=INTEGER}</select><select id="getRoleIdByUserId" resultType="java.lang.Integer" parameterType="java.lang.Integer">select roleId from t_user_role where userId = #{userId,jdbcType=INTEGER}</select><select id="getUsersByIds" resultMap="UserResultMap" parameterType="java.util.List">select <include refid="User_Column" /> from t_user where id in<foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>;</select></mapper>参数类型可以为Integer 、String、List<>、Map<>返回类型可以为Integer 、String、List<>1.根据ID查询用户 t_userpublic User selectById(Integer userId);<select id="selectById" parameterType="java.lang.Integer" resultMap="UserResultMap">select <include refid="User_Column" /> from t_user where u.id = #{id,jdbcType=INTEGER}</select>2.根据ID查询用户姓名 public String selectById(Integer userId);//若结果只有一个返回结果为String 否则为List<String><select id="selectById" parameterType="java.lang.Integer" resultType="java.lang.String">select name from t_user where u.id = #{id,jdbcType=INTEGER}</select>3.根据用户ID批量获取User对象public List<User> getUsersByIds(List<Integer> userList);<select id="getUsersByIds" parameterType="java.util.List" resultMap="UserResultMap">select <include refid="User_Column" /> from t_user where id in<foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>;</select>或者<select id="getUsersByIds" parameterType="java.lang.Integer" resultMap="UserResultMap">select <include refid="User_Column" /> from t_user where id in<foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>;</select>4.根据用户名、或姓名进行模糊查询    <select id="selectUserByText" parameterType="java.lang.String" resultMap="UserResultMap">  <!--返回类型user-->select <include refid="User_Column" /> from t_user where concat(username,name) like #{text,jdbcType=VARCHAR};    </select>5.获取符合条件的记录个数<select id="count" parameterType="java.lang.String" resultType="java.lang.Integer"> <!--resultType="java.lang.Integer" 返回个数 返回类型Integer-->select count(*) from t_user where concat(username, name) like #{text, jdbcType=VARCHAR};</select>6.获取所有用户IdPublic  List<Integer> selectUserIds();<select id="selectById" resultType="java.lang.Integer">select id from t_user;</select>7.获取用户的对象属性User 的属性：id, username,name, phone, email, address,sex,Depart departDepart 的属性：id,name,remarkpublic List<User> selectUserDepart(Map<String, Object> params);//部门与用户一一对应，若一个用户多个部门则限制条数取值不正确        Map<String, Object> params = new HashMap<>();params.put("text", text);//搜索内容params.put("orderBy", orderby);//参数大小写params.put("orderType", orderType);<select id="selectUserDepart" parameterType="java.util.Map" resultMap="UserDepartResultMap">    <!-- 返回结果 UserDepartResultMap 聚集部门对象 -->select <include refid="User_Column_U" /> ,<include refid="Depart_Column_D" />from t_user u left join t_user_depart ud on u.id=ud.userId left join t_depart d on ud.departId=d.idwhere concat(u.username, u.name) like #{text,jdbcType=VARCHAR}<if test="orderType != null">order by convert(${orderType} using gbk) ${orderBy}</if></select>8.获取用户的对象属性        Map<String, Object> params = new HashMap<>();params.put("pageSize", pageSize);//选取条数params.put("startRow", startRow);//起始行params.put("text", text);//搜索内容params.put("orderBy", orderby);//排序方式params.put("orderType", orderType);//排序列<select id="selectUser" resultMap="UserResultMap" parameterType="java.util.Map">select <include refid="User_Column" /> from t_user where concat(u.username, u.name) like #{text,jdbcType=VARCHAR}<if test="orderType != null">order by convert(${orderType} using gbk) ${orderBy}</if>limit #{startRow}, #{pageSize};</select>