Mybatis concise tutorial

Article Directory

Follow WeChat public account:

Insert picture description here

Reply to "Mybatis" for more details



Mybatis

MyBatis is the hottest persistent layer framework in China

The ORM idea is adopted to solve the problem of mapping between entity classes and database tables. Encapsulation of JDBC, shielding the access details of the underlying JDBCAPI, avoiding our dealings with the api of jdbc, can complete the persistence operation of the data.

O--Object java对象
R- Relation 关系,就是数据库中的一张表 
M-mapping 映射

1. Get started quickly

Mybatis official help document: https://mybatis.org/mybatis-3/zh/index.html

1. Create a Maven project

2. Import Maven dependencies

Here, we want to import the dependencies of mybatis, a category of mysql, and the dependencies of unit tests

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

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

3. Configure the Maven plugin

Here, what is to be configured is the Maven compilation plug-in. We specify that the source file and the compiled file are both java version 1.8

<build>
        <plugins>
            <!--编译插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

4. Create a new database and import tables

image-20210605180526736
CREATE TABLE `team` (
  `teamId` int NOT NULL AUTO_INCREMENT COMMENT '球队ID', 
  `teamName` varchar(50) DEFAULT NULL COMMENT '球队名称', 
  `location` varchar(50) DEFAULT NULL COMMENT '球队位置', 
  `createTime` date DEFAULT NULL COMMENT '球队建立时间', 
  PRIMARY KEY (`teamId`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

5. Write Mybatis configuration file

We can get an example of the configuration file directly on the official website

When developing in the future, it is recommended that readers create a document to store the writing rules of the configuration file to facilitate the development

image-20210605181153961

<property>The content in can be obtained through the configuration file, we will introduce it later

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

    <!--mybatis 环境,可以为 mybatis 配置多套环境,然后根据 id 切换-->
    <environments default="development">
        <environment id="development">
            <!--事务类型:使用 JDBC 事务,使用 Connection 的提交和回滚-->
            <transactionManager type="JDBC"/>
            <!--数据源 dataSource:创建数据库 Connection 对象  type: POOLED 使用数据库的连接池 -->
            <dataSource type="POOLED">

                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis_study?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=GMT"/>
                <property name="username" value="admin"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>

    <!--mapper映射,这里我们先不写,后面再讲-->
    <!--<mappers>-->
    <!--    <mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
    <!--</mappers>-->


</configuration>

6, write entity classes

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Team {
    private Integer teamId;
    private String teamName;
    private String location;
    private Date createTime;
}

7. Write the mapper interface

public interface TeamMapper {

    /**
     * 获取全部 team 信息
     * @return
     */
    List<Team> getAll();
}

8. Write mapper implementation

Implementation, which is written in .xml files

id: non-method in the interface

resultTyoe: the return value of the interface method (before configuration, the full class name must be written)

note:XxxMapper.xml, must be interfaced with the corresponding mapper, with the same package name

image-20210605212341902
<?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绑定一个指定的Dao/Mapper接口-->
<mapper namespace="top.faroz.dao.TeamMapper">
    <select id="getAll" resultType="top.faroz.pojo.Team">
        select * from team
    </select>
</mapper>

You can also implement .xml and place it under resources, but you must note that the same package has the same name :

image-20210606122931162

9. In the Mybatis configuration file, add mapper mapping

After writing the .xml implementation, be sure to add mapper mapping in the configuration file

<!--mapper映射-->
    <mappers>
        <package name="top.faroz.dao"/>
    </mappers>
image-20210605185046746

10. Write Mybatis tools

The tool class is used to create a singleton factory for sqlSession

And add a method to get the sqlSession connection

public class MybatisUtil {

    /**
     * 连接工厂
     * 用来创建连接
     */
    private static SqlSessionFactory sqlSessionFactory;

    static {
        //使用MyBatis第一步:获取SqlSessionFactory对象
        try {
            String resource = "mybatis.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            /**
             * 通过配置文件,创建工程
             * (配置文件就是 resources 下的 mybatis.xml)
             */
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        //设置为true,自动提交事务
        return sqlSessionFactory.openSession(true);
    }
}

11. Test

@Test
public void getAllTest() {
  SqlSession sqlSession = MybatisUtil.getSqlSession();
  TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
  List<Team> all = mapper.getAll();
  for (Team team : all) {
    System.out.println(team);
  }
  sqlSession.close();
}

The test results are as follows:

image-20210605185314585

Two, log addition

1. Add Maven dependency

 <!--log4j日志-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

2. Add log4j configuration

image-20210605201937406

log4j.properties

We can adjust the level of log output, in addition to DEBUG, there can also be INFO, WARNING, ERROR

# Global logging configuration info warning error
log4j.rootLogger=DEBUG,stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

Now it is output in the console. In the future, maybe our project will be deployed on the client's server. We can output the log information to an external file.

3. Configure LOG in Mybatis

<!--配置日志-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
image-20210605202009740

4. Perform the test

The console will display detailed log information

image-20210605202421302

Three, Mybatis object analysis

1. Resources

image-20210605210643981

The Resources class, as the name implies, is a resource, which is used to read resource files. There are many ways to return different types of IO stream objects by loading and parsing resource files.

2. SqlSessionFactoryBuilder

The creator used to create the SqlSessionFactory will only be used once in the entire project, which is used to create the SqlSessionFactory. Throw away after use

3. SqlSessionFactory

Create a singleton factory for sqlSession. In the entire project, there should be only one singleton of SqlSessionFactory

4. SqlSession

A SqlSession corresponds to a database session. A session starts with the creation of the SqlSession object and ends with the closure of the SqlSession object.

The SqlSession interface object is not thread-safe, so before the end of each database session, you need to call its close() method immediately to close it. The session is needed again, create it again.

Fourth, improve MybatisUtil

In the Quick Start section, we will get sqlSession objects Mybatis of the process, packaging has become a tool class

However, sqlSession is not stored in MybatisUtil as a member variable. In this way, when we execute close to sqlSession, we must manually call the sqlSession we obtained externally. In fact, we can turn sqlSession into a member variable, and then in MybatisUtil In, write a close method.

However, we should note that sqlSession is not thread-safe. After each use, we need to close it. Then, when we use it next time, connect to sqlSession again. If sqlSession is used as a static member variable, put it in MybatisUtil , It is bound to cause thread-related problems.

In order to solve this problem, we need to introduce ThreadLocal

1. ThreadLocal

2. Use ThreadLocal to rewrite

3. Summary

Personally, I actually don't recommend using sqlSession as a static member variable, and the official documentation does not recommend us to do so. My main purpose of writing this section is to introduce ThreadLocal

Five, input mapping

Previously, in our test, the input was a single value. What should we do if there are multiple values ​​in our input?

If there are multiple parameters, you can use:

#{arg0},#{arg1},#{arg2}…, Or #{param0},#{param2},#{param3}…the way to get different parameters

But this method is not recommended

2. Use annotations

We can before the corresponding parameter mapper interface, plus @Paramnotes, and in the .xmlimplementation class, direct parameter name defined annotation

interface:

Team selectByNameAndLocation(@Param("name") String name,@Param("location") String location);

achieve:

<select id="selectByNameAndLocation" resultType="top.faroz.pojo.Team">
		select * from team where teamName=#{name} and location=#{location}
</select>

test:

@Test
public void selectByNameAndLocationTest() {
  SqlSession sqlSession = MybatisUtil.getSqlSession();
  TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
  Team team = mapper.selectByNameAndLocation("雄鹿", "威斯康星州密尔沃基");
  System.out.println(team);
  sqlSession.close();
}
image-20210606015420014

3. Use map

The parameter passed in can be map, .xmland the parameters obtained in the file must be consistent with the key value in the map

interface:

Team selectByName(Map map);

achieve:

 <select id="selectByName" resultType="top.faroz.pojo.Team" parameterType="map">
        select * from team where teamName=#{name}
    </select>

test:

	 @Test
    public void selectByNameTest() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("name","雄鹿");
        Team team = mapper.selectByName(map);
        System.out.println(team);
        sqlSession.close();
    }

Six, output mapping

1, resultType

1), output simple type

It is general, output a single String or Integer type, there are many previous examples, and I will not demonstrate it here.

2), output pojo type

