Mybatis one to many query

01? 2022-02-13 07:30:03 阅读数:441

mybatis query

1、 establish maven engineering , build mybatis Framework environment

 Wechat screenshot _20211221170010.png

mybatis-config.xml Configuration files and db.properties To configure

<?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>
<!-- Import external profile -->
<properties resource="db.properties"/>
<!-- Configure log files , Be careful not to write wrong -->
<settings>
<!-- Standard log factory -->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<!-- 1、 You can alias entity classes , Entity classes are rarely used -->
<!-- <typeAlias type="pojo.User" alias="User"/>-->
<!-- 2、 You can alias entity classes , The default alias is lowercase
If you have any notes , Take annotation as alias -->
<package name="pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!-- Connect to database , The database USES mysql8.0-->
<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>
<!-- register StudentMapper.xml-->
<mappers>
<mapper class="dao.TeacherMapper"/>
</mappers>
</configuration>
Copy code 

Database use MySQL8.0

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/ Table name ?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username= Database user name
password= Database password
Copy code 

pom.xml rely on

 <dependencies>
<!-- mysql drive -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<!--mybatis what is needed jar package -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
Copy code 

2、 establish stdent and teacher Data sheet

CREATE TABLE student (

id int NOT NULL AUTO_INCREMENT,

name varchar(20) ,

tid int NULL DEFAULT NULL,

PRIMARY KEY (id) USING BTREE,

INDEX fktid(tid) USING BTREE,

CONSTRAINT fktid FOREIGN KEY (tid) REFERENCES teacher (id) ON DELETE RESTRICT ON UPDATE RESTRICT

)

CREATE TABLE teacher (

id int NOT NULL AUTO_INCREMENT,

name varchar(20) ,

PRIMARY KEY (id) USING BTREE

)

3、 Create the corresponding entity class , Students

package pojo;
public class Student {
private int id;
private String name;
public Student() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Student(int id, String name) {
this.id = id;
this.name = name;
}
}
Copy code 

Teacher entity class , One teacher corresponds to many students

package pojo;
import lombok.Data;
import java.util.List;
public class Teacher {
private int id;
private String name;
// One teacher corresponds to many students
List<Student> students;
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = students;
}
public Teacher() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Copy code 

4、 establish TeacherMapper Interface

package dao;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import pojo.Teacher;
public interface TeacherMapper {
// Get the teacher information and all students corresponding to the teacher
// Mode one 、 nested queries
Teacher getTeacher(@Param("id") int id);
// Mode two 、 Subquery
Teacher getTeacher2(@Param("id") int id);
}
Copy code 

5、 establish TeacherMapper.xml The configuration file , write in sql Statement and configuration

<?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="dao.TeacherMapper">
<!-- nested queries -->
<select id="getTeacher" resultType="Teacher" resultMap="getTeachers">
select t.id tid,t.name tname,s.name sname,s.id sid
from teacher t,student s
where t.id=s.tid and t.id=#{id}
</select>
<resultMap id="getTeachers" type="Teacher">
<result column="tid" property="id"/>
<result column="tname" property="name"/>
<!-- Complex properties need to be handled separately
Object use association
Collective collection
The object here is a collection , use collection-->
<!-- javaType Specify the property type , Generics in collections use ofType-->
<collection property="students" ofType="Student">
<result column="sname" property="name"/>
<result column="sid" property="id"/>
</collection>
</resultMap>
<!-- Subquery -->
<select id="getTeacher2" resultMap="getTeacher2" resultType="Teacher">
select * from teacher where id=#{id}
</select>
<resultMap id="getTeacher2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudents" column="id"/>
</resultMap>
<select id="getStudents" resultType="Student">
select * from student where tid=#{tid}
</select>
</mapper>
Copy code 

6、 Create a test class test

package dao;
import org.apache.ibatis.session.SqlSession;
import pojo.Student;
import pojo.Teacher;
import until.MyBatisUntil;
import java.util.List;
public class Test {
@org.junit.Test
public void test2(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
@org.junit.Test
public void test3(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
}
Copy code 

 Wechat screenshot _20211222225521.png

The query is successful


 

copyright:author[01?],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130730006144.html