mybatis 使用注解开发

入门

简单使用:

########Results做字段映射
@Select("select t_id, t_age, t_name  "
            + "from sys_user             "
            + "where t_id = #{id}        ")
    @Results(id="userResults", value={
            @Result(property="id",   column="t_id"),
            @Result(property="age",  column="t_age"),
            @Result(property="name", column="t_name"),
    })
　　 User selectUserById(@Param("id") String id);

###########@ResultMap调用
 @Select("select t_id, t_age, t_name  "
            + "from sys_user             "
            + "where t_name = #{name}        ")
    @ResultMap("userResults")
　　 User selectUserByName(@Param("name") String name);

动态SQL

 * if 对内容进行判断
 * 在注解方法中，若要使用MyBatis的动态SQL，需要编写在<script></script>标签内
 * 在 <script></script>内使用特殊符号，则使用java的转义字符，如  双引号 "" 使用&quot;&quot; 代替
 * concat函数：mysql拼接字符串的函数

条件查询

 @Select("<script>"
            + "select t_id, t_name, t_age                          "
            + "from sys_user                                       "
            + "<where>                                             "
            + "  <if test='id != null and id != &quot;&quot;'>     "
            + "    and t_id = #{id}                                "
            + "  </if>                                             "
            + "  <if test='name != null and name != &quot;&quot;'> "
            + "    and t_name like CONCAT('%', #{name}, '%')       "
            + "  </if>                                             "
            + "</where>                                            "
            + "</script>                                           ")
    #####这里可以使用@resultMap调用
    @Results(id="userResults", value={
        @Result(property="id",   column="t_id"),
        @Result(property="name", column="t_name"),
        @Result(property="age",  column="t_age"),
    })
    List<User> selectUserWithIf(User user);

/**
     * choose when otherwise 类似Java的Switch，选择某一项
     * when...when...otherwise... == if... if...else... 
     */
    @Select("<script>"
            + "select t_id, t_name, t_age                                     "
            + "from sys_user                                                  "
            + "<where>                                                        "
            + "  <choose>                                                     "
            + "      <when test='id != null and id != &quot;&quot;'>          "
            + "            and t_id = #{id}                                   "
            + "      </when>                                                  "
            + "      <otherwise test='name != null and name != &quot;&quot;'> "
            + "            and t_name like CONCAT('%', #{name}, '%')          "
            + "      </otherwise>                                             "
            + "  </choose>                                                    "
            + "</where>                                                       "
            + "</script>                                                      ")
    @ResultMap("userResults")
    List<User> selectUserWithChoose(User user);

根据条件修改

/**
     * set 动态更新语句，类似<where>
     */
    @Update("<script>                                           "
            + "update sys_user                                  "
            + "<set>                                            "
            + "  <if test='name != null'> t_name=#{name}, </if> "
            + "  <if test='age != null'> t_age=#{age},    </if> "
            + "</set>                                           "
            + "where t_id = #{id}                               "
            + "</script>                                        ")
    int updateUserWithSet(User user);

批量更新

/**
     * foreach 遍历一个集合，常用于批量更新和条件语句中的 IN
     * foreach 批量更新
     */
    @Insert("<script>                                  "
            + "insert into sys_user                    "
            + "(t_id, t_name, t_age)                   "
            + "values                                  "
            + "<foreach collection='list' item='item'  "
            + " index='index' separator=','>           "
            + "(#{item.id}, #{item.name}, #{item.age}) "
            + "</foreach>                              "
            + "</script>                               ")
    int insertUserListWithForeach(List<User> list);

