本文主要讲XML版的关联查询
1. 关联查询:一对多
1.1 目标
- 查询用户详情,同时查询到用户管理的所有订单
1.2 步骤
- 查询用户详情(不含订单)
- 查询指定用户的所有订单
- 用户管理订单
- 修改JavaBean
- 映射文件
1.3 实现
1.3.1 用户详情
-
编写功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.User; import org.apache.ibatis.annotations.Param; /** * @author LiReign */ public interface UserMapper { /** * 通过id查询详情(含所有的订单) * @param uid * @return */ public User selectById(@Param("uid") String uid); }
-
编写映射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.czxy.ssm.mapper.UserMapper"> <resultMap id="userResultMap" type="user"> <id property="uid" column="uid"></id> <result property="userName" column="user_name"></result> </resultMap> <select id="selectById" resultMap="userResultMap"> select * from user where uid = #{uid} </select> </mapper>
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.User; import com.czxy.ssm.mapper.UserMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test01_XML_SelectById { public static void main(String[] args) { //1 获得mapper UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class); //2 查询素有 User user = userMapper.selectById("u001"); System.out.println(user); //3 释放 MyBatisUtils.commitAndclose(); } }
-
确认核心配置文件,添加映射文件
1.3.2 用户订单
-
编写功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Order; import org.apache.ibatis.annotations.Param; import java.util.List; /** * @author LiReign */ public interface OrderMapper { /** * 通过uid查询所有的订单 * @param uid * @return */ public List<Order> selectAllByUid(@Param("uid") String uid); }
-
编写映射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.czxy.ssm.mapper.OrderMapper"> <select id="selectAllByUid" resultType="order"> SELECT * FROM orders WHERE uid = #{uid} </select> </mapper>
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Order; import com.czxy.ssm.domain.User; import com.czxy.ssm.mapper.OrderMapper; import com.czxy.ssm.mapper.UserMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test02_XML_SelectAllOrderByUid { public static void main(String[] args) { //1 获得mapper OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class); //2 查询素有 List<Order> list = orderMapper.selectAllByUid("u001"); list.forEach(System.out::println); //3 释放 MyBatisUtils.commitAndclose(); } }
-
检查核心配置文件,添加映射文件
1.3.3 关联
-
编写Java
// 一对多关系:一个用户拥有多个订单 private List<Order> orderList = new ArrayList<>();
-
编写xml映射
<!-- 配置一对多 ,类似@Many --> <collection property="orderList" column="uid" select="com.czxy.ssm.mapper.OrderMapper.selectAllByUid"></collection>
2. 关联查询:多对一
2.1 目标:
- 查询订单时,同时查询所属用户
2.2 步骤
- 通过id查询订单详情
- 通过uid查询用户详情(已有)
- 关联
2.3 实现
2.3.1 通过id查询订单详情
-
功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Order; import org.apache.ibatis.annotations.Param; import java.util.List; /** * @author LiReign */ public interface OrderMapper { /** * 通过oid查询详情 * @param oid * @return */ public Order selectById(@Param("oid") String oid); }
-
xml配置
<resultMap id="orderResultMap" type="order"> <id property="oid" column="oid"></id> <result property="ordertime" column="ordertime"></result> <result property="uid" column="uid"></result> </resultMap> <!-- 查询详情 --> <select id="selectById" resultMap="orderResultMap"> select * from orders where oid = #{oid} </select>
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Order; import com.czxy.ssm.mapper.OrderMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test03_XML_SelectOrderByid { public static void main(String[] args) { //1 获得mapper OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class); //2 查询素有 Order order = orderMapper.selectById("x001"); System.out.println(order); //3 释放 MyBatisUtils.commitAndclose(); } }
2.3.2 关联
-
编写JavaBean
// 多对一:多个订单属于一个用户 private User user;
-
编写XML
<!-- 多对一 @One等效 --> <association property="user" column="uid" select="com.czxy.ssm.mapper.UserMapper.selectById"></association>
2.4 总结
- 注意:在idea拷贝方法签名时,需要
.
分隔。
3.关联查询:多对多
3.1 目标
- 查询所有学生,同时查询每一个学生对应的所有授课老师
3.2 步骤
- 查询所有学生
- 查询指定学生的所有授课老师
- 关联
3.3 实现
3.3.1 查询所有学生
-
查询所有学生功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Student; import java.util.List; /** * @author LiReign */ public interface StudentMapper { /** * 所有的学生 * @return */ public List<Student> selectAll(); }
-
XML映射配置,需要使用ResultMap (后面需要封装老师的数据)
<?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.czxy.ssm.mapper.StudentMapper"> <resultMap id="studentResultMap" type="student"> <id property="sid" column="sid"></id> <result property="name" column="name"></result> </resultMap> <!-- 查询详情 --> <select id="selectAll" resultMap="studentResultMap"> select * from student </select> </mapper>
-
核心配置文件,添加映射文件
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Order; import com.czxy.ssm.domain.Student; import com.czxy.ssm.mapper.OrderMapper; import com.czxy.ssm.mapper.StudentMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test04_XML_SelectAllStudent { public static void main(String[] args) { //1 获得mapper StudentMapper studentMapper = MyBatisUtils.getMapper(StudentMapper.class); //2 查询素有 List<Student> list = studentMapper.selectAll(); list.forEach(System.out::println); //3 释放 MyBatisUtils.commitAndclose(); } }
3.3.2 查询指定学生的所有授课老师
-
功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Student; import com.czxy.ssm.domain.Teacher; import org.apache.ibatis.annotations.Param; import java.util.List; /** * @author LiReign */ public interface TeacherMapper { /** * 查询指定学生的所有授课老师 * @return */ public List<Teacher> selectAllBySid(@Param("sid") Integer sid); }
-
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.czxy.ssm.mapper.TeacherMapper"> <!-- 查询详情 --> <select id="selectAllBySid" resultType="teacher"> select * from teacher t, teacher_student ts where t.tid = ts.teacher_id and ts.student_id = #{sid} </select> </mapper>
-
核心配置文件,添加映射文件
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Student; import com.czxy.ssm.domain.Teacher; import com.czxy.ssm.mapper.StudentMapper; import com.czxy.ssm.mapper.TeacherMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test05_XML_SelectAllTeacherBySid { public static void main(String[] args) { //1 获得mapper TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class); //2 查询素有 List<Teacher> list = teacherMapper.selectAllBySid(1); list.forEach(System.out::println); //3 释放 MyBatisUtils.commitAndclose(); } }
3.3.3 关联
-
编写JavaBean
// 多对多:不同的学生,可以上【不同老师】的课 private List<Teacher> teacherList = new ArrayList<>();
-
修改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.czxy.ssm.mapper.StudentMapper"> <resultMap id="studentResultMap" type="student"> <id property="sid" column="sid"></id> <result property="name" column="name"></result> <!-- 多对多关系 --> <collection property="teacherList" column="sid" select="com.czxy.ssm.mapper.TeacherMapper.selectAllBySid" ></collection> </resultMap> <!-- 查询详情 --> <select id="selectAll" resultMap="studentResultMap"> select * from student </select> </mapper>
3.4 总结
- 在多对多操作中,两边都使用
<collection>
进行配置。