Mybatis study notes

MyBatis study notes

Knowledge preparation:

JDBC
Mysql
Maven
Junit

1. Introduction

1.1. What is MyBatis?

MyBatis is an excellent persistence layer framework
It supports custom SQL, stored procedures, and advanced mapping.
MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.
Originally an open source project of apache, iBatis was renamed MyBatis in 2010
Moved to Github in November 2013

How to live Mybatis?

maven warehouse
GitHub

1.2 persistence

Data persistence

The process of persisting data is the process of persisting the state of the program
Memory: loss upon power failure
Database (jdbc), io persistence
Life: refrigerated, canned

Why persistence?

Some objects can't be lost
Memory expensive

1.3. Durable layer

Dao layer, Service layer, Controller layer

Code block that completes the persistence work
The boundary of the layer is very obvious

1.4. Why do you need MyBatis?

convenient
The traditional JDBC code is too complex, and the framework helps to simplify and automate
Help programmers store data into the database

Many advantages

2. The first MyBatis program

Idea: build environment - > Import MyBatis - > write code - > test

2.1. Construction environment

-- Create database and use
create database mybatis;
use mybatis;

-- Create table
create table if not exists user(
id int(16) not null primary key,
name varchar(32) default null,
pwd varchar(64)
)engine=innodb default charset=utf8;

-- insert data
insert into user (id, name, pwd) values 
(1, "Tom", "123123"),
(2, "Bob", "123456"),
(3, "Rum", "666666");

New project

1. Create a normal maven project

2. Import maven dependencies

    <dependencies>
        <!--        mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.24</version>
        </dependency>

        <!--        mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!--        junit-->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-params</artifactId>
            <version>5.8.0-M1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <!--stay build Medium configuration resuorces，To prevent our resource export from failing-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

2.2. Create a module

Write the core configuration file of mybatis

<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
<!--    <mappers>-->
<!--        <mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
<!--    </mappers>-->
    <mappers>
<!--        every last Mapper.xml All need to be in MyBatis Register in core profile-->
        <mapper resource="com/lxyker/dao/UserMapper.xml"/>
    </mappers>
</configuration>

Write mybatis tool class

package com.lxyker.utils;

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.InputStream;

//sqlSessionFactory --> sqlSession
public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            // Step 1 using MyBatis
            // The purpose of the fixed sentence pattern taken from the official website is to obtain the sqlSessionFactory object
            String resource = "Mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it
     * SqlSession It completely contains all the methods required to execute SQL commands facing the database.
     */

    public static SqlSession getSqlSession() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

2.3. Code writing

Entity class

package com.lxyker.pojo;

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    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 String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

Dao interface

public interface UserDao {
    List<User> getUserList();
}

Interface implementation class (changed from UserDaoImpl.java to Mapper configuration file)

<?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">

<!--namespace = Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.lxyker.dao.UserDao">
    <!--    select Write query statement in tag-->
    <!--    resultType Is the return value type-->
    <select id="getUserList" resultType="com.lxyker.pojo.User">
        select * from mybatis.user;
    </select>
</mapper>

2.4 test

Note:

MapperRegistry registers mappers in the core configuration file

    <mappers>
<!--        every last Mapper.xml All need to be in MyBatis Register in core profile-->
        <mapper resource="com/lxyker/dao/UserMapper.xml"/>
    </mappers>

junit test

package com.lxyker.dao;

import com.lxyker.pojo.User;
import com.lxyker.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;

import java.util.List;

class UserDaoTest {
    @Test
    public void test() {
//        Get SqlSession object
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

//        Method 1: getMapper
//        UserDao mapper = sqlSession.getMapper(UserDao.class);
//        List<User> userList = mapper.getUserList();

//        Mode 2 (not recommended):
        List<User> userList = sqlSession.selectList("com.lxyker.dao.UserDao.getUserList");

        for (User user : userList) {
            System.out.println(user);
        }

//        Close SqlSession
        sqlSession.close();
    }
}

3,CRUD

3.1,namespace

The package name in the namespace should be consistent with the package name of Dao/Mapper interface

3.2 operation steps

Select query statement

id: corresponds to the method name in the namespace
resultType: return value of sql statement execution
parameterType: parameter type

1. Write interface

public interface UserMapper {
    List<User> getUserList();

