mybatis的一对一一对多多对多

桃扇骨 2022-05-27 06:48 332阅读 0赞

1、表

![70][]

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>

[70]: /images/20220527/26630d9f8d3b4a2db52dbf9666ba5d6b.png