mybatis学习使用3关联映射

mybatis关联映射

　1.1mybatis中使用一对一映射

　　在每个公司中，公司里面的员工和员工编号属于一对一关系，用mybatis实现如下：

　　card.java

　　

package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;

/**
 * @author
 * @date 2018/10/21
 */
@data
public class card implements serializable {
    private integer id;
    private string code;
}

view code

staff.java

package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;

/**
 * @author
 * @date 2018/10/21
 */
@data
public class staff implements serializable {
    private integer id;
    private string name;
    private string sex;
    private integer age;
    //职工和公司卡是一一对应的
    private card card;
}

view code

 

　　staffmapper.java

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.staff;

import java.util.list;

/**
 * @author
 * @date 2018/10/21
 */
public interface staffmapper {

    //一对一映射查询
    staff selectstaffbyid(integer id);


}

 

　mybatis-config.xml

<?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">
  <!--  xml 配置文件包含对 mybatis 系统的核心设置 -->
<configuration>

<!--    <typealiases>
        <typealias  alias="user" type="com.rookie.bigdata.domain.user"/>
    </typealiases>-->
    <environments default="mysql">
    <!-- 环境配置，即连接的数据库。 -->
    <environment id="mysql">
    <!--  指定事务管理类型，type="jdbc"指直接简单使用了jdbc的提交和回滚设置 -->
      <transactionmanager type="jdbc"/>
      <!--  datasource指数据源配置，pooled是jdbc连接对象的数据源连接池的实现。 -->
      <datasource type="pooled">
        <property name="driver" value="com.mysql.jdbc.driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </datasource>
    </environment>
  </environments>
  <!-- mappers告诉了mybatis去哪里找持久化类的映射文件 -->
  <mappers>
    <mapper resource="mapper/staffmapper.xml"/>
  </mappers>
</configuration>

 

　　staffmapper.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.rookie.bigdata.mapper.staffmapper">


    <select id="selectstaffbyid" parametertype="int"
            resultmap="staffmapper">
        select * from bs_staff where id = #{id}
    </select>


    <select id="selectcardbyid" parametertype="int" resulttype="com.rookie.bigdata.domain.card">
        select * from bs_card where id = #{id}
    </select>


    <resultmap type="com.rookie.bigdata.domain.staff" id="staffmapper">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <!-- 一对一关联映射:association   -->
        <association property="card" column="card_id"
                     select="com.rookie.bigdata.mapper.staffmapper.selectcardbyid"
                     javatype="com.rookie.bigdata.domain.card"/>
    </resultmap>

</mapper>

　　测试代码staffmappertest.java

　　

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.staff;
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 java.io.ioexception;
import java.io.inputstream;
import java.util.list;

import static org.junit.assert.*;

/**
 * @author
 * @date 2018/10/21
 */
public class staffmappertest {

    public sqlsession session;

    @before
    public void before() throws ioexception {
        // 读取mybatis-config.xml文件
        inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
        // 初始化mybatis，创建sqlsessionfactory类的实例
        sqlsessionfactory sqlsessionfactory = new sqlsessionfactorybuilder()
                .build(inputstream);
        // 创建session实例
        session = sqlsessionfactory.opensession();
    }
    @test
    public void selectstaffbyid() throws exception {

        staffmapper mapper = session.getmapper(staffmapper.class);
       staff staff = mapper.selectstaffbyid(1);
        system.out.println(staff);
    }

}

　1.2mybatis中的一对多或者多对一可以参考最初的简单的应用实例

　　1.3mybatis中的多对多关系映射

　　例如在一个购物系统中，实际会存在如下的情况，即订单和商品是存在多对多的关系，如下代码

　　表结构的sql语句如下：

　　

create table bs_user(
id int primary key auto_increment,
username varchar(18),
loginname varchar(18),
password varchar(18),
phone varchar(18),
address varchar(18)
);

insert into bs_user(username,loginname,password,phone,address)
values('zhangsan','zhangsan','123456','12345678912','上海');


create table bs_goods(
id int primary key auto_increment,
name varchar(18),
price double,
remark varchar(18)
);

insert into bs_goods(name,price,remark)
values('t恤',108.9,'2018经典款');
insert into bs_goods(name,price,remark)
values('牛仔裤',99.9,'2018经典款');
insert into bs_goods(name,price,remark)
values('连衣裙',89.9,'2018经典款');
insert into bs_goods(name,price,remark)
values('上衣外套',69.9,'海贼王图案');

create table bs_order(
id int primary key auto_increment,
code varchar(32),
total double,
user_id int,
foreign key (user_id) references bs_user(id)
);

insert into bs_order(code,total,user_id)
values('6aa3fa359ff14619b77fab5990940a2d',388.6,1);

insert into bs_order(code,total,user_id)
values('6aa3fa359ff14619b77fab5990940b3c',217.8,1);

create table bs_item(
order_id int,
good_id int,
amount int,
primary key(order_id,good_id),
foreign key (order_id) references bs_order(id),
foreign key (good_id) references bs_goods(id)
);

insert into bs_item(order_id,good_id,amount)
values(1,1,1);
insert into bs_item(order_id,good_id,amount)
values(1,2,1);
insert into bs_item(order_id,good_id,amount)
values(1,3,2);

insert into bs_item(order_id,good_id,amount)
values(2,4,2);
insert into bs_item(order_id,good_id,amount)
values(2,1,1);

 

实体类代码如下

package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;
import java.util.list;

/**
 * @author
 * @date 2018/10/21
 */