List<Team> queryAll();
<!--接口方法返回是集合类型,但是映射文件中的resultType需要指定集合中的类型,不是集合本身。--> <select id="queryAll" resultType="com.kkb.pojo.Team">
		select * from team;
</select>

3), output map type

When we only need to query a few columns of data in the table, we can use the sql query result as the key and value of the Map. Generally, Map<Object,Object> is used.

Map as the interface return value, the query result of the sql statement can only have one record at most. More than one record will throw TooManyResultsException. If there are multiple lines, use List<Map<Object,Object>>.

Map<Object,Object> queryTwoColumn(int teamId); 
List<Map<Object,Object>> queryTwoColumnList();
<select id="queryTwoColumn" resultType="java.util.HashMap" paramType="int"> 
	  select teamName,location from team where teamId = #{id}
</select>

<select id="queryTwoColumnList" resultType="java.util.HashMap">
		select teamName,location from team
</select>
@Test
public void test08(){
		Map<String, Object> map = teamMapper.queryTwoColumn(); System.out.println(map);
}

@Test
public void test09(){
	List<Map<String, Object>> list = teamMapper.queryTwoColumnList(); 
  for (Map<String, Object> map : list) {
		System.out.println(map); 
  }
}

2. resultMap

resultMap can customize the mapping relationship between sql results and java object attributes. More flexible assignment of column values ​​to specified attributes.

