mybatis IDEA——注解方式将sql语句写到接口中

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();
        }
    }