侧边栏壁纸
  • 累计撰写 793 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

目 录CONTENT

文章目录

注解方式sql

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 170 阅读 / 1,340 字
温馨提示:
本文最后更新于 2022-07-23,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
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 = #{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);
0

评论区