mybatis学习3

mybatis注解方式：

1、@Select注解、@Update注解、@Delete注解、@Insert注解。

数据表结构可查看mybatis学习2。
以Role角色为例：

RoleMapper：

package cn.linst.mapper;


import cn.linst.model.SysRole;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface RoleMapper {

    // @Select注解
    @Select({"select id, role_name roleName, enabled, create_by createBy, create_time createTime from sys_role where id = #{id}"})
    SysRole selectById(Long id);


    @Results(id="roleResultMap", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "roleName", column = "role_name"),
            @Result(property = "enabled", column = "enabled"),
            @Result(property = "createBy", column = "create_by"),
            @Result(property = "createTime", column = "create_time")
    })
    @Select ("select id, role_name, enabled, create_by, create_time from sys_role where id = #{id}")
    SysRole selectById2(Long id);

    // 使用＠ResultMap 注解引用即可，注解的参数值就是上面代码中设置的 id 的值。 // Mybatis3.3.1及以上版本才支持id。
    // 当配合使用 XML 方式的时候，还可以是 XML <resultMap> 元素的 id 属性值。
   
    @ResultMap("roleResultMap")
    @Select("select * from sys_role")
    List<SysRole> selectAll();


    // @Insert 注解. 1.不需要返回主键
    @Insert({"insert into sys_role (id, role_name, enabled, create_by, create_time )",
            "values(#{id}, #{roleName}, #{enabled}, #{createBy},",
            "#{createTime, jdbcType=TIMESTAMP })"})
    int insert(SysRole sysRole);

    // @Insert 注解. 2.返回自增主键
    @Insert({"insert into sys_role (role_name, enabled, create_by, create_time)",
            "values(#{roleName}, #{enabled}, #{createBy},",
            "#{createTime, jdbcType=TIMESTAMP })"})
    @Options(useGeneratedKeys =true, keyProperty ="id")
    int insert2(SysRole sysRole);


    //  @Insert 注解. 3.返回非自增主键
    // 配置属性基本上都是相同的，其中 before为false时功能等同于
    // order="AFTER"， before为true 时功能等同于order="BEFORE"
    @Insert({"insert into sys_role (role_name, enabled, create_by, create_time)",
            "values(#{roleName}, #{enabled}, #{createBy},",
            "#{createTime, jdbcType=TIMESTAMP })"})
    @SelectKey(statement ="SELECT LAST_INSERT_ID()",
               keyProperty="id",
               resultType = Long.class,
               before = false)
    int insert3(SysRole sysRole);


    // @Update注解
    @Update ({"update sys_role",
                "set role_name = #{roleName},",
                "enabled = #{enabled},",
                "create_by = #{createBy},",
                "create_time = #{createTime, jdbcType=TIMESTAMP }",
                "where id = #{id}"})
    int updateById(SysRole sysRole);


    // @Delete注解
    @Delete("delete from sys_role where id = #{id}")
    int deleteById(Long id);
}

role表结构：

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) DEFAULT NULL COMMENT '角色名',
  `enabled` int(11) DEFAULT NULL COMMENT '有效标志',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';

RoleMapperTest：

package cn.linst;

import cn.linst.mapper.RoleMapper;
import cn.linst.model.SysRole;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;
import java.util.List;

public class RoleMapperTest extends BaseMapperTest{

    @Test
    public void testSelectById() {
        //获取 sqlSession
        SqlSession sqlSession= getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            //调selectById 方法，查询 id =1 的角色
            SysRole role = roleMapper.selectById(1l);
            // role 不为空
            Assert.assertNotNull(role);
            //roleName ＝管理员
            Assert.assertEquals("管理员", role.getRoleName());
        } finally {
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void selectAll() {
        //获取 sqlSession
        SqlSession sqlSession= getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            //调selectById 方法，查询 id =1 的角色
            List<SysRole> sysRoleList = roleMapper.selectAll();
            // role 不为空
            Assert.assertNotNull(sysRoleList);
        } finally {
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void selectById2() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            //调selectById 方法，查询 id =1 的角色
            SysRole role = roleMapper.selectById2(1l);
            // role 不为空
            Assert.assertNotNull(role);
        } finally {
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void insert() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            SysRole sysRole = new SysRole();
            sysRole.setRoleName("测试角色");
            Assert.assertEquals(1, roleMapper.insert(sysRole));
        } finally {
            sqlSession.commit();
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void insert2() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            SysRole sysRole = new SysRole();
            sysRole.setRoleName("测试角色2");
            Assert.assertEquals(1, roleMapper.insert2(sysRole));
        } finally {
            sqlSession.commit();
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void insert3() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            SysRole sysRole = new SysRole();
            sysRole.setRoleName("测试角色3");
            Assert.assertEquals(1, roleMapper.insert3(sysRole));
        } finally {
            sqlSession.commit();
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void updateById() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            SysRole sysRole = new SysRole();
            sysRole.setId(3l);
            sysRole.setRoleName("测试角色。。。。");
            Assert.assertEquals(1, roleMapper.updateById(sysRole));
        } finally {
            sqlSession.commit();
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void deleteById() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 RoleMapper
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class) ;
            Assert.assertEquals(1, roleMapper.deleteById(4l));
        } finally {
            sqlSession.commit();
            //不要忘记关 sqlSession
            sqlSession.close();
        }
    }
}

2、Provider 注解

分别是@SelectProvider、＠InsertProvider、＠UpdateProvider 和＠DeleteProvider。实现查询、插入、更新、删除操作。

PrivilegeMapper：

package cn.linst.mapper;


import cn.linst.PrivilegeProvider;
import cn.linst.model.SysPrivilege;
import org.apache.ibatis.annotations.SelectProvider;

public interface PrivilegeMapper {

    @SelectProvider(type = PrivilegeProvider.class, method = "selectById")
    SysPrivilege selectById(Long id);
}

PrivilegeProvider：

package cn.linst;

import org.apache.ibatis.jdbc.SQL;

public class PrivilegeProvider {

    public String selectById(final Long id) {
        return new SQL() {
            {
                SELECT("id, privilege_name, privilege_url");
                FROM("sys_privilege");
                WHERE("id = #{id}");
            }
        }.toString();
    }
}

PrivilegeMapperTest：

package cn.linst;

import cn.linst.mapper.PrivilegeMapper;
import cn.linst.model.SysPrivilege;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;

public class PrivilegeMapperTest extends BaseMapperTest {

    @Test
    public void testSelectById() {
        //获取 sqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            //获取 PrivilegeMapper
            PrivilegeMapper privilegeMapper = sqlSession.getMapper(PrivilegeMapper.class) ;
            //调用 selectById 方法，查询 id = 1 的权限
            SysPrivilege privilege = privilegeMapper.selectById(1l);
            //privilege 不为空
            Assert.assertNotNull(privilege);
            //privilegeName ＝ 用户管理
            Assert.assertEquals("用户管理", privilege.getPrivilegeName()) ;
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}