Mybatis|MyBatis的注解开发和多表操作

一、MyBatis的多表操作

引言：首先表的关系有几种呢？
1、一对一、一对多(多对一看表的方向、多对多
2、一对一： 建表原则 共用同一个主键 、主键都一样、要么就是在某张表当中、有一个外键与另一张表的主键相关系。
一对多：建表原则：多的一方(多表当中)，要有一个外键，这个外键要与1的主键相关系。
多对多：建表原则：有一张中间表，去维护两张主表的主键。

1.1 xml方式的一对一查询

1.一对一查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于个用户一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户…

2.一对一查询的语句

对应的sql语句：

SELECT *FROM orders o,USER u WHERE o.uid = u.id;

查询的结果如下：

3.创建 Order和User实体

User

package com.itheima.domain;


import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    private Date birthday;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }



    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

Order

import java.util.Date;

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

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Date getDate() {
        return ordertime;
    }

    public void setDate(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", date=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

4.创建 OrderMapper接口

5.配置 OrderMapper.xml

<mapper namespace="com.itheima.mapper.OrderMapper">

    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column：数据表的字段名称
            propertry：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <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 *,o.id oid,u.id uid FROM orders o,USER u WHERE o.uid = u.id
    </select>
</mapper>

6.测试结果
测试

public class MapperTest {
    @Test
    public void test1() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = builder.build(inputStream);
        SqlSession sqlSession = build.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }
}

另外一种配置方式

<resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column：数据表的字段名称
            propertry：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--
            property：当前实体(order)中的属性名称(private User user)
            javaType: 当前实体(order)中的属性的类型(User)
        -->
        <association property="user" javaType="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>
        </association>
</resultMap>

1.2 xml方式的一对多查询

1.一对多查询的模型

2.一对多查询的语句
user表

order表

对应的sql语句：

SELECT u.id,username,PASSWORD,birthday,ordertime,total,u.id uid,o.id oid FROM USER u,orders o WHERE u.id = o.uid

查询的结果如下：

3.修改User实体

import com.sun.org.apache.xpath.internal.operations.Or;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;

    //描述当前用户有哪些订单
    private List<Order> orderList;

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    private Date birthday;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", orderList=" + orderList +
                ", birthday=" + birthday +
                '}';
    }
}

4.创建UserMapper接口

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

5.配置 UserMapper. xml

Test2

@Test
public void test2() throws IOException {
    InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory build = builder.build(inputStream);
    SqlSession sqlSession = build.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userList = mapper.findAll();
    for (User user : userList) {
        System.out.println(user);
    }
    
    sqlSession.close();
}

6.测试结果

User{id=1, username='lucy', password='123', orderList=[Order{id=1, date=Wed Dec 12 00:00:00 CST 2018, total=3000.0, user=null}, Order{id=2, date=Wed Dec 12 00:00:00 CST 2018, total=4000.0, user=null}], birthday=null}
User{id=2, username='haohao', password='123', orderList=[Order{id=3, date=Wed Dec 12 00:00:00 CST 2018, total=5000.0, user=null}], birthday=null}
User{id=3, username='zhangsan', password='123', orderList=[Order{id=4, date=Wed Dec 12 00:00:00 CST 2018, total=6000.0, user=null}], birthday=null}

1.3 xml方式的多对多查询

1. 多对多查询的模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用多对多查询的需求：查询用户同时查询出该用户的所有角色

2. 多对多查询的语句
对应的sql语句：

查询的结果如下：

3. 创建Role实体，修改User实体

Role

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

User

import com.sun.org.apache.xpath.internal.operations.Or;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;

    //代表当前用户有哪些订单
    private List<Order> orderList;

    //代表当前用户具备那些角色
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    private Date birthday;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                ", birthday=" + birthday +
                '}';
    }
}

4. 添加UserMapper接口方法

public List<User> findAllUserAndRole();

5.配置UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.itheima.mapper.UserMapper">
    <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="findAllUserAndRole" resultMap="userRoleMap">
        SELECT *FROM USER u,sys_user_role ur,sys_role r WHERE u.id = ur.`userId` AND ur.`roleId` = r.`id`
    </select>
</mapper>

Test3

@Test
public void test3() throws IOException {
    InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory build = builder.build(inputStream);
    SqlSession sqlSession = build.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> userAndRoleAll = mapper.findAllUserAndRole();
    for (User user : userAndRoleAll) {
        System.out.println(user);
    }

    sqlSession.close();
}

6. 测试结果
查询结果：

User{id=1, username='lucy', password='123', orderList=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}], birthday=null}
User{id=2, username='haohao', password='123', orderList=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}], birthday=null}

1.4 知识小结

MyBatis多表配置方式

• 一对一配置：使用<resultMap>做配置
• 一对多配置：使用<resultMap>+<collection>做配置
• 多对多配置：使用<resultMap>+<collection>做配置
• 一对多和多对多的差别就是多了一张中间表。

二、Mybatis的注解开发

2.1 MyBatis的常用注解

这几年来注解开发越来越流行， Mybatis也可以使用注解开发方式，这样我们就可以减少编写 Mapper映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。
@Insert： 实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@ Result一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封裝

2.2 xml方式的增删改查

完成简单的user表的增删改査的操作

测试的时候发现了这个错误

Type interface com.itheima.mapper.UserMapper is not known to the MapperRegistry.
直译过来应该是com.itheima.mapper类型接口。MapperRegistry不知道UserMapper。

仔细排查发现是我的UserMapper.xml中的namespace配置错误