一般主键列用id , 其余列用result
column:表示数据库表中的列名,不区分大小写 property:表示实体类中的对应的属性名,区分大小写 javaType:实体类中的对应的属性的类型,可以省略,mybatis会自己推断 jdbcType="数据库中的类型column的类型" 一般省略
<resultMap id="baseResultMap" type="com.kkb.pojo.Team"> 
  	<!--主键列,使用 id-->
		<id column="teamId" property="teamId" javaType="java.lang.Integer"></id>
		<!--其余列,使用 result-->
		<result column="teamName" property="teamName" javaType="java.lang.String"></result> 
  	<result column="location" property="location" javaType="java.lang.String"></result> 
  	<result column="createTime" property="createTime" javaType="java.util.Date"></result>
</resultMap>

When implementing the statement, when used, the return value is changed from the original resultType to the resultMap we wrote:

<select id="queryAll2" resultMap="baseResultMap">
		select * from team;
</select>

Using resultMap for attribute mapping can also solve the problem of inconsistency between attribute names and database table column names

3. The processing method of the inconsistency between the columns in the database table and the entity class attributes

1), use resultMap to solve

As mentioned above, I won’t demonstrate it here anymore

2), alias in sql

If the naming method in sql uses the underscore method, but in pojo, the camel case naming method is used, then we can use the following aliasing method to change the query result to the camel case naming method:

select user_id as userId,user_name as userName,user_age as userAge from users where user_id=#{id};

7. The difference between #{} and ${}

This question is also often tested in interview questions

#{}:Represents a placeholder to notify Mybatis to use actual parameter values ​​instead. And use the PrepareStatement object to execute the sql statement, #{…} instead of the "?" in the sql statement. This is the preferred method in Mybatis, safe and fast.

In this way, SQL injection can be prevented.


${}:Indicates that the string is replaced as it is , and tells Mybatis to$replace the locationwith thecontained "string". Use Statement or PreparedStatement to connect the sql statement with${}the content. It is generally used in operations such as replacing table names, column names, and sorting different columns.

In this way, you can modify the columns of the sql statement. For example, we have such a requirement:

You need to query user information according to name, age, address, etc., according to the general writing method, you must write 3 sql

select * from xxx where name = #{name}
select * from xxx where age = #{age}
select * from xxx where address = #{address}

We can see that different, just where the back part, where we can use ${}to replace part of the back where, so do not write so much sql

select * from xxx where ${column} = #{columnValue}

