mybatis的一对一一对多多对多

1、表

mybatis的一对一一对多多对多

2、建表语句

order_t表

CREATE TABLE `order_t` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `order_t_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

orderdetail表

CREATE TABLE `orderdetail` (
  `id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `item_num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `orderdetail_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order_t` (`id`),
  CONSTRAINT `orderdetail_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

items表

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `detail` varchar(255) DEFAULT NULL,
  `pic` varchar(255) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

user表

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3、pojo

public class Items {

    private Integer id;
    private String name;
    private Double price;
    private String detail;
    private String pic;
    private Timestamp datetime;
    private OrderDetail orderDetail;

public class Order {

    private Integer order_id;
    private User user;
    private Timestamp datetime;
    private String note;
    private OrderDetail orderDetail;

public class OrderDetail {

    private Integer id;
    private Order order;
    private List<Items> itemsList;
    private Integer item_num;

public class User {

    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private List<Order> orderList;

4、mapper与mapper.xml

public interface UserMapper {

     List<User> findAll();

     void insert(User user);

    void update(User user);

    void delete(Integer id);
}

<?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.evior.test.mapper.UserMapper">
    <resultMap id="userRM" type="com.evior.test.pojo.User">
        <id property="id" column="id"/>
        <result property="age" column="age"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <collection property="orderList" ofType="com.evior.test.pojo.Order">
            <id property="order_id" column="order_id"/>
            <result property="note" column="note"/>
            <result property="datetime" column="createtime"/>
            <association property="orderDetail" javaType="com.evior.test.pojo.OrderDetail">
                <id property="id" column="id"/>
                <result property="item_num" column="item_num"/>
                <collection property="itemsList" ofType="com.evior.test.pojo.Items">
                    <id property="id" column="id"/>
                    <result property="name" column="name"/>
                    <result property="price" column="price"/>
                    <result property="detail" column="detail"/>
                    <result property="pic" column="pic"/>
                    <result property="datetime" column="createtime"/>
                </collection>
            </association>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userRM">
        SELECT u.*,o.* ,od.*,i.*
        FROM USER u,order_t o ,orderdetail od,items i
        WHERE u.`id`=o.`user_id`
        AND od.`id`=o.`id`
        AND i.`id`=od.`item_id`;
    </select>

