Mapping of simple types
Returns a simple base type
Definition in interface
int getAdminCount();//How many pieces of data are returned to the database
Specific implementation in xml
<select id="getAdminCount" resultType="int">
select count(*) from admin
</select>
Here I chose a jar package for testing, which was directly used for testing without trying the main method.
@Test//How many pieces of data are returned to the data port
public void find3() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
int count = mapper.getAdminCount();
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
test result
Return multiple sets of data
In the database, to find multiple groups of data, you need to accept, and the acceptance mainly depends on the collection to accept the data returned by the database.
Definition in interface:
/*Multiple tables*/
List<Admin> getAdminList();//The returned data is in the form of a collection
//The query result is a collection, which is automatically encapsulated into a collection through List
Definition of xml in Mybatis:
<select id="getAdminList" resultType="Admin">
select * from admin
</select>
Here I chose a jar package for testing, which was directly used for testing without trying the main method.
@Test//Columns are passed and collections are returned
public void findList() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
List<Admin> adminList = mapper.getAdminList();
System.out.println(adminList);
sqlSession.commit();
sqlSession.close();
}
Here, I try to check the log. The database returns several pieces of data and prints them out
POJO object output mapping
POJO (Plain Old Java Objects)
If the column name of the table in the database is exactly the same as the attribute name in the class, Mybatis will automatically encapsulate the query results into the POJO object
If the standard hump naming is used in java, the standard underline connection naming method is also used in the database, so you can start global settings and realize automatic conversion.
<settings>
<!--Open from the classic database name to java Conversion between hump naming
user_name==>userName-->
<!--Hump naming automatic mapping-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
public User findUserInfoById(int id)
<select id="findAdminInfoById" parameterType="int" resultType="User">
select * from dmin where id=#{id}
</select>
Return of complex types
resultMap
In special cases, the column names and attribute names of the database are inconsistent, so we need to solve the problem of mapping.
So here is the use of resultMap. resultMap is the mapping of the result set. It is a kind of custom mapping
However, resultMap is mainly used for multi table Association.
Define resultMap
The labels in the resultMap mainly include id (mapped primary key) and result (mapped non primary key)
Column represents the data column name in the database, and property represents the property name defined in the class
If the attribute name in the class is consistent with the column name in the database, you don't need to write all the data in the resultMap
Write only the required data names, and you don't need to write the same.
<!--resultMap Is the mapping of the result set-->
<!--column Represents the name of the data column in the database, property Represents the property name defined in the class-->
<!--If the attribute name in the class is consistent with the column name in the database, you don't have to write all the data in the database resultMap among
Just write some links between the required data names-->
<resultMap id="one" type="Admin">
<id column="id" property="id"></id>
<result column="account" property="account"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex"></result>
</resultMap>
- The id attribute of the resutlMap is the unique identifier of the resutlMap, which is defined as "one" in this example.
- The id attribute of the resutlMap is the mapped POJO class.
- The id tag maps to a primary key, and the result tag maps to a non primary key.
- Property sets the property name of the POJO, and column maps the column name of the query result
Using resultMap
<select id="getAdminById" resultMap="one">
select * from admin where id = #{id}
</select>
The output map is not resultType, but resultMap. The value of resultMap is filled in the resultMap value of the defined id
Define a resultMap value corresponding to the resultMap value
<resultMap id="one" type="Admin">
<id column="id" property="id"></id>
<result column="account" property="account"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex"></result>
</resultMap>
Multi table Association processing result set (resultMap)
resultMap is mainly used for queries between multiple tables
association and collection elements in resultMap element
association and collection are associations of complex types,
association: it is a one-to-one relationship. For example, the operation of a department is directed to an operator.
association can refer to itself or from other places.
collection: a one to many or many to one relationship. For example, a department targets multiple employees.
collection can refer to itself or from other places.
The association Association element handles relationships of type "there is one", that is, one-to-one associations.
It can be associated in two ways:
- Nested query: returns the expected complex type by executing another SQL mapping statement.
- Nested results: use nested result mapping to handle a subset of duplicate federated results.
The Collection Association element handles one to many associations.
Mybatis is extremely convenient in associating multiple tables
When creating classes for storing information, we can create a class for each table to map the data association of adjacent tables to our class. Make the class under construction, name and so on
Create the corresponding data storage class first
Class corresponding to Admin (operator)
package com.demo.mybatispro.model;
import java.io.Serializable;
public class Admin implements Serializable {
private int id;
private String account;
private String password;
private String sex;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Admin{" +
"id=" + id +
", account='" + account + '\'' +
", password='" + password + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
Class corresponding to dept
package com.demo.mybatispro.model;
import java.io.Serializable;
import java.util.List;
public class Dept implements Serializable {
private int id;
private String name;
private List<Employer> employers;//Collection of employees
//private List<Employer> employers = new ArrayList<>;
// The latter can be written without writing. It will be automatically encapsulated when it is created
private Admin admin;//Encapsulate the operator information directly into the object
public int getId(int i) {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Employer> getEmployers() {
return employers;
}
public void setEmployers(List<Employer> employers) {
this.employers = employers;
}
public Admin getAdmin() {
return admin;
}
public void setAdmin(Admin admin) {
this.admin = admin;
}
@Override
public String toString() {
return "Dept{" +
"id=" + id +
", name='" + name + '\'' +
", employers=" + employers +
", admin=" + admin +
'}';
}
}
Class corresponding to employee
package com.demo.mybatispro.model;
import javafx.scene.DepthTest;
import java.io.Serializable;
public class Employer implements Serializable {
// implements Serializable generates the serial number (the serial number is the serial number of the line to be generated as soon as the class is updated,
// This specifies that it can only generate one serial number)
private int id;
private String name;
private int age;
private Dept dept;
private Admin admin;
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Admin getAdmin() {
return admin;
}
public void setAdmin(Admin admin) {
this.admin = admin;
}
@Override
public String toString() {
return "Employer{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", dept=" + dept +
", admin=" + admin +
'}';
}
}
Read the core configuration of Mybatis, create a factory such as SqlSessionFactory, and encapsulate the created SqlSession objects into a class
package com.demo.mybatispro.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
public class MybatisUtil {
static SqlSessionFactory development = null;
//Because it is only executed once, you can write all these to static. With the loading of the class, it is only executed once
static {
Reader resourceAsReader = null;
try {
resourceAsReader = Resources.getResourceAsReader("mybtis.xml");
} catch (IOException e) {
e.printStackTrace();
}
development = new SqlSessionFactoryBuilder().build(resourceAsReader);
}
public static SqlSession getSqlSession() {
return development.openSession();
}
}
association refers to the one-to-one relationship between tables
Interface definition method name
public interface EmployerMapper {
Employer getEmployerById(int id);
}
Mybatis adopts the specific implementation of association in xml (one-to-one relationship)
<resultMap id="empmap" type="Employer">
<!-- column Corresponding to the name in the database, property The corresponding is the name in the attribute-->
<id column="id" property="id"></id>
<result column="ename" property="name"></result>
<result column="age" property="age"></result>
<!--Information of packaging Department-->
<!--property The corresponding attribute name, javaType Is the type corresponding to the property-->
<association property="dept" javaType="Dept">
<result column="dname" property="name"></result>
</association>
<!--Encapsulate operator information-->
<association property="admin" javaType="Admin">
<result column="account" property="account"></result>
</association>
</resultMap>
<select id="getEmployerById" resultMap="empmap">
SELECT
emp.id,
emp.name ename,
emp.age,
d.name dname,
a.account
FROM employee emp LEFT JOIN dept d ON emp.deptId = d.id
LEFT JOIN admin a ON emp.adminId = a.id
WHERE emp.id = #{id}
</select>
Method call: (I use the tested jar package, so I can start the function directly without using the main method)
@Test
public void find(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmployerMapper mapper = sqlSession.getMapper(EmployerMapper.class);
Employer employer = mapper.getEmployerById(1);
System.out.println(employer.getName());
System.out.println(employer.getDept().getName());
System.out.println(employer.getAdmin().getAccount());
System.out.println(employer);
sqlSession.commit();
sqlSession.close();
}
I use the log to achieve an overall output
In this relationship, an employee corresponds to a department (dept) and an operator (admin), which is a one-to-one relationship
collection refers to the one-to-one relationship between tables
Interface defines the method to be implemented.
public interface DeptMapper {
Dept getDeptById(int id);
}
Specific implementation of Mybatis in xml (one-to-one relationship)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mybatispro.mapper.DeptMapper">
<resultMap id="deptmap" type="Dept">
<id column="id" property="id"></id>
<result column="dname" property="name"></result>
<!--Crazy operator information, single information, one-to-one-->
<association property="admin" javaType="Admin">
<result column="account" property="account"></result>
</association>
<!--It encapsulates a collection of employee information, one to many-->
<collection property="employers" javaType="list" ofType="Employer">
<result column="ename" property="name"></result>
</collection>
</resultMap>
<select id="getDeptById" resultMap="deptmap">
SELECT
d.id,
d.name dname,
a.account,
e.name ename
FROM dept d LEFT JOIN admin a ON d.adminId = a.Id
LEFT JOIN employee e ON d.id = e.deptId
WHERE d.id = #{id}
</select>
</mapper>
Method call: (I use a jar package of the test, so I can start the function directly without using the main method)
@Test
public void find() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept deptById = mapper.getDeptById(2);
System.out.println(deptById);
System.out.println(deptById.getName());
List<Employer> list = deptById.getEmployers();
//Make a cyclic output for more in the one to many relationship
for (Employer employer : list) {
System.out.println(employer.getName());
}
sqlSession.commit();
sqlSession.close();
}
The log is used to achieve an output of the whole
In this relationship, a dept corresponds to an operator (admin) and multiple employee s.
association is used when the Department corresponds to the operator, and collection is used when the Department corresponds to the employee.
nested queries
What is nested query?
Nested query is actually the result of multi table Association processing.
When you need to query association information, you can use the nested query feature of Mybatis.
Mybatis one-to-one association and one to many collection can realize lazy loading. When nesting loops, the resultMap is used, not the resultType.
Examples of nested queries
Method name defined in the interface
Employer getEmlpyeeById1(int id);
Implementation in xml
<resultMap id="emp1" type="Employer">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="age" property="age"></result>
<!--property Is the name of the attribute, JavaType Is the type of the property fetchType Type of load lazy/eager
select Your own name corresponds to the name of the associated table column Columns in the value database-->
<!--Nested query of department information-->
<association property="dept" javaType="Dept" fetchType="lazy"
select="findDeptById" column="deptId">
<result column="name" property="name"></result>
</association>
<!--Nested query operator information-->
<association property="admin" javaType="Admin" fetchType="lazy"
select="findAdminById" column="adminId">
<result column="account" property="account"></result>
</association>
</resultMap>
<select id="getEmlpyeeById1" resultMap="emp1">
SELECT
id,
name,
age,
deptId,
adminId
FROM employee
WHERE id = #{id}
</select>
<select id="findDeptById" resultType="Dept">
select name from dept where id = #{deptId};
</select>
<select id="findAdminById" resultType="Admin">
select account from admin where id = #{adminId};
</select>
Call method
@Test
public void find2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
EmployerMapper mapper = sqlSession.getMapper(EmployerMapper.class);
Employer employer = mapper.getEmlpyeeById1(2);
System.out.println(employer.getName());
System.out.println(employer.getDept().getName());
System.out.println(employer.getAdmin().getAccount());
System.out.println(employer);
sqlSession.commit();
sqlSession.close();
}
When the nested query is queried for the first time, only the information of the main table is queried, and the information of the associated table is obtained by loading when the user needs it.
Lazy load (load on demand)
The use of lazy loading can effectively reduce the pressure on the database.
Lazy loading is to add a global configuration of setting to the nested loop.
The lazy value should also be assigned in the tag. fetchType="lazy" means to enable delayed loading (query will be triggered when necessary)
The premise of lazy loading is that it must first be a nested query
In the core configuration file, configure lazyloading enabled through settings to close nested queries.
<settings>
<setting name="lazyLoadTriggerMethods" value=""/>
</settings>
This lazy loading is implemented based on the nested loop above.
Make a breakpoint and use debug to debug step by step.
- first
- the second
- Third
This is the way and process of lazy loading.
Previous article: > > > addition, deletion, modification and query of data by mybatis