mapper.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.hisoft.mapper.OrderMapper">
<!-- 一对一查询 -->
<resultMap id="orderuser" type="Order" autoMapping="true">
<id column="oid" property="id"/>
<association property="user" javaType="User" autoMapping="true">
<id column="uid" property="id"/>
</association>
</resultMap>
<!-- 一对多查询 -->
<resultMap id="orderuserdetail" type="Order" autoMapping="true" extends="orderuser">
<!-- <id column="id" property="id"/>
<association property="user" javaType="User" autoMapping="true">
<id column="id" property="id"/>
</association> -->
<collection property="order" javaType="List" ofType="OrderShow" autoMapping="true">
<id column="odid" property="id"/>
</collection>
</resultMap>
<!-- 多对多查询 -->
<resultMap id="orderuserdetailitem" type="Order" autoMapping="true" extends="orderuser">
<!-- <id column="id" property="id"/>
<association property="user" javaType="User" autoMapping="true">
<id column="id" property="id"/>
</association> -->
<collection property="order" javaType="list" ofType="OrderShow" autoMapping="true">
<id column="odid" property="id"/>
<association property="item" javaType="Item" autoMapping="true">
<id column="iid" property="id"/>
</association>
</collection>
</resultMap>
<select id="selectAllUserAndOrder" resultMap="orderuser">
select
u.id uid,u.user_name,u.password,u.name,u.age,u.sex,u.birthday,u.created,u.updated,o.id
oid,o.user_id,o.order_number from tb_user u, tb_order o
where u.id = o.user_id and u.id=#{id}
</select>
<select id="selectUserOrderAndDetail" resultMap="orderuserdetail">
select
u.id uid,u.user_name,u.password,u.name,u.age,u.sex,u.birthday,u.created,u.updated,o.id
oid,o.user_id,o.order_number,od.id odid,od.order_id,od.item_id,od.total_price,od.status
from tb_user u,tb_order o,tb_orderdetail od
where u.id = o.user_id and o.id = od.order_id and o.order_number = #{orderNumber}
</select>
<select id="selectUserOrderDetailAndItem" resultMap="orderuserdetailitem">
select
u.id uid,u.user_name,u.password,u.name,u.age,u.sex,u.birthday,u.created,u.updated,o.id
oid,o.user_id,o.order_number,od.id odid,od.order_id,od.item_id,od.total_price,od.status,
i.id iid,i.item_name,i.item_price,i.item_detail
from tb_user u,tb_order o,tb_orderdetail od,tb_item i
where u.id = o.user_id and o.id = od.order_id and i.id = od.item_id and o.order_number =
#{orderNumber}
</select>
</mapper>
select 标签中resultMap属性的值就是对应resultMap标签中id的值
resultMap 标签中 id表示唯一标识 type就是返回值类型 autoMapping="true"表示打开自动映射 ,extends表示继承某个标签的内容
order实体类
public class Order {
private int id;
private long userId;
private String orderNumber;
private User user;
private List<OrderShow> order;
}
association 中property属性的值是order实体类类中定义User类的变量名称user javaType就是User 表示类型
id标签中column属性的值表示数据库的字段名 property表示实体类的变量名
collection 标签中的property是指order实体类中的变量名order, javaType值order实体类中的List集合 ,ofType是指order实体类中List集合的泛型
注解
一对一查询注解
@Results({
@Result(column = "sid",property = "sid"),
@Result(column = "gid",property = "grade",one = @One(select="com.hisoft.dao.GradeMapper.selectGrade"))
})
@Select("select * from student")
public List<Student> selectAllStudentAndGrade();
当通过学生信息查找对应班级信息的时候,就是一对一查找,因为一个学生只能对应一个班级,
@Results相当于resultMap标签 ,@Result相当于id标签 是对应匹配的意思
@Result(column = “gid”,property =“grade”,one=@One(select=“com.hisoft.dao.GradeMapper.selectGrade”)) })
因为是通过查询学生表的信息中的班级表的外键来查找对应的班级信息,所以这行代码中的column 的值是学生表的外键字段,property 的值表示传入参数的类型 ,也就是学生实体类中引入的班级类,com.hisoft.dao.GradeMapper.selectGrade这句代码的意识是引入这个路径下的sql语句,该路径下的内容为:
public interface GradeMapper {
@Results({
@Result(column = "grid",property = "grid"),
@Result(column = "class",property = "gcalss")
})
@Select("select * from grade where grid = #{gid}")
public Grade selectGrade();
}
一对多注解 内容是通过班级查找学生信息,一个班级对应多个学生
@Results({
@Result(column = "grid",property = "grid"),
@Result(column = "class",property = "gcalss"),
@Result(column = "grid",property = "list",many=@Many(select = "com.hisoft.dao.StudentMapper.selectAllStudent"))
})
@Select("select * from grade")
public List<Grade> selectGradeAndStudent();
与一对一注解内容大致相同
com.hisoft.dao.StudentMapper.selectAllStudent路径下内容如下:
@Results({
@Result(column = "sid",property = "sid")
})
@Select("select s.sid,s.sname,s.ssex,s.gid from student s where s.gid = #{grid}")
public List<Student> selectAllStudent();
以下是各自实体类的内容:
学生实体类:
public class Student {
int sid;
String sname;
String ssex;
int gid;
Grade grade;
省略set/get/tosTring 无参构造 有参构造方法
}
班级实体类:
public class Grade {
int grid;
String gcalss;
List<Student> list;
省略set/get/tosTring 无参构造 有参构造方法
}
不管是一对一或者是一对多查询,查询的时候把要查询的后表的实体类放入到前表的实体类中,是最为合适并且容易记住的(个人理解),