@data
public class good implements serializable {
    private integer id;        // 商品id，主键
    private string name;    // 商品名称
    private double price;    // 商品价格
    private string remark;    // 商品描述

    //商品和订单属于多对多关系
    private list<order> orders;
}


package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;
import java.util.list;

/**
 * @author
 * @date 2018/10/21
 */
@data
public class order implements serializable {

    private integer id;  // 订单id，主键
    private string code;  // 订单编号
    private double total; // 订单总金额

    private user user;

    private list<good> goods;
}

package com.rookie.bigdata.domain;

import lombok.data;

import java.io.serializable;
import java.util.list;

/**
 * @author
 * @date 2018/10/21
 */
@data
public class user implements serializable {
    private integer id;  // 用户id，主键
    private string username;  // 用户名
    private string loginname; // 登录名
    private string password;  // 密码
    private string phone;    // 联系电话
    private string address;  // 收货地址

    //用户和订单属于一对多关系
    private list<order> orders;

}

view code

 

 mapper类如下：

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.order;

/**
 * @author
 * @date 2018/10/21
 */
public interface ordermapper {


    order selectorderbyid(int id);

}


package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.user;

/**
 * @author
 * @date 2018/10/21
 */
public interface usermapper {

    user selectuserbyid(int id);
}

 

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.rookie.bigdata.mapper.goodmapper">
  
  <select id="selectgoodbyorderid" parametertype="int"
  resulttype="com.rookie.bigdata.domain.good">
      select * from bs_goods where id in (
        select good_id from bs_item where order_id = #{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.rookie.bigdata.mapper.ordermapper">

    <resultmap type="com.rookie.bigdata.domain.order" id="orderresultmap">
        <id property="id" column="oid"/>
          <result property="code" column="code"/>
          <result property="total" column="total"/>
        <!-- 多对一关联映射:association   -->
        <association property="user" javatype="com.rookie.bigdata.domain.user">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="loginname" column="loginname"/>
            <result property="password" column="password"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
        <!-- 多对多映射的关键:collection   -->
        <collection property="goods" javatype="arraylist"
      column="oid" oftype="com.rookie.bigdata.domain.user"
      select="com.rookie.bigdata.mapper.goodmapper.selectgoodbyorderid"
      fetchtype="lazy">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="price" column="price"/>
          <result property="remark" column="remark"/>
      </collection>
    </resultmap>
    

  <select id="selectorderbyid" parametertype="int" resultmap="orderresultmap">
      select u.*,o.id as oid,code,total,user_id
       from bs_user u,bs_order o
      where u.id = o.user_id
       and o.id = #{id}
  </select>
  
  <!-- 根据userid查询订单 -->
  <select id="selectorderbyuserid" parametertype="int" resulttype="com.rookie.bigdata.domain.order">
      select * from bs_order where user_id = #{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.rookie.bigdata.mapper.usermapper">

    <resultmap type="com.rookie.bigdata.domain.user" id="userresultmap">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="loginname" column="loginname"/>
        <result property="password" column="password"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <!-- 一对多关联映射:collection   -->
        <collection property="orders" javatype="arraylist"
                    column="id" oftype="com.rookie.bigdata.domain.user"
                    select="com.rookie.bigdata.mapper.ordermapper.selectorderbyuserid"
                    fetchtype="lazy">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
            <result property="total" column="total"/>
        </collection>
    </resultmap>

    <select id="selectuserbyid" parametertype="int" resultmap="userresultmap">
        select * from bs_user  where id = #{id}
    </select>

</mapper>

 

测试代码如下：

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.order;
import com.rookie.bigdata.domain.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.before;
import org.junit.test;

import java.io.ioexception;
import java.io.inputstream;
import java.util.list;

import static org.junit.assert.*;

/**
 * @author
 * @date 2018/10/21
 */
public class ordermappertest {

    public sqlsession session;

    @before
    public void before() throws ioexception {
        // 读取mybatis-config.xml文件
        inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
        // 初始化mybatis，创建sqlsessionfactory类的实例
        sqlsessionfactory sqlsessionfactory = new sqlsessionfactorybuilder()
                .build(inputstream);
        // 创建session实例
        session = sqlsessionfactory.opensession();
    }

    @test
    public void selectorderbyid() throws exception {

        ordermapper mapper = session.getmapper(ordermapper.class);
        order order = mapper.selectorderbyid(2);
        system.out.println(order);
        user user = order.getuser();
        system.out.println(user);
        list<order> orders = user.getorders();
        system.out.println(orders);
    }

}

-------------------------------------------------------------------------

package com.rookie.bigdata.mapper;

import com.rookie.bigdata.domain.order;
import com.rookie.bigdata.domain.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.before;
import org.junit.test;

import java.io.ioexception;
import java.io.inputstream;
import java.util.list;

import static org.junit.assert.*;

/**
 * @author
 * @date 2018/10/21
 */
public class usermappertest {
    public sqlsession session;

    @before
    public void before() throws ioexception {
        // 读取mybatis-config.xml文件
        inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
        // 初始化mybatis，创建sqlsessionfactory类的实例
        sqlsessionfactory sqlsessionfactory = new sqlsessionfactorybuilder()
                .build(inputstream);
        // 创建session实例
        session = sqlsessionfactory.opensession();
    }
    @test
    public void selectuserbyid() throws exception {
        usermapper mapper = session.getmapper(usermapper.class);
        user user = mapper.selectuserbyid(1);
        system.out.println(user);
        list<order> orders = user.getorders();
        for (order order : orders) {
            system.out.println(order);
        }
    }

}