多表查询

一对一查询

用户和订单，一个订单对应一个用户；

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

对应的sql语句：

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

查询结果

创建order和user实体

order

package com.itheibai.entity;
public class Order {
    private int id;
    //为了方便这里我将时间定义为String类型
    private String ordertime;
    private double total;
    //当前订单属于哪个用户
    private User user;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getOrdertime() {
        return ordertime;
    }
    public void setOrdertime(String 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 +
                '}';
    }
}

创建OrderDao接口

package com.itheibai.dao;
import com.itheibai.entity.Order;
import java.util.List;
public interface OrderDao {
    public List<Order> findAllOrder();
}

创建OrderMapper文件，

这里有两种方式：

方式一：

<?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.itheibai.dao.OrderDao">
    <resultMap id="orderMap" type="order">
        <id column="id" 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="findAllOrder" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

方式二：

<?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.itheibai.dao.OrderDao">
    <resultMap id="orderMap" type="order">
        <id column="id" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <association property="user" javaType="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>
    <select id="findAllOrder" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

还要在核心配置文件配置别名和引入mapper文件

测试

package com.itheibai.test;
import com.itheibai.dao.OrderDao;
import com.itheibai.entity.Order;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
    /**
     * 一对一查询
     * @throws IOException
     */
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
        List<Order> allOrder = orderDao.findAllOrder();
        for (Order order:allOrder){
            System.out.println(order);
        }
        System.out.println(allOrder);
        sqlSession.close();
    }
}

结果

一对多查询

一个用户，有多个订单

一对多需求：查询一个用户，于此同时查询出该用户具有的订单

对应的sql语句：

select * from user u left join orders o on u.id=o.uid

查询结果

修改User和Order实体

package com.itheibai.entity;
import java.util.Date;
import java.util.List;
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
    public List<Order> getOrderList() {
        return orderList;
    }
    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }
    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;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

配置mapper文件

<?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.itheibai.dao.UserDao">
    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <collection property="orderList" ofType="order">
            <result column="oid" property="id"></result>
            <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 left join orders o on u.id=o.uid
    </select>
    <insert id="save" parameterType="user">
        insert into user values (#{id},#{username},#{password},#{birthday})
    </insert>
</mapper>

测试

package com.itheibai.test;
import com.itheibai.dao.OrderDao;
import com.itheibai.dao.UserDao;
import com.itheibai.entity.Order;
import com.itheibai.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
    /**
     * 一对多查询
     * @throws IOException
     */
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> all = userDao.findAll();
        for (User user:all){
            String username = user.getUsername();
            System.out.println(username);
            List<Order> orderList = user.getOrderList();
            for (Order order:orderList){
                System.out.println(order);
            }
        }
        sqlSession.close();
    }
}

结果

多对多查询

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

多对多查询的需求：查询用户的同时，查询出该用户的所有角色

对应的sql语句：

select u.*,r.id rid,r.rolename  from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id
ORDER BY u.id

查询结果

创建 Role 实体，修改 User 实体

role

package com.itheibai.entity;
public class Role {
    private int id;
    private String rolename;
    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;
    }
    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", rolename='" + rolename + '\'' +
                '}';
    }
}

user

package com.itheibai.entity;
import java.util.Date;
import java.util.List;
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    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 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;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}

在UserDao接口中添加方法

package com.itheibai.dao;
import com.itheibai.entity.Role;
import com.itheibai.entity.User;
import java.util.List;
public interface UserDao{
    public void save(User user);
    public List<User> findAll();
    public List<User> findAllRole();
}

配置mapper文件

<?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.itheibai.dao.UserDao">
    <resultMap id="userRoleMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <collection property="roleList" ofType="role">
            <result column="rid" property="id"></result>
            <result column="rolename" property="rolename"></result>
        </collection>
    </resultMap>
    <select id="findAllRole" resultMap="userRoleMap">
        select u.*,r.id rid,r.rolename  from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id
        ORDER BY u.id
    </select>

</mapper>

在核心配置文件中配置别名

测试

package com.itheibai.test;
import com.itheibai.dao.OrderDao;
import com.itheibai.dao.UserDao;
import com.itheibai.entity.Order;
import com.itheibai.entity.Role;
import com.itheibai.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
    /**
     * 多对多查询
     * @throws IOException
     */
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> all = userDao.findAllRole();
        for (User user:all){
            String username = user.getUsername();
            System.out.println(username);
            List<Role> roleList = user.getRoleList();
            for (Role role:roleList){
                System.out.println(role);
            }
        }
        sqlSession.close();
    }
}

结果