    //    Query user by id
    User getUserById(int id);

    //    Insert user
    int addUser(User user);

    //    Modify user
    int updateUser(User user);

    //    delete user
    int delUser(int id);
}

2. Write the sql statement in the corresponding mapper

<mapper namespace="com.lxyker.dao.UserMapper">
    <!--    select Write query statement in tag-->
    <!--    resultType return type-->
    <select id="getUserList" resultType="com.lxyker.pojo.User">
        select *
        from mybatis.user;
    </select>

    <select id="getUserById" parameterType="int" resultType="com.lxyker.pojo.User">
        select *
        from mybatis.user
        where id = #{id};
    </select>

    <!--The properties in the object can be taken out directly-->
    <insert id="addUser" parameterType="com.lxyker.pojo.User">
        insert into mybatis.user (id, name, pwd)
        values (#{id}, #{name}, #{pwd});
    </insert>

    <!--    Modify user-->
    <update id="updateUser" parameterType="com.lxyker.pojo.User">
        update mybatis.user
        set name = #{name},
            pwd  = #{pwd}
        where id = #{id};
    </update>

    <!--    delete user-->
    <delete id="delUser" parameterType="Integer">
        delete
        from mybatis.user
        where id =
              #{id};
    </delete>
</mapper>

3. Test

	@Test
    public void test2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    @Test
    public void test3() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.addUser(new User(4, "Marry", "000000"));
//        Add, delete and modify transactions that need to be submitted
        sqlSession.commit();

        sqlSession.close();
    }

    @Test
    public void testUpdate() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(4, "Mogala", "000111"));

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDel() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.delUser(4);
        sqlSession.commit();
        sqlSession.close();
    }

3.3 universal map

Assuming that there are too many fields / parameters in entity classes or database tables, you should consider using map at this time

UserMapper interface:

int updateUser2(Map<String, Object> map);

UserMapper.xml:

<update id="updateUser2" parameterType="map">
    update mybatis.user set id = #{uid} where name = #{uname};
</update>

Test class:

@Test
public void testUpdateMap(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    map.put("uid", 4);
    map.put("uname", "Marry");
    mapper.updateUser2(map);
    sqlSession.commit();
    sqlSession.close();
}

4. Configuration resolution

4.1. Core configuration file

mybatis-config.xml

The MyBatis configuration file contains settings and attribute information that will deeply affect MyBatis behavior. The top-level structure of the configuration document is as follows:

configuration(Configuration)
	properties(Properties)
	settings(Settings)
	typeAliases(Type alias)
	typeHandlers(Type (processor)
	objectFactory(Object factory)
	plugins(Plug in)
	environments(Environment configuration)
		environment(Environment variables)
			transactionManager(Transaction manager)
			dataSource((data source)
	databaseIdProvider(Database (manufacturer identification)
	mappers(Mapper)

4.2. Environment configuration

MyBatis can be configured to adapt to a variety of environments

However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance

The default transaction manager of MyBatis is JDBC, and the connection pool is POOLED

4.3. properties

We can reference the configuration file through the properties attribute

These properties are externally configurable and dynamically replaceable. They can be configured in a typical Java property file or passed through the child elements of the properties element.

Write configuration file dB properties

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username = root
password = 123456

In the core configuration file mybatis config XML

<!--    Import external profile. The order should be in the front-->
<properties resource="db.properties"/>

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <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>

4.4 type aliases

A type alias is a short name set for a Java type
The meaning of existence is only to reduce the redundancy of fully qualified names

    <typeAliases>
        <typeAlias type="com.lxyker.pojo.User" alias="User"/>
    </typeAliases>

You can also specify a package name. MyBatis will search for the required Java beans under the package name, such as:

Scan the package of an entity class. Its default alias is the lowercase class name of the class

    <typeAliases>
<!--        <typeAlias type="com.lxyker.pojo.User" alias="User"/>-->
        <package name="com.lxyker.pojo"/>
    </typeAliases>

When there are few entity classes, use the first method

If there are many entity classes, the second method is recommended

The second way is to annotate the class and customize the alias:

@Alias("myUser")
public class User {
	......

4.5 setting

Set name	describe	Effective value	Default value
cacheEnabled	Globally turn on or off any cache configured in all mapper profiles.	true \| false	true
lazyLoadingEnabled	Global switch for delayed loading. When turned on, all associated objects are loaded late. In a specific association relationship, the switch state of the item can be overridden by setting the fetchType property.	true \| false	false
logImpl	Specify the specific implementation of the log used by MyBatis. If it is not specified, it will be found automatically.	SLF4J \| LOG4J \| LOG4J2 \| JDK_LOGGING \| COMMONS_LOGGING \| STDOUT_LOGGING \| NO_LOGGING	Not set

4.6 mappers

<!-- Use resource references relative to Classpaths -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>

<!-- Use the mapper interface to implement the fully qualified class name of the class -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

<!-- Register all the mapper interface implementations in the package as mappers -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

4.7 life cycle and scope

Life cycle and scope are crucial, because incorrect use can lead to very serious concurrency problems

SqlSessionFactoryBuilder:

Once the sqlSessionFactory is created, it is no longer needed
local variable

SqlSessionFactory:

It can be used as a database connection pool
Once SqlSessionFactory is created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance
Therefore, the best scope of SqlSessionFactory is the application scope
The simplest is to use singleton mode or static singleton mode

SqlSession

A request to connect to the connection pool
The instance of SqlSession is not thread safe, so it cannot be shared, so its best scope is request or method scope
You need to shut it down immediately after it is used up, otherwise the resources will be occupied

Each Mapper in this represents a specific business!

5. Solve the problem of inconsistency between attribute name and field name

Fields in the database

id	name	pwd

Create a new project to test the inconsistency of entity class fields

public class User {
    private int id;
    private String name;
    private String password;
............

You can see that the field pwd in the database is password in the User class. At this time, print the queried data in the test class:

User{id=1, name='Tom', password='null'}

resolvent:

Alias

<select id="getUserById" parameterType="int" resultType="User">
    select id, name, pwd as password
    from mybatis.user
    where id = #{id};
</select>

5.1,resultMap

Result set mapping

<!--column Fields in the database, property Properties in entity classes-->
<resultMap id="myUserMap" type="User">
    <result column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="pwd" property="password"/>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="myUserMap">
    select *
    from mybatis.user
    where id = #{id};
</select>

The resultMap element is the most important and powerful element in MyBatis
The design idea of resultMap is that there is no need to configure explicit result mapping for simple statements, but only need to describe their relationship for more complex statements.

6. Log

6.1 log factory

If an exception occurs in a database operation, we need to troubleshoot and log at this time.

The specific log implementation used in MyBatis is set in settings.

STDOUT_LOGGING standard log output

LOG4J

In the core configuration file mybatis config XML, make the following configuration:

    <settings>
        <!--        Standard log factory:-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

Run the test at this time, and you can see the console output:

Opening JDBC Connection
Created connection 1174586025.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4602c2a9]
==>  Preparing: select * from mybatis.user where id = ?;
==> Parameters: 1(Integer)
<==    Columns: id, name, pwd
<==        Row: 1, Tom, 123123
<==      Total: 1
User{id=1, name='Tom', password='123123'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4602c2a9]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4602c2a9]
Returned connection 1174586025 to pool.

Process finished with exit code 0

6.2,LOG4J

What is LOG4J?

Log4j yes Apache An open source project
By using Log4j, you can control the destination of log information transmission (console, files, GUI components, etc.)
You can control the output format of each log
Controls the log level of the output
It can be flexibly configured through configuration file

1. Import the package of log4j first

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2. New log4j Properties file:

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Relevant settings of console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Relevant settings for file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/lxyker.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

3. Configure log4j as the implementation of log

    <settings>
        <!--        Standard log factory:-->
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/>-->
        
        <setting name="logImpl" value="LOG4J"/>
    </settings>

4. Run test class

6.3 simple use of LOG4J

1. In the class to use LOG4J, import the package

2. Get the log object. The method parameter is the class of the current class

static Logger logger = Logger.getLogger(UserMapperTest.class);

7. Pagination

What is the purpose of paging?

It can reduce the processing capacity of one request data

limit used in MySQl

select * from user limit startIndex, pageSize;
select * from user limit 3;		-- amount to limit 0, 3;

7.1. Use MyBatis to realize paging. Core SQL

1. Interface

    //    paging
    List<User> getUserByLimit(Map<String, Integer> map);

2,Mapper.xml

    <!--    paging-->
    <select id="getUserByLimit" parameterType="map" resultType="user" resultMap="myUserMap">
        select *
        from mybatis.user
        limit #{startIndex}, #{pageSize};
    </select>

3. Testing

    @Test
    public void testGetUserByLimit() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Integer> map = new HashMap<>();
        map.put("startIndex", 0);
        map.put("pageSize", 1);
        List<User> userList = mapper.getUserByLimit(map);

        for (User user : userList) {
            System.out.println(user);
        }

        sqlSession.close();
    }

8. Using annotation development

8.1 interface oriented programming

8.2 annotation development

1. Add annotation on interface

@Select("select * from mybatis.user")
List<User> getUsers();

2. The interface needs to be bound in the core configuration file

<!--    Binding interface-->
<mappers>
    <mapper class="com.lxyker.dao.UserMapper"/>
</mappers>

3. Testing

Essence: reflection mechanism

Bottom layer: dynamic proxy

8.3,CRUD

We can automatically commit transactions when the tool class is created

public static SqlSession getSqlSession() {
    return sqlSessionFactory.openSession(true);
}

Write methods in the interface and add comments

Test class test

About @ Param() annotation

Parameters of basic type or String type need to be added
The reference type does not need to be added
The property name set in @ Param("uid") here is referenced in SQL

8.4 MyBatis execution process

9,Lombok

Plug in usage steps:

Import Lombok's jar package (maven) into the project

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
    <scope>provided</scope>
</dependency>

Just annotate the entity class:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private int id;
    private String name;
    private String password;
}

10. Many to one processing

For students, multiple students are associated with one teacher, many to one
For teachers, a teacher gathers multiple students one to many

Case:

Create table

create table teacher(
	id int(10) not null,
	name varchar(30) default null,
	primary key (id)
)engine=innodb default charset=utf8;

insert into teacher (id, name) values (1, "Miss Liu");

create table student(
	id int(10) not null,
	name varchar(30) default null,
	tid int(10) default null,
	primary key (id),
	key fktid (tid),
	constraint fktid foreign key (tid) references teacher(id)
)engine=innodb default charset=utf8;

insert into student(id, name, tid) values(1, "Xiao Ming", 1);
insert into student(id, name, tid) values(2, "floret", 1);
insert into student(id, name, tid) values(3, "Xiao Hong", 1);
insert into student(id, name, tid) values(4, "Bruce Lee", 1);
insert into student(id, name, tid) values(5, "Xiao Gang", 1);

Test environment construction:

Import Lombok

Create new entity classes Teacher and Student

package com.lxyker.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}
--------------------------------------------------------------
package com.lxyker.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;

