Mybatis executes many to one queries

01? 2022-02-13 07:30:05 阅读数:313

mybatis executes queries

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.StudentMapper"/>
</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

package pojo;
public class Student {
private int id;
private String name;
// Students associate with a teacher
private Teacher teacher;
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 Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
}
Copy code 
package pojo;
import lombok.Data;
public class Teacher {
private int id;
private String name;
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;
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
}
Copy code 

4、 establish StudentMapper Interface with

package dao;
import pojo.Student;
import java.util.List;
public interface StudentMapper {
// Multiple students to one teacher
// Find all student information and corresponding teachers
List<Student> getStudents();
}
Copy code 

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

Method 1: nested query by result

 <select id="getStudents" resultMap="Students">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid=t.id
</select>
<resultMap id="Students" type="Student">
<!-- property Corresponding entity class name ,column Corresponding database column name -->
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<!-- Complex properties need to be handled separately
Object use association
Collective collection-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
Copy code 

Mode two 、 Subquery

<!-- Subquery -->
<select id="getStudents" resultMap="Student2">
select * from student
</select>
<resultMap id="Student2" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- Complex properties need to be handled separately
Object use association
Collective collection-->
<!-- property Is the name of the corresponding entity class ,javaType return type ,getTeacher Bound for subquery id-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select name from teacher where id=#{id}
</select>
Copy code 

6、 Create a test class test

package dao;
import org.apache.ibatis.session.SqlSession;
import pojo.Student;
import until.MyBatisUntil;
import java.util.List;
public class Test {
@org.junit.Test
public void test(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
}
Copy code 

 Wechat screenshot _20211221174341.png

The query is successful


 

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