Loading

MyBatis注解开发

@Insert注解

  • 注解属性value:写入SQL语句

@Options注解

  • 实现添加新数据的主键封装
  • 注解属性
    • useGeneratedKeys：使用生成的主键，配置为true
    • keyProperty：主键封装的pojo对象属性

@SelectKey注解

  • 实现添加新数据的主键封装
  • 注解属性
    • statement：要执行的SQL语句
    • before：在添加SQL语句之前还是之后进行，配置为false
    • keyProperty：主键封装的pojo对象属性

注解实现添加数据

  • UserMapper接口

    //新增用户数据
@Insert("insert into user(username,sex,birthday,address)values(#{username},#{sex},#{birthday},#{address})")
void saveUser(User user);

  • UserMapper测试

    /**
 * 注解开发
 * 添加用户数据
 */
@Test
public void testSaveUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    User user = new User();
    user.setUsername("孙权");
    user.setSex("1");
    user.setBirthday(new Date());
    user.setAddress("东吴");
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    userMapper.saveUser(user);
    sqlSession.commit();
    System.out.println(user);
    sqlSession.close();
}

@Update注解

  • 注解属性value:写入SQL语句

注解实现更新数据

  • UserMapper接口

    //跟新用户
@Update("update user set username=#{username},sex=#{sex},birthday=#{birthday},address=#{address} where id=#{id}")
void updateUser(User user);

  • UserMapper测试

    /**
 * 注解开发
 * 更新用户数据
 */
@Test
public void testUpdateUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    User user = new User();
    user.setId(21);
    user.setUsername("孙策");
    user.setSex("2");
    user.setBirthday(new Date());
    user.setAddress("江东");
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    userMapper.updateUser(user);
    sqlSession.commit();
    sqlSession.close();
}

@Delete注解

  • 注解属性value:写入SQL语句

注解实现删除数据

  • UserMapper接口

    //删除用户
@Delete("delete from user where id = #{id}")
void deleteUser(int id);

  • UserMapper测试

    /**
 * 注解开发
 * 更新用户数据
 */
@Test
public void testDeleteUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    userMapper.deleteUser(21);
    sqlSession.commit();
    sqlSession.close();
}

@Select注解

  • 注解属性value:写入SQL语句

注解实现主键查询用户数据

  • UserMapper接口

    //主键查询用户
@Select("select id,username,sex,birthday,address from user where id = #{id}")
User queryUserById(int id);

  • UserMapper测试

    /**
 * 注解开发
 * 主键查询用户
 */
@Test
public void testQueryUserById(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.queryUserById(1);
    System.out.println(user);
    sqlSession.close();
}

注解实现查询全部用户数据

  • UserMapper接口

    //查询全部用户
@Select("select id,username,sex,birthday,address from user")
List<User> queryUserByList();

  • UserMapper测试

    /**
 * 注解开发
 * 查询全部用户
 */
@Test
public void testQueryUserByList(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userMapper.queryUserByList();
    if (userList != null && userList.size() > 0){
        for (User user : userList){
            System.out.println(user);
        }
    }
    sqlSession.close();
}

动态SQL语句之where和if

  • UserMapper接口

    //多条件查询用户
@Select("<script>select id,username,sex,birthday,address from user" +
        "<where>" +
        "<if test = \"sex != null and sex != ''\">" +
        " and sex = #{sex}" +
        "</if>" +
        "<if test = \"username != null and username != ''\">" +
        " and username like #{username}" +
        "</if>" +
        "</where></script>")
List<User> queryUserByWhere(User user);

  • UserMapper测试

     /**
  * 注解开发
  * 多条件查询用户信息
  */
 @Test
public void testQueryUserByWhere(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    user.setSex("2");
    user.setUsername("%王%");
    List<User> userList = userMapper.queryUserByWhere(user);
    if(userList != null && userList.size() > 0){
        for(User user1 : userList){
            System.out.println(user1);
        }
    }
    sqlSession.close();
}

动态SQL语句之foreach

  • UserMapper接口

    @Select("<script>select id,username,sex,birthday,address from user" +
        "<foreach collection = \"list\" open = \"where id in(\" close = \")\" separator = \",\" item = \"item\">" +
        "#{item}" +
        "</foreach>" +
        "</script>")
List<User> queryUserByListIds(List<Integer> ids);

  • UserMapper测试

    /**
  * 注解开发
  * 传入List集合，查询用户数据
  */
@Test
public void testQueryUserByListIds(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    List<User> userList = userMapper.queryUserByListIds(ids);
    if(userList != null && userList.size() > 0){
        for(User user : userList){
            System.out.println(user);
        }
    }
    sqlSession.close();
}

一对一的注解开发

@Results注解

配置手动映射，取代resultMap标签

@Result注解

配置手动映射，取代result标签

  • OrdersMapper接口

    //根据订单查询用户，一对一查询
@Select(" SELECT o.id,o.user_id,o.number,o.createtime,o.note FROM orders o")
@Results({
        //配置主键映射，id默认false,不是主键
        @Result(id = true,column = "id",property = "id"),
        //配置其他映射关系
        @Result(column = "user_id",property = "userId"),
        @Result(column = "number",property = "number"),
        @Result(column = "createtime",property = "createtime"),
        @Result(column = "note",property = "note"),
        /*
             配置关联查询用户表
             property查询的pojo对象哪个属性做为条件查询
               这个属性还是个pojo对象
             column查询条件的pojo对象的属性
             @One注解配置一对一的另一个查询语句
               此语句需要对应的接口方法出现
         */
        @Result(column = "user_id",property = "user",javaType = User.class,
                one = @One(select = "com.itheima.mapper.UserMapper.queryUserByUserId",fetchType = FetchType.LAZY))
})
List<Orders> queryOrdersUser();

  • UserMapper接口

    @Select("select id,username,sex,birthday,address from user where id=#{user_Id}")
User queryUserByUserId(Integer id);

  • OrdersMapp接口测试

    /**
 *  注解开发
 *  一对一延迟加载测试
 */
@Test
public void testQueryOrdersUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> ordersList = ordersMapper.queryOrdersUser();
    if(ordersList != null && ordersList.size() > 0){
        for(Orders orders : ordersList){
            System.out.println(orders);
            System.out.println(orders.getUser());
        }
    }
    sqlSession.close();
}

一对多注解开发

  • UserMapper接口

    //用户查询订单，一对多
@Select("select id,username,sex,birthday,address from user")
@Results({
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "username",property ="username" ),
        @Result(column = "sex",property ="sex" ),
        @Result(column = "birthday",property ="birthday" ),
        @Result(column = "address",property ="address" ),
        @Result(column = "id",property = "ordersList",javaType = List.class,
        many = @Many(select = "com.itheima.mapper.OrdersMapper.queryOrdersByUserId",fetchType = FetchType.LAZY))
})
List<User> queryUserOrders();

  • OrdersMapper接口

    //用户查询订单，一对多
@Select("select number,createtime from orders where user_id = #{user_id}" )
List<Orders> queryOrdersByUserId(Integer user_id);

  • UserMapper接口测试

    /**
  * 注解开发
  * 查询用户下的订单，一对多
  */
@Test
public void testQueryUserOrders(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userMapper.queryUserOrders();
    if(userList != null && userList.size() > 0){
        for(User user : userList){
            System.out.println(user);
            System.out.println(user.getOrdersList());
        }
    }
    sqlSession.close();
}
posted @ 2021-03-30 09:49  克豪  阅读(55)  评论(0)    收藏  举报