1 一对一关联关系
配套视频:【编程不良人】Mybatis 从入门到精通_哔哩哔哩_bilibili
1.1 建表
-- 身份信息表 DROP TABLE IF EXISTS `t_card`; CREATE TABLE IF NOT EXISTS `t_card`( `id` VARCHAR(36) PRIMARY KEY, `no` VARCHAR(18), `address` VARCHAR(100), `fork` VARCHAR(30) )ENGINE=INNODB CHARSET=utf8; -- 用户表 DROP TABLE IF EXISTS `t_users`; CREATE TABLE IF NOT EXISTS `t_users`( `id` VARCHAR(36) PRIMARY KEY, `name` VARCHAR(40), `age` INTEGER, `bir` DATE, `cardId` VARCHAR(36) REFERENCES `t_card`(`id`) )ENGINE=INNODB CHARSET=utf8; -- 插入t_card数据 INSERT INTO t_card VALUES ('1','1','北京市海淀区','汉族'), ('2','2','北京市昌平区','满族'); -- 插入t_users数据 INSERT INTO t_users VALUES ('1','小黑',15,CURDATE(),'1'), ('2','小白',23,CURDATE(),'2'); -- 查看表中数据 SELECT * FROM t_card; SELECT * FROM t_users; -- 进行连接查询 SELECT u.`id`,u.`name`,u.`age`,u.`bir`,c.`no`,c.`address`,c.`fork` FROM t_users u LEFT JOIN t_card c ON u.`cardId`=c.`id`; -- 或 SELECT u.id userId,u.name,u.age,u.bir, c.id cardId,c.no,c.fork,c.address FROM t_users u LEFT JOIN t_card c ON u.cardId=c.id; -- 或 SELECT c.id cardId,c.no,c.address,c.fork, u.id userId,u.name,u.age,u.bir FROM t_card c LEFT JOIN t_users u ON c.id=u.cardId;
查询结果:
1.2 创建实体类
User
注意:先添加User的属性、无参有参构造方法、set、get方法、toString方法,再添加Card属性以及get、set方法,防止在toString方法里面产生死环,打印时异常,栈溢出
package entity; import java.util.Date; /** * @ClassName User * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 14:31 * @Version 1.0 */ public class User { private String id; private String name; private Integer age; private Date bir; //关系属性,需要额外添加get、set方法 //注意此属性不能出现在toString方法里面,否则会产生死环,打印时异常,栈溢出 private Card card; public Card getCard() { return card; } public void setCard(Card card) { this.card = card; } public User(String id, String name, Integer age, Date bir) { this.id = id; this.name = name; this.age = age; this.bir = bir; } public User() { } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Date getBir() { return bir; } public void setBir(Date bir) { this.bir = bir; } @Override public String toString() { return "User{" + "id='" + id + '\'' + ", name='" + name + '\'' + ", age=" + age + ", bir=" + bir + '}'; } }
Card
注意:先添加Card的属性、无参有参构造方法、set、get方法、toString方法,再添加User属性以及get、set方法,防止在toString方法里面产生死环,打印时异常,栈溢出
package entity; /** * @ClassName Card * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 14:37 * @Version 1.0 */ public class Card { private String id; private String no; private String address; private String fork; //关系属性,需要额外添加get、set方法 // 注意此属性不能出现在toString方法里面,否则会产生死环,打印时异常,栈溢出 private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } public Card() { } public Card(String id, String no, String address, String fork) { this.id = id; this.no = no; this.address = address; this.fork = fork; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getNo() { return no; } public void setNo(String no) { this.no = no; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getFork() { return fork; } public void setFork(String fork) { this.fork = fork; } @Override public String toString() { return "Card{" + "id='" + id + '\'' + ", no='" + no + '\'' + ", address='" + address + '\'' + ", fork='" + fork + '\'' + '}'; } }
1.3 创建DAO组件
UserDAO
package dao; import entity.User; import java.util.List; /** * @ClassName UserDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 14:42 * @Version 1.0 */ public interface UserDAO { /** * @MethodName selectAll * @Description 查询用户信息及身份信息 * @return: java.util.List<entity.User> * @Author Jiangnan Cui * @Date 2022/4/16 14:43 */ List<User> selectAll(); }
CardrDAO
package dao; import entity.Card; import java.util.List; /** * @ClassName CardDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 16:03 * @Version 1.0 */ public interface CardDAO { /** * @MethodName selectAll * @Description 查询身份信息及用户信息 * @return: java.util.List<entity.Card> * @Author Jiangnan Cui * @Date 2022/4/16 16:03 */ List<Card> selectAll(); }
1.4 创建mapper配置文件
UserDAOMapper.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="dao.UserDAO"> <!-- 处理关联关系时,resultType无法将关系属性进行自动封装,其只能处理单表简单类型(对象) 处理关系属性赋值时,要使用resultMap type:封装对象类型 1.如果是一个对象,直接写对象的全名 2.如果是多个对象,同样要写泛型的类型 id:resultMap的名字 --> <resultMap id="userMap" type="entity.User"> <!--id:用来封装外部表的主键,column:数据库中的列名(字段名),property:实体类中的属性名--> <id column="userId" property="id"/> <!--result:用来封装外部表的普通属性值,column:数据库中的列名(字段名),property:实体类中的属性名--> <result column="name" property="name"/> <result column="age" property="age"/> <result column="bir" property="bir"/> <!-- 关系属性封装:一对一 association:用来处理一对一关系属性封装 property:关系属性名 javaType:关系属性的类型 --> <association property="card" javaType="entity.Card"> <!--规则同上--> <id column="cardId" property="id"/> <result column="no" property="no"/> <result column="address" property="address"/> <result column="fork" property="fork"/> </association> </resultMap> <!-- selectAll resultMap:定义封装对象时,外部使用哪个resultMap进行封装,书写resultMap标签的id属性 --> <select id="selectAll" resultMap="userMap"> select u.id userId,u.name,u.age,u.bir, c.id cardId,c.no,c.fork,c.address from t_users u left join t_card c on u.cardId=c.id </select> </mapper>
CardDAOMapper.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="dao.CardDAO"> <resultMap id="cardMap" type="entity.Card"> <id column="userId" property="id"/> <result column="no" property="no"/> <result column="address" property="address"/> <result column="fork" property="fork"/> <association property="user" javaType="entity.User"> <id column="userId" property="id"/> <result column="name" property="name"/> <result column="age" property="age"/> <result column="bir" property="bir"/> </association> </resultMap> <!--selectAll--> <select id="selectAll" resultMap="cardMap"> select c.id cardId,c.no,c.address,c.fork, u.id userId,u.name,u.age,u.bir from t_card c left join t_users u on c.id=u.cardId </select> </mapper>
1.5 mybatis-config.xml配置mapper
<mappers> <mapper resource="mapper/UserDAOMapper.xml"/> <mapper resource="mapper/CardDAOMapper.xml"/> </mappers>
1.6 测试连接查询
package test; import dao.CardDAO; import dao.UserDAO; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import util.MybatisUtil; /** * @ClassName TestDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 16:37 * @Version 1.0 */ public class TestDAO { /** * @MethodName testUserDAO * @Description 测试UserDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 16:38 */ @Test public void testUserDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); UserDAO userDAO = sqlSession.getMapper(UserDAO.class); userDAO.selectAll().forEach(user -> System.out.println("user = " + user + " " + user.getCard())); MybatisUtil.close(); } /** * @MethodName testCardDAO * @Description 测试CardDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 16:39 */ @Test public void testCardDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); CardDAO cardDAO = sqlSession.getMapper(CardDAO.class); cardDAO.selectAll().forEach(card -> System.out.println("card = " + card + " " + card.getUser())); MybatisUtil.close(); } }
测试结果:
(1)UserDAO
[DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==> Preparing: select u.id userId,u.name,u.age,u.bir, c.id cardId,c.no,c.fork,c.address from t_users u left join t_card c on u.cardId=c.id [DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.UserDAO.selectAll <== Total: 2 user = User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='1', no='1', address='北京市海淀区', fork='汉族'} user = User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='2', no='2', address='北京市昌平区', fork='满族'}
(2)CardDAO
[DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==> Preparing: select c.id cardId,c.no,c.address,c.fork, u.id userId,u.name,u.age,u.bir from t_card c left join t_users u on c.id=u.cardId [DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.CardDAO.selectAll <== Total: 2 card = Card{id='1', no='1', address='北京市海淀区', fork='汉族'} User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022} card = Card{id='2', no='2', address='北京市昌平区', fork='满族'} User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022}
2 一对多关联关系
配套视频:【编程不良人】Mybatis 从入门到精通_哔哩哔哩_bilibili
2.1 建表
-- 部门表 DROP TABLE IF EXISTS t_dept; CREATE TABLE IF NOT EXISTS `t_dept`( `id` VARCHAR(36) PRIMARY KEY, `name` VARCHAR(40) )ENGINE=INNODB CHARSET=utf8; -- 员工表 DROP TABLE IF EXISTS t_emps; CREATE TABLE IF NOT EXISTS `t_emps`( `id` VARCHAR(36) PRIMARY KEY, `name` VARCHAR(40), `age` INTEGER, `bir` DATE, `salary` DOUBLE(7,2), `deptId` VARCHAR(36) REFERENCES `t_dept`(`id`) )ENGINE=INNODB CHARSET=utf8; -- 部门测试数据 INSERT INTO t_dept VALUES ('1','教学部'), ('2','研发部'); -- 员工的测试数据 INSERT INTO t_emps VALUES ('1','小黑',12,CURDATE(),23000.13,'1'), ('2','小三',13,CURDATE(),24000.14,'2'), ('3','小黄',14,CURDATE(),25000.15,'1'), ('4','小牛',15,CURDATE(),26000.16,'1'), ('5','小金',16,CURDATE(),27000.17,'1'), ('6','小陈',17,CURDATE(),28000.18,'2'); -- 查看表数据 SELECT * FROM t_dept; SELECT * FROM t_emps; -- 根据部门找员工信息 SELECT d.id,d.`name`, e.`id`,e.`name`,e.`age`,e.`bir`,e.`salary`,e.`deptId` FROM t_dept d LEFT JOIN t_emps e ON d.`id`=e.`deptId`;
2.2 创建实体类
Dept
package entity; import java.util.List; /** * @ClassName Dept * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 20:19 * @Version 1.0 */ public class Dept { private String id; private String name; //关系属性 private List<Emp> emps; public List<Emp> getEmps() { return emps; } public void setEmps(List<Emp> emps) { this.emps = emps; } public Dept() { } public Dept(String id, String name) { this.id = id; this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Dept{" + "id='" + id + '\'' + ", name='" + name + '\'' + '}'; } }
Emp
package entity; import java.util.Date; /** * @ClassName Emp * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 20:21 * @Version 1.0 */ public class Emp { private String id; private String name; private Integer age; private Double salary; private Date bir; //关系属性 private Dept dept; public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public Emp() { } public Emp(String id, String name, Integer age, Double salary, Date bir) { this.id = id; this.name = name; this.age = age; this.salary = salary; this.bir = bir; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } public Date getBir() { return bir; } public void setBir(Date bir) { this.bir = bir; } @Override public String toString() { return "Emp{" + "id='" + id + '\'' + ", name='" + name + '\'' + ", age=" + age + ", salary=" + salary + ", bir=" + bir + '}'; } }
2.3 创建DAO组件
DeptDAO
package dao; import entity.Dept; import java.util.List; /** * @ClassName DeptDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 20:25 * @Version 1.0 */ public interface DeptDAO { /** * @MethodName selectAll * @Description 查询部门的同时将部门中所有的员工信息一并查到 * @return: java.util.List<entity.Dept> * @Author Jiangnan Cui * @Date 2022/4/16 20:26 */ List<Dept> selectAll(); }
EmpDAO
package dao; import entity.Emp; import java.util.List; /** * @ClassName EmpDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 20:53 * @Version 1.0 */ public interface EmpDAO { /** * @MethodName selectAll * @Description 查询员工信息的同时将部门的信息一并查到 * @return: java.util.List<entity.Emp> * @Author Jiangnan Cui * @Date 2022/4/16 20:54 */ List<Emp> selectAll(); }
2.4 创建mapper配置文件
DeptDAOMapper
<?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="dao.DeptDAO"> <!-- id = "接口中的方法名" parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用,引用类型使用类的全限定名(包名.类名) resultType = "返回实体类对象:包.类名" 处理结果集 自动封装 注意: 1.sql语句后不要出现";"号 2.Mybatis框架在执行插入(insert)、更新(update)操作时,默认不允许插入或修改NULL值到数据库中, 要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写)),例如:#{age,jdbcType=INTEGER} --> <resultMap id="deptMap" type="entity.Dept"> <id column="id" property="id"/> <result column="name" property="name"/> <!-- 处理关系属性的封装 collection:用来处理封装集合类型的管理属性,用来处理一对多这种情况 property: 关系属性名 javaType: 关系属性类型 ofType : 集合中泛型类型 全名 --> <collection property="emps" javaType="list" ofType="entity.Emp"> <id column="eid" property="id"/> <result column="ename" property="name"/> <result column="age" property="age"/> <result column="salary" property="salary"/> <result column="bir" property="bir"/> </collection> </resultMap> <!--selectAll--> <select id="selectAll" resultMap="deptMap"> select d.id,d.name, e.id eid,e.name ename,e.age,e.salary,e.bir from t_dept d left join t_emps e on d.id=e.deptId </select> </mapper>
EmpDAOMapper
<?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="dao.EmpDAO"> <!-- id = "接口中的方法名" parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用,引用类型使用类的全限定名(包名.类名) resultType = "返回实体类对象:包.类名" 处理结果集 自动封装 注意: 1.sql语句后不要出现";"号 2.Mybatis框架在执行插入(insert)、更新(update)操作时,默认不允许插入或修改NULL值到数据库中, 要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写)),例如:#{age,jdbcType=INTEGER} --> <resultMap id="empMap" type="entity.Emp"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="age" property="age"/> <result column="salary" property="salary"/> <result column="bir" property="bir"/> <!-- 处理关系属性的封装 collection:用来处理封装集合类型的管理属性,用来处理一对多这种情况 property: 关系属性名 javaType: 关系属性类型 ofType : 集合中泛型类型 全名 --> <association property="dept" javaType="entity.Dept"> <id column="did" property="id"/> <result column="dname" property="name"/> </association> </resultMap> <!--selectAll--> <select id="selectAll" resultMap="empMap"> select e.id,e.name,e.age,e.salary,e.bir, d.id did,d.name dname from t_dept d left join t_emps e on d.id=e.deptId </select> </mapper>
2.5 mybatis-config.xml配置mapper
<mappers> <mapper resource="mapper/DeptDAOMapper.xml"/> <mapper resource="mapper/EmpDAOMapper.xml"/> </mappers>
2.6 测试连接查询
package test; import dao.CardDAO; import dao.DeptDAO; import dao.EmpDAO; import dao.UserDAO; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import util.MybatisUtil; /** * @ClassName TestDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 16:37 * @Version 1.0 */ public class TestDAO { /** * @MethodName testDeptDAO * @Description 测试DeptDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 20:50 */ @Test public void testDeptDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); DeptDAO deptDAO = sqlSession.getMapper(DeptDAO.class); deptDAO.selectAll().forEach(dept -> System.out.println("dept = " + dept + " " + dept.getEmps())); MybatisUtil.close(); } /** * @MethodName testEmpDAO * @Description 测试EmpDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 20:50 */ @Test public void testEmpDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); EmpDAO empDAO = sqlSession.getMapper(EmpDAO.class); empDAO.selectAll().forEach(emp -> System.out.println("emp = " + emp + " " + emp.getDept())); MybatisUtil.close(); } }
测试结果:
(1)DeptDAO
[DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==> Preparing: select d.id,d.name, e.id eid,e.name ename,e.age,e.salary,e.bir from t_dept d left join t_emps e on d.id=e.deptId [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll <== Total: 6 dept = Dept{id='1', name='教学部'} [Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022}] dept = Dept{id='2', name='研发部'} [Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022}]
(2)EmpDAO
[DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==> Preparing: select e.id,e.name,e.age,e.salary,e.bir, d.id did,d.name dname from t_dept d left join t_emps e on d.id=e.deptId [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll <== Total: 6 emp = Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'} emp = Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'} emp = Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'} emp = Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'} emp = Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'} emp = Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'}
3 多对多关联关系
配套视频:【编程不良人】Mybatis 从入门到精通_哔哩哔哩_bilibili
思路:多对多拆分成一对多,添加关系表
3.1 建表
-- 学生表 DROP TABLE IF EXISTS `t_student`; CREATE TABLE IF NOT EXISTS `t_student`( `id` VARCHAR(36) PRIMARY KEY, `name` VARCHAR(40), `age` INTEGER )ENGINE=INNODB CHARSET=utf8; -- 课程表 DROP TABLE IF EXISTS `t_course`; CREATE TABLE IF NOT EXISTS `t_course`( `id` VARCHAR(36) PRIMARY KEY, `name` VARCHAR(36) )ENGINE=INNODB CHARSET=utf8; -- 学生选课表(关系表:主键作为字段) DROP TABLE IF EXISTS `t_student_course`; CREATE TABLE IF NOT EXISTS `t_student_course`( `sid` VARCHAR(36) REFERENCES t_student(`id`), `cid` VARCHAR(36) REFERENCES t_course(`id`) )ENGINE=INNODB CHARSET=utf8; -- 插入测试数据 INSERT INTO t_student VALUES ('11','小明',23), ('22','小王',23); INSERT INTO t_course VALUES ('1','语文'), ('2','数学'), ('3','政治'); INSERT INTO t_student_course VALUES ('11','1'), ('11','2'), ('11','3'), ('22','1'), ('22','3'); -- 查看表数据 SELECT * FROM t_student; SELECT * FROM t_course; SELECT * FROM t_student_course; -- 关联查询 (t_student+t_student_course)+t_course SELECT s.id,s.name,s.age, c.id cid,c.name cname FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id`;
3.2 创建实体类
Student
package entity; import java.util.List; /** * @ClassName Student * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/7 9:17 * @Version 1.0 */ public class Student { private String id; private String name; private int age; //关联属性 List<Course> courses; public List<Course> getCourses() { return courses; } public void setCourses(List<Course> courses) { this.courses = courses; } public Student() { } public Student(String id, String name, int age) { this.id = id; this.name = name; this.age = age; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Student{" + "id='" + id + '\'' + ", name='" + name + '\'' + ", age=" + age + '}'; } }
Course
package entity; import java.util.List; /** * @ClassName Course * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 21:53 * @Version 1.0 */ public class Course { private String id; private String name; //关联属性 List<Student> students; public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } public Course() { } public Course(String id, String name) { this.id = id; this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Course{" + "id='" + id + '\'' + ", name='" + name + '\'' + '}'; } }
3.3 创建DAO组件
StudentDAO
package dao; import entity.Student; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; /** * @ClassName StudentDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/7 9:18 * @Version 1.0 */ public interface StudentDAO { /** * @MethodName selectAll * @Description 查询学生信息的同时也把课程的信息一并查出来 * @return: java.util.List<entity.Student> * @Author Jiangnan Cui * @Date 2022/4/16 21:55 */ List<Student> selectAll(); }
CourseDAO
package dao; import entity.Course; import java.util.List; /** * @ClassName CourseDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 21:58 * @Version 1.0 */ public interface CourseDAO { /** * @MethodName selectAll * @Description 查询课程信息的同时把学生信息也一并查出来 * @return: java.util.List<entity.Course> * @Author Jiangnan Cui * @Date 2022/4/16 21:58 */ List<Course> selectAll(); }
3.4 创建mapper配置文件
StudentDAOMapper
<?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="dao.StudentDAO"> <!-- id = "接口中的方法名" parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用,引用类型使用类的全限定名(包名.类名) resultType = "返回实体类对象:包.类名" 处理结果集 自动封装 注意: 1.sql语句后不要出现";"号 2.Mybatis框架在执行插入(insert)、更新(update)操作时,默认不允许插入或修改NULL值到数据库中, 要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写)),例如:#{age,jdbcType=INTEGER} --> <resultMap id="studentMap" type="entity.Student"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="age" property="age"/> <!-- 封装课程信息 一对多 collection类型 --> <collection property="courses" javaType="list" ofType="entity.Course"> <id column="cid" property="id"/> <result column="cname" property="name"/> </collection> </resultMap> <!--selectAll--> <select id="selectAll" resultMap="studentMap"> SELECT s.id,s.name,s.age, c.id cid,c.name cname FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` </select> </mapper>
CourseDAOMapper
<?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="dao.CourseDAO"> <!-- id = "接口中的方法名" parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用,引用类型使用类的全限定名(包名.类名) resultType = "返回实体类对象:包.类名" 处理结果集 自动封装 注意: 1.sql语句后不要出现";"号 2.Mybatis框架在执行插入(insert)、更新(update)操作时,默认不允许插入或修改NULL值到数据库中, 要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写)),例如:#{age,jdbcType=INTEGER} --> <resultMap id="courseMap" type="entity.Course"> <id column="id" property="id"/> <result column="name" property="name"/> <!-- 封装学生信息 一对多 collection类型 --> <collection property="students" javaType="list" ofType="entity.Student"> <id column="sid" property="id"/> <result column="sname" property="name"/> <result column="age" property="age"/> </collection> </resultMap> <!--selectAll--> <select id="selectAll" resultMap="courseMap"> SELECT c.id,c.name, s.id sid,s.name sname,s.age FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` </select> </mapper>
3.5 mybatis-config.xml配置mapper
<mappers> <mapper resource="mapper/StudentDAOMapper.xml"/> <mapper resource="mapper/CourseDAOMapper.xml"/> </mappers>
3.6 测试连接查询
package test; import dao.*; import entity.Course; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import util.MybatisUtil; /** * @ClassName TestDAO * @Description TODO * @Author Jiangnan Cui * @Date 2022/4/16 16:37 * @Version 1.0 */ public class TestDAO { /** * @MethodName testStudentDAO * @Description 测试StudentDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 22:05 */ @Test public void testStudentDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); studentDAO.selectAll().forEach(student -> System.out.println("student = " + student + " " + student.getCourses())); MybatisUtil.close(); } /** * @MethodName testCourseDAO * @Description 测试CourseDAO组件连接查询 * @Author Jiangnan Cui * @Date 2022/4/16 22:12 */ @Test public void testCourseDAO(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); CourseDAO courseDAO = sqlSession.getMapper(CourseDAO.class); courseDAO.selectAll().forEach(course -> System.out.println("course = " + course + " " + course.getStudents())); MybatisUtil.close(); } }
测试结果:
(1)StudentDAO
DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==> Preparing: SELECT s.id,s.name,s.age, c.id cid,c.name cname FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll <== Total: 5 student = Student{id='11', name='小明', age=23} [Course{id='1', name='语文'}, Course{id='2', name='数学'}, Course{id='3', name='政治'}] student = Student{id='22', name='小王', age=23} [Course{id='1', name='语文'}, Course{id='3', name='政治'}]
(2)CourseDAO
[DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==> Preparing: SELECT c.id,c.name, s.id sid,s.name sname,s.age FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==> Parameters: [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll <== Total: 5 course = Course{id='1', name='语文'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}] course = Course{id='2', name='数学'} [Student{id='11', name='小明', age=23}] course = Course{id='3', name='政治'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}]