mybatis高级查询

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 无参构造 有参构造方法 }

不管是一对一或者是一对多查询，查询的时候把要查询的后表的实体类放入到前表的实体类中，是最为合适并且容易记住的(个人理解),