MyBatis之关联查询

package oyb.domain;

import java.util.Date;

public class Orders {
    private Integer id;
    private Integer user_id;
    private String note;//备注
    private String number;
    private Date createtime;//写意的创建时间
    private User user;//定单所属的用户

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", user_id=" + user_id +
                ", note=‘" + note + ‘\‘‘ +
                ", number=‘" + number + ‘\‘‘ +
                ", createtime=" + createtime +
                ‘}‘;
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public User getUser() {
        return user;
    }

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

package oyb.domain;

import java.io.Serializable;
import java.util.Date;

public class User  implements Serializable{

    private  int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

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

    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 Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public User(String username, Date birthday, String sex, String address) {
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }

    public User() {
    }
}

package oyb.mapper;

import oyb.domain.Orders;

public interface OrderMapper {

    public Orders findOrderById(int ordersId);
}

<?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="oyb.mapper.OrderMapper">
    <resultMap id="ordersRslMap" type="orders">
        <id column="id" property="id"></id>
        <id column="note" property="note"></id>
        <id column="number" property="number"></id>
        <id column="createtime" property="createtime"></id>

        <!--往orders的user中匹配数据
        模型里有模型用association来配置-->
        <association property="user" javaType="user">
            <id column="user_id" property="id"></id>
            <id column="username" property="username"></id>
            <id column="address" property="address"></id>
        </association>

    </resultMap>

    <select id="findOrderById" parameterType="int" resultMap="ordersRslMap">
        SELECT
        o.*,u.username,u.address
        FROM
        orders o,user u
        WHERE o.user_id = u.id AND  o.id = #{id}
    </select>
</mapper>

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

    
    <properties resource="db.properties"></properties>
    <!--配置别名-->
    <typeAliases>
        <!--单个别名-->
        <typeAlias type="oyb.domain.User" alias="user"></typeAlias>

        <!--批量配置,别名为类名，大小写均可-->
        <package name="oyb.domain"></package>
        <package name="oyb.vo"></package>
    </typeAliases>
    
    <!--配置mybatis的环境信息-->
    <environments default="development">
        <environment id="development">
            <!--配置JDBC事务控制，由mybatis管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置数据源，采用dbcp连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${driverClass}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="oyb/mapper/UserMapper.xml"></mapper>
       <!-- <mapper resource="oyb/sqlmap/User.xml"></mapper>-->
        <mapper resource="oyb/mapper/OrdersMapper.xml"></mapper>
    </mappers>


</configuration>

package oyb.test;

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.Before;
import org.junit.Test;
import oyb.domain.Orders;
import oyb.domain.User;
import oyb.mapper.OrderMapper;
import oyb.mapper.UserMapper;
import oyb.vo.UserQueryVO;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class test {
    SqlSessionFactory sessionFactory;
    @Before
    public void setup()throws Exception{
//读取配置文件
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //通过SqlSessionFactoryBuilder创建SqlSessionFactory会话工厂
        sessionFactory = new SqlSessionFactoryBuilder().build(is);
    }
    @Test
    public void test() throws Exception {

        SqlSession session = sessionFactory.openSession();
        OrderMapper orderMapper = session.getMapper(OrderMapper.class);
        Orders orders= orderMapper.findOrderById(3);
        System.out.println(orders);

    }



}

    private List<OrderDetail> orderDetails;