1)增
Test1

public class MyBatisTest {
    private UserMapper mapper;
	
	//前置通用方法
    @Before
    public void before() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = builder.build(inputStream);
        SqlSession sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    //增
    @Test
    public void test1() {
        User user = new User();
        user.setUsername("tim");
        user.setPassword("123");
        mapper.save(user);
    }
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.itheima.mapper.UserMapper">
    <select id="save" parameterType="user">
        insert into user values(#{id}, #{username}, #{password}, #{birthday})
    </select>
</mapper>

2)删
Test2

//删
@Test
public void test2() {
    mapper.delete(4);
}

3)改
Test3

//改
@Test
public void test3() {
    User user = new User();
    user.setId(2);
    user.setUsername("kitty");
    user.setPassword("321");
    mapper.update(user);
}

通过id查询
Test4

//查
@Test
public void test4() {
    User user = mapper.findById(2);
    System.out.println(user);
}

查询全部

//查全部
@Test
public void test5() {
    List<User> userList = mapper.findAll();
    for (User user : userList) {
        System.out.println(user);
    }
}

UserMapper.java

public interface UserMapper {
    public void save(User user);

    public void update(User user);

    public void delete(int id);

    public User findById(int id);

    public List<User> findAll();
}

2.3 注解方式的增删改查

修改 My Batis的核心配置文件，我们使用了注解替代的映射文件，所以我们只需要加载使用了注解的 Mapper接口即可

或者指定扫描包含映射关系的接口所在的包也可以

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

UserMapper.java

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();
}

测试
原表

其他如此 正常使用即可

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

2.5 注解方式的一对一查询

1.一对一查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

2.一对一查询的语句
对应的sql语句

"select *,o.id oid from orders o,user u where o.uid = u.id"

3.创建 Order和User实体

Order

public class Order {

    private int id;
    private Date ordertime;
    private double total;

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

User

public class User {

    private int id;
    private String username;
    private String password;
    private Date birthday;

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

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    //描述的是当前用户具有的订单
    private List<Order> orderList;

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", roleList=" + roleList +
                ", orderList=" + orderList +
                '}';
    }
}

Role

public class Role {

    private int id;
    private String roleName;
    private String roleDesc;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }   
}

4.创建 OrderMapper接口
5.使用注解配置 Mapper

OrderMapper.java

public interface OrderMapper {

    @Select("select *,o.id oid from orders o,user u where o.uid = u.id")
    @Results({
            @Result(column = "oid", property = "id"),
            @Result(column = "ordertime", property = "ordertime"),
            @Result(column = "total", property = "total"),
            @Result(column = "uid", property = "user.id"),
            @Result(column = "username", property = "user.username"),
            @Result(column = "password", property = "user.password")
    })
    public List<Order> findAll();
}

Test1

public class MyBatisTest2 {
    private OrderMapper mapper;

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

    //查全部
    @Test
    public void test1() {
        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }
}

6.测试结果

Test2

上述的OrderMapper还可以写成这样

UserMapper.java

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

测试结果是一样的！

2.6 注解方式的一对多查询

1.一对多查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

2.一对多查询的语句

select from user.
select* from orders where uid=查询出用户的id；

3.修改User实体
4.刨建 UserMapper接口
5.使用注解配置 Mapper

@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",//代表使用id去查询
                javaType = List.class,
                many = @Many(select = "com.itheima.mapper.OrderMapper.findById")
        )
})
public List<User> findUserAndOrderAll();

6.测试结果

User{id=1, username='lucy', password='123', birthday=null, roleList=null, orderList=[Order{id=1, ordertime=Wed Dec 12 00:00:00 CST 2018, total=3000.0, user=null}, Order{id=2, ordertime=Wed Dec 12 00:00:00 CST 2018, total=4000.0, user=null}]}
User{id=2, username='kitty', password='321', birthday=null, roleList=null, orderList=[Order{id=3, ordertime=Wed Dec 12 00:00:00 CST 2018, total=5000.0, user=null}]}
User{id=3, username='zhangsan', password='123', birthday=null, roleList=null, orderList=[Order{id=4, ordertime=Wed Dec 12 00:00:00 CST 2018, total=6000.0, user=null}]}
User{id=5, username='tim', password='123', birthday=null, roleList=null, orderList=[]}

2.7 注解方式的多对多查询

1.多对多查询的模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用多对多查询的需求：查询用户同时查询出该用户的所有角色

2.多对多查询的语句
对应的sql语句

SELECT *FROM USER;

SELECT *FROM sys_user_role su,sys_role r WHERE  su.`roleId` = r.`id` AND su.userId = 

查询的结果如下

3.创建Role实体，修改User实体

4.添加 UserMapper接口方法
5.使用注解配置 Mapper


UserMapper.java

@Select("select *from user")
@Results(value = {
        @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.itheima.mapper.RoleMapper.findByUid")
        )
})
public List<User> findUserAndRoleAll();

中间表
RoleMapper

public interface RoleMapper {
    @Select("SELECT *FROM sys_user_role su,sys_role r WHERE  su.`roleId` = r.`id` AND su.userId = #{id};")
    public List<Role> findByUid();
}

6.测试结果

User{id=1, username='lucy', password='123', birthday=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}], orderList=null}
User{id=2, username='kitty', password='321', birthday=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}], orderList=null}
User{id=3, username='zhangsan', password='123', birthday=null, roleList=[], orderList=null}
User{id=5, username='tim', password='123', birthday=null, roleList=[], orderList=null}