使用mybatis的一些总结

前言

这段时间项目中需要写一些api，我负责其中的几个，也是第一次真正意义上的使用mybatis，所以我认为有必要作一个总结来反映我学习的成果。

---

首先我们应该都知道，mybatis的sql语句要么是写在xml文件中，要么直接使用@select注解在mapper类中写。但我个人偏向于写在xml文件中，因为有时候sql语句比较长、比较复杂，直接用注解的话可能一眼看过去并不知道是实现了什么功能，相反xml文件中使用特有的标签能帮助我们理解语句完成的功能。

1. 动态sql
mybatis写动态sql真的非常非常方便简洁了，好用到想为它疯狂打call！！！
例如有三个可选的条件，那么sql语句可以这么写：

SELECT
    A.authorName,A.country,B.bookName
    FROM author AS A
    LEFT OUTER JOIN book AS B ON (A.author_id = B.author_id AND A.book_id = B.book_id)
    <where>
      <if test="authorName!=null"></if>
      A.author_name = #{authorName}
      <if test="bookName!=null">
        AND B.bookName = #{bookName}
      </if>
      <if test="publicTime!=null">
        AND B.public_time = #{publicTime}
      </if>
    </where>
    GROUP BY A.authorName

在这个例子中，需要使用到几个知识点：
a.表与表的连接
b.动态条件判断语句的使用
c.<if>元素中，如果包括变量，不需要写成#{varName}的形式，而是直接写它的变量名就可以了。（我就是在这个地方吃了亏的，调试很久才发现，希望看到的读者不要踩这个雷。）

2. 多张表连接的写法

SELECT A.id,A.app_id,A.app_name,B.menu ,C.author_name
  FROM user_collection_app AS A
  LEFT OUTER JOIN app AS B ON A.app_id = B.app_id
  LEFT OUTER JOIN auth_user AS C ON B.author_id = C.author_id
  WHERE A.user_id = #{userId} GROUP BY A.app_id

3. 可以定义一个公共的接口实现基本的增删改查
如果经常需要使用基本的增删改查方法，为了减少每次书写语句的工作量，可以定义一个通用接口，让其他的接口去继承它，那么每次要使用的时候，就能直接去调用了，这可以节约不少时间跟精力。
例如：

public interface MyService<T> {
    void save(T model);//持久化
    void save(List<T> models);//批量持久化
    void deleteById(Integer id);//通过主鍵刪除
    void deleteByIds(String ids);//批量刪除 eg：ids -> “1,2,3,4”
    void update(T model);//更新
    T findById(Integer id);//通过ID查找
    T findById(Long id);//通过ID查找
    List<T> findBy(T record);
    T findBy(String fieldName, Object value) throws TooManyResultsException; //通过Model中某个成员变量名称（非数据表中column的名称）查找,value需符合unique约束
    List<T> findByIds(String ids);//通过多个ID查找//eg：ids -> “1,2,3,4”
    List<T> findByCondition(Example condition);//根据条件查找
    List<T> findAll();//获取所有
}

4. xml文件中特殊字符的写法
在sql语句中，难免会用到类似”<“、”>“这样的符号，但是xml文件却不能识别。所以要用的时候，必须进行转义。

    &lt;         < 

    &gt;         >  

    &amp;        & 

    &apos;       '

    &quot;       "

    也可以使用<![CDATA[ ]]>符号进行说明，格式为<![CDATA[ sql语句 ]]>  

参考博客：mybatis的一些特殊符号标识（大于，小于，等于，不等于）

5. 在数据库筛选datetime类型的字段
除了select* fromtable where datetime between datetime1 and datetime2;这种写法以外，还可以写成：

select * from table where unix_timestamp(datetime) > unix_timestamp(datetime1) and unix_timestamp(datetime) < unix_
timestamp(datetime2);

这种形式，unix_timestamp函数是将字符型的时间，转成unix时间戳。因为不转换的话虽然结果不报错，但是并不准确。

6. 关于多表的连接可能会遇到的小问题
两张表连接后（不管是左连接还是交叉连接），字段为null的属性，会被直接略掉，为了避免这种情况的发生，有两种方法：

a. 在sql语句中为可能出现null的属性添加ifnull函数，例如：

`select ifnull(status,0) AS status from table1 left join table2;`

当status为null时，自动设为默认值0，但是必须要为它设置一个别名，否则有可能识别失败。

当然如果设计的两张表逻辑上比较简单，字段并不是很多的时候，可以使用上面的方法。
但如果涉及两张表以上的连接，并且字段较多的时候，明显使用ifnull()函数就不太合适了，
这个时候我会更喜欢使用下面这种方式。

b. 用mybatis查询返回的类型与entity联系起来，这个entity应在xml文件中作好映射

示例代码：

