Mybatis入门（三）

Mybatis之对象关系

　　分为一对一、一对多、多对一和多对多。关系详解在MySQL数据库多表查询时有解释。

　　这里详细介绍多对一和多对多的增删改查。

多对一、一对多

目录结构

代码示例：

@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 + '\'' +
                '}';
    }
}

Department

@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 +
                '}';
    }
}

Employee

public interface DepartmentMapper {
    void save(Department d);
    Department findByDeptId(Integer d_id);

    Department findByDeptId2(Integer d_id);
}

DepartmentMapper

<?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>

DepartmentMapper.xml

public interface EmployeeMapper {
    void save(Employee e);

    List<Employee> find();
    List<Employee> find2();
    Employee findOne(Integer id);

    Employee findOne2(Integer id);
}

EmployeeMapper

<?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>

EmployeeMapper.xml

public interface IDepartmentService {
    void save(Department d);
    Department findByDeptId(Integer d_id);
    Department findByDeptId2(Integer d_id);
}

IDepartmentService

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;
    }
}

DepartmentServiceImpl

public interface IEmployeeService {
    void save(Employee e);

    Employee findOne(Integer id);
    List<Employee> find();
    List<Employee> find2();

    Employee findOne2(Integer id);
}

IEmployeeService

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;

    }
}

EmployeeServiceImpl

<?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>

mybatis.xml

代码中知识点：额外sql查询（外联查询），关联查询，延迟加载

多对多

目录结构

代码示例：

@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 + '\'' +
                '}';
    }
}

Student

@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 + '\'' +
                '}';
    }
}

Teacher

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);
}

StudentMapper

<?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>

StudentMapper.xml

public interface TeacherMapper {
    void save(Teacher teacher);
}

TeacherMapper

<?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>

TeacherMapper.xml

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);
}

IStudentService

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();
    }


}

StudentServiceImpl

public interface ITeacherService {
    void save(Teacher teacher);
}

ITeacherService

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();
    }
}

TeacherServiceImpl

posted on 2019-09-02 08:59 幸福的小耗子阅读(157) 评论(0) 编辑收藏举报