MyBatis 常用写法

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

https://blog.csdn.net/javadhh/article/details/52422242

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

foreach**用法：**

  1. /**
  2.  * 查询出用户最大的登录时间
  3.  * @param informedObjIds 用户id集合
  4.  * @return
  5.  */
  6. List<UserLastLoginBean> selectUserMaxLoginTimeByUserIds(
  7.         List<Integer> informedObjIds);
  9. <resultMap id="MaxLoginTimeMap" type="com.clou.douliu.server.bean.UserLastLoginBean">
  10.     <id column="userId" property="userId" jdbcType="INTEGER" />
  11.     <result column="maxLoginTime" property="maxLoginTime" jdbcType="TIMESTAMP" />
  12. </resultMap>
  15. <!-- 根据用户ID集合查询出用户最近登录时间 -->
  16. <select id="selectUserMaxLoginTimeByUserIds" parameterType="map"  resultMap="MaxLoginTimeMap">
  17.     SELECT
  18.         USER_ID as userId ,
  19.         MAX(LOGIN_TIME) as maxLoginTime
  20.     FROM
  21.         user_login_details
  22.     WHERE
  23.         USER_ID IN
  24.         <foreach collection="list" item="userId" index="index" open="("
  25.             close=")" separator=",">
  26.             #{userId}
  27.         </foreach>
  28.     group by USER_ID
  29.     ORDER BY USER_ID ASC
  30. </select>

