以商品网站的订单为例
1.数据库表设计
实体类有Book、User、Order，他们的关系如下
一个用户可以拥有多个订单，一个订单只能属于一个用户。->一对多关系 一个订单中应该包含多个商品，一个商品可以属于多个订单。->多对多关系
转换为数据表的话需要在Order中加入Uid作为外键 订单和商品需要引入新表OrderItem来关联，其中包括订单Oid和商品Bid作为外键 转换后的数据表如下
也就是说一个订单属于一个用户；一个订单中有多个订单条目；一个订单条目对应同一本书，但是数量可能是多个。
2.实体类设计
省略get和set
2.1 User
public class User {
private Integer id;
private String userName;
private String password;
private String email;
private Integer isLock;
private String rePassword;
}
2.2 Book
public class Book {
private Integer id;
private String name;
private Double price;
private Double discount;
private Integer stock;
private String image;
private int sale;
}
这两个都很简单，因为他们内部不包含外键
2.3 Order
import java.util.Date;
public class Order {
private Integer id;
private String orderNum;
private Double totalPrice;
private Date createDate;
private User user;
}
注意，我们在Order中有一个User的外键，但是在面向对象中并不是直接写Integer类型的Uid，而是声明一个User类型的对象，后面Mapper中还要对其映射。
2.4 OrderItem
public class OrderItem {
private Integer id;
private Order order;
private Book book;
private Integer num;
}
order和book分别对应 参照Order和Book两个表创建的外键
3.Mapper映射
3.1 UserMapper
<?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.spring.dao.UserDao">
<resultMap id="userMapper" type="com.spring.pojo.User">
<id property="id" column="id"/>
<result property="userName" column="username"/>
<result property="password" column="password"/>
<result property="email" column="email"/>
<result property="isLock" column="isLock"/>
<result property="rePassword" column="repassword"/>
</resultMap>
<insert id="createUser">
insert into user (username) values (#{name})
</insert>
<select id="findByName" resultType="com.spring.pojo.User" parameterType="string">
select * from user where username = #{name}
</select>
<select id="findAll" resultMap="userMapper">
select * from user
</select>
</mapper>
3.2 BookMapper
<?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.spring.dao.BookDao">
<resultMap id="bookMapper" type="com.spring.pojo.Book">
<id property="id" column="id"></id>
<result property="name" column="id"></result>
<result property="price" column="price"></result>
<result property="discount" column="discount"></result>
<result property="stock" column="stock"></result>
<result property="image" column="image"></result>
<result property="sale" column="sale"></result>
</resultMap>
<select id="findAll" resultMap="bookMapper">
select * from book;
</select>
</mapper>
3.3 OrderMapper
<?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.spring.dao.OrderDao">
<resultMap id="orderMapper" type="com.spring.pojo.Order">
<id property="id" column="id"/>
<result property="orderNum" column="ordernum"/>
<result property="totalPrice" column="totalPrice"/>
<result property="createDate" column="createDate"/>
<association property="user" resultMap="com.spring.dao.UserDao.userMapper"/>
</resultMap>
</mapper>
需要注意，user这个字段要用association标签来映射
3.4 OrderItemMapper
<?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.spring.dao.OrderItemDao">
<resultMap id="orderItemMapper" type="com.spring.pojo.OrderItem">
<id property="id" column="id"/>
<result property="num" column="num"/>
<association property="order" resultMap="com.spring.dao.OrderDao.orderMapper"/>
<association property="book" resultMap="com.spring.dao.BookDao.bookMapper"/>
</resultMap>
</mapper>
order和book这两个字段同样去找他们的mapper
4.一些操作
4.1 查询包含User的所有订单信息
<
select id=
"findAll" resultMap=
"orderMapper">
select order.id,ordernum,totalprice,createDate,
user.id,username,password,email,islock,repassword
from `
order`, user
where userid=user.id
</
select>
注意：
两个表中都存在id时要用表名加以区别order为Mysql的保留字需要用“引起来select的属性要和mapper对应起来，或者直接用*
4.2 查询所有的订单条目详细信息
<
select id=
"findAll" resultMap=
"orderItemMapper">
select *
from orderItem, `
order`, book
where orderId =
order.id
and bookId = book.id
</
select>
注意要用resultMap，不要用resultType，用resultType返回的user和book都是null
在Controller里如果想用@ResponseBody返回json类型的数据需要用Gson包，即把另外两个包注释掉
<dependency>
<groupId>com.google.code.gson
</groupId>
<artifactId>gson
</artifactId>
<version>2.8.2
</version>
</dependency>
