Mybatis的多表操作与注解开发

一、Mybatis多表操作

1.一对一查询

        比如有一个用户表和一个订单表，一个用户有多个订单，但一个订单只从属于一个用户，这时查询一个订单，与此同时查询出该订单所属的用户就为一对一查询。

 

①.创建order与user实体

public class Order {
    private int id;
    private Date ordertime;
    private double total;

    //当前订单属于哪一个用户
    private User user;
}

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
}

②.创建OrderMapper接口

public interface OrderMapper {
    public List<Order> findAll();
}

③.配置OrderMapper.xml

<mapper namespace="xx.xx.xxx.OrderMapper">
    <resultMap id="orderMap" type="xx.xx.xxx.Order">
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

或

<resultMap id="orderMap" type="xx.xx.xxx.Order">
    <result property="id" column="id"></result>
    <result property="ordertime" column="ordertime"></result>
    <result property="total" column="total"></result>
    <association property="user" javaType="com.itheima.domain.User">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap

④.测试

@Test
public void test() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> orderList = mapper.findAll();
    for (Order order : orderList) {
        System.out.println(order);
    }
    sqlSession.close();
}

2.一对多查询

        一个用户可以有多个订单，所以查询一个用户，同时查询出该用户具有的订单就是一对多查询。

①.更新user实体

//描述的是当前用户存在哪些订单
private List<Order> orderList;

②.创建usermapper接口

public interface UserMapper {
    public List<User> findAll();
}

③.配置UserMapper.xml

<resultMap id="userMap" type="user">
    <id column="uid" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
    <!--配置集合信息
        property:集合名称
        ofType：当前集合中的数据类型
    -->
    <collection property="orderList" ofType="order">
        <!--封装order的数据-->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
    </collection>
</resultMap>

<select id="findAll" resultMap="userMap">
    SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>

④.测试

@Test
public void test2() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = mapper.findAll();
    for(User user : userList){
        System.out.println(user.getUsername());
        List<Order> orderList = user.getOrderList();
        for(Order order : orderList){
            System.out.println(order);
        }
        System.out.println("----------------------------------");
    }
    sqlSession.close();
}

3.多对多查询

        如一个用户有多个角色，一个角色被多个用户使用。

public class Role {
    private int id;
    private String roleName;
    private String roleDesc;
}

①.更新user

//描述的是当前用户具备哪些角色
private List<Role> roleList;

②.在usermapper接口添加方法

List<User> findAllUserAndRole();

③.配置UserMapper.xml

<resultMap id="userRoleMap" type="user">
    <!--user的信息-->
    <id column="userId" property="id"></id>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
    <!--user内部的roleList信息-->
    <collection property="roleList" ofType="role">
        <id column="roleId" property="id"></id>
        <result column="roleName" property="roleName"></result>
        <result column="roleDesc" property="roleDesc"></result>
    </collection>
</resultMap>

<select id="findUserAndRoleAll" resultMap="userRoleMap">
    SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>

④.测试

@Test
public void test3() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> userAndRoleAll = mapper.findUserAndRoleAll();
    for(User user : userAndRoleAll){
        System.out.println(user.getUsername());
        List<Role> roleList = user.getRoleList();
        for(Role role : roleList){
            System.out.println(role);
        }
        System.out.println("----------------------------------");
    }
    sqlSession.close();
}

二、Mybatis注解开发

1.常用注解

@Insert：实现新增                                    @Update：实现更新

@Delete：实现删除                                   @Select：实现查询

@Result：实现结果集封装                        @Results：可以与@Result 一起使用，封装多个结果集

@One：实现一对一结果集封装                 @Many：实现一对多结果集封装

2.注解配置Mapper例子

public interface OrderMapper {

    @Select("select * from orders where uid=#{uid}")
    public List<Order> findByUid(int uid);

    @Select("select * from orders")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(
                    property = "user", //要封装的属性名称
                    column = "uid", //根据那个字段去查询user表的数据
                    javaType = User.class, //要封装的实体类型
                    //select属性 代表查询那个接口的方法获得数据
                    one = @One(select = "com.itheima.mapper.UserMapper.findById")
            )
    })
    List<Order> findAll();
}

@Test
public void testSelectOrderAndUser() {
    List<Order> all = orderMapper.findAll();
    for(Order order : all){
        System.out.println(order);
    }
}