/**
     * foreach 条件语句中的 IN
     */
    @Select("<script>"
            + "select t_id, t_name, t_age                             "
            + "from sys_user                                          "
            + "where t_name in                                        "
            + "  <foreach collection='list' item='item' index='index' "
            + "    open='(' separator=',' close=')' >                 "
            + "    #{item}                                            "
            + "  </foreach>                                           "
            + "</script>                                              ")
    @ResultMap("userResults")
    List<User> selectUserByINName(List<String> list);
    
    /**
     * bind 创建一个变量，绑定到上下文中
     */
    @Select("<script>                                              "
            + "<bind name=\"lname\" value=\"'%' + name + '%'\"  /> "
            + "select t_id, t_name, t_age                          "
            + "from sys_user                                       "
            + "where t_name like #{lname}                          "
            + "</script>                                           ")
    @ResultMap("userResults")
    List<User> selectUserWithBind(@Param("name") String name);

对一 对多

```java
  /**
     * 对@Result的解释
     * property：       java bean 的成员变量
     * column：         对应查询的字段，也是传递到对应子查询的参数，传递多参数使用Map column = "{param1=SQL_COLUMN1,param2=SQL_COLUMN2}"
     * one=@One：       对一查询
     * many=@Many：     对多查询
     * select：         需要查询的方法，全称或当前接口的一个方法名
　　　* fetchType.EAGER: 急加载
     */
    @Select("select t_id, t_name, t_age "
            + "from sys_user            "
            + "where t_id = #{id}       ")
    @Results({
        @Result(property="id",    column="t_id"),
        @Result(property="name",  column="t_name"),
        @Result(property="age",   column="t_age"),
        @Result(property="login", column="t_id",
          one=@One(select="com.github.mybatisTest.dao.OneManySqlDao.selectLoginById", fetchType=FetchType.EAGER)),
        @Result(property="identityList", column="t_id",
          many=@Many(select="selectIdentityById", fetchType=FetchType.EAGER)),
    })
    User2 selectUser2(@Param("id") String id);
    
    /**
     * 对一 子查询
     */
    @Select("select t_username, t_password "
            + "from sys_login              "
            + "where t_id = #{id}          ")
    @Results({
        @Result(property="username", column="t_username"),
        @Result(property="password", column="t_password"),
    })
    Login selectLoginById(@Param("id") String id);
    
    /**
     * 对多 子查询
     */
    @Select("select t_id_type, t_id_no "
            + "from sys_identity              "
            + "where t_id = #{id}          ")
    @Results({
        @Result(property="idType", column="t_id_type"),
        @Result(property="idNo", column="t_id_no"),
    })
    List<Identity> selectIdentityById(@Param("id") String id);
复制代码
　　测试结果，可以看到只调用一个方法查询，相关对一查询和对多查询也会一并查询出来

复制代码
// 测试类代码
    @Test
    public void testSqlIf() {
        User2 user = dao.selectUser2("00000005");
        System.out.println(user);
        System.out.println(user.getLogin());
        System.out.println(user.getIdentityList());
    }    

// 查询结果
    User2 [id=00000005, name=name_00000005, age=5]
    Login [username=the_name, password=the_password]
    [Identity [idType=01, idNo=12345678], Identity [idType=02, idNo=987654321]]

MyBatis开启事务

 使用 @Transactional 注解开启事务
　　@Transactional标记在方法上，捕获异常就rollback，否则就commit。自动提交事务
/**
     * @Transactional 的参数
     * value                   |String                        | 可选的限定描述符，指定使用的事务管理器
     * propagation             |Enum: Propagation             | 可选的事务传播行为设置
     * isolation               |Enum: Isolation               | 可选的事务隔离级别设置
     * readOnly                |boolean                       | 读写或只读事务，默认读写
     * timeout                 |int (seconds)                 | 事务超时时间设置
     * rollbackFor             |Class<? extends Throwable>[]  | 导致事务回滚的异常类数组
     * rollbackForClassName    |String[]                      | 导致事务回滚的异常类名字数组
     * noRollbackFor           |Class<? extends Throwable>[]  | 不会导致事务回滚的异常类数组
     * noRollbackForClassName  |String[]                      | 不会导致事务回滚的异常类名字数组
     */
    @Transactional(timeout=4)
    public void testTransactional() {
            // dosomething..
    }