栾城seo整站排名,韩雪个人官方网站,网站备案号什么情况下被注销,部队网站制作目录 一、简介
1、使用嵌套查询:
2、使用多个 SQL 语句#xff1a;
3、使用关联查询#xff1a;
4、使用自定义映射查询#xff1a;
二、业务场景
三、示例
1、一对一查询 2、一对多查询 一、简介
MyBatis 是一个优秀的持久层框架#xff0c;它提供了强大的支持来执…目录 一、简介
1、使用嵌套查询:
2、使用多个 SQL 语句
3、使用关联查询
4、使用自定义映射查询
二、业务场景
三、示例
1、一对一查询 2、一对多查询 一、简介
MyBatis 是一个优秀的持久层框架它提供了强大的支持来执行数据库操作包括多表查询。多表查询是指从多个数据库表中检索数据的过程这在实际的应用中非常常见。MyBatis 提供了多种方法来执行多表查询以下是一些常见的技术和方法
1、使用嵌套查询:
这是最基本的多表查询方法通过在 SQL 语句中嵌套子查询来联合多个表的数据。例如你可以在 SELECT 语句中使用子查询来从一个表中获取数据然后再将其用于主查询中。这种方法在某些简单情况下是有效的但对于复杂的多表查询可能会变得冗长和难以维护。
2、使用多个 SQL 语句
在 MyBatis 中你可以编写多个独立的 SQL 语句每个语句都从一个表中检索数据然后在 Java 代码中将这些数据组合起来。这通常需要在 Java 代码中手动执行每个查询并进行数据处理但对于一些复杂的多表查询情况可能更加灵活。
3、使用关联查询 MyBatis 支持使用关联查询来执行多表查询特别是在映射文件中配置了表之间的关联关系。通过在 Mapper XML 文件中配置 association 或 collection 来表示表之间的关联关系MyBatis 可以自动根据关系从多个表中检索数据并构造结果对象。
4、使用自定义映射查询
有时候多表查询的结果可能不适合于一个实体类这时你可以使用自定义映射查询来将结果映射到一个 Map 或者其他自定义的数据结构中以适应查询的需要。
二、业务场景 模拟的业务场景为订单与用户的关系可以是一对一、一对多。 比如一个用户有多个订单一个订单只有一个用户。 还有就是用户与角色的关系多个用户拥有多个角色一个角色可以被多个用户拥有一个用户可以拥有多个角色。 三、示例
1、一对一查询
案例查询所有订单信息关联查询下单用户信息。 注意因为一个订单信息只会是一个人下的订单所以从查询订单信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的订单信息则为一对多查询因为一个用户可以下多个订单。
一对一查询的需求查询一个订单与此同时查询出该订单所属的用户
数据库 对应的SQL语句 select o.id,o.ordername,o.ordercount,s.studentname,s.address from orders o,student s where o.student_id s.id 用户表student
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 email, String remark) {this.studentName studentName;this.gender gender;this.address address;this.email email;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
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;}
}StudentMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.xinhua.mapper.StudentMapper!--查询出每个用户的信息和对应的所有订单--resultMap idstudentOrder typeStudentid columnid propertyid/idresult columnstudentName propertystudentName/resultresult columngender propertygender/resultresult columnaddress propertyaddress/resultcollection propertylistOrder ofTypecom.xinhua.domain.Orderid columnorder_id propertyid/idresult columnordername propertyordername/resultresult columnordercount propertyordercount/result/collection/resultMapselect idfindStudentOrderAll resultMapstudentOrderSELECT s.id,s.studentname,s.gender,s.address,o.id order_id,o.ordername,o.ordercount FROM student s LEFT JOIN Orders o ON s.ido.student_id/select
/mapper
mapper接口类
public interface StudentMapper {public ListStudent findStudentOrderAll();
}测试类
public class TestDemo {SqlSessionFactory ssf null;Beforepublic void creatFactory(){InputStream input null;try {input Resources.getResourceAsStream(SqlMapConfig.xml);} catch (IOException e) {e.printStackTrace();}ssf new SqlSessionFactoryBuilder().build(input);}Testpublic void testMapper10() {SqlSession sqlSession ssf.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.findStudentOrderAll();for (Student student : students){System.out.println(student.getId(),student.getStudentName(),student.getGender(),student.getAddress());ListOrder listOrder student.getListOrder();for (Order order : listOrder){System.out.println( order.getOrdername(),order.getOrdercount());}}} 2、一对多查询
用户表和订单表的关系为一个用户有多个订单一个订单只从属于一个用户
一对多查询的需求查询一个用户与此同时查询出该用户具有的订单 主查询表订单表 关联查询表用户表、订单明细表、商品表 订单表 用户表 商品表 订单明细表 对应的SQL语句 SELECTorders.id o_id,orders.ordername,student.studentname,student.gender,orderdetail.id od_id,orderdetail.items_count,orderdetail.items_all_price ,items.id it_id,items.items_name,items.items_price
FROM ORDERs,student,orderdetail,itemsWHERE orders.student_id student.idAND orders.id orderdetail.orders_idAND orderdetail.items_id items.id 用户表
相比之前一对一变化在student类多了一个ListOrder orderList
public class Student {private int id;private String studentName;private String gender;private String address;private String email;private String remark;private ListOrder listOrder;public Student() {}public Student( String studentName, String gender, String address, String email, String remark) {this.studentName studentName;this.gender gender;this.address address;this.email email;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;}public ListOrder getListOrder() {return listOrder;}public void setListOrder(ListOrder listOrder) {this.listOrder listOrder;}
}Order
public class Order {private int id;private String ordername;private int ordercount;private Student student;private ListOrderDetail orderDetailList;public ListOrderDetail getOrderDetailList() {return orderDetailList;}public void setOrderDetailList(ListOrderDetail 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;}
}商品表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;}
}
订单明细表
public class OrderDetail {private int id;private int items_count;private int items_all_price;private Order order;private ListItems listItems;public ListItems getListItems() {return listItems;}public void setListItems(ListItems listItems) {this.listItems listItems;}public Order getOrder() {return order;}public void setOrder(Order order) {this.order order;}public int getId() {return id;}public void setId(int id) {this.id id;}public int getItems_count() {return items_count;}public void setItems_count(int items_count) {this.items_count items_count;}public int getItems_all_price() {return items_all_price;}public void setItems_all_price(int items_all_price) {this.items_all_price items_all_price;}
}OrderMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.xinhua.mapper.OrderMapper
resultMap idorderStudentDetailItems typecom.xinhua.domain.Orderid columno_id propertyid/idresult columnordername propertyordername/resultassociation propertystudent javaTypeStudentresult columnstudentName propertystudentName/resultresult columngender propertygender/result/association!-- 一对多关联映射 --collection propertyorderDetailList ofTypecom.xinhua.domain.OrderDetailid columnod_id propertyid/idresult columnitems_count propertyitems_count/resultresult columnitems_all_price propertyitems_all_price/resultcollection propertylistItems ofTypecom.xinhua.domain.Itemsid columnit_id propertyid/idresult columnitems_name propertyitems_name/resultresult columnitems_price propertyitems_price/result/collection/collection/resultMapselect idfindOrderStudentDetailItems resultMaporderStudentDetailItemsSELECTorders.id o_id,orders.ordername,student.studentname,student.gender,orderdetail.id od_id,orderdetail.items_count,orderdetail.items_all_price ,items.id it_id,items.items_name,items.items_priceFROM ORDERs,student,orderdetail,itemsWHERE orders.student_id student.idAND orders.id orderdetail.orders_idAND orderdetail.items_id items.id/select
/mapper
mapper接口类
public interface OrderMapper {public ListOrder findOrderStudentDetailItems();
}测试类
public class TestDemo {SqlSessionFactory ssf null;Beforepublic void creatFactory(){InputStream input null;try {input Resources.getResourceAsStream(SqlMapConfig.xml);} catch (IOException e) {e.printStackTrace();}ssf new SqlSessionFactoryBuilder().build(input);}Testpublic void testMapper11() {SqlSession sqlSession ssf.openSession();OrderMapper mapper sqlSession.getMapper(OrderMapper.class);ListOrder orderList mapper.findOrderStudentDetailItems();for (Order order : orderList){System.out.println(order.getId(),order.getOrdername(),order.getOrdercount(),order.getStudent().getStudentName());ListOrderDetail orderDetailList order.getOrderDetailList();for (OrderDetail orderDetail : orderDetailList){System.out.println( orderDetail.getId(),orderDetail.getItems_count(),orderDetail.getItems_all_price());ListItems list orderDetail.getListItems();for (Items listItems : list){System.out.println( listItems.getId(),listItems.getItems_name(),listItems.getItems_price());}}}}
