Mybatis 注解开发

      Mybats全局配置跟xml开发一样，只不过注解开发是注解替代sql映射xml开发。

CRUD操作

UserMapper.java

public interface UserMapper {
    @Insert("insert userinfo(UserId,UserName,UserSex,UserAge,UserAddress) values(#{id},#{name},#{sex},#{age},#{address})")
    int addUser(User user);

    @Delete("delete from userinfo where userid=#{id}")
    int deleteUserById(int id);

    @Update("update Userinfo set UserName=#{name},UserSex=#{sex},UserAge=#{age},UserAddress=#{address} where UserId=#{id}")
    int updateUser(User user);

    @Select("select * from userinfo where userid=#{id}")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address")
    })
    User queryUserByUserId(int id);

    @Select("select * from userinfo")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address")
    })
    List<User> queryUserList();
}

测试

public class MybatisAnnotationsTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void setSession() throws Exception {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }

    @Test
    public void addUserTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user=new User();
        user.setId(20);
        user.setName("杜兰特");
        user.setSex("男");
        user.setAge(30);
        user.setAddress("篮网");
       int insertCount=  userMapper.addUser(user);
       sqlSession.commit();
       System.out.println("新增行数："+insertCount);
       sqlSession.close();
    }

    @Test
    public void deleteUserByIdTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int deleteCount = userMapper.deleteUserById(168);
        sqlSession.commit();
        System.out.println("删除行数："+deleteCount);
        sqlSession.close();
    }

    @Test
    public void updateUserTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user=new User();
        user.setId(20);
        user.setName("欧文");
        user.setAge(29);
        user.setAddress("布鲁克林");
        int updateCount = userMapper.updateUser(user);
        sqlSession.commit();
        System.out.println("更新行数："+updateCount);
        sqlSession.close();
    }

    @Test
    public void queryUserByUserIdTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.queryUserByUserId(20);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void queryUserList(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryUserList();
        userList.forEach(System.out::println);
        sqlSession.close();
    }
}

多条件动态查询

UserMapper.java

    @Select("<script>" +
            "select * from userinfo" +
            "<where>" +
            "<if test=\"name!=null and name!=''\">" +
            "and username like #{name}" +
            "</if>" +
            "<if test=\"age!=null and age!=''\">" +
            "and userage=#{age}" +
            "</if>" +
            "</where>" +
            "</script>")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address")
    })
    List<User> queryUserListByWhere(User user);

• 当sql语句中含有<where>、<if>等其他标签，一定要在sql语句首尾加上<script>，否则程序无法识别sql语句中的标签。
• age!=null 表示该属性不为null值，而age!=''表示该属性不为默认值，如age的默认值为0，用以区分age不作为条件查询和age=0作为条件查询两种情况。
• Resutls用于映射查询出来的结果集到POJO

测试

    @Test
    public void queryUserByWhereTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user=new User();
        //user.setName("%二%");
        user.setAge(27);
        List<User> userList = userMapper.queryUserListByWhere(user);
        userList.forEach(System.out::println);
    }

Foreach

UserMapper.java

    @Select("<script>" +
            "select * from userinfo where userid in " +
            "<foreach item=\"id\" collection=\"list\" open=\"(\" close=\")\" separator=\",\">" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address")
    })
    List<User> queryUserListByIds(List<Integer> list);

测试

    @Test
    public void queryUserListByIdsTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> list=new ArrayList<>();
        list.add(2);
        list.add(3);
        list.add(5);
        list.add(8);
        List<User> userList = userMapper.queryUserListByIds(list);
        userList.forEach(System.out::println);
    }

 回显主键ID(自增列)

UsersMapper.java

public interface UsersMapper {
    /**
     * 方法一：采用Options注解
     *        useGeneratedKeys=true 表示启用自动生成的注解机制
     *        keyProperty 回显到对象中对应的哪个属性
     * 方法二：采用SelectKey注解
     *         statement属性表示要执行的sql语句 "select last_insert_id()"
     *         keyProperty属性回写到对象中对应的哪个属性
     *         before属性 true表示在sql语句执行之前执行，false表示在sql语句执行之后执行
     *         resultType属性表示返回值类型
     * */
    //@Options(useGeneratedKeys = true,keyProperty = "id")
    @SelectKey(statement = "select last_insert_id()",keyProperty = "id",before = false,resultType = Integer.class)
    @Insert("insert into users(username,sex,birthday,address) " +
            "values(#{username},#{sex},#{birthday},#{address})")
    void addUsers( Users user);
}

测试

    @Test
    public void addUsersTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
        Users user = new Users();
        user.setUsername("诸葛亮");
        user.setSex('男');
        user.setBirthday(new Date());
        user.setAddress("南阳");
        usersMapper.addUsers(user);
        sqlSession.commit();
        System.out.println(user.getId());
        sqlSession.close();
    }

 一对一注解开发

OrdersMapper.java

public interface OrdersMapper {
    @Select("select * from orders")
    /**
     * 手动映射
     * */
    @Results({
            /**
             * 基本数据映射
             * */
            @Result(column = "id",property = "id"),
            @Result(column = "user_id",property = "userId"),
            @Result(column = "number",property = "number"),
            @Result(column = "createtime",property = "createTime"),
            @Result(column = "remark",property = "remark"),
            /**
             * 关联数据映射
             * column属性 表示上一个sql查询的字段，作为下一个sql语句的条件
             * property属性 表示在POJO类型Orders中属性
             * javaType属性 表示property的类型
             * one=@One 一对一关系
             *       select:关联sql的方法
             *       fetchType:FetchType.LAZY 延迟加载
             * */
            @Result(column = "user_id",property = "user",javaType = com.company.pojo.User.class,one = @One(select="com.company.mapper.UserMapper.queryUserById",fetchType = FetchType.LAZY))
    })
    List<Orders> queryOrdersUser();
}

UserMapper.java

public interface UserMapper {
    @Select("select * from userinfo where UserId=#{userid}")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address")
    })
    User queryUserById(int userid);
}

测试

    @Test
    public void queryOrdersUser(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> ordersList = ordersMapper.queryOrdersUser();
        for (Orders orders : ordersList) {
            //System.out.println(orders.getNumber());
            System.out.println(orders.getUser().getName());
        }
    }

一对多注解开发

UserMapper.java

    @Select("select * from userinfo")
    @Results({
            @Result(id=true,column = "UserId",property = "id"),
            @Result(column = "UserName",property = "name"),
            @Result(column = "UserSex",property = "sex"),
            @Result(column = "UserAge",property = "age"),
            @Result(column = "UserAddress",property = "address"),
            @Result(column = "UserId",property = "ordersList",javaType =List.class,many=@Many(select="com.company.mapper.OrdersMapper.queryOrdersById", fetchType= FetchType.LAZY) )
    })
    List<User> queryUserOrders();

OrdersMapper.java

    @Select("select * from orders where user_id=#{id}")
    @Results({
            /**
             * 基本数据映射
             * */
            @Result(column = "id", property = "id"),
            @Result(column = "user_id", property = "userId"),
            @Result(column = "number", property = "number"),
            @Result(column = "createtime", property = "createTime"),
            @Result(column = "remark", property = "remark")
    }
    )
    List<Orders> queryOrdersById(int id);

测试

    @Test
    public void queryUserOrders(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryUserOrders();
        for (User user : userList) {
           // System.out.println(user.getName());
            List<Orders> ordersList = user.getOrdersList();
            if(ordersList!=null){
                for (Orders orders : ordersList) {
                    System.out.println(orders.getNumber());
                }
            }
        }
        sqlSession.close();
    }