Mybatis 学习笔记 -注解 SQL

灰太狼 2022-10-27 15:54 138阅读 0赞

### Mybatis 学习笔记 -注解 SQL ###

*  注解
 *  按条件插入
 *  查询
 *  子查询
 *  参考资料

# 注解 #

*  `@SelectKey`：只有在`@Insert`、`@InsertProvider`、`@Update`、`@UpdateProvider`上使用才生效。用于在插入、生成前创建一个值。

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>statement</td> 
   <td align="left">SQL 语句</td> 
  </tr> 
  <tr> 
   <td>before</td> 
   <td align="left">是否在插入语句<code>前执行</code></td> 
  </tr> 
  <tr> 
   <td>keyProperty</td> 
   <td align="left">返回结果将赋给对象中的哪个属性</td> 
  </tr> 
  <tr> 
   <td>resultType</td> 
   <td align="left">返回结果的类型</td> 
  </tr> 
 </tbody> 
</table>

# 按条件插入 #

通过`标题+时间`判断。如果存在就更新，不存在就插入。

@SelectKey(statement="SELECT COUNT(*) FROM news WHERE title = #{title} AND pub_time = #{time}" , before=true, resultType=int.class, keyProperty="count")
    
    @Insert("<script>" +
    "<if test=\"count > 0\">" +
    " UPDATE news SET title=#{title}, content=#{content}, time=#{time} WHERE title=#{title} AND time=#{time}" +
    "</if>" +
    "<if test=\"count==0\">" +
    " INSERT INTO news (title, content, time) VALUES (#{title}, #{content}, #{time})" +
    "</if>" +
    "</script>")

# 查询 #

package com.covid19.api.mapper;
    
    import com.covid19.api.entity.XxData;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Select;
    
    @Mapper
    public interface XxDataMapper { 
    
        /** * 查最新的一条数据 * @return */
        @Select("SELECT * FROM xx_data WHERE is_delete = 0 ORDER BY create_time DESC LIMIT 1")
        XxData findLatest();
    
    }

# 子查询 #

先查 `hero`  
使用结果中的`id`关联条用 `com.mybatis.demo.mapper.PoemMapper.findByHeroId`

@Select("SELECT * FROM `hero` WHERE id = #{id}")
     @Results({ 
             @Result(id = true, property = "id", column = "id"),
             @Result(property = "poems", column = "id", javaType = List.class,
             many = @Many(select = "com.mybatis.demo.mapper.PoemMapper.findByHeroId"))
     })
     Hero queryById(Long id);

关联的子查询：`com.mybatis.demo.mapper.PoemMapper.findByHeroId`  
注意

*  每行末尾都加了空格，否者拼接后语法就错了。
 *  这里`hero`与`poem`有个中间表`hero_poem`。

@Select("SELECT poem.* " +
                "FROM hero_poem " +
                "LEFT JOIN poem ON hero_poem.poem = poem.id " +
                "WHERE hero_poem.hero = #{heroId}")
        List<Poem> findByHeroId(Long heroId);

# 参考资料 #

[mybatis-3：映射器注解][mybatis-3]

[mybatis-3]: https://mybatis.org/mybatis-3/zh/java-api.html