MyBatis注解方式就是将SQL语句写到接口中，优点是对于需求比较简单的系统，效率很高，缺点是当SQL语句有变化时需要重新编译代码，一般情况下不建议使用注解方式。
@Select注解
@Select({
"select * from sys_user where id = #{id}"
})
SysUser queryByIdAnno(Long id);
测试
@Test
public void testQueryRoleByUser(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<SysRole> list = userMapper.queryRoleByUser(1L);
for (SysRole sysRole : list){
System.out.println(sysRole.getUser());
}
}finally {
sqlSession.close();
}
}
@Insert注解
不需要返回主键
@Insert({
"insert into sys_user(id,user_name,user_password,"
+ "user_email,create_time) values(#{id},"
+ "#{userName},#{userPassword},#{userEmail},"
+ "#{createTime,jdbcType=TIMESTAMP})"
})
int insertAnno(SysUser user);
返回自增主键，sql中少了id字段，注解多了@Options
@Insert({
"insert into sys_user(user_name,user_password,"
+ "user_email,create_time) values("
+ "#{userName},#{userPassword},#{userEmail},"
+ "#{createTime,jdbcType=TIMESTAMP})"
})
@Options(useGeneratedKeys=true,keyProperty="id")
int insertAnno2(SysUser user);
返回非自增主键
@Insert({
"insert into sys_user(user_name,user_password,"
+ "user_email,create_time) values("
+ "#{userName},#{userPassword},#{userEmail},"
+ "#{createTime,jdbcType=TIMESTAMP})"
})
@SelectKey(
statement="select LAST_INSERT_ID()",
keyProperty="id",
resultType=Long.class,
before=false
)
int insertAnno3(SysUser user);
测试方法
@Test
public void testInsertAnno(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = new SysUser();
sysUser.setId(2L);
sysUser.setUserName("张三");
sysUser.setUserPassword("hello");
sysUser.setUserEmail("hello@qq.com");
sysUser.setCreateTime(new Date());
int result = userMapper.insertAnno(sysUser);
//测试
Assert.assertEquals(1,result);
}finally{
sqlSession.close();
}
}
@Test
public void testInsertAnno2(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = new SysUser();
sysUser.setUserName("张三2");
sysUser.setUserPassword("hello");
sysUser.setUserEmail("hello@qq.com");
sysUser.setCreateTime(new Date());
int result = userMapper.insertAnno(sysUser);
//测试
Assert.assertEquals(1,result);
}finally{
sqlSession.close();
}
}
@Test
public void testInsertAnno3(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = new SysUser();
sysUser.setId(2L);
sysUser.setUserName("张三3");
sysUser.setUserPassword("hello");
sysUser.setUserEmail("hello@qq.com");
sysUser.setCreateTime(new Date());
int result = userMapper.insertAnno(sysUser);
//测试
Assert.assertEquals(1,result);
}finally{
sqlSession.close();
}
}
@Update注解
@Update({
" update sys_user " +
" set user_name = #{userName}," +
" user_password = #{userPassword}," +
" user_email = #{userEmail}," +
" create_time = #{createTime,jdbcType=TIMESTAMP}" +
" where id = #{id}"
})
int updateByIdAnno(SysUser user);
测试
@Test
public void testUpdateByIdAnno(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = userMapper.queryByIdAnno(1l);
//当前userName为admin
Assert.assertEquals("admin",sysUser.getUserName());
//修改用户名
sysUser.setUserName("张三");
//修改数据
int result = userMapper.updateByIdAnno(sysUser);
//只更新一条数据
Assert.assertEquals(1,result);
//根据Id查询修改后的数据
sysUser = userMapper.queryByIdAnno(1L);
//修改后的名字为管理员
Assert.assertEquals("张三",sysUser.getUserName());
}finally{
sqlSession.close();
}
}
@Delete注解
@Delete({
"delete from sys_user where id = #{id}"
})
int deleteByIdAnno(Long id);
测试
@Test
public void testDeleteByIdAnno(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = userMapper.queryByIdAnno(1l);
Assert.assertNotNull(sysUser);
Assert.assertEquals(1,userMapper.deleteByIdAnno(1l));
Assert.assertNull(userMapper.queryByIdAnno(1l));
}finally{
sqlSession.close();
}
}
复杂语句
/**
* 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> ")
@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 = # "
+ " </when> "
+ " <otherwise test='name != null and name != ""'> "
+ " and t_name like CONCAT('%', #, '%') "
+ " </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=#, </if> "
+ " <if test='age != null'> t_age=#, </if> "
+ "</set> "
+ "where t_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=','> "
+ "(#, #, #) "
+ "</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=')' > "
+ " # "
+ " </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 # "
+ "</script> ")
@ResultMap("userResults")
List<User> selectUserWithBind(@Param("name") String name);
评论区