mybatis的一对一 一对多 多对多
1、表
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>
