MyBatis多表查询笔记整理

一、基于xml文件的多表查询

1.一对一查询

实体类：

public class User {
    private int id;
    private String username;
    private String password;
}
public class Order {
    private int id;
    private Date ordertime;
    private double total;
}

Order接口：

public interface OrderMapper {

    public List<Order> findAll();
}

OrderMapper.xml文件：

    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column：数据表的字段名称
            property：实体的属性名称-->
        <id column="oid" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
        <!--property：当前实体（order）中的属性名称（private User user）
            javaType：当前实体（order）中的属性的类型（User）-->
        <association property="user" javaType="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT * ,o.id oid FROM orders o,USER u WHERE o.uid=u.id
    </select>

测试类：

    @Test
    public void test1() 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接口：

public interface UserMapper {
    public List<User> findAll();
}
```xml
##### UserMapper.xml文件：

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

**测试：**
```java
   @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);
       }
       sqlSession.close();
   }

3.多对多查询

UserMapper.xml文件：

    <resultMap id="userRoleMap" type="user">
<!--        user的信息-->
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="birthday" property="birthday"/>
<!--        user内部的roleList信息-->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"/>
            <result column="roleName" property="roleName"/>
            <result column="roleDesc" property="roleDesc"/>
        </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);
        }
        sqlSession.close();
    }

二、MyBatis的注解开发

基本注解	功能
@Insert	实现新增
@Update	实现更新
@Delete	实现删除
@Select	实现查询
@Result	实现结果集封装
@Results	封装多个结果集
@One	实现一对一结果集封装
@Many	实现一对多结果集封装

1.注解基本使用：

核心配置文件sqlMapConfig.xml:

<!--加载映射关系-->
    <mappers>
<!--        指定接口所在的包-->
        <package name="com.itzhuo.mapper"/>
    </mappers>

UserMapper接口：

public interface UserMapper {
    @Insert("insert into user values (#{id},#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user")
    public List<User> findAll();
}

测试：

public class MyBatisTest {

    private UserMapper mapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testSave(){
        User user=new User();
        user.setUsername("timo");
        user.setPassword("369");
        mapper.save(user);
    }
    @Test
    public void testUpdate(){
        User user=new User();
        user.setId(11);
        user.setUsername("jiansheng");
        user.setPassword("123");
        mapper.update(user);
    }
    @Test
    public void testDelete(){
        mapper.delete(11);
    }
    @Test
    public void testFindAll(){
        List<User> users=mapper.findAll();
        for (User user:users){
            System.out.println(user);
        }
    }
    @Test
    public void testFindById(){
        User user=mapper.findById(1);
        System.out.println(user);
    }
}

2.MyBatis的注解实现复杂映射开发

@Results：代替的是标签。该注解中可以使用单个@Result注解，也可以使用@Result集合
@Result：代替了标签和标签
@Result中属性介绍：

1. column：数据库的列名
2. property：需要装配的属性名
3. one：需要使用的@One注解
4. many：需要使用的@Many注解

@One（一对一）：代替了标签，是多表查询的关键，在注解中用来指定子查询返回单一对象
@One注解属性介绍：
select：指定用来多表查询的sqlmapper
@Many（多对一）：代替了标签，是多表查询的关键，在注解中用来指定子查询返回对象集合

Ⅰ.一对一查询

OrderMapper接口：

@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.itzhuo.mapper.UserMapper.findById")
        )
})
    public List<Order> findAll();

测试：

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

Ⅱ.一对多查询

OrderMapper接口：

    @Select("select * from orders where uid=#{uid}")
    public List<Order> findByUid(int uid);
UserMapper接口：
    @Select("select * from user")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(
                    property = "orderList",
                    column = "id",
                    many = @Many(select = "com.itzhuo.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderAll();

测试：

@Test
    public void testSave(){
        List<User> userAndOrderAll = mapper.findUserAndOrderAll();
        for (User user : userAndOrderAll) {
            System.out.println(user);
        }
    }

Ⅲ.多对多查询

RoleMapper接口：

public interface RoleMapper {
    @Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=#{uid}")
    public List<Role> findByUid(int uid);
}

UserMapper接口：

    @Select("select * from user")
    @Results({
            @Result(id=true,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "password",property = "password"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select="com.itzhuo.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();

测试：

@Test
    public void testSave(){
        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
    }