//    Students need to associate with a teacher
//    private int tid;
    private Teacher teacher;
}

Establish Mapper interface

public interface TeacherMapper {
    @Select("select * from teacher where id = #{id}")
    Teacher getTeacherById(@Param("id") int id);
}

Create mapper XML file

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lxyker.dao.TeacherMapper">
</mapper>

Bind and register our Mapper interface or file in the core configuration file

    <mappers>
        <mapper class="com.lxyker.dao.TeacherMapper"/>
        <mapper class="com.lxyker.dao.StudentMapper"/>
    </mappers>

Test whether the query is successful

public class TeacherMapperTest {
    @Test
    public void myTest1() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherById = mapper.getTeacherById(1);
        System.out.println(teacherById);
        sqlSession.close();
    }
}

Query all student information and corresponding teacher information

Nested processing by query:

<mapper namespace="com.lxyker.dao.StudentMapper">

    <!--    Idea:
            1,Query all student information
            2,According to the students found tid，Find the corresponding teacher
    -->
    <select id="getStus" resultMap="StuTea">
        select * from student;
    </select>

    <resultMap id="StuTea" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--    <result property="" column="id"/>   Complex attributes need to be handled separately: object: associate   Set: collection  -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTea"/>
    </resultMap>

    <select id="getTea" resultType="Teacher">
        select * from teacher where id = #{id};
    </select>
</mapper>

Nested processing according to results

<select id="getStus2" resultMap="StuTea2">
    select s.id sid, s.name sname, t.name tname
    from student s,
         teacher t
    where s.tid = t.id;
