入门
简单使用:
########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的转义字符,如 双引号 "" 使用"" 代替
* concat函数:mysql拼接字符串的函数
条件查询
@Select("<script>"
+ "select t_id, t_name, t_age "
+ "from sys_user "
+ "<where> "
+ " <if test='id != null and id != ""'> "
+ " and t_id = #{id} "
+ " </if> "
+ " <if test='name != null and name != ""'> "
+ " 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 != ""'> "
+ " and t_id = #{id} "
+ " </when> "
+ " <otherwise test='name != null and name != ""'> "
+ " 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..
}