<resultMap id="ordersRslMap2" type="orders">
        <id column="id" property="id"></id>
        <id column="note" property="note"></id>
        <id column="number" property="number"></id>
        <id column="createtime" property="createtime"></id>

        <!--一对一-->
        <!--往orders的user中匹配数据
        模型里有模型用association来配置-->
        <association property="user" javaType="user">
            <id column="user_id" property="id"></id>
            <id column="username" property="username"></id>
            <id column="address" property="address"></id>
        </association>

        <!--一对多匹配-->
        <collection property="orderDetails" ofType="orderDetail">
            <id column="detail_id" property="id"></id>
            <id column="items_id" property="itemsId"></id>
            <id column="items_num" property="itemsNum"></id>
        </collection>
    </resultMap>

    <select id="findOrderById2" parameterType="int" resultMap="ordersRslMap2">
        SELECT
        o.*,
        u.username,
        u.address,
        od.id detail_id,
        od.items_id,
        od.items_num
        FROM
        orders o,
        user u,
        orderdetail od
        WHERE
        o.user_id = u.id
        AND o.id = od.orders_id
        AND o.id = #{id}
    </select>

 @Test
    public void test1() throws Exception {

        SqlSession session = sessionFactory.openSession();
        OrderMapper orderMapper = session.getMapper(OrderMapper.class);
        Orders orders= orderMapper.findOrderById2(3);
        System.out.println(orders);
        System.out.println(orders.getUser());
        System.out.println(orders.getOrderDetails());

    }

private List<Orders> ordersList;

private List<OrderDetail> orderDetails;

 private Items items;//商品模型

<?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="oyb.mapper.UserMapper">
    <!--
        id:statement的id，要求在命名空间内唯一
        parameterType:传入的参数类型
        resultType:返回的类型
        #{id}：表示该占位符待接收参数的名称为id。
    -->
    <select id="findUserById" parameterType="int" resultType="oyb.domain.User">
        SELECT * FROM USER WHERE id = #{id}
    </select>

    <insert id="insertUser" parameterType="oyb.domain.User">
        INSERT  INTO USER(username,birthday,sex,address)
        VALUES (#{username},#{birthday},#{sex},#{address})
    </insert>
    
  <!--  <select id="findUserList" parameterType="userQueryVO" resultType="user">
        SELECT * FROM USER
        <where>
            <if test="user != null">
                <if test="user.sex != null and user.sex != ‘‘ ">
                    sex = #{user.sex}
                </if>
                <if test="user.username != null and user.username != ‘‘ ">
                   AND  username LIKE ‘%${user.username}%‘
                </if>
            </if>

        </where>
    </select>-->

    <select id="findUserList" parameterType="userQueryVO" resultType="user">
        SELECT * FROM USER
        <where>
            <if test="ids !=null and ids.size >0">
                <foreach collection="ids" item="id" open="And id IN (" close=")" separator=",">
                    ${id}
                </foreach>

            </if>
        </where>
    </select>
    
    
    <resultMap id="userandItem" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>

        <!--user中的orderList属性-->
        <collection property="ordersList" ofType="orders">
            <id column="order_id" property="id"></id>
            <result column="number" property="number"></result>
            <result column="createtime" property="createtime"></result>
            <result column="note" property="note"></result>

            <collection property="orderDetails" ofType="orderDetail">
                <id column="detail_id" property="id"></id>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>

                <association property="items" javaType="items">
                    <id column="items_id" property="id"/>
                    <result column="name" property="name"/>
                    <result column="price" property="price"/>
                    <result column="detail" property="detail"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="findUserAndItems" resultMap="userandItem">
        SELECT
        u.id,
        u.username,
        u.address,
        o.id order_id,
        o.number,
        o.createtime,
        o.note,
        od.id detail_id,
        od.items_id,
        od.items_num,
        it.name,
        it.price,
        it.detail
        FROM
        user u,
        orders o,
        orderdetail od,
        items it
        WHERE
        o.user_id = u.id
        AND o.id = od.orders_id
        AND od.items_id = it.id
    </select>
</mapper>

 @Test
    public void test3() throws Exception {

        SqlSession session = sessionFactory.openSession();
        UserMapper  mapper =  session.getMapper(UserMapper.class);

        List<User> users = mapper.findUserAndItems();
        for (User user : users){
            System.out.println("用户信息:" + user);
            for (Orders order : user.getOrdersList()){
                System.out.println("定单信息:" + order);
                System.out.println("订单详情:");
                for (OrderDetail od :  order.getOrderDetails()){
                    System.out.println(od + ":" + od.getItems());
                }

                System.out.println("------------------------------");
            }

        }
    }