</select>
<resultMap id="StuTea2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

11. One to many processing

Entity class:

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}
--------------------------------------------------------
@Data
public class Teacher {
    private int id;
    private String name;

    //    A teacher gathers multiple students
    private List<Student> students;
}

Mapper.xml:

<mapper namespace="com.lxyker.dao.TeacherMapper">
    <select id="getTeacher" resultMap="TeaStu">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student s,
             teacher t
        where s.tid = t.id
          and t.id = #{tid};
    </select>

    <resultMap id="TeaStu" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
<!--        Complex attributes need to be handled separately      Object use association    For collection collection-->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

12. Dynamic SQL

Dynamic SQL refers to generating different SQL statements according to different conditions

Build environment

create table blog
(
    id          int(16)  not null comment 'Blog id',
    title       varchar(128) not null comment 'Blog title',
    author      varchar(32)  not null comment 'Blogger',
    create_time datetime     not null comment 'Creation time',
    views       int(32)      not null comment 'Views'
) engine = innodb
  default charset = utf8;

Create basic engineering

Import jar package

Write configuration file

Writing entity classes

@Data
public class Blog {
    private int id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

Write the Mapper interface and Mapper corresponding to the entity class XML file

if

    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>

Different results can be found according to different parameters

    @Test
    public void test2() {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<Object, Object> map = new HashMap<>();
//        map.put("title", "simple Java");
        map.put("author", "Zhang San");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

choose (when, otherwise)

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

trim (where, set)

<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

The < where > tag can help redundant and or statements in SQL

The < set > tag removes redundant commas from SQL,

SQL fragment

Sometimes we need to extract the common parts of some functions for reuse.

Extract common parts using < SQL > tags
Use < include > tags where needed

<sql id="if-tit-aut">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <include refid="if-tit-aut"/>
</select>

Foreach

select * from user where 1=1 and (id=1 or id=2 or id=3);

13. Cache

The query needs to connect to the database, which consumes resources

When the result of a query is temporarily stored in a directly desirable place, it can be queried again without connecting to the database to improve the utilization rate

title = #{title} and author = #{author} and views = #{views} ```

trim (where, set)

<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

The < where > tag can help redundant and or statements in SQL

The < set > tag removes redundant commas from SQL,

SQL fragment

Sometimes we need to extract the common parts of some functions for reuse.

Extract common parts using < SQL > tags
Use < include > tags where needed

<sql id="if-tit-aut">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <include refid="if-tit-aut"/>
</select>

Foreach

select * from user where 1=1 and (id=1 or id=2 or id=3);

13. Cache

The query needs to connect to the database, which consumes resources

When the result of a query is temporarily stored in a directly desirable place, it can be queried again without connecting to the database to improve the utilization rate

The L1 cache is enabled by default and is only valid in one sqlSession

Keywords: Java Mybatis

Added by calumstevens on Thu, 17 Feb 2022 14:46:27 +0200

Programming VIP

Mybatis study notes

MyBatis study notes

1. Introduction

1.1. What is MyBatis?

1.2 persistence

1.3. Durable layer

1.4. Why do you need MyBatis?

2. The first MyBatis program

2.1. Construction environment

2.2. Create a module

2.3. Code writing

2.4 test

3,CRUD

3.1,namespace

3.2 operation steps

3.3 universal map

4. Configuration resolution

4.1. Core configuration file

4.2. Environment configuration

4.3. properties

4.4 type aliases

4.5 setting

4.6 mappers

4.7 life cycle and scope

5. Solve the problem of inconsistency between attribute name and field name

5.1,resultMap

6. Log

6.1 log factory

6.2,LOG4J

6.3 simple use of LOG4J

7. Pagination

7.1. Use MyBatis to realize paging. Core SQL

8. Using annotation development

8.1 interface oriented programming

8.2 annotation development

8.3,CRUD

8.4 MyBatis execution process

9,Lombok

10. Many to one processing

11. One to many processing

12. Dynamic SQL

Build environment

Create basic engineering

if

choose (when, otherwise)

trim (where, set)

SQL fragment

Foreach

13. Cache

trim (where, set)

SQL fragment

Foreach

13. Cache

Popular Keywords