    <insert id="insert" parameterType="com.evior.test.pojo.User">
         INSERT INTO USER(id,NAME,age,sex) VALUES(#{id},#{name},#{age},#{sex});
    </insert>

    <update id="update" parameterType="com.evior.test.pojo.User">
        UPDATE USER
        <set>
            <if test="age!=null">AGE=#{age},</if>
            <if test="name!=null">NAME =#{name},</if>
            <if test="id!=null">SEX=#{sex},</if>
        </set>
        <where>
            ID=#{id}
        </where>
    </update>


    <delete id="delete" parameterType="int">
        DELETE FROM USER WHERE id=#{id};
    </delete>
</mapper>

public interface OrderMapper {
    List<Order> findAll();

    void insert(Order order);

    void update(Order order);

    void delete(Integer order_id);
}

<?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.evior.test.mapper.OrderMapper">
    <resultMap id="orderRM" type="com.evior.test.pojo.Order">
        <id property="order_id" column="id"/>
        <result property="datetime" column="createtime"/>
        <result property="note" column="note"/>
        <association property="user" javaType="com.evior.test.pojo.User">
            <id property="id" column="id"/>
            <result property="age" column="age"/>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
        </association>
         <association property="orderDetail" javaType="com.evior.test.pojo.OrderDetail">
             <id property="id" column="id"/>
             <result property="item_num" column="item_num"/>
             <collection property="itemsList" ofType="com.evior.test.pojo.Items">
                 <id property="id" column="id"/>
                 <result property="pic" column="pic"/>
                 <result property="detail" column="detail"/>
                 <result property="price" column="price"/>
                 <result property="name" column="name"/>
                 <result property="datetime" column="createtime"/>
             </collection>
         </association>
    </resultMap>

    <select id="findAll" resultMap="orderRM">
        SELECT o.*,u.* ,od.*,i.*
        FROM USER u,order_t o ,orderdetail od,items i
        WHERE u.`id`=o.`user_id`
        AND od.`id`=o.`id`
        AND i.`orderdetail_id`=od.`id`
    </select>

    <insert id="insert" parameterType="com.evior.test.pojo.Order">
        INSERT INTO ORDER_T(id,user_id,createtime,note)
        VALUES(#{order_id},#{user.id},#{datetime},#{note});
    </insert>

    <update id="update" parameterType="com.evior.test.pojo.Order">
        UPDATE ORDER_T
        <set>
            <if test="user.id!=null">USER_ID=#{user.id},</if>
            <if test="datetime!=null">CREATETIME =#{datetime},</if>
            <if test="note!=null">NOTE=#{note},</if>
        </set>
        <where>
            ID=#{order_id}
        </where>
    </update>


    <delete id="delete" parameterType="int">
        DELETE FROM ORDER_T
        WHERE id=#{order_id};
    </delete>
</mapper>

public interface OrderDetailMapper {
    List<OrderDetail> findAll();

    void insert(OrderDetail orderDetail);

    void update(OrderDetail orderDetail);

    void delete(Integer id);
}

<?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.evior.test.mapper.OrderDetailMapper">
    <resultMap id="orderDetailRM" type="com.evior.test.pojo.OrderDetail">
        <id property="id" column="id"/>
        <result property="item_num" column="item_num"/>
        <association property="order" javaType="com.evior.test.pojo.Order">
            <id property="order_id" column="order_id"/>
            <result property="datetime" column="createtime"/>
            <result property="note" column="note"/>
            <association property="user" javaType="com.evior.test.pojo.User">
                <id property="id" column="id"/>
                <result property="sex" column="sex"/>
                <result property="name" column="name"/>
                <result property="age" column="age"/>
            </association>
        </association>
        <collection property="itemsList" ofType="com.evior.test.pojo.Items">
            <id property="id" column="id"/>
            <result property="datetime" column="createtime"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
            <result property="detail" column="detail"/>
            <result property="pic" column="pic"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="orderDetailRM">
         SELECT o.*,u.* ,od.*,i.*
        FROM USER u,order_t o ,orderdetail od,items i
        WHERE u.`id`=o.`user_id`
        AND od.`id`=o.`id`
        AND i.`orderdetail_id`=od.`id`
    </select>

    <insert id="insert" parameterType="com.evior.test.pojo.OrderDetail">
        INSERT INTO orderdetail(id,order_id,item_num)
        VALUES(#{id},#{order.order_id},#{item_num});
    </insert>

    <update id="update" parameterType="com.evior.test.pojo.OrderDetail">
        UPDATE orderdetail
        <set>
            <if test="order.order_id!=null">ORDER_ID=#{order.order_id},</if>
            <if test="item_num!=null">ITEM_NUM=#{item_num},</if>
        </set>
        <where>
            ID=#{id}
        </where>
    </update>


    <delete id="delete" parameterType="int">
        DELETE FROM orderdetail
        WHERE id=#{id};
    </delete>
</mapper>

public interface ItemsMapper {
    List<Items> findAll();

    void insert(Items items);

    void update(Items items);

    void delete(Integer id);
}

<?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.evior.test.mapper.ItemsMapper">
    <resultMap id="ItemsRM" type="com.evior.test.pojo.Items">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="price" column="price"/>
        <result property="detail" column="detail"/>
        <result property="datetime" column="createtime"/>
        <association property="orderDetail" javaType="com.evior.test.pojo.OrderDetail">
            <id property="id" column="id"/>
            <result property="item_num" column="item_num"/>
            <association property="order" javaType="com.evior.test.pojo.Order">
                <id property="order_id" column="id"/>
                <result property="note" column="note"/>
                <result property="datetime" column="createtime"/>
                <association property="user" javaType="com.evior.test.pojo.User">
                    <id property="id" column="id"/>
                    <result property="age" column="age"/>
                    <result property="name" column="name"/>
                    <result property="sex" column="sex"/>
                </association>
            </association>
        </association>

    </resultMap>

    <select id="findAll" resultMap="ItemsRM">
        SELECT o.*,u.* ,od.*,i.*
        FROM USER u,order_t o ,orderdetail od,items i
        WHERE u.`id`=o.`user_id`
        AND od.`id`=o.`id`
        AND i.`orderdetail_id`=od.`id`
    </select>

    <insert id="insert" parameterType="com.evior.test.pojo.Items">
        INSERT INTO items(id,name,price,detail,pic,createtime,orderdetail_id)
        VALUES(#{id},#{name},#{price},#{detail},#{pic},#{datetime},#{orderDetail.id});
    </insert>

    <update id="update" parameterType="com.evior.test.pojo.Items">
        UPDATE items
        <set>
            <if test="name!=null">NAME=#{name},</if>
            <if test="price!=null">PRICE =#{price},</if>
            <if test="detail!=null">DETAIL=#{detail},</if>
            <if test="pic!=null">PIC=#{pic},</if>
            <if test="datetime!=null">CREATETIME=#{datetime},</if>
            <if test="orderDetail.id!=null">ORDERDETAIL_ID=#{orderDetail.id},</if>
        </set>
        <where>
            ID=#{id}
        </where>
    </update>


    <delete id="delete" parameterType="int">
        DELETE FROM items
        WHERE id=#{id};
    </delete>
</mapper>

mybatis的一对一 一对多 多对多

相关推荐

mybatis的一对一一对多多对多