Herepay attention, Because it is a non-string splicing method, it ${}must not be used on parameters, otherwise it may cause SQL injection problems. But why can it be used in other places in sql? This is because if the SQL injection statement is written in a non-parameter place, it will cause an SQL statement error and report an error.

Eight, Mybatis global configuration file

1. Overview of configuration content

When configuring, it doesn’t matter if you have fewer configurations, but you must configure them in accordance with the following sequence

configuration(配置) 
    properties--属性:加载外部的配置文件,例如加载数据库的连接信息 
    Settings--全局配置参数:例如日志配置
    typeAliases--类型别名
    typeHandlers----类型处理器
    objectFactory-----对象工厂
    Plugins------插件:例如分页插件 
    Environments----环境集合属性对象
        environment(环境变量) 
            transactionManager(事务管理器)
            dataSource(数据源)
    Mappers---映射器:注册映射文件用

2. Properties

You can configure Mybatis to read external configuration files through properties, such as loading database connection information

1), create a new configuration file

In the resources folder, create jdbc.propertiesprofiles

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis_study?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
jdbc.username=admin
jdbc.password=123

2) In the mybatis configuration, introduce configuration file information

<!--加载配置文件-->
<properties resource="jdbc.properties"/>

3), read the configuration file

<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>

3. Settings

Very important adjustment settings in MyBatis, they will change the runtime behavior of MyBatis. For example, the log we configured is one of the applications. The rest of the content refers to the setting document

https://mybatis.org/mybatis-3/zh/configuration.html#settings

4. Type aliases (typeAliases)

Type aliases can set an abbreviated name for a Java type. It is only used for XML configuration and is intended to reduce redundant writing of fully qualified class names.

1) Supported aliases

image-20210606124343034

2) Custom alias

Type aliases can set an abbreviated name for a Java type. It is only used for XML configuration and is intended to reduce redundant writing of fully qualified class names. E.g:

<typeAliases>
  <typeAlias alias="Author" type="domain.blog.Author"/>
  <typeAlias alias="Blog" type="domain.blog.Blog"/>
  <typeAlias alias="Comment" type="domain.blog.Comment"/>
  <typeAlias alias="Post" type="domain.blog.Post"/>
  <typeAlias alias="Section" type="domain.blog.Section"/>
  <typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>

When so configured, Blogit may be used in any domain.blog.Blogplace.

(Below 👇This method should be more convenient)

You can also specify a package name, MyBatis will search for the required Java Bean under the package name, for example:

<typeAliases>
		<package name="top.faroz.pojo"/>
</typeAliases>
<select id="getUserList" resultType="user">
    select * from user
</select>

5. Mappers

image-20210127172910752

Method 1: [Recommended use]

<!-- 使用相对于类路径的资源引用 -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>

Method 2: Use class file binding

<!-- 使用映射器接口实现类的完全限定类名 -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

be careful:

  • The interface and the configuration file have the same name
  • The interface and its Mapper configuration file must be in the same package

Method 3: Use scanning package for injection binding

<!-- 将包内的映射器接口实现全部注册为映射器 -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

6. Data Source (dataSource)

There are three built-in data source types (that is, type="[UNPOOLED|POOLED|JNDI]"):

  • UNPOOLED -The implementation of this data source opens and closes the connection every time it is requested. Although a bit slow, but for simple applications that do not require high database connection availability
  • POOLED -The realization of this data source uses the concept of "pool" to organize JDBC connection objects, avoiding the initialization and authentication time necessary to create a new connection instance. (Database connection pool)
  • JNDI -This data source is implemented in order to be used in containers such as EJB or application servers. The container can configure the data source centrally or externally, and then place a JNDI context data source reference.

POOLED is used by default

7. Transaction (transactionManager)

1), the default is to manually commit the transaction

The Mybatis framework is a encapsulation of JDBC, so the transaction control method of the Mybatis framework itself also uses the commit() and rollback() of the JDBC Connection object. The setAutoCommit() method of the Connection object to set the transaction commit method.

