For one more

Save many to one

Taking employee and department as an example, employee and department have a many-to-one relationship

Note: Save one side first, then save many sides

/** ** employee */
public class Employee {
    private Long id;
    private String name;
    private Integer age;
    private Dept dept;
    // Getter, setter, toString
}
Copy the code

/** * Department */
public class Dept {
    private Long id;
    private String name;
    // Getter, setter, toString
}
Copy the code

Deptmapper. XML: The primary key ID must be returned; otherwise, the employee cannot associate with the department

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_dept(name) VALUES (#{name})
</insert>
Copy the code

EmployeeMapper.xml

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_employee(name, age, dept_id) VALUES (#{name},#{age},#{dept.id})
</insert>
Copy the code

The tests are saved many-to-one

@Test
public void testSave(a) throws Exception{
    SqlSession sqlSession = MybatisUtils.openSession();
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    Dept dept = new Dept();
    dept.setName(Development department);
    // Save one side first
    deptMapper.save(dept);
    Employee e1 = new Employee();
    e1.setName("Zhang");
    e1.setAge(22);
    e1.setDept(dept);
    Employee e2 = new Employee();
    e2.setName("Bill");
    e2.setAge(23);
    e2.setDept(dept);
    // Save multiple
    employeeMapper.save(e1);
    employeeMapper.save(e2);
    sqlSession.commit();
}
Copy the code

Many-to-one mapping of associated objects

Use the Association tag mapping in the resultMap tag

<resultMap id="employeeResultMap" type="cn.itsource._02many2one.domain.Employee">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <! JavaType ="cn. Itsource._02many2one.domain.Dept dept" javaType="cn. Itsource._02many2one.domain. Default mapping rules are invalid when you use association mapping -->
    <association property="dept" javaType="cn.itsource._02many2one.domain.Dept">
        <id column="did" property="id"/>
        <result column="dname" property="name"/>
    </association>
</resultMap>

<select id="selectAll" resultMap="employeeResultMap">
    select e.id,e.name,e.age,d.id did,d.name dname
    from t_employee e join t_dept d
    on e.dept_id = d.id
</select>
Copy the code

Many-to-one subqueries encapsulate objects

Sub-query is inefficient. You are advised to use associated query

DeptMapper.xml

<select id="selectById" resultType="cn.itsource._02many2one.domain.Dept">
    SELECT * FROM t_dept WHERE id=#{id}
</select>
Copy the code

EmployeeMapper.xml

<resultMap id="employeeResultMap2" type="cn.itsource._02many2one.domain.Employee">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <! Select * from Employee dept where dept_id = selectById; select * from Employee DEPT where dept_id = selectById;
    <association property="dept" column="dept_id"
                 select="cn.itsource._02many2one.mapper.DeptMapper.selectById"/>
</resultMap>

<select id="selectAll2" resultMap="employeeResultMap2">
    SELECT * FROM t_employee
</select>
Copy the code

More than a pair of

One to many save

Departments and employees have a one-to-many relationship

Note: Save one side first, then save many sides

/** * Department */
public class Dept implements Serializable {
    private Long id;
    private String name;
    private List<Employee> employees = new ArrayList<>();
    // Getter, setter, toString
}
Copy the code

/** ** employee */
public class Dept implements Serializable {
    private Long id;
    private String name;
    private Integer age;
    // Getter, setter, toString
}
Copy the code

DeptMapper.xml

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_dept(name) VALUES (#{name})
</insert>
Copy the code

EmployeeMapper.xml

<insert id="save">
    INSERT INTO t_employee(name, age, dept_id) VALUES (#{e.name},#{e.age},#{deptId})
</insert>
Copy the code

The saved

@Test
public void testSave(a) throws Exception{
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    Dept dept = new Dept();
    dept.setName("Testing Department");
    Employee e1 = new Employee();
    e1.setName("Xiao feng.");
    e1.setAge(22);

    Employee e2 = new Employee();
    e2.setName("Clearly");
    e2.setAge(33);

    dept.getEmployees().add(e1);
    dept.getEmployees().add(e2);
    // Save one side
    deptMapper.save(dept);
    for (Employee employee : dept.getEmployees()) {
        // Save multiple
        employeeMapper.save(employee, dept.getId());
    }
    sqlSession.commit();
}
Copy the code

One-to-many associated query

One to many associative queries must be sorted, which is a bug in mybatis

<resultMap id="deptResultMap" type="cn.itsource._03one2many.domain.Dept">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <! Note: The default mapping rule is invalid after the collection mapping is used -->
    <collection property="employees" ofType="cn.itsource._03one2many.domain.Employee">
        <id column="eid" property="id"/>
        <result column="ename" property="name"/>
        <result column="eage" property="age"/>
    </collection>
</resultMap>

<select id="selectAll" resultMap="deptResultMap">
    SELECT d.id,d.name,e.id eid,e.name ename,e.age eage
    FROM t_dept d JOIN t_employee e
    ON d.id = e.dept_id
    ORDER BY d.id
</select>
Copy the code

Test a one-to-many associative query

/** * Associated query */
@Test
public void testQuery(a) throws Exception {
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    List<Dept> depts = deptMapper.selectAll();
    for(Dept dept : depts) { System.out.println(dept); }}Copy the code

One to many subqueries

EmployeeMapper.xml

<select id="selectByDeptId" resultType="cn.itsource._03one2many.domain.Employee">
    SELECT * FROM t_employee WHERE dept_id=#{deptId}
</select>
Copy the code

DeptMapper.xml

<resultMap id="deptResultMap2" type="cn.itsource._03one2many.domain.Dept">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection property="employees" ofType="cn.itsource._03one2many.domain.Employee"
                column="id"
                select="cn.itsource._03one2many.mapper.EmployeeMapper.selectByDeptId"/>
</resultMap>

<select id="selectAll2" resultMap="deptResultMap2">
    SELECT * FROM t_dept
</select>
Copy the code

Test one to many subqueries

/** * subquery *@throws Exception
 */
@Test
public void testQuery2(a) throws Exception {
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    List<Dept> depts = deptMapper.selectAll2();
    for(Dept dept : depts) { System.out.println(dept); }}Copy the code