https://blog.csdn.net/mtm001/article/details/106340963
这篇是xml版的,今天改成了注解版,我自己看着都头晕,但思路理清了就好。
<mapper class="com.hr.mapper.Orders2Mapper"/>
package com.hr.mapper;
import com.hr.entity.Items;
import com.hr.entity.Orderdetail;
import com.hr.entity.Orders;
import com.hr.entity.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.List;
public interface Orders2Mapper {
//一对一
//查询订单属于哪个用户
//订单查用户,一个订单一个用户
@Select("select * from user where id = #{id}")
User findUserById(Integer id);
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="user_id",property="user",one=@One(select="com.hr.mapper.Orders2Mapper.findUserById",fetchType= FetchType.EAGER))
})
@Select("select * from orders")
List<Orders> findOrderAndUser();
//--------------------------------------------------------------------------------------------------------
//一对多
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="number",property="number"),
@Result(column="id",property="orderdetails",//orders表中的id是orderdetail表的主键
many=@Many(select="com.hr.mapper.Orders2Mapper.findDetailsByID",fetchType= FetchType.LAZY))
})
@Select("select * from orders")
List<Orders> findOrdersAndOrderDetail();
@Results(id = "findDetailsByID",value = {
@Result(id=true,column="id",property="id"),
@Result(column="orders_id",property="ordersId"),
@Result(column="items_id",property="itemsId"),
@Result(column="items_num",property="itemsNum")
})
@Select("select * from orderdetail where orders_id = #{orders_id}")
List<Orderdetail> findDetailsByID(Integer id);
//--------------------------------------------------------------------------------------------------------
//多对多
//一、用户查订单
@Results(id = "findUserOrdersOrderdetailItems",value = {
@Result(id=true,column="id",property="id"),
//一对多
@Result(column="id",property="myOrders",//user表的id是orders表的主键
many=@Many(select="com.hr.mapper.Orders2Mapper.findOrdersByID",fetchType= FetchType.LAZY)),
})
@Select("select * from user")
List<User> findUserOrdersOrderdetailItems();
//用户下的订单
//二、订单查订单详情
@Results(id = "findOrdersByID",value = {
@Result(column="id",property="orderdetails",//orders表中的id是orderdetail表的主键
many=@Many(select="com.hr.mapper.Orders2Mapper.findOrderdetailById",fetchType= FetchType.LAZY))
})
@Select("select * from orders where user_id= #{user_id}")
List<Orders> findOrdersByID(Integer id);
//订单详情查商品
@Results(id = "findOrderdetailById",value = {
@Result(column = "items_num",property = "itemsNum"),
@Result(column="items_id",property="items",one=@One(select="com.hr.mapper.Orders2Mapper.findItemsById",fetchType= FetchType.EAGER))
})
@Select("select * from orderdetail where orders_id = #{orders_id}")
Orderdetail findOrderdetailById(Integer id);
@Select("select * from items where id = #{id}")
Items findItemsById(Integer id);
}
测试
package com.hr.test;
import com.hr.entity.Items;
import com.hr.entity.Orderdetail;
import com.hr.entity.Orders;
import com.hr.entity.User;
import com.hr.mapper.Orders2Mapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.Reader;
import java.util.List;
/**
* @ClassName TestOrders
* @Description: TODO
* @Author 汤永红
* @Date 2020/5/25 0025
* @Version V1.0
**/
public class Test2Orders {
SqlSessionFactory factory;//工厂 和数据对话
SqlSession sqlSession;//会话
Orders2Mapper mapper ;
@Before
public void init() throws Exception{
//1.加载配置文件
String xml = "mybatis.xml";
Reader reader = Resources.getResourceAsReader(xml);
factory = new SqlSessionFactoryBuilder().build(reader);
//2.创建会话
sqlSession=factory.openSession();
mapper = sqlSession.getMapper(Orders2Mapper.class);
}
@Test
public void findOrderAndUser(){
List<Orders> orders = mapper.findOrderAndUser();
for (Orders o : orders) {
System.out.println(o.getId()+","+o.getNumber()+","+o.getUser().getUsername());
}
}
@Test
public void findOrdersAndOrderDetail(){
List<Orders> orders = mapper.findOrdersAndOrderDetail();
for (Orders o : orders) {
System.out.println(o.getId()+","+o.getNumber());
List<Orderdetail> orderdetails = o.getOrderdetails();
for (Orderdetail orderdetail : orderdetails) {
System.out.println("商品编号:"+orderdetail.getItemsId()+",订单数量:"+orderdetail.getItemsNum());
}
System.out.println("------------------------");
}
}
@Test
public void findUserOrdersOrderdetailItems(){
List<User> users = mapper.findUserOrdersOrderdetailItems();
for (User user : users) {
System.out.println("用户:"+user.getId()+","+user.getUsername());
List<Orders> myOrders = user.getMyOrders();
for (Orders myOrder : myOrders) {
System.out.println("订单:"+myOrder.getNumber());
List<Orderdetail> orderdetails = myOrder.getOrderdetails();
for (Orderdetail orderdetail : orderdetails) {
System.out.println("订单详情商品数量:"+orderdetail.getItemsNum());
Items items = orderdetail.getItems();
System.out.println("商品:"+items.getName());
}
System.out.println("----------------------------------");
}
System.out.println("----------------------");
}
}
@After
public void destroy(){
sqlSession.close();
}
}
测试结果
一对一
com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.hr.test.Test2Orders,findOrderAndUser
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1935972447.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - ==> Preparing: select * from orders
DEBUG [main] - ==> Parameters:
DEBUG [main] - ====> Preparing: select * from user where id = ?
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - ====> Preparing: select * from user where id = ?
DEBUG [main] - ====> Parameters: 10(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - <== Total: 3
3,1000010,孙司空
4,1000011,孙司空
5,1000012,唐僧
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Returned connection 1935972447 to pool.
Process finished with exit code 0
一对多
com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.hr.test.Test2Orders,findOrdersAndOrderDetail
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1935972447.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - ==> Preparing: select * from orders
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
3,1000010
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <== Total: 2
商品编号:1,订单数量:1
商品编号:2,订单数量:3
------------------------
4,1000011
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 4(Integer)
DEBUG [main] - <== Total: 2
商品编号:3,订单数量:4
商品编号:2,订单数量:3
------------------------
5,1000012
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 5(Integer)
DEBUG [main] - <== Total: 1
商品编号:3,订单数量:1
------------------------
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Returned connection 1935972447 to pool.
Process finished with exit code 0
多对多
E:\soft\jdk\jdk8\jdk1.8.0_152\bin\java.exe -ea -
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1935972447.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - ==> Preparing: select * from user
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 7
用户:1,孙司空
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 2
订单:1000010
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - ====> Preparing: select * from items where id = ?
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - ====> Preparing: select * from items where id = ?
DEBUG [main] - ====> Parameters: 2(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - <== Total: 2
订单详情商品数量:1
商品:台式机
订单详情商品数量:3
商品:笔记本
----------------------------------
订单:1000011
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 4(Integer)
DEBUG [main] - ====> Preparing: select * from items where id = ?
DEBUG [main] - ====> Parameters: 3(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - <== Total: 2
订单详情商品数量:4
商品:背包
订单详情商品数量:3
商品:笔记本
----------------------------------
----------------------
用户:2,白骨精
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <== Total: 0
----------------------
用户:3,沙河尚
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <== Total: 0
----------------------
用户:4,猪八戒
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 4(Integer)
DEBUG [main] - <== Total: 0
----------------------
用户:5,观音
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 5(Integer)
DEBUG [main] - <== Total: 0
----------------------
用户:6,如来
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 6(Integer)
DEBUG [main] - <== Total: 0
----------------------
用户:10,唐僧
DEBUG [main] - ==> Preparing: select * from orders where user_id= ?
DEBUG [main] - ==> Parameters: 10(Integer)
DEBUG [main] - <== Total: 1
订单:1000012
DEBUG [main] - ==> Preparing: select * from orderdetail where orders_id = ?
DEBUG [main] - ==> Parameters: 5(Integer)
DEBUG [main] - <== Total: 1
订单详情商品数量:1
商品:背包
----------------------------------
----------------------
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7364985f]
DEBUG [main] - Returned connection 1935972447 to pool.
Process finished with exit code 0