Automatic submission and manual submission, <transactionManager type="JDBC"/>this tag is used to specify the transaction manager used by MyBatis. MyBatis supports two types of transaction managers: JDBC and MANAGED.

JDBC: Use the JDBC transaction management mechanism, submit through the commit() method of the Connection object, and rollback through the rollback() method. By default, mybatis has turned off the automatic submission function and changed it to manual submission. It can be seen from the observation log, so we all need to submit the transaction or roll back the transaction in the program.

image-20210606125340722

MANAGED: The entire life cycle of the transaction is managed by the container (such as the Spring container).

2), automatically commit the transaction

image-20210606125442011

Nine, relationship mapping

1. One-to-one relationship

There is such a demand, there are many students, many students correspond to a certain teacher, now we need to query the attributes of the students and the corresponding teachers

SQL statement:

# 学生表
drop table if exists `student`;
CREATE TABLE `student` (
                        `id` int NOT NULL AUTO_INCREMENT COMMENT '学生 id',
                        `name` varchar(50) DEFAULT NULL COMMENT '学生姓名',
                        `tid` int DEFAULT NULL COMMENT '学生所属老师 id',
                        PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into student value (1,'jojo',1);
insert into student value (2,'dio',1);
insert into student value (3,'faro',2);
insert into student value (4,'kkk',2);
insert into student value (5,'ttt',3);

# 老师表
drop table if exists `teacher`;
CREATE TABLE `teacher` (
                           `id` int NOT NULL AUTO_INCREMENT COMMENT '老师 id',
                           `name` varchar(50) DEFAULT NULL COMMENT '老师姓名',
                           PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into teacher value (1,'老师1');
insert into teacher value (2,'老师2');
insert into teacher value (3,'老师3');

student:

public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

teacher:

public class Teacher {
    private int id;
    private String name;
}

1), according to the query nesting processing

interface:

List<Student> getAll();

.xml implementation

It is equivalent to executing another query in resultMap

		<select id="getAll" resultMap="stusta">
        select * from student
    </select>
    <resultMap id="stusta" type="Student" >
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="tid" column="tid"/>
      	<!--这里的tid的值,会传递到 getTeacher 中,从而查询出对应教师-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
image-20210207163446803

test:

		@Test
    public void getAllTest() {

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        /**
         * 使用动态代理的方式,生成 mapper 的实现类
         */
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> all = mapper.getAll();
        for (Student student : all) {
            System.out.println(student);
        }
        sqlSession.close();
    }
image-20210606134457129

2), nested processing according to the results

This method is similar to join table query

<!--方法2:按照结果嵌套处理(这种方式更好懂)-->
    <select id="getStudentList" resultMap="StudentTeacher">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
image-20210207171715894

In terms of convenience, the first method is obviously more convenient (less SQL is written)

But in terms of performance, the second type has better performance (after all, you only need to query the database once)

2. To many relationships

One-to-many is similar to many-to-one

For example: a teacher has multiple students

  1. The environment is set up as before
  2. Writing entity classes
public class Student {
   private int id;
   private String name;
   private int tid;
}
public class Teacher {
   private int id;
   private String name;
   //一个老师多个学生
   private List<Student> students;
}

1), in accordance with the query nested query

interface:

List<Teacher> getAll();

.xml implementation

The ofType here indicates the generic type of the elements in the collection

image-20210606141223318
		<select id="getAll" resultMap="getStaStu">
        select * from teacher
    </select>
    <resultMap id="getStaStu" type="Teacher">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--这里的id ,是老师的id ,会自动映射到子查询的中的 tid 中-->
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="selectStudentByTid"/>
    </resultMap>
    <select id="selectStudentByTid" resultType="Student">
        select * from student where tid =#{tid}
    </select>

test:

		@Test
    public void getAllTest() {

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        /**
         * 使用动态代理的方式,生成 mapper 的实现类
         */
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> all = mapper.getAll();
        for (Teacher teacher : all) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
image-20210606141042272

2) Nested query according to the result

interface:

List<Teacher> getAll();

.xml implementation:

