MyBatis--对象的联合查询

于 2020-07-17 22:51:52 发布
阅读量354 收藏 2
点赞数 1
分类专栏： mybatis 文章标签： java mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nglhaeg/article/details/107419675
版权

以下测试类都用到的工具类MyBatisUtil.java

public class MyBatisUtil {
	private static SqlSessionFactory sessionFactory;
	public static SqlSession getSqlSession() {
		SqlSession sqlSession = null;
		if(sqlSession==null) {			
			try {
				//加载主配置文件
				InputStream inputStream = Resources.getResourceAsStream("mybatisconfig.xml");
				//创建SqlSession工厂
				sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
				//获取SqlSession
				sqlSession = sessionFactory.openSession();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
		return sqlSession;
	}
}

一、一对多查询

实体类：Student.java

public class Student {
	private Integer sid;//学生id
	private String sname;//学生姓名
	private Integer sage;//年龄
	private Character ssex;//性别
	//一个学生对应一个班级
	private Clbum clbum;//班级对象

实体类：Clbum.java班级类

public class Clbum {
	private Integer cid;//班级id
	private String cname;//班级名称
	//一个班级有多个学生
	private List<Student> students;//存放该班级的所有学生

DAO接口类：ClassDAO.java

public interface ClassDAO {
	Clbum selectClassByCid(int cid);
}

xml映射文件：ClassDAO.xml

<mapper namespace="com.woniu.dao.ClassDAO">
	<!-- 一步查询 -->
	<!-- <resultMap type="Clbum" id="ClassMapper">
		<id column="cid" property="cid"/>
		<result column="cname" property="cname" />
		collection:一对多关系
			property:需要赋值的属性
			ofType:属性的数据类型
		<collection property="students" ofType="Student">
			<id column="sid" property="sid"/>
			<result column="sname" property="sname"/>
			<result column="sage" property="sage" />
			<result column="ssex"  property="ssex"/>
		</collection>
	</resultMap>
	<select id="selectClassByCid" resultMap="ClassMapper">
		SELECT * FROM class c,student s WHERE c.cid=s.cid AND c.cid=#{cid}
	</select>
	
	<select id="selectClbum" resultMap="ClassMapper">
		SELECT * FROM class c,student s WHERE c.cid=s.cid
	</select>  -->
	
	<!-- 分步查询 -->
	<select id="selectClassByCid" resultMap="ClassMapper">
		SELECT * FROM class WHERE cid=#{cid}
	</select>
	
	<resultMap type="Clbum" id="ClassMapper">
		<id column="cid" property="cid"/>
		<result column="cname" property="cname" />
		<collection property="students" ofType="Student" select="selectStudentsBycid" column="cid" />
	</resultMap>
	<!-- select:指定第二次查询 column：将表某列的值，传递给下一次查询 -->
	<select id="selectStudentsBycid" resultType="Student">
		select * from student where cid=#{cid}
	</select>
	
	<select id="selectClbum" resultMap="ClassMapper">
		select * from class
	</select>
</mapper>

测试类

public class ClassDAOImpTest {
	SqlSession sqlSession;
	ClassDAO ClassDaoImp;	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		ClassDaoImp = sqlSession.getMapper(ClassDAO.class);
	}	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	@Test
	public void selectClassByCid() {
		System.out.println(ClassDaoImp.selectClassByCid(1));
	}	
}

二、多对一查询

实体类：Student.java

public class Student {
	private Integer sid;//学生id
	private String sname;//学生姓名
	private Integer sage;//年龄
	private Character ssex;//性别
	//一个学生对应一个班级
	private Clbum clbum;//班级对象

实体类：Clbum.java班级类

public class Clbum {
	private Integer cid;//班级id
	private String cname;//班级名称
	//一个班级有多个学生
	private List<Student> students;//存放该班级的所有学生

DAO接口类：StudentDAO.java

public interface StudentDao {
	Student selectStuBySid(int sid);
	List<Student> selectAllStudent();
}

xml映射文件：StudentDAO.xml

<mapper namespace="com.woniu.dao.StudentDao">
	<!-- 一步查询 -->
	<!-- <resultMap type="Student" id="stuMapper">
		<id column="sid" property="sid"/>
		<result column="sname" property="sname"/>
		<result column="sage" property="sage" />
		<result column="ssex" property="ssex"/>
		<association property="clbum" javaType="Clbum">
			<id column="cid" property="cid" />
			<result column="cname" property="cname" />
		</association>
	
	</resultMap>
	<select id="selectStuBySid" resultMap="stuMapper">
		SELECT * FROM class c,student s WHERE c.cid=s.cid AND s.sid=#{sid} 
	</select> -->
	<!-- 分布查询 -->
	<select id="selectStuBySid" resultMap="stuMapper">
		SELECT * FROM student  WHERE sid=#{sid}
	</select>
	<resultMap type="Student" id="stuMapper">
		<id column="sid" property="sid" />
		<result column="sname" property="sname"/>
		<result column="sage" property="sage" />
		<result column="ssex" property="ssex"/>
		<association property="clbum" javaType="Clbum" select="selectClbum" column="cid" />
	</resultMap>
	<select id="selectClbum" resultType="Clbum">
		SELECT * FROM class WHERE cid=#{cid}
	</select>
</mapper>

测试类：

public class StudentDAOImpTest {
	SqlSession sqlSession;
	StudentDao StuDaoImp;
	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		StuDaoImp = sqlSession.getMapper(StudentDao.class);
	}
	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	
	@Test
	public void selectStuBySid() {
		System.out.println(StuDaoImp.selectStuBySid(1));
	}
	
	@Test
	public void selectClbum() {
		System.out.println(StuDaoImp.selectAllStudent());
	}
}

三、多对多查询

实体类：成绩Score.java

public class Score {
	private Integer scno;//成绩编号
	//域属性：一条成绩属于一个学生，属于一门课程
	//一个学生有多门课程，一门课程有多个学生
	private Student student;//学生
	private Course course;//课程
	private int score;//成绩

实体类：学生Student.java

public class Student {
	private String sno;//学生id
	private String sname;//学生姓名
	private Integer sage;//年龄
	private Character ssex;//性别
	//一个学生有多门课程
	private List<Course> course;

实体类：课程Course.java

public class Course {
	private String cno;//课程编号
	private String cname;//课程名称
	//一门课程有多个学生
	private List<Student> students;//所有学生

1、根据成绩编号查询学生信息和课程信息

Score接口类：ScoreDAO.java

public interface ScoreDAO {
	Score selectScoreByScid(int scno);
}

xml映射文件：

<mapper namespace="com.woniu.dao.ScoreDAO">
	<select id="selectScoreByScid" resultMap="scoreMapper">
		select * from sc where scno=#{scno}
	</select>
	<resultMap type="Score" id="scoreMapper">
		<id column="scno" property="scno"/>
		<result column="score" property="score" />
		<association property="student" javaType="Student" select="selectStu" column="sno" />
		<association property="course" javaType="Course" select="selectCou" column="cno" />
	</resultMap>
	<select id="selectStu" resultType="Student">
		select * from student where sno=#{sno}
	</select>
	<select id="selectCou" resultType="Course">
		select * from course where cno=#{cno}
	</select>
</mapper>

测试类：

public class ScoreDAOImpTest {
	SqlSession sqlSession;
	ScoreDAO scoreDaoImp;
	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		scoreDaoImp = sqlSession.getMapper(ScoreDAO.class);
	}
	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	
	@Test
	public void selectScoreByScid() {
		System.out.println(scoreDaoImp.selectScoreByScid(1));
	}
	
	@Test
	public void selectClbum() {
		System.out.println();
	}
}

2、根据学生编号查询出学的所有课程

StudentDAO.java接口类：

public interface StudentDAO {
	Student selectAllCourseBySno(String sno);
}

映射文件xml:

<mapper namespace="com.woniu.dao.StudentDAO">
	<select id="selectAllCourseBySno" resultMap="stuMapper">
		select * from student where sno=#{sno} 
	</select>
	
	<resultMap type="Student" id="stuMapper">
		<id column="sno" property="sno" />
		<result column="sname" property="sname" />
		<result column="sage" property="sage"/>
		<result column="ssex" property="ssex"/>
		<collection property="course" ofType="Course" select="selectCourse" column="sno"></collection>
	</resultMap>
	<select id="selectCourse" resultType="Course">
		select * from sc s,course c where s.cno=c.cno and s.sno=#{sno}
	</select>
</mapper>

测试类：

public class StudentDAOImpTest {
	SqlSession sqlSession;
	StudentDAO studentDaoImp;
	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		studentDaoImp = sqlSession.getMapper(StudentDAO.class);
	}
	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	
	@Test
	public void selectAllCourseBySno() {
		System.out.println(studentDaoImp.selectAllCourseBySno("s001"));
	}
	
	@Test
	public void selectClbum() {
		System.out.println();
	}
}

四、订单查询

传统项目:

​ 后台管理系统

​ 数据库设计：满足三阶范式

互联网项目

​ 电商，点餐

​ 数据库设计：不满足三阶范式，数据容易大，重复数据库，DAO很多单表操作

查询五张表：goods(商品表)、goodstype(商品类型表)、orderform(订单表)、orderitem(订单项表)、user(用户表)

实体类

商品：

public class Goods {
	private Integer id;//商品编号
	private String name;//名称
	private Double price;//价格
	private String image;//图片
	private String description;//描述
	private Integer stock;//库存
	//一个商品对应一个类型
	private GoodsType goodstype;

商品类型：

public class GoodsType {
	private Integer id;//商品类型编号
	private String name;//名称
	private String image;//图片
	private String description;//描述

订单：

public class OrderForm {
	private Integer id;
	private String orderNumber;
	private String payNumber;
	private Date createTime;
	private Integer state;
	//一个订单属于一个用户，有一个邮寄信息
	private User user;
	//一个订单有一个邮寄信息
	private Postal postal;
	//一个订单有多个订单项
	private List<OrderItem> orderItem;

订单项：

public class OrderItem {
	private Integer id;
	private Goods goods;//商品
	//订单
	private OrderForm orderForm;
	private Integer number;
	private Double totalprices;

用户：

public class User {
	private Integer id;
	private String account;
	private String password;

订单DAO接口类

public interface OrderDAO {
	List<OrderForm> selectOrderByUid(int id);
}

订单映射文件xml

<mapper namespace="com.woniu.dao.OrderDAO">
	<select id="selectOrderByUid" resultMap="orderMapper">
		select * from orderform where userid=#{id}
	</select>
	
	<resultMap type="OrderForm" id="orderMapper">
		<id column="id" property="id" />
		<result column="orderNumber" property="orderNumber" />
		<result column="payNumber" property="payNumber" />
		<result column="createTime" property="createTime" />
		<result column="state" property="state" />
		<association property="user" javaType="User" select="selectUser" column="userid" />
		<collection property="orderItem" ofType="OrderItem" select="selectOrderItem" column="id"/>
	</resultMap>
	
	<!-- 查询用户信息 -->
	<select id="selectUser" resultType="User" >
		select * from user where id=#{userid}
	</select>
	
	<!-- 查询订单项 -->
	<select id="selectOrderItem" resultMap="orderItemMapper">
		select * from orderitem where orderid=#{id}
	</select>
	
	<resultMap type="orderItem" id="orderItemMapper">
		<id column="id" property="id" />
		<result column="number" property="number"/>
		<result column="totalprices" property="totalprices" />
		<collection property="goods" ofType="Goods" select="selectGoods" column="goodsid"/>
	</resultMap>
	
	<select id="selectGoods" resultMap="goodsMapper">
		select * from goods where id=#{goodsid}
	</select>
	
	<resultMap type="Goods" id="goodsMapper">
		<id column="id" property="id" />
		<result column="name" property="name"/>
		<result column="price" property="price"/>
		<result column="image" property="image"/>
		<result column="description" property="description"/>
		<result column="stock" property="stock"/>
		<association property="goodstype" javaType="GoodsType" select="selectGoodsType" column="id"/>
	</resultMap>
	
	<select id="selectGoodsType" resultType="GoodsType">
		select * from goodstype where id=#{id}
	</select>
</mapper>

测试类

public class OrderDAOTest {
	SqlSession sqlSession;
	OrderDAO orderDaoImp;
	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		orderDaoImp = sqlSession.getMapper(OrderDAO.class);
	}
	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	
	@Test
	public void selectOrderByUid() {
		System.out.println(orderDaoImp.selectOrderByUid(1));
	}
	
}
TROWS
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值