得到insert后的自增长ID

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

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

  1. <!-- 选取分享列表,包含发布分享的人，以及该分享对应的图片列表. -->
  2. <select id="selectHotShareAlternativeListByUserId" parameterType="map"    resultMap="ShareDetailMap">
  3. SELECT  * FROM
  4.        (SELECT 
  5. <include refid="Alias_Column_List" />,
  6. <include refid="com.clou.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />,
  7. <include     refid="com.clou.douliu.server.mybatis.mapper.UserDetailsMapper.Alias_Column_List"/>
  8.         FROM
  9.         ACTIVITYS ACTIVITYS 
  10.       left join USER USER on (USER.ID=ACTIVITYS.USER_ID) 
  11.       left join user_details user_details ON (user_details.USER_ID = USER.ID)
  12.       WHERE
  13.         USER.TYPE > 0 
  14.         AND USER.ID = #{userId,jdbcType=INTEGER}
  15. AND ACTIVITYS.valid = 1
  16. AND ACTIVITYS.timetag >= #{startDate,jdbcType=TIMESTAMP}
  17.         AND ACTIVITYS.timetag <= date_add(#{endDate,jdbcType=TIMESTAMP},interval 24 hour)
  18.       ORDER BY date(ACTIVITYS.timetag) desc
  19.       LIMIT #{start,jdbcType=INTEGER},#{loadSize,jdbcType=INTEGER})  TEMP
  20. WHERE
  21. NOT EXISTS(SELECT 1 FROM HOTS HOTS WHERE HOTS.ACT_ID = TEMP.ACTIVITYS_ID)
  22. </select>

list参数传递

  1. /**
  2.     * 通过id集合删除记录
  3.     * @param delPhotoIdList id集合
  4.     * @return
  5. */
  6. public abstract int removeByIdList(List<Integer> delPhotoIdList);
  9. <!-- 通过id集合删除记录 -->
  10. <delete id="removeByIdList">
  11.       delete from album_detail where ID in
  12.        <foreach collection="list" item="photoId" index="index"
  13.            open="(" close=")" separator=",">
  14.            #{photoId}
  15.       </foreach>
  16. </delete>

Map参数传递(find_in_set用法）

  1. /**
  2.  * 根据群id集合获取群的全部信息，按照list里面的序号排序
  3.  * @param paramMap 参数map <br/>
  4.  *  groupIdList  --- List<Integer>   <br/>
  5.  *  sortStr ---- String   排序字符串,格式：'1932,2342,3242' 也就是集合中的id,已逗号隔开
  6.  */
  7. List<Groups> selectGroupListBySort(Map<String, Object> paramMap);
  10. <!-- 根据群id集合获取群的全部信息，按照list里面的序号排序 -->
  11. <select id="selectGroupListBySort" resultMap="BaseResultMap" parameterType="java.util.Map" >
  12.   select
  13.   <include refid="Base_Column_List" />
  14.   from groups
  15.   where id in
  16.    <foreach collection="groupIdList" item="item" index="index" open="(" close=")" separator=",">
  17.     #{item}
  18.  </foreach>
  19.  and status = 1
  20.  order by find_in_set(id, #{sortStr,jdbcType=VARCHAR}) asc;
  21. </select>
  24. Bussiness方法：从redis中取出热门群组列表，按照指定顺序从数据库中查询详细信息
  25. /**
  26.  * 获取推荐群列表
  27.  * @return
  28.  */
  29. public List<Groups> selectTopGroups() {
  30.     CacheManager cm=CacheManager.getInstance();
  31.     Set<String> s=cm.getRecommandGroups();
  33.     logger.debug("推荐列表" + s);
  35.     List<Integer> groupIds = null; 
  36.     if(s!=null && s.size() > 0){
  37.         groupIds = new ArrayList<Integer>();
  38.         for(String g : s){
  39.             try {
  40.                 Integer gId = Integer.parseInt(g);
  41.                 groupIds.add(gId);
  42.             } catch (Exception e) {
  43.             }
  44.         }
  45.     }
  46.     if(groupIds != null && groupIds.size() > 0){
  47.             Map<String, Object> paramMap = new HashMap<String, Object>();
  48.             StringBuilder sortStr = new StringBuilder();
  49.             for(Integer id : groupIds){
  50.                 sortStr.append(id).append(",");
  51.             }
  52.             sortStr.delete(sortStr.lastIndexOf(","), sortStr.length());
  54.             paramMap.put("groupIdList", groupIds);
  55.             paramMap.put("sortStr", sortStr.toString());
  57.         return this.mapperFactory.groupsMapper.selectGroupListBySort(paramMap);
  58.     }
  59.     return null;
  60. }

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

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

  1. <sql id="Example_Where_Clause" >
  2.   <where >
  3.     <foreach collection="oredCriteria" item="criteria" separator="or" >
  4.       <if test="criteria.valid" >
  5.         <trim prefix="(" suffix=")" prefixOverrides="and" >
  6.           <foreach collection="criteria.criteria" item="criterion" >
  7.             <choose >
  8.               <when test="criterion.noValue" >
  9.                 and ${criterion.condition}
  10.               </when>
  11.               <when test="criterion.singleValue" >
  12.                 and ${criterion.condition} #{criterion.value}
  13.               </when>
  14.               <when test="criterion.betweenValue" >
  15.                 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
  16.               </when>
  17.               <when test="criterion.listValue" >
  18.                 and ${criterion.condition}
  19.                 <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
  20.                   #{listItem}
  21.                 </foreach>
  22.               </when>
  23.             </choose>
  24.           </foreach>
  25.         </trim>
  26.       </if>
  27.     </foreach>
  28.   </where>
  29. </sql>
  31. 例子：
  32. List<PushSchema> selectByExample(PushSchemaCriteria example) throws RuntimeException;
  34. <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.clou.douliu.server.bean.mybatis.PushSchemaCriteria" >
  35.   select
  36.   <if test="distinct" >
  37.     distinct
  38.   </if>
  39.   <include refid="Base_Column_List" />
  40.   from push_schema
  41.   <if test="_parameter != null" >
  42.     <include refid="Example_Where_Clause" />
  43.   </if>
  44.   <if test="orderByClause != null" >
  45.     order by ${orderByClause}
  46.   </if>
  47.   <if test="start >= 0 " >
  48.     limit ${start}
  49.   </if>
  50.   <if test="count >= 0 " >
  51.     ,${count}
  52.   </if>
  53. </select>

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

  1. <sql id="Alias_Update_By_Example_Where_Clause" >
  2.   <where >
  3.     <foreach collection="example.oredCriteria" item="criteria" separator="or" >
  4.       <if test="criteria.valid" >
  5.         <trim prefix="(" suffix=")" prefixOverrides="and" >
  6.           <foreach collection="criteria.criteria" item="criterion" >
  7.             <choose >
  8.               <when test="criterion.noValue" >
  9.                 and PUSH_SCHEMA.${criterion.condition}
  10.               </when>
  11.               <when test="criterion.singleValue" >
  12.                 and PUSH_SCHEMA.${criterion.condition} #{criterion.value}
  13.               </when>
  14.               <when test="criterion.betweenValue" >
  15.                 and PUSH_SCHEMA.${criterion.condition} #{criterion.value} and #{criterion.secondValue}
  16.               </when>
  17.               <when test="criterion.listValue" >
  18.                 and PUSH_SCHEMA.${criterion.condition}
  19.                 <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
  20.                   #{listItem}
  21.                 </foreach>
  22.               </when>
  23.             </choose>
  24.           </foreach>
  25.         </trim>
  26.       </if>
  27.     </foreach>
  28.   </where>
  29. </sql>
  31. 例子：
  32. /**
  33.  * 查询方案列表（包含具体方案step的List）
  34.  * @param example
  35.  * @return
  36.  * @throws RuntimeException
  37.  */
  38. List<PushSchema> selectWithStepByExample(PushSchemaCriteria example) throws RuntimeException;
  40. <!-- 查询方案列表（关联查询具体方案） -->
  41. <select id="selectWithStepByExample" resultMap="SchemaWithStepMap" parameterType="com.clou.douliu.server.bean.mybatis.PushSchemaCriteria" >
  42.   select
  43.   <include refid="Alias_Column_List" />,
  44.   <include refid="com.clou.douliu.server.mybatis.mapper.PushStepMapper.Alias_Column_List" />
  45.   from push_schema PUSH_SCHEMA 
  46.       left join push_step PUSH_STEP on (PUSH_SCHEMA.ID=PUSH_STEP.SCHEMA_ID) 
  47.   <if test="_parameter != null" >
  48.     <include refid="Alias_Example_Where_Clause" />
  49.   </if>
  50.   <if test="orderByClause != null" >
  51.     order by ${orderByClause}
  52.   </if>
  53.   <if test="start >= 0 " >
  54.     limit ${start}
  55.   </if>
  56.   <if test="count >= 0 " >
  57.     ,${count}
  58.   </if>
  59. </select>

3、复杂条件筛选：

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

原理：

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

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


AND

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

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

Collection 和 association 的用法

  1. /**
  2.  * selectShareDetailList: 选取分享列表,包含发布分享的人，以及该分享对应的图片列表. <br/>
  3.  * 
  4.  * 
  5.  * @author Hongbin Yuan
  6.  * @param bean
  7.  * @return
  8.  * @since JDK 1.6
  9.  */
  10. public abstract List<Share> selectShareDetailList(ShareCriteria paramShareCriteria);
  12. <!-- 分享列表,包含发布分享的人，以及该分享对应的图片列表 -->
  13. <resultMap id="ShareDetailMap" type="com.clou.douliu.server.bean.mybatis.Share" extends="AliasResultMap">
  14.     <association property="publisher"
  15.         resultMap="com.clou.douliu.server.mybatis.mapper.UserMapper.UserDetailResultMap"></association>
  16.     <collection property="sharePictureList" resultMap="com.clou.douliu.server.mybatis.mapper.SharePictureMapper.AliasResultMap"></collection>
  17. </resultMap>
  19. <!-- 选取分享列表,包含发布分享的人，以及该分享对应的图片列表. -->
  20. <select id="selectShareDetailList" parameterType="com.clou.douliu.server.bean.mybatis.ShareCriteria" resultMap="ShareDetailMap">
  21.     SELECT
  22.     <include refid="Alias_Column_List" />,
  23.     <include
  24.         refid="com.clou.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />,
  25.     <include
  26.         refid="com.clou.douliu.server.mybatis.mapper.SharePictureMapper.Alias_Column_List" />
  27.     FROM
  28.         ACTIVITYS ACTIVITYS 
  29.         left join USER USER on (USER.ID=ACTIVITYS.USER_ID) 
  30.         left join ACT_PICTURE ACT_PICTURE on (ACTIVITYS.ID=ACT_PICTURE.ACT_ID)
  31.     WHERE 
  32.         USER.type > 0 
  33.         <foreach collection="oredCriteria" item="criteria" index="index" separator="or">
  34.             <if test="criteria.valid" >
  35.             <if test="index == 0">
  36.                 AND
  37.             </if>
  38.             <trim prefix="(" suffix=")" prefixOverrides="and">
  39.                 <foreach collection="criteria.criteria" item="criterion">
  40.                     <choose>
  41.                         <when test="criterion.noValue">
  42.                             and ACTIVITYS.${criterion.condition}
  43.                         </when>
  44.                         <when test="criterion.singleValue">
  45.                             and ACTIVITYS.${criterion.condition} #{criterion.value}
  46.                         </when>
  47.                         <when test="criterion.betweenValue">
  48.                             and ACTIVITYS.${criterion.condition} #{criterion.value} and
  49.                             #{criterion.secondValue}
  50.                         </when>
  51.                         <when test="criterion.listValue">
  52.                             and ACTIVITYS.${criterion.condition}
  53.                             <foreach collection="criterion.value" item="listItem"
  54.                                 open="(" close=")" separator=",">
  55.                                 #{listItem}
  56.                             </foreach>
  57.                         </when>
  58.                     </choose>
  59.                 </foreach>
  60.             </trim>
  61.         </if>
  62.     </foreach>
  63.     <if test="orderByClause != null">
  64.         order by ${orderByClause}
  65.     </if>
  66.     <if test="start >= 0 ">
  67.         limit ${start}
  68.     </if>
  69.     <if test="end >= 0 ">
  70.         ,${end}
  71.     </if>
  72. </select>

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

  1. /**
  2.  * selectCommonShareList:查询分享列表，包含分享人的信息 <br/>
  3.  * @author dhh
  4.  * @param userId        查询指定用户的分享
  5.  * @param daysBeforeToday   默认查询多少天之内的分享
  6.  * @param lastShareId        页面加载时，查询的上一条分享的id
  7.  * @param loadSize        每次加载的分享数量
  8.  * @return
  9.  * @since JDK 1.6
  10.  */
  11. public abstract List<Share> selectCommonShareList(
  12.         @Param("userId") Integer userId,
  13.         @Param("daysBeforeToday") Integer daysBeforeToday,
  14.         @Param("lastShareId") Integer lastShareId,
  15.         @Param("loadSize") Integer loadSize);
  17. <!-- 查询普通分享 -->
  18. <resultMap id="SharePublisherMap" type="com.clou.douliu.server.bean.mybatis.Share"     extends="AliasResultMap">
  19.     <association property="publisher"
  20.         resultMap="com.clou.douliu.server.mybatis.mapper.UserMapper.AliasResultMap">
  21. </association>
  22. </resultMap>
  25. <!-- 查询普通分享 -->
  26. <select id="selectCommonShareList" parameterType="map" resultMap="SharePublisherMap">
  27.     SELECT
  28.     <include refid="Alias_Column_List" />,
  29.     <include refid="com.douliu.server.mybatis.mapper.UserMapper.Alias_Column_List" />
  30.     FROM
  31.         USER USER,
  32.         ACTIVITYS ACTIVITYS
  33.     WHERE
  34.         USER.ID=ACTIVITYS.USER_ID
  35.         AND ACTIVITYS.TYPE = 5
  36.         AND USER.TYPE > 0
  37.         AND ACTIVITYS.VALID > -1
  38.         <if test="userId != null and userId >0">
  39.             AND USER.ID = #{userId,jdbcType=INTEGER}
  40.         </if>
  41.         AND ACTIVITYS.TIMETAG > DATE_ADD(CURDATE(),INTERVAL -
  42.         <choose>
  43.             <when test="daysBeforeToday != null and daysBeforeToday > 0">
  44.                 #{daysBeforeToday}
  45.             </when>
  46.             <otherwise>
  47.                 7
  48.             </otherwise>
  49.         </choose>
  50.         DAY)
  51.         <if test="lastShareId != null">
  52.             AND ACTIVITYS.ID < #{lastShareId}
  53.         </if>
  54.         ORDER BY
  55.             ACTIVITYS.ID DESC
  56.         LIMIT 0 ,
  57.         <choose>
  58.             <when test="loadSize != null and loadSize > 0">
  59.                 #{loadSize}
  60.             </when>
  61.             <otherwise>
  62.                 10
  63.             </otherwise>
  64.         </choose>
  65. </select>

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

Collection附加一种写法：

Center

Center 1

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

发表评论

表情：
评论列表 （有 0 条评论，515人围观）

还没有评论，来说两句吧...

相关阅读

    相关 jsp分页的常用写法

    这里运用mvc格式去写分页，期间会用到jstl标签，c3p0连接池，jdbc数据库连接，tomcat服务器，以及可以先把包导入到项目中，具体的导包过程可以百度。 ![在这里

    短命女短命女/ 0 赞/ 234 阅读