<select id="getAll" resultMap="getStaStu">
        select teacher.id tid,teacher.name tname,student.id sid,student.name sname
        from teacher,student
        where teacher.id=student.tid;
    </select>
    <resultMap id="getStaStu" type="Teacher">
        <id column="tid" property="id"/>
        <result column="tname" property="name"/>
        <collection property="students" javaType="ArrayList" ofType="Student">
            <result column="sid" property="id"/>
            <result column="sname" property="name"/>
        </collection>
    </resultMap>

test:

		@Test
    public void getAllTest() {

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        /**
         * 使用动态代理的方式,生成 mapper 的实现类
         */
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> all = mapper.getAll();
        for (Teacher teacher : all) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
image-20210606142508194

Ten, dynamic SQL

Use dynamic SQL to avoid the trouble of SQL splicing

The use of where tag in select

note:In the xml achieve mybatis >can be used directly, but <can not be used directly, you must use the transition symbol

image-20210606144305227
<select id="queryByVO" parameterType="QueryVO" resultMap="baseResultMap">
    select * from team
    <where>
        <!-- 如果用户输入了名称,就模糊查询 and teamName like '%?%'--> 
      	<if test="name!=null ">
        		and teamName like concat(concat('%',#{name}),'%')
        </if>
        <if test="beginTime!=null ">
		        and createTime>=#{beginTime}
        </if>
        <if test="endTime!=null ">
		        and createTime&lt;=#{endTime} 
      	</if>
        <if test="location!=null "> 
          	and location=#{location}
        </if> 
  	</where>
</select>

Fuzzy query:

When using fuzzy query, you must use the concat function to concatenate the strings, and you cannot use the + sign

	<select id="getByName" resultType="top.faroz.pojo.Teacher" parameterType="string">
        select * from teacher
        <where>
            <if test="name!=null">
--             使用 concat 函数,将多个字符串进行拼接
                and name like concat(concat('%',#{name}),'%')
            </if>
        </where>
   </select>

Use of set tag in update

<update id="update1" parameterType="com.kkb.pojo.Team"> 
  	update team
    <set>
        <if test="teamName!=null">
		        teamName=#{teamName},
        </if>
        <if test="location!=null">
    		    location=#{location},
        </if>
        <if test="createTime!=null">
        		createTime=#{createTime},
        </if> 
  	</set>
    where teamId=#{teamId}
</update>

forEach tags

Batch add

<insert id="addList" parameterType="arraylist">
		INSERT INTO team (teamName,location) VALUES
		<!--collection:要遍历的集合;参数是集合类型,直接写list item:遍历的集合中的每一个数据 separator:将遍历的结果用,分割-->
		<foreach collection="list" item="t" separator=",">
				(#{t.teamName},#{t.location})
		</foreach>
</insert>

batch deletion

<delete id="delList" >
		delete from team where teamId in 
  	<!--collection:要遍历的集合;参数是集合类型,直接写list item:遍历的集合中的每一个数据separator:将遍历的结果用,分割
open="(" close=")":表示将遍历结果用open close包裹起来-->
		<foreach collection="list" item="teamId" separator="," open="(" close=")">
				#{teamId}
		</foreach> 
</delete>

SQL fragment

Extract SQL fragments:

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

Quoting SQL fragment:

<select id="queryBlogIf" parameterType="map" resultType="blog">
  select * from blog
   <where>
       <!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace -->
       <include refid="if-title-author"></include>
       <!-- 在这里还可以引用其他的 sql 片段 -->
   </where>
</select>

note:

① It is best to define SQL fragments based on a single table to improve the reusability of fragments

②, do not include where in the sql fragment

Eleven, paging plug-in

Paging plugin, we use pageHelper

1. Get started quickly

1. Maven dependency

<!--pagehelper-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>

2. Add plug-in configuration to Mybatis global configuration file

<plugins>
        <!-- 引入 pageHelper插件 -->
        <!--注意这里要写成PageInterceptor, 5.0之前的版本都是写PageHelper, 5.0之后要换成PageInterceptor-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--reasonable:分页合理化参数,默认值为false,直接根据参数进行查询。当该参数设置为 true 时,pageNum<=0 时会查询第一页, pageNum>pages(超过总数时),会查询最后一页。 方言可以省略,会根据连接数据的参数url自动推断-->
            <!--<property name="reasonable" value="true"/>-->
        </plugin>
    </plugins>

3. Use plug-ins

2. Introduction to PageHelper

PageHelper will intercept the query statement, and then add the paging statement

 @Test
    public void getAllTest() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        /**
         * 使用动态代理的方式,生成 mapper 的实现类
         */
        TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);

        /**
         * 查询页数为1,查询5条
         */
        PageHelper.startPage(2,5);
        List<Team> all = mapper.getAll();
        for (Team team : all) {
            System.out.println(team);
        }
        sqlSession.close();
    }
image-20210606153125033

3. Introduction to PageInfo

It can be used to print the relevant information of paging, such as the total number of entries, the current number of pages, the total number of pages, etc.

@Test
    public void getAllTest() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        /**
         * 使用动态代理的方式,生成 mapper 的实现类
         */
        TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);

        /**
         * 查询页数为1,查询5条
         */
        PageHelper.startPage(2,5);
        List<Team> all = mapper.getAll();
        for (Team team : all) {
            System.out.println(team);
        }

        PageInfo<Team> pageInfo = new PageInfo<>(all);
        System.out.println("分页信息如下:");
        System.out.println("总条数:"+pageInfo.getTotal());
        System.out.println("总页数:"+pageInfo.getPages());
        System.out.println("当前页数:"+pageInfo.getPageNum());
        System.out.println("每页条数:"+pageInfo.getPageSize());

        sqlSession.close();
    }
image-20210606153224038

12. Cache

By using the cache, the queried information can be put into the cache during the first query. In this way, the cache will be used during the second query, thereby reducing database pressure, improving query efficiency, and solving high problems. Concurrency issues.

MyBatis also has a first-level cache and a second-level cache, and reserves an interface for integrating third-party caches.

1. Level 1 cache

Automatically open, SqlSession level cache

The sqlSession object needs to be constructed when operating the database. There is a (memory area) data structure (HashMap) in the object for storing cached data. The cache data area (HashMap) between different sqlSessions does not affect each other.

The scope of the first level cache is the same SqlSession. The same sql statement is executed twice in the same sqlSession. The data queried in the database will be written to the cache (memory) after the first execution, and the data will be obtained from the cache the second time. Will no longer query from the database, thereby improving query efficiency.

When a sqlSession ends, the first level cache in the sqlSession will no longer exist. Mybatis turns on the first level cache by default, and the memory (local cache) cannot be closed. You can call clearCache() to clear the local cache or change the scope of the cache.

1) Introduction to Level 1 Cache

image-20210606153755735

When the user initiates the first query of team=1001, first go to the cache to find out whether there is an object of team=1001; if not, continue to send query statements to the data, after the query is successful, the result of teamId=1001 will be stored in the cache in;

When the user initiates the second query team=1001, first go to the cache to find whether there is an object with team=1001, because the first query is successful, it has been stored in the cache, and the data can be obtained directly from the cache. , Which means that there is no need to send query statements to the database.

If the SqlSession executes a commit (addition, deletion, and modification), the cache area corresponding to the SqlSession is completely emptied at this time to avoid dirty reads.

**Prerequisite: **SqlSession is not closed.

2), the way to clear the cache