<!--xml文件-->
<resultMap id="DevelopersResultMap" type="com.acloudapp.entity.developers.Developer">
    <id column="id" jdbcType="INTEGER" property="id"></id>
    <result column="user_id" jdbcType="VARCHAR" property="developerId" />
    <result column="real_name" jdbcType="VARCHAR" property="developerName" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName"/>
    <result column="head_portrait_img" jdbcType="VARCHAR" property="headPortrait"/>
    <result column="school_name" jdbcType="VARCHAR" property="school"/>
    <result column="mobile" jdbcType="VARCHAR" property="phoneNumber"/>
    <result column="create_time" jdbcType="TIMESTAMP" property="time" />
    <result column="type" jdbcType="VARCHAR" property="type" />
    <result column="province" jdbcType="VARCHAR" property="province" />
    <result column="city" jdbcType="VARCHAR" property="city" />
  </resultMap>

//Developer实体
public class Developer {
    private String developerId;
    private String developerName;
    private String nickName;
    private String headPortrait;
    private String school;
    private String phoneNumber;
    private Date time;
    private String type;
    private String province;
    private String city;

    //省略getter和setter方法
} 

<!--sql语句-->
SELECT
    auth_user.user_id,
    auth_user.real_name,
    auth_user.create_time,
    auth_user.nick_name,
    auth_user.head_portrait_img,
    auth_user.mobile,
    auth_user.school_name,
    auth_user.type,
    base_districts.city AS city,
    base_districts1.province AS province,
    #{recommendationId} AS recommendationId,
    #{position} AS position,
    #{title} AS title
    FROM auth_user
    LEFT JOIN base_districts ON base_districts.adcode=auth_user.adcode
    WHERE auth_user.user_id in (
            SELECT developers_id
            FROM user_quality_developers
            WHERE user_quality_developers.recommendation_id = #{recommendationId}
    )
    ORDER BY auth_user.create_time DESC

类似这种sql语句，比较复杂，且字段比较多的，我们就需要用一个实体与其进行映射。其中有的字段，如recommendationId、position，由于它们并没有使用到表的字段，因此不需要写入映射。

7. 内连接和外连接

参考资料：MySQL多表查询之外键、表连接、子查询、索引

8. 一段逻辑很复杂的sql语句
为了写这个查询语句，简直是绞尽脑汁，茶不思饭不想，找了许多资料（甚至去翻以前数据库原理课的ppt），最后终于得到了相要的结果！！！喜大普奔啊！！！尽管知道逻辑复杂，肯定要耗费一些时间去查，只不过我已经尽量将语句精简，希望在效率上不要太拖后腿。
这个语句用到了：子查询、表连接、嵌套查询、case-when句式、动态sql（丧心病狂啊有没有？？？），难点是：我需要返回一个自定义的字段，该字段为布尔类型的，因此我要用case-when进行逻辑判断以确定返回的是1还是0；另外，在此基础上，我还需要使用该自定义字段作为外层查询的条件进行筛选。。。总之我有点语无伦次了。直接po代码- -。

 SELECT*
  FROM(
      SELECT
         A.operate_type AS appMenu,
         A.time AS time,
         A.operate_obj_name AS appName,
         B.icon,
         B.app_id AS appId,
         CASE
             when (SELECT COUNT(*) FROM comment
                    WHERE comment.user_id = #{userId} AND comment.app_id = A.operate_obj_id) > 0 then 1
             else 0 END AS isEvaluate
      FROM auth_user_log AS A
      LEFT OUTER JOIN app AS B ON A.operate_obj_id = B.app_id
      <where>
          <if test="userId!=null"></if>
              A.user_id = #{userId}
          <if test="startTime!=null">
              AND unix_timestamp(A.time) &gt; unix_timestamp(#{startTime})
          </if>
          <if test="endTime!=null">
              AND unix_timestamp(A.time) &lt; unix_timestamp(#{endTime})
          </if>
      </where>
      ORDER BY A.time DESC
  )C
    <where>
      <if test="isEvaluate!=null">
        isEvaluate = #{isEvaluate}
      </if>
    </where>

可以明显看到，isEvaluate字段在外层查询中被当作一个查询的条件了，另外case-when里的查询语句的功能是判断comment表中是否存在匹配的记录（存在说明已评价，不存在则说明未评价），还有另一种简单的写法：

SELECT 1 FROM comment 
              WHERE comment.user_id = '10089'  AND comment.app_id = A.operate_obj_id limit 1

它返回的要么就是1，要么就是0，十分易于判断。特别解释下limit 1，mysql在找到一条记录后就不会往下继续找了。性能提升很多。

参考博客：

• Mysql判断记录是否存在
• mysql 中case when 的用法
• Mysql判断记录是否存在

• mysql怎么查询条件以另一个表中条件进行查询

  写完这段sql语句，感觉收获良多，以前学的数据库查询，有模糊不清的，现在也感觉变清晰了。总之，多写、多练总是没错的。

  9. xml文件中#和$的区别
  #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。而$将传入的数据直接显示生成在sql中，可理解为字符串替换。

---

结语

使用mybatis俩仨月来，深刻体会到了它的轻便与魅力，如果还回去使用老旧的sql拼接，就真的太落后了。使用之余，进行了一番感叹，
希望能够将mybatis学的更透彻。