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
- Tool class (same as before, unchanged);
- Create an entity class (attribute names should correspond to database column names);
- Create interfaces;
- Create the package and xml configuration file corresponding to the interface under the resources package;
- Bind the created interface in the mybatis-config.xml core configuration file.
- 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.