MyBatis 常用写法

绝地灬酷狼 2021-12-16 08:41 339阅读 0赞

[https://blog.csdn.net/javadhh/article/details/52422242][https_blog.csdn.net_javadhh_article_details_52422242]

**实际项目总结的一些用法，以后用相似的可以直接拷贝**

**foreach****用法：**

/**
     * 查询出用户最大的登录时间
     * @param informedObjIds 用户id集合
     * @return
     */
    List<UserLastLoginBean> selectUserMaxLoginTimeByUserIds(
    		List<Integer> informedObjIds);
    
    <resultMap id="MaxLoginTimeMap" type="com.clou.douliu.server.bean.UserLastLoginBean">
    	<id column="userId" property="userId" jdbcType="INTEGER" />
    	<result column="maxLoginTime" property="maxLoginTime" jdbcType="TIMESTAMP" />
    </resultMap>
    
    
    
    <select id="selectUserMaxLoginTimeByUserIds" parameterType="map"  resultMap="MaxLoginTimeMap">
    	SELECT
    		USER_ID as userId ,
    		MAX(LOGIN_TIME) as maxLoginTime
    	FROM
    		user_login_details
    	WHERE
    		USER_ID IN
    		<foreach collection="list" item="userId" index="index" open="("
    			close=")" separator=",">
    			#{userId}
    		</foreach>
    	group by USER_ID
    	ORDER BY USER_ID ASC
    </select>

得到insert后的自增长ID

调用：(count为影响条数，reply的id字段在添加之后会有值---自增长ID)
    int count = this.mapperFactory.replyMapper.insertSelective(reply);
    
    
    //mapper.java
    public abstract int insert(Reply paramReply);
    
    
    <insert id="insert" parameterType="com.clou.douliu.server.bean.mybatis.Reply" >
        <selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER" >
          SELECT LAST_INSERT_ID()
        </selectKey>
        insert into comment (USER_ID, CONTENT, PICTURE, 
          TIMETAG, TYPE, ACT_ID, 
          VALID)
        values (#{userId,jdbcType=INTEGER}, #{content,jdbcType=VARCHAR}, 
      	 #{picture,jdbcType=VARCHAR,javaType=String,
    typeHandler=com.clou.douliu.server.mybatis.plugin.RemoveDomainTypeHandler}, 
          #{timetag,jdbcType=TIMESTAMP}, #{type,jdbcType=INTEGER},#{actId,jdbcType=INTEGER},      
          #{valid,jdbcType=SMALLINT})
     </insert>

排除（筛选热门分享，排除管理员1号）

<select id="selectHotShareAlternativeListByUserId" parameterType="map"	resultMap="ShareDetailMap">
    SELECT  * FROM
       	(SELECT 
    <include refid="Alias_Column_List" />,
    <include refid="com.clou.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />,
    <include 	refid="com.clou.douliu.server.mybatis.mapper.UserDetailsMapper.Alias_Column_List"/>
       	 FROM
    		ACTIVITYS ACTIVITYS 
      	left join USER USER on (USER.ID=ACTIVITYS.USER_ID) 
      	left join user_details user_details ON (user_details.USER_ID = USER.ID)
      	WHERE
    		USER.TYPE > 0 
    		AND USER.ID = #{userId,jdbcType=INTEGER}
    AND ACTIVITYS.valid = 1
    AND ACTIVITYS.timetag >= #{startDate,jdbcType=TIMESTAMP}
    		AND ACTIVITYS.timetag <= date_add(#{endDate,jdbcType=TIMESTAMP},interval 24 hour)
      	ORDER BY date(ACTIVITYS.timetag) desc
      	LIMIT #{start,jdbcType=INTEGER},#{loadSize,jdbcType=INTEGER})  TEMP
    WHERE
    NOT EXISTS(SELECT 1 FROM HOTS HOTS WHERE HOTS.ACT_ID = TEMP.ACTIVITYS_ID)
    </select>

list参数传递

/**
        * 通过id集合删除记录
        * @param delPhotoIdList id集合
        * @return
    */
    public abstract int removeByIdList(List<Integer> delPhotoIdList);
    
    
    
    <delete id="removeByIdList">
      	delete from album_detail where ID in
           <foreach collection="list" item="photoId" index="index"
               open="(" close=")" separator=",">
               #{photoId}
          </foreach>
    </delete>

Map参数传递(find\_in\_set用法）

/**
     * 根据群id集合获取群的全部信息，按照list里面的序号排序
     * @param paramMap 参数map <br/>
     *  groupIdList  --- List<Integer>   <br/>
     *  sortStr ---- String   排序字符串,格式：'1932,2342,3242' 也就是集合中的id,已逗号隔开
     */
    List<Groups> selectGroupListBySort(Map<String, Object> paramMap);
    
    
    
    <select id="selectGroupListBySort" resultMap="BaseResultMap" parameterType="java.util.Map" >
      select
      <include refid="Base_Column_List" />
      from groups
      where id in
       <foreach collection="groupIdList" item="item" index="index" open="(" close=")" separator=",">
        #{item}
     </foreach>
     and status = 1
     order by find_in_set(id, #{sortStr,jdbcType=VARCHAR}) asc;
    </select>
    
    
    Bussiness方法：从redis中取出热门群组列表，按照指定顺序从数据库中查询详细信息
    /**
     * 获取推荐群列表
     * @return
     */
    public List<Groups> selectTopGroups() {
    	CacheManager cm=CacheManager.getInstance();
    	Set<String> s=cm.getRecommandGroups();
    	
    	logger.debug("推荐列表" + s);
    	
    	List<Integer> groupIds = null; 
    	if(s!=null && s.size() > 0){
    		groupIds = new ArrayList<Integer>();
    		for(String g : s){
    			try {
    				Integer gId = Integer.parseInt(g);
    				groupIds.add(gId);
    			} catch (Exception e) {
    			}
    		}
    	}
    	if(groupIds != null && groupIds.size() > 0){
        		Map<String, Object> paramMap = new HashMap<String, Object>();
        		StringBuilder sortStr = new StringBuilder();
        		for(Integer id : groupIds){
        			sortStr.append(id).append(",");
        		}
        		sortStr.delete(sortStr.lastIndexOf(","), sortStr.length());
        	
        		paramMap.put("groupIdList", groupIds);
        		paramMap.put("sortStr", sortStr.toString());
        	
    		return this.mapperFactory.groupsMapper.selectGroupListBySort(paramMap);
    	}
    	return null;
    }

**Criteria的Where条件拼凑**（工具自动生成）

**1、不带别名的写法，用于单张表的操作**

<sql id="Example_Where_Clause" >
      <where >
        <foreach collection="oredCriteria" item="criteria" separator="or" >
          <if test="criteria.valid" >
            <trim prefix="(" suffix=")" prefixOverrides="and" >
              <foreach collection="criteria.criteria" item="criterion" >
                <choose >
                  <when test="criterion.noValue" >
                    and ${criterion.condition}
                  </when>
                  <when test="criterion.singleValue" >
                    and ${criterion.condition} #{criterion.value}
                  </when>
                  <when test="criterion.betweenValue" >
                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                  </when>
                  <when test="criterion.listValue" >
                    and ${criterion.condition}
                    <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                      #{listItem}
                    </foreach>
                  </when>
                </choose>
              </foreach>
            </trim>
          </if>
        </foreach>
      </where>
    </sql>
    
    例子：
    List<PushSchema> selectByExample(PushSchemaCriteria example) throws RuntimeException;
    
    <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.clou.douliu.server.bean.mybatis.PushSchemaCriteria" >
      select
      <if test="distinct" >
        distinct
      </if>
      <include refid="Base_Column_List" />
      from push_schema
      <if test="_parameter != null" >
        <include refid="Example_Where_Clause" />
      </if>
      <if test="orderByClause != null" >
        order by ${orderByClause}
      </if>
      <if test="start >= 0 " >
        limit ${start}
      </if>
      <if test="count >= 0 " >
        ,${count}
      </if>
    </select>

**带别名的写法，用于多张表联合操作**

<sql id="Alias_Update_By_Example_Where_Clause" >
      <where >
        <foreach collection="example.oredCriteria" item="criteria" separator="or" >
          <if test="criteria.valid" >
            <trim prefix="(" suffix=")" prefixOverrides="and" >
              <foreach collection="criteria.criteria" item="criterion" >
                <choose >
                  <when test="criterion.noValue" >
                    and PUSH_SCHEMA.${criterion.condition}
                  </when>
                  <when test="criterion.singleValue" >
                    and PUSH_SCHEMA.${criterion.condition} #{criterion.value}
                  </when>
                  <when test="criterion.betweenValue" >
                    and PUSH_SCHEMA.${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                  </when>
                  <when test="criterion.listValue" >
                    and PUSH_SCHEMA.${criterion.condition}
                    <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                      #{listItem}
                    </foreach>
                  </when>
                </choose>
              </foreach>
            </trim>
          </if>
        </foreach>
      </where>
    </sql>
    
    例子：
    /**
     * 查询方案列表（包含具体方案step的List）
     * @param example
     * @return
     * @throws RuntimeException
     */
    List<PushSchema> selectWithStepByExample(PushSchemaCriteria example) throws RuntimeException;
    
    
    <select id="selectWithStepByExample" resultMap="SchemaWithStepMap" parameterType="com.clou.douliu.server.bean.mybatis.PushSchemaCriteria" >
      select
      <include refid="Alias_Column_List" />,
      <include refid="com.clou.douliu.server.mybatis.mapper.PushStepMapper.Alias_Column_List" />
      from push_schema PUSH_SCHEMA 
     	 left join push_step PUSH_STEP on (PUSH_SCHEMA.ID=PUSH_STEP.SCHEMA_ID) 
      <if test="_parameter != null" >
        <include refid="Alias_Example_Where_Clause" />
      </if>
      <if test="orderByClause != null" >
        order by ${orderByClause}
      </if>
      <if test="start >= 0 " >
        limit ${start}
      </if>
      <if test="count >= 0 " >
        ,${count}
      </if>
    </select>

**3、复杂条件筛选**：

刚刚这个例子，只是针对与Push\_Schma表有筛选条件，如果Push\_step表也有条件，那么条件语句就要改写了

原理：

先有where语句，再把Criteria的条件拼凑到后面，

这就涉及到Criteria条件到底存不存在的问题，存在才有and ，不存在就直接结束

暂时没有研究传递两个参数（PushSchemaCriteria, PushStepCriteria）的形式

有改写了where条件的写法，详细请看下一个案例。

**Collection 和 association 的用法**

/**
     * selectShareDetailList: 选取分享列表,包含发布分享的人，以及该分享对应的图片列表. <br/>
     * 
     * 
     * @author Hongbin Yuan
     * @param bean
     * @return
     * @since JDK 1.6
     */
    public abstract List<Share> selectShareDetailList(ShareCriteria paramShareCriteria);
    
    
    <resultMap id="ShareDetailMap" type="com.clou.douliu.server.bean.mybatis.Share" extends="AliasResultMap">
    	<association property="publisher"
    		resultMap="com.clou.douliu.server.mybatis.mapper.UserMapper.UserDetailResultMap"></association>
    	<collection property="sharePictureList" resultMap="com.clou.douliu.server.mybatis.mapper.SharePictureMapper.AliasResultMap"></collection>
    </resultMap>
    
    
    <select id="selectShareDetailList" parameterType="com.clou.douliu.server.bean.mybatis.ShareCriteria" resultMap="ShareDetailMap">
    	SELECT
    	<include refid="Alias_Column_List" />,
    	<include
    		refid="com.clou.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />,
    	<include
    		refid="com.clou.douliu.server.mybatis.mapper.SharePictureMapper.Alias_Column_List" />
    	FROM
    		ACTIVITYS ACTIVITYS 
    		left join USER USER on (USER.ID=ACTIVITYS.USER_ID) 
    		left join ACT_PICTURE ACT_PICTURE on (ACTIVITYS.ID=ACT_PICTURE.ACT_ID)
    	WHERE 
    		USER.type > 0 
    		<foreach collection="oredCriteria" item="criteria" index="index" separator="or">
    			<if test="criteria.valid" >
    			<if test="index == 0">
    				AND
    			</if>
    			<trim prefix="(" suffix=")" prefixOverrides="and">
    				<foreach collection="criteria.criteria" item="criterion">
    					<choose>
    						<when test="criterion.noValue">
    							and ACTIVITYS.${criterion.condition}
    						</when>
    						<when test="criterion.singleValue">
    							and ACTIVITYS.${criterion.condition} #{criterion.value}
    						</when>
    						<when test="criterion.betweenValue">
    							and ACTIVITYS.${criterion.condition} #{criterion.value} and
    							#{criterion.secondValue}
    						</when>
    						<when test="criterion.listValue">
    							and ACTIVITYS.${criterion.condition}
    							<foreach collection="criterion.value" item="listItem"
    								open="(" close=")" separator=",">
    								#{listItem}
    							</foreach>
    						</when>
    					</choose>
    				</foreach>
    			</trim>
    		</if>
    	</foreach>
    	<if test="orderByClause != null">
    		order by ${orderByClause}
    	</if>
    	<if test="start >= 0 ">
    		limit ${start}
    	</if>
    	<if test="end >= 0 ">
    		,${end}
    	</if>
    </select>

**日期操作，choose语法，分页做法**

/**
     * selectCommonShareList:查询分享列表，包含分享人的信息 <br/>
     * @author dhh
     * @param userId		查询指定用户的分享
     * @param daysBeforeToday   默认查询多少天之内的分享
     * @param lastShareId		页面加载时，查询的上一条分享的id
     * @param loadSize		每次加载的分享数量
     * @return
     * @since JDK 1.6
     */
    public abstract List<Share> selectCommonShareList(
    		@Param("userId") Integer userId,
    		@Param("daysBeforeToday") Integer daysBeforeToday,
    		@Param("lastShareId") Integer lastShareId,
    		@Param("loadSize") Integer loadSize);
    
    
    <resultMap id="SharePublisherMap" type="com.clou.douliu.server.bean.mybatis.Share" 	extends="AliasResultMap">
    	<association property="publisher"
    		resultMap="com.clou.douliu.server.mybatis.mapper.UserMapper.AliasResultMap">
    </association>
    </resultMap>
     
     
    
    <select id="selectCommonShareList" parameterType="map" resultMap="SharePublisherMap">
    	SELECT
    	<include refid="Alias_Column_List" />,
    	<include refid="com.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />
    	FROM
    		USER USER,
    		ACTIVITYS ACTIVITYS
    	WHERE
    		USER.ID=ACTIVITYS.USER_ID
    		AND ACTIVITYS.TYPE = 5
    		AND USER.TYPE > 0
    		AND ACTIVITYS.VALID > -1
    		<if test="userId != null and userId >0">
    			AND USER.ID = #{userId,jdbcType=INTEGER}
    		</if>
    		AND ACTIVITYS.TIMETAG > DATE_ADD(CURDATE(),INTERVAL -
    		<choose>
    			<when test="daysBeforeToday != null and daysBeforeToday > 0">
    				#{daysBeforeToday}
    			</when>
    			<otherwise>
    				7
    			</otherwise>
    		</choose>
    		DAY)
    		<if test="lastShareId != null">
    			AND ACTIVITYS.ID < #{lastShareId}
    		</if>
    		ORDER BY
    			ACTIVITYS.ID DESC
    		LIMIT 0 ,
    		<choose>
    			<when test="loadSize != null and loadSize > 0">
    				#{loadSize}
    			</when>
    			<otherwise>
    				10
    			</otherwise>
    		</choose>
    </select>

**☞分页查询一个注意点：如果想分页查出的数据不出现重复（app有新增），最后记录上一次查询的最大值，然后在分页查询的时候 id>最大值 limit (也可以传最小值，不过客户端要每次维护这个值，但是如果存最大值，就第一次维护就行了）**

**Collection附加一种写法：**

![Center][]

![Center 1][]

不推荐使用，只是语法是这样，之前做举报功能列表，而一个聊天举报可能对应很多条聊天信息，用这种方法查询，结果很慢，后来先查出列表，取出ID列表，然后in(ids) 查询聊天记录，然后for循坏加到对应的id,再set进去，效率反而快一些。

[https_blog.csdn.net_javadhh_article_details_52422242]: https://blog.csdn.net/javadhh/article/details/52422242
[Center]: /images/20211214/1336cbe68a724c5cad00f5862e740c16.png
[Center 1]: /images/20211214/5f6ee0b288654b7b862073b7ad5d99fd.png