Mybatis之对象关系
分为一对一、一对多、多对一和多对多。关系详解在MySQL数据库多表查询时有解释。
这里详细介绍多对一和多对多的增删改查。
多对一、一对多
目录结构
代码示例:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@Setter@Getter@NoArgsConstructor@AllArgsConstructor public class Department { private Integer d_id; private String d_name; private List<Employee> employees = new ArrayList<>(); public String toString() { return "Department{" + "d_id=" + d_id + ", d_name='" + d_name + '\'' + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@Setter @Getter @NoArgsConstructor @AllArgsConstructor public class Employee { private String e_name; private Integer e_id; private Department dept; @Override public String toString() { return "Employee{" + "e_name='" + e_name + '\'' + ", e_id=" + e_id + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface DepartmentMapper { void save(Department d); Department findByDeptId(Integer d_id); Department findByDeptId2(Integer d_id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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.many2one.mapper.DepartmentMapper"> <!--增加部门--> <insert id="save" useGeneratedKeys="true" keyColumn="d_id" keyProperty="d_id"> insert into dept(d_id,d_name) values(null,#{d_name}) </insert> <select id="get" resultType="com.many2one.bean.Department"> select * from dept where d_id=#{dept_id} </select> <!--一对多查询--> <resultMap id="ByDeptId" type="com.many2one.bean.Department"> <result property="d_id" column="d_id"/> <result property="d_name" column="d_name"/> <!--collection:用户处理集合类型的关联对象时使用 property:把结果集封装到哪个属性 ofType:集合的泛型 select:发送sql语句的statement column:查询到的结果的哪一列作为参数来查询关联信息 --> <collection property="employees" select="com.many2one.mapper.EmployeeMapper.findByDid" ofType="com.many2one.bean.Employee" column="d_id"/> </resultMap> <!--额外sql查询--> <select id="findByDeptId" resultMap="ByDeptId"> select * from dept where d_id=#{id} </select> <!--关联查询--> <resultMap id="ByDeptId2" type="com.many2one.bean.Department"> <result property="d_id" column="d_id"/> <result property="d_name" column="d_name"/> <!--property:关联属性的名字 ofType:关联属性的泛型 --> <collection property="employees" ofType="com.many2one.bean.Employee"> <result property="e_id" column="e_id"/> <result property="e_name" column="e_name"/> </collection> </resultMap> <select id="findByDeptId2" resultMap="ByDeptId2"> select * from dept d,employee e where d.d_id=e.dept_id and d.d_id=#{id} </select> </mapper>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface EmployeeMapper { void save(Employee e); List<Employee> find(); List<Employee> find2(); Employee findOne(Integer id); Employee findOne2(Integer id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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.many2one.mapper.EmployeeMapper"> <!--增加员工--> <insert id="save"> insert into employee(e_name,dept_id) values(#{e_name},#{dept.d_id}) </insert> <!--寻找员工,外连查询--> <resultMap id="employeeMapper" type="com.many2one.bean.Employee"> <result column="e_id" property="e_id"/> <result column="e_name" property="e_name"/> <!--association:处理单一的属性关联关系 select:发送哪条sql语句的statement javaType:关联属性的类型(可以省略) property:把sql语句的查询结果封装到哪个属性集 column:把哪个列作为结果集去查询 --> <association property="dept" column="dept_id" javaType="com.many2one.bean.Department" select="com.many2one.mapper.DepartmentMapper.get"/> </resultMap> <select id="find" resultMap="employeeMapper"> select * from employee </select> <select id="findOne" resultMap="employeeMapper"> select * from employee where e_id=#{id} </select> <!--寻找员工,关联查询--> <resultMap id="employees" type="com.many2one.bean.Employee"> <result property="e_id" column="e_id"/> <result property="e_name" column="e_name"/> <!--使用封装的方法 property:封装到哪个属性 javaType:关联对象的类型 --> <association property="dept" javaType="com.many2one.bean.Department"> <result property="d_id" column="d_id"/> <result property="d_name" column="d_name"/> </association> </resultMap> <select id="findOne2" resultMap="employees"> select e.e_id,e.e_name,d.d_id,d.d_name from employee e,dept d where e.dept_id=d.d_id and e.e_id=#{id} </select> <select id="find2" resultMap="employees"> select e.e_id,e.e_name,d.d_id,d.d_name from employee e,dept d where e.dept_id=d.d_id </select> <!--一对多查询外连查询--> <select id="findByDid" resultType="com.many2one.bean.Employee"> select e.e_id,e.e_name from employee e where e.dept_id=#{d_id} </select> </mapper>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface IDepartmentService { void save(Department d); Department findByDeptId(Integer d_id); Department findByDeptId2(Integer d_id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class DepartmentServiceImpl implements IDepartmentService { @Override public void save(Department d) { SqlSession session = MybatisUtil.getSession(); DepartmentMapper dept = session.getMapper(DepartmentMapper.class); dept.save(d); session.commit(); session.close(); } @Override public Department findByDeptId(Integer d_id) { SqlSession session = MybatisUtil.getSession(); DepartmentMapper dept = session.getMapper(DepartmentMapper.class); Department department = dept.findByDeptId(d_id); return department; } @Override public Department findByDeptId2(Integer d_id) { SqlSession session = MybatisUtil.getSession(); DepartmentMapper dept = session.getMapper(DepartmentMapper.class); Department department = dept.findByDeptId2(d_id); return department; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface IEmployeeService { void save(Employee e); Employee findOne(Integer id); List<Employee> find(); List<Employee> find2(); Employee findOne2(Integer id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class EmployeeServiceImpl implements IEmployeeService { @Override public void save(Employee e) { SqlSession session = MybatisUtil.getSession(); EmployeeMapper emp = session.getMapper(EmployeeMapper.class); emp.save(e); session.commit(); session.close(); } @Override public Employee findOne(Integer id) { SqlSession session = MybatisUtil.getSession(); EmployeeMapper emp = session.getMapper(EmployeeMapper.class); Employee employee = emp.findOne(id); session.commit(); session.close(); return employee; } @Override public List<Employee> find() { SqlSession session = MybatisUtil.getSession(); EmployeeMapper emp = session.getMapper(EmployeeMapper.class); List<Employee> list = emp.find(); session.commit(); session.close(); return list; } @Override public List<Employee> find2() { SqlSession session = MybatisUtil.getSession(); EmployeeMapper emp = session.getMapper(EmployeeMapper.class); List<Employee> list = emp.find2(); session.commit(); session.close(); return list; } @Override public Employee findOne2(Integer id) { SqlSession session = MybatisUtil.getSession(); EmployeeMapper emp = session.getMapper(EmployeeMapper.class); Employee employee = emp.findOne2(id); session.commit(); session.close(); return employee; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!--引入连接数据库的属性配置文件--> <properties resource="db.properties"/> <settings> <!--打开延迟加载的全局开关--> <setting name="lazyLoadingEnabled" value="true"/> <!--不要积极的去查询关联的对象--> <setting name="aggressiveLazyLoading" value="false"/> <!--覆盖延迟加载的方法--> <setting name="lazyLoadTriggerMethods" value=""/> </settings> <!--配置别名 --> <typeAliases> <package name="com.many2one"/> <package name="com.many2many"/> </typeAliases> <!-- 配置环境 default:development启动时使用哪个环境 --> <environments default="development"> <environment id="development"> <!-- 使用mysql的事物管理器来管理事务--> <transactionManager type="JDBC"/> <!--数据源--> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!--关联mapper文件--> <mappers> <mapper resource="com/many2one/mapper/DepartmentMapper.xml"/> <mapper resource="com/many2one/mapper/EmployeeMapper.xml"/> <mapper resource="com/many2many/mapper/StudentMapper.xml"/> <mapper resource="com/many2many/mapper/TeacherMapper.xml"/> </mappers> </configuration>
代码中知识点:额外sql查询(外联查询),关联查询,延迟加载
多对多
目录结构
代码示例:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@Getter @Setter @NoArgsConstructor @AllArgsConstructor public class Student { private Integer stu_id; private String stu_name; private List<Teacher> teachers = new ArrayList<>(); @Override public String toString() { return "Student{" + "stu_id=" + stu_id + ", stu_name='" + stu_name + '\'' + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@Getter@Setter@NoArgsConstructor@AllArgsConstructor public class Teacher { private Integer tea_id; private String tea_name; @Override public String toString() { return "Teacher{" + "tea_id='" + tea_id + '\'' + ", tea_name='" + tea_name + '\'' + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface StudentMapper { void save(Student stu); void saveRelation(@Param("stu_id")Integer stu_id,@Param("tea_id")Integer tea_id); Student findByStuId(Integer stu_id); Student findByStuId2(Integer stu_id); Student findByStuColumn(Student student); void deleteStuById(Integer stu_id); void deleteRelationById(Integer stu_id); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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.many2many.mapper.StudentMapper"> <!--保存学生--> <insert id="save" useGeneratedKeys="true" keyProperty="stu_id"> insert into student(stu_name) values(#{stu_name}) </insert> <!--保存关系--> <insert id="saveRelation"> insert into teacher_student(tea_id,stu_id) values(#{tea_id},#{stu_id}) </insert> <!--查询学生--> <resultMap id="ByStuIdMap" type="com.many2many.bean.Student"> <result property="stu_id" column="stu_id"/> <result property="stu_name" column="stu_name"/> <!--查询关联的老师信息--> <collection property="teachers" select="com.many2many.mapper.TeacherMapper.findByStuId" column="stu_id" ofType="com.many2many.bean.Teacher"/> </resultMap> <select id="findByStuId" resultMap="ByStuIdMap"> select stu_id,stu_name from student where stu_id=#{stu_id} </select> <select id="findByStuColumn" resultMap="ByStuIdMap"> select stu_id,stu_name from student <where> <if test="stu_id!=null"> stu_id=#{stu_id} </if> <if test="stu_name!=null"> and stu_name=#{stu_name} </if> </where> </select> <!--使用关联查询--> <resultMap id="ByStuIdMap2" type="com.many2many.bean.Student"> <result property="stu_id" column="stu_id"/> <result property="stu_name" column="stu_name"/> <collection property="teachers" ofType="com.many2many.bean.Teacher"> <result property="tea_id" column="tea_id"/> <result property="tea_name" column="tea_name"/> </collection> </resultMap> <select id="findByStuId2" resultMap="ByStuIdMap2"> select s.stu_id,s.stu_name,t.tea_id,t.tea_name from student s,teacher t,teacher_student t_s where s.stu_id=#{stu_id} and s.stu_id=t_s.stu_id and t_s.tea_id=t.tea_id </select> <!--删除--> <delete id="deleteStuById"> delete from student where stu_id=#{stu_id} </delete> <!--删除对象的关联关系--> <delete id="deleteRelationById"> delete from teacher_student where stu_id=#{stu_id} </delete> </mapper>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface TeacherMapper { void save(Teacher teacher); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?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.many2many.mapper.TeacherMapper"> <!--保存老师--> <insert id="save" useGeneratedKeys="true" keyProperty="tea_id"> insert into teacher(tea_name) values(#{tea_name}) </insert> <!--外连查询--> <select id="findByStuId" resultType="com.many2many.bean.Teacher"> select t.tea_id,t.tea_name from teacher t,teacher_student t_s where t.tea_id=t_s.tea_id and t_s.stu_id=#{stu_id} </select> </mapper>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface IStudentService {
void save(Student stu);
Student findByStuId(Integer stu_id);
Student findByStuId2(Integer stu_id);
Student findByStuColumn(Student student);
void deleteStuById(Integer stu_id);
}
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class StudentServiceImpl implements IStudentService { @Override public void save(Student stu) { SqlSession session = MybatisUtil.getSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); studentMapper.save(stu); for(Teacher teacher:stu.getTeachers()){ //System.out.println(stu.getStu_id()+"....."+teacher.getTea_id()); studentMapper.saveRelation(stu.getStu_id(),teacher.getTea_id()); } session.commit(); session.close(); } @Override public Student findByStuId(Integer stu_id) { SqlSession session = MybatisUtil.getSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); Student student = studentMapper.findByStuId(stu_id); session.commit(); session.close(); return student; } @Override public Student findByStuId2(Integer stu_id) { SqlSession session = MybatisUtil.getSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); Student student = studentMapper.findByStuId2(stu_id); session.commit(); session.close(); return student; } @Override public Student findByStuColumn(Student student) { SqlSession session = MybatisUtil.getSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); Student student1 = studentMapper.findByStuColumn(student); session.commit(); session.close(); return student1; } @Override public void deleteStuById(Integer stu_id) { SqlSession session = MybatisUtil.getSession(); StudentMapper studentMapper = session.getMapper(StudentMapper.class); studentMapper.deleteRelationById(stu_id); studentMapper.deleteStuById(stu_id); session.commit(); session.close(); } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public interface ITeacherService { void save(Teacher teacher); }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class TeacherServiceImpl implements ITeacherService { @Override public void save(Teacher teacher) { SqlSession session = MybatisUtil.getSession(); TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class); teacherMapper.save(teacher); session.commit(); session.close(); } }