MyBatis 是一款优秀的?ORM?框架，支持多表查询操作。在实际开发中，经常需要使用多表查询来获取业务数据。
表与表之间存在的三种关系：一对一，一对多，多对多
? ? ? ? student（学生类）
import java.util.List;
public class Student {
private int id;
private String studentName;
private String gender;
private String address;
private String email;
private String remark;
public Student() {
}
public Student(String studentName, String gender, String address,String remark) {
this.studentName = studentName;
this.gender = gender;
this.address = address;
this.remark = remark;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}? ? ? ? order（订单类）
import java.util.List;
public class Order {
private int id;
private String ordername;
private int ordercount;
private Student student;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrdername() {
return ordername;
}
public void setOrdername(String ordername) {
this.ordername = ordername;
}
public int getOrdercount() {
return ordercount;
}
public void setOrdercount(int ordercount) {
this.ordercount = ordercount;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
}public interface QueryOrderMapper {
public List<Order> findOrderStudentAll();
}<?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.batis.mapper.QueryOrderMapper">
<select id="findOrderStudentAll" resultType="com.batis.domain.QueryOrderStudent" >
SELECT o.ordername,o.ordercount,s.id,s.studentName,s.address FROM orders o,student s WHERE o.id=s.id;
</select>
<resultMap id="orderStudent" type="com.batis.domain.Order">
<result column="ordername" property="ordername"></result>
<result column="ordercount" property="ordercount"></result>
<association property="student" javaType="com.batis.domain.Student">
<id column="student_id" property="id"></id>
<result column="studentName" property="studentName"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<select id="findOrderStudent" resultMap="orderStudent">
SELECT o.ordername,o.ordercount,s.id,s.studentName,s.address FROM orders o,student s WHERE o.id=s.id;
</select>
</mapper> @Test
public void testMapper6(){
SqlSession sqlSession = ssf.openSession();
QueryOrderMapper mapper = sqlSession.getMapper(QueryOrderMapper.class);
List<Order> orders = mapper.findOrderStudentAll();
QueryOrderStudent student = new QueryOrderStudent();
for (Order order : orders) {
System.out.println(
order.getOrdername()+","+order.getOrdercount()+","+
student.getId()+","+student.getStudentName()
+","+student.getAddress()
);
}
}? ? ? ? student.java
package com.batis.domain;
import java.util.List;
public class Student {
private int id;
private String studentName;
private String gender;
private String address;
private String email;
private String remark;
private List<Order> listOrder;
public Student() {
}
public Student(String studentName, String gender, String address,String remark) {
this.studentName = studentName;
this.gender = gender;
this.address = address;
this.remark = remark;
}
public List<Order> getListOrder() {
return listOrder;
}
public void setListOrder(List<Order> listOrder) {
this.listOrder = listOrder;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
? ? ? ? order.java
import java.util.List;
public class Order {
private int id;
private String ordername;
private int ordercount;
private Student student;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrdername() {
return ordername;
}
public void setOrdername(String ordername) {
this.ordername = ordername;
}
public int getOrdercount() {
return ordercount;
}
public void setOrdercount(int ordercount) {
this.ordercount = ordercount;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
}public interface StudentMapper {
public List<Student> findStudentOrderAll();
}<?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.batis.mapper.StudentMapper">
<resultMap id="studentOrder" type="Student">
<id column="id" property="id"></id>
<result column="studentName" property="studentName"></result>
<result column="address" property="address"></result>
<collection property="listOrder" ofType="com.batis.domain.Order">
<result column="ordername" property="ordername"></result>
<result column="ordercount" property="ordercount"></result>
</collection>
</resultMap>
<select id="findStudentOrderAll" resultMap="studentOrder">
SELECT s.id,s.studentName,s.address,o.ordername,o.ordercount FROM student s LEFT JOIN orders o on s.id=o.id;
</select>
</mapper>
@Test
public void testMapper8(){
SqlSession sqlSession = ssf.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.findStudentOrderAll();
for (Student student : students) {
System.out.println(student.getId()+","+student.getStudentName()+","+student.getAddress());
List<Order> listOrder = student.getListOrder();
for (Order order : listOrder) {
System.out.println(order.getOrdername()+","+order.getOrdercount());
}
}
}? ? ? ? items（物品类）
public class Items {
private int id;
private String items_name;
private int items_price;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getItems_name() {
return items_name;
}
public void setItems_name(String items_name) {
this.items_name = items_name;
}
public int getItems_price() {
return items_price;
}
public void setItems_price(int items_price) {
this.items_price = items_price;
}
}
? ? ? ? order
import java.util.List;
public class Order {
private int id;
private String ordername;
private int ordercount;
private Student student;
private List<OrderDetail> orderDetailList;
public List<OrderDetail> getOrderDetailList() {
return orderDetailList;
}
public void setOrderDetailList(List<OrderDetail> orderDetailList) {
this.orderDetailList = orderDetailList;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrdername() {
return ordername;
}
public void setOrdername(String ordername) {
this.ordername = ordername;
}
public int getOrdercount() {
return ordercount;
}
public void setOrdercount(int ordercount) {
this.ordercount = ordercount;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
}? ? ? ? orderdetail（订单详细）
import java.util.List;
public class OrderDetail {
private int id;
private int item_count;
private int item_all_price;
private Order orderL;
private List<Items> itemsList;
public Order getOrderL() {
return orderL;
}
public void setOrderL(Order orderL) {
this.orderL = orderL;
}
public List<Items> getItemsList() {
return itemsList;
}
public void setItemsList(List<Items> itemsList) {
this.itemsList = itemsList;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getItem_count() {
return item_count;
}
public void setItem_count(int item_count) {
this.item_count = item_count;
}
public int getItem_all_price() {
return item_all_price;
}
public void setItem_all_price(int item_all_price) {
this.item_all_price = item_all_price;
}
}
import com.batis.domain.Order;
import java.util.List;
public interface QueryOrderMapper {
public List<Order> findOrderStudentItemsOrderDetail();
}<?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.batis.mapper.QueryOrderMapper">
<resultMap id="OrderStudentItemsOrderDetail" type="com.batis.domain.Order">
<id column="o_id" property="id"></id>
<result column="ordername" property="ordername"></result>
<association property="student" javaType="com.batis.domain.Student">
<id column="student_id" property="id"></id>
<result column="studentName" property="studentName"></result>
<result column="gender" property="gender"></result>
</association>
<collection property="itemsList" ofType="com.batis.domain.OrderDetail">
<id column="od_id" property="id"></id>
<result column="item_count" property="item_count"></result>
<result column="item_all_price" property="item_all_price"></result>
</collection>
<collection property="items" ofType="com.batis.domain.Items">
<result column="items_name" property="items_name"></result>
<result column="items_price" property="items_price"></result>
</collection>
</resultMap>
<select id="findOrderStudentItemsOrderDetail" resultMap="OrderStudentItemsOrderDetail">
SELECT
orders.id o_id,
orders.ordername,
student.studentName,
student.gender,
orderdetail.id od_id,
orderdetail.item_count,
orderdetail.item_all_price ,
items.items_name,
items.items_price
FROM ORDERs,student,orderdetail,items
WHERE orders.student_id = student.id
AND orders.id = orderdetail.orders_id
AND orderdetail.items_id = items.id
</select>
</mapper>
@Test
public void testMapper9(){
SqlSession sqlSession = ssf.openSession();
QueryOrderMapper mapper = sqlSession.getMapper(QueryOrderMapper.class);
List<Order> orders = mapper.findOrderStudentItemsOrderDetail();
for (Order order : orders) {
System.out.println(order.getId()+","+order.getOrdername()+","+order.getStudent().getStudentName()+","+order.getStudent().getGender());
List<OrderDetail> orderDetailList = order.getOrderDetailList();
for (OrderDetail orderDetail : orderDetailList) {
System.out.println(orderDetail.getId()+","+orderDetail.getItem_count()+","+orderDetail.getItem_all_price());
List<Items> itemsList = orderDetail.getItemsList();
for (Items items : itemsList) {
System.out.println(items.getItems_name()+","+items.getItems_price());
}
}
}
}