1s、 session.clearCache( ) ;
2、 execute update(增删改) ;
3、 session.close( );
4、 xml配置 flushCache="true" ;
5、 rollback;
6、 commit。

2. Level 2 cache

Mapper-level cache, which can span sqlSession

1) Introduction to Level 2 Cache

The secondary cache is shared by multiple SqlSessions, and its scope is the same namespace of the mapper.

Different sqlSession executes the same sql statement under the same namespace twice with the same parameters, that is, the same sql statement is finally executed. The data queried in the database will be written to the cache (memory) after the first execution, and the data will be retrieved from the cache the second time. Then query from the database to improve query efficiency.

Mybatis does not enable the second-level cache by default , and you need to configure to enable the second-level cache in the setting global parameters. If there is data in the cache, there is no need to get it from the database, which greatly improves system performance.

Like a cache, once the raw anti- additions and deletions, and the commit , will empty the cache data, thereby avoiding dirty reads data

The schematic diagram is as follows:

image-20210606154356633

2), use the second-level cache

image-20210606154655024

3) Disable the secondary cache

Why do I need to disable the second-level cache?

In some cases, some data is modified very frequently. Once the second-level cache is turned on, the operation of emptying the cache will also be very frequent, which will increase the pressure on the database. We can query for these sql , Turn off the second-level cache:

在开始了二级缓存的XML中对应的statement中设置useCache=false禁用当前Select语句的二级缓存,意味着该SQL语句每次只需都去查询数据库,不会查询缓存。 useCache默认值是true。对于一些很重要的数据尽不放在二级缓存中。

4), cache attribute configuration

<cache>
    <property name="eviction" value="LRU"/><!--回收策略为LRU-->
    <property name="flushInterval" value="60000"/><!--自动刷新时间间隔为60S--> 
    <property name="size" value="1024"/><!--最多缓存1024个引用对象--> 
    <property name="readOnly" value="true"/><!--只读-->
</cache>

The source code is as follows:

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface CacheNamespace {
    Class<? extends Cache> implementation() default PerpetualCache.class;

    Class<? extends Cache> eviction() default LruCache.class;

    long flushInterval() default 0L;

    int size() default 1024;

    boolean readWrite() default true;

    boolean blocking() default false;

    Property[] properties() default {};
}

/**属性介绍:
1.映射语句文件中的所有select语句将会被缓存; 
2.映射语句文件中的所有CUD操作将会刷新缓存;
3.缓存会默认使用LRU(Least Recently Used)算法来收回;
    3.1、LRU – 最近最少使用的:移除最长时间不被使用的对象。
    3.2、FIFO – 先进先出:按对象进入缓存的顺序来移除它们。
    3.3、SOFT – 软引用:移除基于垃圾回收器状态和软引用规则的对象。 3.4、WEAK – 弱引用:更积极地移除基于垃圾收集器状态和弱引用规则的对象。
4.缓存会根据指定的时间间隔来刷新(默认情况下没有刷新间隔,缓存仅仅调用语句时刷新); 
5.缓存会存储列表集合或对象(无论查询方法返回什么),默认存储1024个对象。
6.缓存会被视为是read/write(可读/可写)的缓存,意味着检索对象不是共享的,而且可以安全地被调用者修改,而不干扰其他调用者或线程所做的潜在修改。*/

If you want to share the same cache configuration and instance in the namespace, you can use the cache-ref element to refer to another cache.

So-calledNamespaces, In fact, is realized one by one xml

<cache-ref namespace="com.kkb.mapper.TeamMapper" /> 
//引用TeamMapper命名空间中的cache。

13. Reverse generator

Using the reverse generator, you can automatically generate the code of the persistence layer according to the database table

1. Configuration

slightly

2. Use

Here are some of the precautions for use

1), dynamic update/insert

With selective keyword, it means dynamic change

//动态插入
mapper.insertSelective(User user);
//动态更新
mapper.updateByPrimaryKeySelective(User user);

For insert , it is to insert the object where the attribute is empty, do not write insert sql

For update , it is for the part with empty attributes. If the update operation is not performed (), the hollow part of the new object will not be used to overwrite the metadata area.

2), multi-condition query

Multi-condition query, need to use XxxExample

image-20210606161714546

The use steps are as follows:

//1、创建被查询对象的 Example 对象
EbookExample ebookExample = new EbookExample();
//2、创建盛放查询条件的容器
EbookExample.Criteria criteria = ebookExample.createCriteria();
//3、在容器中,防止多查询条件
criteria.andNameLike("spring");//模糊查询
criteria.andNameEqualTo("spr");//等于
//...还有很多,对于每个属性,都有等量的多查询条件可供选择

//4、传入 Example 对象,进行多条件查询
mapper.selectByExample(ebookExample);