Explanation of many-to-one and one-to-many relationships: taking multiple students to one teacher as an example
1. Regarding students: Use the key “association” association to associate multiple students with one teacher ———–many-to-one
2. For teachers: Use the keyword “collection”, that is, a teacher has many students (collection)———one-to-many
1. Create database
create table teacher(
id int not null primary key ,
name varchar(50) not null
)ENGINE =INNODB DEFAULT CHARSET=utf8;
insert into teacher(id,name) values (1001,"Teacher Liu"), (1002,"Teacher Ye"), (1003,"Teacher Zhang");
create table student(
id int not null primary key,
name varchar(50) not null,
age int not null ,
tid int default null,
constraint `fk` foreign key (`tid`) references `teacher`(`id`)
)ENGINE =INNODB DEFAULT CHARSET=utf8 collate utf8_general_ci
insert into student(id,name,age,tid) values (1201,"Wang Dongxue",20,1001),
(1202,"好平平",19,1001),
(1203,"Liu Meiling",18,1001),
(1204,"Wang Haihua",32,1001),
(1205,"Zhang Li",34,1001),
(1206,"Jiang Feng",45,1001),
(1207,"Ren Li",67,1001);
insert into student(id,name,age,tid) values (1201,"Wang Dongxue",20,1001),
(1202,"好平平",19,1001),
(1203,"Liu Meiling",18,1001),
(1204,"Wang Haihua",32,1001),
(1205,"Zhang Li",34,1001),
(1206,"Jiang Feng",45,1001),
(1207,"Ren Li",67,1001);
\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t
insert into student(id,name,age,tid) values (1301,"Bruce Lee",20,1002),
(1302,"Wang Meiling",19,1002),
(1303,"和小",18,1002),
(1304,"Wang Dandan",32,1002),
(1305,"Houtiantian",34,1002),
(1306,"Li Daming",45,1002),
(1307,"Li Tie",43,1002);
insert into student(id,name,age,tid) values (1401,"王小梅",20,1003),
(1402,"Li Daniu",19,1003),
(1403,"Yang Yang",18,1003),
(1404,"Wang Erxiao",32,1003),
(1405,"Li Fenli",34,1003),
(1406,"Niu Xiaotong",45,1003),
(1407,"He Xiaoming",23,1003);
2. Many-to-one and one-to-many
Step 1: Guide the package
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
Step 2: Create entity classes Teacher and Student
Teacher
package ljg.com.pojo;
import lombok.*;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
public class Teacher {
private int id;
private String name;
//A teacher has multiple students
private List<Student> students;
}
Student
package ljg.com.pojo;
import lombok.*;
@Data//Improving the simplicity of the code, you can eliminate a large number of get(), set(), toString() and other methods in the entity class
@AllArgsConstructor//Annotated version of full parameters
@NoArgsConstructor//Annotation version has no parameters
@Setter
@Getter
public class Student {
private int id;
private String name;
private int age;
//Students need to associate with a teacher many-to-one
private teacher teacher;
//One-to-many, one teacher has multiple students
//There is only one teacher
private int tid;
}
Step 3: Establish mapper interface
package ljg.com.mapper;
import ljg.com.pojo.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
//@Select mybatis annotation is used to map sql statements
@Select("select * from student where id = #{sid}")
// The function of @Param is to name the parameters.
// For example, a certain method A (int id) in mapper,
// After adding annotation A (@Param("sid") int id),
// That is to say, the outside wants to take out the incoming id value,
// Just take its parameter name sid.
// Pass the parameter value into the SQL statement, and use #{sid} to get the value and assign it to the SQL parameter.
Student getStudent(@Param("sid") int id);
List<Student> getStudents();
List<Student> getStudents2();
}
package ljg.com.mapper;
import ljg.com.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
@Select("select * from teacher where id = #{tid}")
Teacher getTeacher(@Param("tid") int id);
//test
List<Teacher> getTeacher3();
// Get a teacher. Get all the information of the specified teacher and the teacher.
Teacher getTeacher1(@Param("tid") int id);
// subquery
Teacher getTeacher2(@Param("tid") int id);
}
Step 4: Create Mapper.xml file
StudentMapper.xml (many-to-one)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ljg.com.mapper.StudentMapper">
<!--According to the result nesting process, joint table query-->
<select id="getStudents2" resultMap="StudentTeacher2" >
select s.id as sid,s.name as sname,age as sage,t.name as tname
from student s,teacher t
where s.tid=t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<!--The student's attribute id corresponds to sid one-to-one -->
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<association property="teacher" javaType="Teacher">
<!--Teacher is a complex type. After the results are processed, map the corresponding fields in the teacher-->
<result property="name" column="tname"/>
</association>
</resultMap>
<!--/
1. Query all student information
2. Find the corresponding teacher based on the queried student’s tid
-->
<!-- Processed according to query nesting -->
<select id="getStudents" resultMap="StudentTeacher">
select * from student
</select>
<!--Result set mapping associates the two to solve the problem of inconsistent attribute names and field names-->
<resultMap id="StudentTeacher" type="Student">
<!--Primary key, one-to-one correspondence-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<!-- //property parameter type name
//column field name
//The type of attributes in the javaType entity class
//select nested statement -->
<!--Complex attributes need to be processed separately, object (teacher): association javaType: used to specify the type of attributes in the entity class, select is performing nested query-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--tid There is no need for one-to-one correspondence between the following ids, mybatis will infer it on its own -->
<select id="getTeacher" resultType="Teacher">
select * from Teacher where id=#{tid}
</select>
</mapper>
Description: The above code details the two methods of many-to-one, namely query nesting processing and result nesting processing.
TeacherMapper.xml (one-to-many)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ljg.com.mapper.TeacherMapper">
<select id="getTeacher3" resultType="Teacher">
select * from xsgl2.teacher;
</select>
<!--Nesting according to results-->
<select id="getTeacher1" resultMap="TeacherStudent">
select s.id sid, s.name sname,s.age sage,t.id tid,t.name tname
from teacher t ,student s
where t.id=s.tid and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--The type of the attribute in javaType, the generic information in the collection, we use ofType to obtain-->
<!--There is no need to write javaType because each value is taken out directly and then corresponded one to one-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="age" column="sage"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!--Query nesting processing-->
<select id="getTeacher2" resultMap="TeacherStuden2">
select * from xsgl2.teacher where id =#{tid}
</select>
<resultMap id="TeacherStuden2" type="Teacher">
<!--
private List<Student> students;
list is a Java type using javaType="ArrayList"
<Student> is generic data, use ofType="Student
select="getByTidOfStudents" Query the corresponding students according to the teacher's ID
tid=#{tid} This result is the teacher’s ID
-->
<!--It is originally a collection. The collection object and generic information are written out-->
<collection property="students" javaType="ArrayList" ofType="Student"
select="getByTidOfStudents" column="id"/>
</resultMap>
<!--Two statement queries and then use a subquery to check getByTidOfStudents-->
<select id="getByTidOfStudents" resultType="Student">
select * from xsgl2.student where tid=#{tid}
</select>
</mapper>
Description: The above code details the two methods of many-to-one, namely query nesting processing and result nesting processing.
Step 5: Test class
package ljg.com.test;
import ljg.com.mapper.StudentMapper;
import ljg.com.mapper.TeacherMapper;
import ljg.com.pojo.Student;
import ljg.com.pojo.Teacher;
import ljg.com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.util.List;
public class YouTest {
//Match the environment
@Test
public void getteacher(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
Student student = mapper.getStudent(1201);
System.out.println(student);
TeacherMapper mapper1 = salSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper1.getTeacher(1001);
System.out.println(teacher);
}
//many to one
//The first method is query nesting processing
@Test
public void getstudent(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
}
//The second method results in nested query
@Test
public void getstudent1(){
SqlSession salSession = MybatisUtils.getSqlSession();
StudentMapper mapper = salSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents2();
for (Student student : students) {
System.out.println(student);
}
}
@Test
public void getTeacher1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher1(1002);
System.out.println(teacher);
sqlSession.close();
}
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1002);
System.out.println(teacher);
sqlSession.close();
}
@Test
public void getTeacher3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
for (Teacher teacher :sqlSession.getMapper(TeacherMapper.class).getTeacher3()){
System.out.println(teacher);
}
sqlSession.close();
}
}
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java skill treeUsing JDBC to operate the databaseDatabase operation 139365 people are learning the system