MyBatis 常用写法

关注

原创最新推荐文章于 2026-03-04 00:12:02 发布 1w 阅读 AI 写同款· GEO 优化 ›

mybatis学习(52):扩展结果的展现首先使用逆向工程创建相关类数据库表 author blog 新建一个项目，将逆向工程的生成的拷贝进来配置文件 log4j.properties ### \u914D\u7F6E\u6839 ### log4j.rootLogger = debug,console ,fileAppender,dailyRollingFile,ROLLING_FILE,MAIL... 阅读详情

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

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>

<!-- 根据用户ID集合查询出用户最近登录时间 -->
<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);

<!-- 通过id集合删除记录 -->
<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);

<!-- 根据群id集合获取群的全部信息，按照list里面的序号排序 -->
<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 ，不存在就直接结束

<if test="index == 0">
	AND
</if>

暂时没有研究传递两个参数（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附加一种写法：