mybatis learning notes-05


This article is to write some relatively complex SQL statements.

1. Complex environment construction

This environment construction is not much different from the environment construction in my first mybatis article, but some knowledge points mentioned in my previous articles will be used here. If you haven't read my previous articles, you can read my previous articles.

step

  1. Tool class (same as before, unchanged);
  2. Create an entity class (attribute names should correspond to database column names);
  3. Create interfaces;
  4. Create the package and xml configuration file corresponding to the interface under the resources package;
  5. Bind the created interface in the mybatis-config.xml core configuration file.
  6. Set alias

    After the environment is built, you can start the next operation.

2. Many to one processing

Many to one: for example, many students have the same teacher. We query students and teachers from the aspect of students.

2.1 entity class

Entity class - Students

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    //The teacher's id is to be queried from the teacher's table, so it is given to the object that returns the teacher
    private Teacher teacher;
}

Entity class - teacher

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

2.2. Nested processing by query

StudentMapper interface

public interface StudentMapper {
    List<Student> selectStudentAndTeacher();
}

StudentMapper.xml configuration file

<mapper namespace="com.ZXF.dao.StudentMapper">
<!--    Nested by query=========================================-->
<select id="selectStudentAndTeacher" resultMap="StudentTeacher">
    select * from student;
</select>
    <resultMap id="StudentTeacher" type="Student">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
    <!--  Complex attributes are handled separately. If you want to deal with:
			Objects: with association
			Sets: with collection
		javaType: Because the object we want to deal with is a class, we pass this class in
		-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
<select id="getTeacher" resultType="Teacher">
    <!--For a single parameter,#{id} the parameter name can be written freely, but it is recommended to be consistent -- >
    select * from teacher where id=#{id};
</select>

test

@Test
public  void  selectStudentAndTeacher(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> students = mapper.selectStudentAndTeacher();
    for (Student student : students) {
        System.out.println(student);
    }
    sqlSession.close();
}

test result

2.3. Nested processing according to results

StudentMapper interface

public interface StudentMapper {
    List<Student> selectStudentAndTeacher2();
}

StudentMapper.xml configuration file

<!--    Nesting through results=========================================-->
<select id="selectStudentAndTeacher2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t where s.tid=t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result column="sid" property="id"/>
    <result column="sname" property="name"/>
    <association property="teacher" javaType="Teacher">
        <result column="tname" property="name"/>
    </association>
</resultMap>

test

@Test
public  void  selectStudentAndTeacher2(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> students2 = mapper.selectStudentAndTeacher2();
    for (Student student : students2) {
        System.out.println(student);
    }
    sqlSession.close();
}

test result

3. One to many processing

One to many: for example, a teacher teaches many students. We inquire about students and teachers from the aspect of teachers.

3.1 entity class

Entity class - Students

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
}

Entity class - teacher

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    //Here is to check the students from the teacher's direction and return to the collection
    private List<Student> students;
}

3.2. Nested query by result

TeacherMapper interface

public interface TeacherMapper {
    //Query teachers and students
    List<Teacher> getTeacher(int id);
}

TeacherMapper.xml configuration file

<select id="getTeacher" resultMap="TeacherStudent">
    select s.id sid,s.name sname,t.name tname,t.id tid
    from student s,teacher t
    where t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--        javaType :  Specifies the type of the property
            ofType :  Generic information in the collection
                -->
    <collection property="students" ofType="Student">
        <result column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="tid" property="tid"/>
    </collection>
</resultMap>

test

@Test
    public void getTeacherStudent(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> teacher = mapper.getTeacher(1);
    for (Teacher teacher1 : teacher) {
        System.out.println(teacher1);
    }
    sqlSession.close();
}

test result

3.3. Nested processing by query

TeacherMapper interface

public interface TeacherMapper {
    //Query teachers and students
    List<Teacher> getTeacher2(int id);
}

TeacherMapper.xml configuration file

<select id="getTeacher2" resultMap="TeacherStudent2">
    select id,name from teacher where id=#{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentById" column="id">
    </collection>
</resultMap>
<select id="getStudentById" resultType="Student">
    select * from student where tid=#{id}
</select>
</

test

@Test
    public void getTeacherStudent2(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> teacher2 = mapper.getTeacher2(1);
    for (Teacher teacher : teacher2) {
        System.out.println(teacher);
    }
    sqlSession.close();
}

test result

The query result here has a problem that the teacher's id is 0! I don't know how to solve [cry].

4. Distinction

  • Objects: Using association
  • Collection: using collection
  • javaType: Specifies the type of the attribute
  • ofType: generic information in the collection
  • Whether it is the property of association or collection, the passed in parameter is the property name of the association or collection.

There are also some details written in the comments of the code,

This is the fifth of my mybatis self-study notes. If you haven't read my previous articles, you can see the links of the first four articles ↓
mybatis learning notes-04
mybatis learning notes-03
mybatis learning notes-02
mybatis learning notes-01

This article ends here. Self study is not easy. If you don't write it in detail, you can read it in combination with the official documents or exchange it in the comment area. This article can be optimized. I hope the boss can give you advice.

Tags: Java Database Maven Mybatis

Posted on Sat, 23 Oct 2021 02:38:18 -0400 by Jim