Mybatis study notes

Mybatis study notes

Mybatis Tutorial [Meet the Mad God]

Mybatis Chinese Documentation

1. What is Mybatis

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 as well as setting parameters and getting 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.

Migrating to GitHub now

1.2. How to get Mybatis

Maven repository: https://mvnrepository.com/

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.10</version>
</dependency>

GitHub: https://github.com/mybatis/mybatis-3
Official documentation: https://mybatis.org/mybatis-3/zh/index.html

1.3. Persistence

1.3.1. Data persistence

  • Persistence is the process of converting program data between persistent and transient states
  • Memory: lost when power is off
  • Database (jdbc), io file persistence
  • Combining Life: Refrigerator Refrigeration

1.3.2. Why do we need persistence?

  • There are some objects that he can't let him lose
  • memory is too expensive

1.4. Persistence layer

  • Dao layer, Service layer, Controller layer...
  • The code block that does the persistence work
  • Layers are clearly demarcated

1.5. Why do you need MyBatis

  • Help programmers store data into the database

  • convenient

  • The traditional jdbc code is too complicated, simplify the code and form a framework. ----automation

  • It is also possible without MyBatis, it is easier to get started
    advantage:

  • Easy to learn: Small and simple in itself. Without any third-party dependencies, the simplest installation requires only two jar files + configuration of several sql mapping files. Easy to learn and easy to use. Through the documentation and source code, you can fully grasp its design ideas and implementation.

  • Flexible: mybatis does not impose any influence on the existing design of the application or database. sql is written in xml, which is convenient for unified management and optimization. All the requirements for operating the database can be met through sql statements.

  • Decoupling sql and program code: By providing a DAO layer, business logic and data access logic are separated, making the system design clearer, easier to maintain, and easier to unit test. The separation of sql and code improves maintainability.

  • Provides a mapping tag to support the mapping of the orm field relationship between the object and the database.

  • Provides object-relational mapping labels to support the maintenance of object-relational components.

  • Provides xml tags to support writing dynamic sql.

2. The first Mybatis program

Idea: build the environment -> import Mybatis -> write code -> test

2.1 Setting up the environment

2.1.1, create database

create database 'mybatis';

use 'mybatis';

create table 'user' {
  'id'  int(20) not null primary key,
  'name' varchar(30) default null,
  'pwd' varchar(30) default null
}engine=innodb default charset=utf-8

insert into 'user' ('id', 'name', 'pwd') values 
(1001, 'Mad God', '123'),
(1002, 'Java', '111'),
(1003, 'PPP', '122')

2.1.2 Create a new normal maven project

import maven dependencies

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <!--  parent project  -->
    <groupId>com.kuang</groupId>
    <artifactId>mybatis-study</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!--  import dependencies  -->
    <dependencies>
        <!--  mysql drive  -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <!--  mybatis  -->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <!--  junit  -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

2.2 Create a module

2.2.1 Ordinary maven module

[External link image transfer failed, the source site may have an anti-leech mechanism, it is recommended to save the image and upload it directly (img-uV3ZQTtD-1664800617496)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800012105.png)]

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-5eMxWHJt-1664800617497)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800027865.png)]

2.2.2 Write the core configuration file of mybatis

[connection to@localhost failed solution]

<?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">
<!--core configuration file-->
<configuration>
    <environments default="development">
        <!--surroundings -->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--    Every Mapper.xml need to be in MyBatis registered in the core configuration file-->
    <mappers>
        <mapper resource="com/qjd/dao/UserMapper.xml"/>
    </mappers>
</configuration>

2.2.3 Write the tool class of mybatis

package com.kuang.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
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //Use mybatis to get the sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            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 provides all the methods needed to execute SQL commands in the database
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

2.3 Writing code

2.3.1 Entity class

package com.kuang.poji;
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;
    }
}

2.3.2 Dao interface

package com.kuang.dao;
import com.kuang.poji.User;
import java.util.List;
public interface UserDao {
    List<User> getUserList();
}

2.3.3 Interface Implementation Class

The interface implementation class is changed from the original UserDaolmpl to the 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 one Dao/Mapper interface  -->
<mapper namespace="com.qjd.dao.UserDao">
    <!--  select Check for phrases, ID corresponding method name  -->
    <!-- ★★★ resultType , resultMap -->
    <select id="getUserList"  resultType="com.kuang.pojo.User">
        <!-- implement sql -->
        select * from mybatis.user
    </select>
</mapper>

2.4 Testing

package com.kuang.dao;
import com.kuang.poji.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
    @Test
    public void test() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        
        try{
            //execute sql
            //Method one getMapper
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            List<User> userList = mapper.getUserList();
            //Method 2
            //List<User> userList1 = sqlSession.selectList("com.qjd.dao.UserDao.getUserList");
            for (User user : userList) {
                System.out.println(user);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
}

Pay attention to the problem

  1. Profile not registered
  2. bind interface error
  3. wrong method name
  4. wrong return type
  5. maven export resource problem
    Since the xxx.xml file is placed in the java directory instead of the resource directory, there is a resource configuration problem:

Solution, add in pom.xml: Manually configure resource filtering

  <!--    exist build Medium configuration resources ， To prevent the problem of our resource export failure-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

**ERROR:**java.io.IOException: Could not find resource mybatis-config.xml
Modification method

Put in the pom.xml file under the root module

<packaging>pom</packaging>
change into
<packaging>jar</packaging>
or annotated
<!--<packaging>pom</packaging>-->

3. Add, delete, modify and check to achieve CRUD

3.1 namespace

The package name in the namespace should be the same as the package name in the interface—uesrDao–>userMapper

<mapper namespace="com.kuang.dao.UserMapper">

3.2 select

Select query statement:

  • id: is the method name in the corresponding namespace;
  • resultType: is the return value of the Sql statement execution;
  • parameterType: is the parameter type;

3.3 Insert

3.4 update

3.5 delete

package com.kuang.dao;
import com.kuang.pojo.User;
import java.util.List;
public interface UserMapper {
    // Query all users
    List<User> getUserList();
    // query user by id
    User getUserById(int id);
    // Add user
    int addUser(User user);
    // modify user
    int updateUser(User user);
    // delete users
    int deleteUser(int id);
}

<?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 one Dao/Mapper interface  -->
<mapper namespace="com.kuang.dao.UserMapper">
    <!--  select Check for phrases, id corresponding method name  -->
    <!-- ★★★ resultType , resultMap -->
    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from mybatis.user
    </select>
    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        select * from mybatis.user where id = #{id}
    </select>
    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd});
    </insert>
    <update id="updateUser" parameterType="com.kuang.pojo.User">
        update mybatis.user set name = #{name} , pwd = #{pwd} where id = #{id}
    </update>
    <delete id="deleteUser" parameterType="com.kuang.pojo.User">
        delete from mybatis.user where id = #{id};
    </delete>
</mapper>

test code

package com.kuang.dao;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
    @Test
    public void testSelectAll() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try{
            //execute sql
            //Method one getMapper
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = mapper.getUserList();
            //Method 2
            //List<User> userList1 = sqlSession.selectList("com.qjd.dao.UserMapper.getUserList");
            for (User user : userList) {
                System.out.println(user);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
    @Test
    public void getUserByIdTest() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try{
            //execute sql
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User userById = mapper.getUserById(101);
            System.out.println(userById);
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
    // [Additions, deletions and modifications] Transactions need to be submitted
    @Test
    public void addUserTest() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try{
            //execute sql
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int num = mapper.addUser(new User(1001, "wahahah", "wahha"));
            if(num > 0) {
                System.out.println("Added successfully");
            }
            // commit transaction
            sqlSession.commit();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
    @Test
    public void updateUserTest() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try{
            //execute sql
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int num = mapper.updateUser(new User(1001, "wahahah", "hehehe"));
            if(num > 0) {
                System.out.println("Successfully modified");
            }
            // commit transaction
            sqlSession.commit();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
    @Test
    public void deleteUserTest() {
        //Step 1: Get the SqlSession object
        // In jdbc is to get the prepareStatement object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try{
            //execute sql
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int num = mapper.deleteUser(1001);
            if(num > 0) {
                System.out.println("successfully deleted");
            }
            // commit transaction
            sqlSession.commit();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            //close SqlSession
            sqlSession.close();
        }
    }
}

// commit transaction
sqlSession.commit();

3.6 Common mistakes

[Note] Additions, deletions and changes must submit transactions

  • Do not match wrong tags select, update...
  • resource binding mapper, need to use path
  • Program configuration files must conform to the specification
  • Null pointer exception, not registered to resource
  • There is a problem of Chinese garbled characters in the target output xml file
  • maven resource not exporting problem

3.7 The all-purpose map method

Assuming that our entity class, or table in the database, has too many fields or parameters, we should consider using Map!

//all-purpose map
int  addUser2(Map<String,Object> map);

<insert id="addUser2" parameterType="map">
    <!-- transfer map of key -->
    insert into mybatis.user (id, name, pwd) values (#{userid},#{userName},#{passWord});
</insert>

@Test    public  void addUser2Test (){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Object> map = new HashMap<String, Object>();
    map.put("userid",5);
    map.put("userName","hello");
    map.put("passWord","976543");
    
    mapper.addUser2(map);
    
    sqlSession.commit();
    sqlSession.close();
}
  • Map passes parameters, and you can directly take out the key in sql! [parameterType = 'map']
  • The object passes parameters, and you can directly go to the properties of the object in sql! [parameterType = 'Object']
  • When there is only one basic type parameter, you can get it directly in sql!
  • Multiple parameters use Map, or annotation

3.8 Fuzzy query

When the java code is executed, pass the wildcard %%

List<User> userList = mapper.getUserLike("plum%");

Use wildcards in sql concatenation (there is sql injection problem)

4. Configuration Analysis

4.1 Core configuration file

mybatis-config.xml

MyBatis configuration files contain settings and property information that can deeply affect MyBatis behavior. The top-level structure of the configuration document is as follows:

  1. Create the mybatis-config.xml file under the src/main/resources path
  2. Write the tool class MybatisUtils.java in the src/main/java/com/qjd/utils path Read the configuration file to get the sqlsessionfactory
  3. Write the entity class User.java under the path of src/main/java/com/qjd/pojo
  4. Write the interfaces UserMapper.java and UserMapper.xml under the src/main/java/com/qjd/dao path
  5. write test class

4.2 Environment configuration (environments)

  • MyBatis can be configured to adapt to a variety of environments
  • Remember though: Although multiple environments can be configured, only one environment can be selected per SqlSessionFactory instance.
<environments default = "development">
    <!--surroundings -->
    <environment id = "development">
        <!--Environmental information -->
    </environment>
    <environment id = "test">
        <!--Environmental information -->
    </environment>
</environments>
  • The configuration of the data source (eg: type="POOLED")
  • Learn to configure multiple operating environments ----- change id
  • The default transaction manager of Mybatis is JDBC, connection pool: POOLED

4.3 Properties

<dataSource type="POOLED">
    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
    <!-- Escape: &amp; Equivalent to &  -->
    <!--<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>-->
    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
</dataSource>

We can refer to the configuration file through the properties attribute

These properties can be configured externally and can be dynamically replaced. You can configure these properties either in a typical Java properties file or in a sub-element of the properties element [db.properties]

Write a configuration file db.properties:

driver=com.mysql.cj.jdbc.Driver
#url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8
url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT
username=root
#password=123456

Introduced in the core configuration file:

<!--core configuration file-->
<configuration>
    <!-- Import external configuration files -->
    <properties resource="db.properties">
        <property name="password" value="123456"/>
    </properties>
    <!-- Environment configuration -->
    <environments default="development">
        <!--surroundings -->
        <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>

    <!--    Every Mapper.xml need to be in MyBatis registered in the core configuration file-->
    <mappers>
        <mapper resource="com/yxy/dao/UserMapper.xml"/>
    </mappers>

</configuration>

[Precautions]

  1. External files can be imported directly;
  2. You can add some property configurations to it;
  3. If two files have the same field, the external configuration file is preferred;

4.4 typeAliases

  • A type alias can set an abbreviated name for a Java type.
  • It is only used for XML configuration and is intended to reduce redundant fully qualified class name writing
    method one:
<!-- Alias ​​settings -->
<typeAliases>
    <typeAlias type="com.yxy.pojo.User" alias="User"></typeAlias>
</typeAliases>

Method Two
You can specify a package name, MyBatis will search for the required Java Bean under the package name,

For example: scan the package of the entity class, its default alias is the class name of this class, the first letter is lowercase

<typeAliases>
    <typeAlias type="com.yxy.pojo.User" alias="User"></typeAlias>
    <package name="com.yxy.pojo"/>
</typeAliases>

    <select id="getUserList"  resultType="user">
        select * from mybatis.user
    </select>

When there are few entity classes, use the first method;
If there are many entity classes, it is recommended to use the second one (using annotations in entity classes can be aliased)

@Alias("author")
public class Author {
    ...
}

Below are some built-in type aliases for common Java types. They are all case-insensitive, note that a special naming style is adopted to deal with duplication of names for primitive types.

aliastype of mapping
_bytebyte
_char (since 3.5.10)char
_longlong
_shortshort
_intint
_integerint
_doubledouble
_floatfloat
_booleanboolean
stringString
byteByte
char (since 3.5.10)Character
character (since 3.5.10)Character
longLong
shortShort
intInteger
integerInteger
doubleDouble
floatFloat
booleanBoolean
dateDate
decimalBigDecimal
bigdecimalBigDecimal
bigintegerBigInteger
objectObject
date[]Date[]
decimal[]BigDecimal[]
bigdecimal[]BigDecimal[]
biginteger[]BigInteger[]
object[]Object[]
mapMap
hashmapHashMap
listList
arraylistArrayList
collectionCollection
iteratorIterator

4.5 settings

These are extremely important tuning settings in MyBatis that change the runtime behavior of MyBatis. The following table describes the meaning, default value, etc. of each setting in Settings.

setting namedescribevalid valueDefaults
cacheEnabledGlobally turns on or off any cache that has been configured in all mapper configuration files.true | falsetrue
lazyLoadingEnabledA global switch for lazy loading. When on, all associated objects are lazy loaded. The switch state of the item can be overridden by setting the fetchType property in a specific relationship.true | falsefalse
aggressiveLazyLoadingWhen on, calls to either method will load all lazy-loaded properties of the object. Otherwise, each lazy-loaded property is loaded on demand (see lazyLoadTriggerMethods).true | falsefalse (default true in 3.4.1 and earlier)
multipleResultSetsEnabledWhether to allow a single statement to return multiple result sets (requires database driver support).true | falsetrue
useColumnLabelUse column labels instead of column names. The actual performance depends on the database driver. For details, please refer to the relevant documentation of the database driver, or observe through the comparison test.true | falsetrue

............

An example of a fully configured settings element is as follows:

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="aggressiveLazyLoading" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="safeResultHandlerEnabled" value="true"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
  <setting name="defaultScriptingLanguage" value="org.apache.ibatis.scripting.xmltags.XMLLanguageDriver"/>
  <setting name="defaultEnumTypeHandler" value="org.apache.ibatis.type.EnumTypeHandler"/>
  <setting name="callSettersOnNulls" value="false"/>
  <setting name="returnInstanceForEmptyRow" value="false"/>
  <setting name="logPrefix" value="exampleLogPreFix_"/>
  <setting name="logImpl" value="SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING"/>
  <setting name="proxyFactory" value="CGLIB | JAVASSIST"/>
  <setting name="vfsImpl" value="org.mybatis.example.YourselfVfsImpl"/>
  <setting name="useActualParamName" value="true"/>
  <setting name="configurationFactory" value="org.mybatis.example.ConfigurationFactory"/>
</settings>

4.6 Other configuration (just understand)

4.7 mappers

MapperRegistry: Register and bind our Mapper file, and write a Mapper file every time you write a dao layer.

Now that the behavior of MyBatis has been configured by the above elements, we are now going to define the SQL mapping statement. But first, we need to tell MyBatis where to find these statements. Java doesn't provide a good solution for automatically finding resources, so the best way is to tell MyBatis directly where to look for the mapping file. You can use classpath-relative resource references, or fully qualified resource locators (including URL s of the form file:///), or class and package names, etc. E.g:

Method 1: It is recommended to use

<!-- Use classpath-relative resource references -->
<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 the class file to bind the registration

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

Notice:

  • The interface and its Mapper configuration file must have the same name
  • The interface and its Mapper configuration file must be in the same package
    Method 3: Use scan package for injection
<!-- Register all mapper interface implementations in the package as mappers -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

Note the same method 2
practise:

  1. Import the database configuration file externally
  2. entity class name
  3. Ensure that the UserMapper interface and UserMapper.xml have the same name and are placed in the same package

4.8 Lifecycle and scope

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-Ee8v4PJZ-1664800617499)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800229452.png)]

Different scopes and lifecycle classes are critical, because incorrect usage can lead to very serious concurrency issues.

SqlSessionFactoryBuilder:

  • Once the SqlSessionFactory is created, the SqlSessionFactoryBuilder is no longer needed;

  • local variables;
    SqlSessionFactory:

  • It can be understood as a database connection pool;

  • The SqlSessionFactory once created should exist for the duration of the application's runtime, there is no reason to discard it or recreate another instance;

  • The best scope of SqlSessionFactory is the application scope, the easiest is to use the singleton mode or static singleton mode;
    SqlSession:

  • A request to connect to the connection pool;

  • Instances of SqlSession are not thread-safe and therefore cannot be shared, so their optimal scope is request or method scope;

  • After using it, you need to close it quickly, otherwise the resources will be occupied;

Each Mapper here represents a specific business;

5. Solve the problem of inconsistent attribute name and field name

5.1 Question

fields in the database

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-bvEGBkES-1664800617500)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800278805.png)]

Create a new project, copy the previous one, and test if the entity class fields are different;

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

Caused the problem: password is null

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-j5v37uKt-1664800617500)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800299673.png)]

problem causes:

select * from mybatis.user where id =#{id}
select id, name, pwd from mybatis.user where id =#{id}
//There is no pwd at this time

[Solution]

  • alias
  <select id="getUserById" parameterType="int" resultType="com.yxy.pojo.User">
        select id,name,pwd as password from mybatis.user where id =#{id}
  </select>
  • resultMap

5.2 resultMap

result set mapping

id name pwd
id name password

<!--    result set mapping-->
    <resultMap id="UserMap" type="User">
<!--    column fields in the database, properties Properties in Entity Classes-->
        <result column="id"   property="id"/>
        <result column="name" property="name"/>
        <result column="pwd"  property="password"/>
    </resultMap>

<select id="getUserById" parameterType="int" resultMap="UserMap">
    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 to achieve zero configuration for simple statements, and for more complex statements, you only need to describe the relationship between the statements.

5.3 Review steps

Create a new mybatis-04 module:

  1. Create the mybatis-config.xml file in the src/main/resources path to create the core configuration file
  2. Write the tool class MybatisUtils.java in the src/main/java/com/qjd/utils path to read the configuration file to get the sqlsessionfactory
  3. Write the entity class User.java under the src/main/java/com/qjd/pojo path
  4. Write the interfaces UserMapper.java and UserMapper.xml under the src/main/java/com/qjd/dao path
  5. write test class

6. Log

6.1 Log Factory

If there is an abnormality in a database operation, we need to troubleshoot, and the log is the best assistant;

Before: sout,debug;

now: log factory;

  • SLF4J
  • LOG4J (obsolete since 3.5.9) ----- master
  • LOG4J2
  • JDK_LOGGING
  • COMMONS_LOGGING
  • STDOUT_LOGGING — master
  • NO_LOGGING
    Which log implementation to use in Mybatis is set in the settings;

STDOUT_LOGGING — standard log output;

In the mybatis core configuration file, configure our log;

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-oliu6VJX-1664800617502)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800365323.png)]

6.2 log4j

what is log4j

  • Log4j is an open source project of Apache. By using Log4j, we can control the destination of log information delivery to console, file, and GUI components
  • We can also control the output format of each log
  • By defining the level of each log information, we can control the log generation process in more detail
  • Flexible configuration through a configuration file without modifying the application code
    1. Import log4j dependencies
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2,log4j.properties : 

### configure root ###
log4j.rootLogger = debug,console,file

### Configure output to console ###
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 =  %d{ABSOLUTE} %5p %c{1}:%L - %m%n

### Configure output to file ###
log4j.appender.file = org.apache.log4j.FileAppender
log4j.appender.file.File = ./log/yxy.log

log4j.appender.file.Append = true
log4j.appender.file.Threshold = debug

log4j.appender.file.layout = org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### Configure output to a file, and create a file every day ###
log4j.appender.dailyRollingFile = org.apache.log4j.DailyRollingFileAppender
log4j.appender.dailyRollingFile.File = logs/log.log
log4j.appender.dailyRollingFile.Append = true
log4j.appender.dailyRollingFile.Threshold = debug
log4j.appender.dailyRollingFile.layout = org.apache.log4j.PatternLayout
log4j.appender.dailyRollingFile.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### set output sql level, where logger The following content is all jar The package name contained in the package ###
log4j.logger.org.mybatis=debug
log4j.logger.java.sql=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug
log4j.logger.java.sql.ResultSet=debug

3. Configure log4j as the implementation of the log

<settings>
    <!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
    <setting name="logImpl" value="LOG4J"/>
</settings>

4. The use of Log4j, directly run the test just now

Simple to use

  1. In the class where you want to use Log4j, import the package import org.apache.log4j.Logger;
  2. The log object, the parameter is the class of the current class
import org.apache.log4j.Logger;
static  Logger logger = Logger.getLogger(UserMapperTest.class);
  1. log level
logger.info("info:got inside testLog4j");
logger.debug("debug:got inside testLog4j");
logger.error("error:got inside testLog4j");

7. Paging

Think: Why paginate?

  • Reduce the amount of data processed

7.1 Paging with Limit

SELECT * FROM user limit startIndex,pageSize;
SELECT * FROM user limit 0,2;
SELECT * FROM user limit 3;#[0,n]

Use Mybatis to implement paging, the core is sql

1. Interface

// pagination
List<User> getUserByList(Map<String, Integer> map);

2,Mapper.xml

<!-- resultMap element is MyBatis The most important and powerful element in -->
<resultMap id="UserMap" type="User">
    <!-- column :  fields in the database -->
    <!-- property:  Properties in Entity Classes -->
    <result column="pwd" property="password"></result>
</resultMap>

<!-- Paging query -->
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
    select * from mybatis.user limit #{startIndex}, #{pageSize}
</select>

3. Test

@Test
public void getUserByLimitTest() {
    //Step 1: Get the SqlSession object
    // In jdbc is to get the prepareStatement object
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try{
        //execute sql
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Integer> map = new HashMap<String, Integer>();
        map.put("startIndex", 0);
        map.put("pageSize", 2);

        List<User> userList = mapper.getUserByLimit(map);
        for (User user : userList) {
            System.out.println(user);
        }
    }
    catch (Exception e){
        e.printStackTrace();
    }
    finally {
        //close SqlSession
        sqlSession.close();
    }
}

7.2 RowBounds tab

1. Interface

//Pagination 2
List<User> getUserByRowBounds();

2,Mapper.xml

<!--    Pagination 2-->
<select id="getUserByRowBounds"  resultMap="UserMap">
    select * from mybatis.user
</select>

3. Test

@Test
public void getUserByRowBounds(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //RowBounds implementation
    RowBounds rowBounds = new RowBounds(1, 2);

    //Paging through java code level
    List<User> userList = sqlSession.selectList("com.qjd.dao.UserMapper.getUserByRowBounds",null,rowBounds);
    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();
}

7.3 Paging plugin

MyBatis paging plugin PageHelper

How to use ---- https://pagehelper.github.io/docs/howtouse/

8. Develop with annotations

8.1 Interface-oriented programming

8.1.1 , Concept

1. What is interface-oriented programming

Interface-oriented programming is to first extract the customer's business logic line as an interface, and the specific implementation of the business is completed through the implementation class of the interface.

When customer requirements change, you only need to write a new implementation class of the business logic, by changing the interface in the configuration file (such as the Spring framework)

The implementation class can complete the requirements without rewriting the existing code, reducing the impact on the system.

copy code

2. The advantages of interface-oriented programming

1 Reduce the coupling of the program. It can decouple to the greatest extent. The so-called decoupling means decoupling, which is opposite to coupling. Coupling is connection

, the stronger the coupling, the closer the connection. A tight connection in a program is not a good thing, because the more closely connected two things are, the more you replace

The more difficult one of them is, the more difficult it is to extend functionality and debug.

2 Easy program extension;

3 is conducive to the maintenance of the program;

copy code

3. The embodiment of interface programming in design patterns: the open-closed principle

The idea it follows is: open for extension, closed for modification. It is precisely what follows is to use the interface to achieve. using interface-oriented programming

, the specific logic and implementation are separated, which reduces the interdependence between various classes. When each class changes, there is no need to perform any

To make changes, you can add new implementation classes, and you don't need to worry about the impact of the newly changed classes on other modules of the system.

copy code

8.1.2. Design Patterns

procedural programming

Object-Oriented Programming

interface-oriented programming

1. Procedural programming

Process-oriented is to analyze the steps required to solve the problem, and then use functions to implement these steps step by step, and call them one by one when they are used.

Process-oriented is a process-centric programming idea. Process-oriented is the most practical way of thinking, and even object-oriented methods have process-oriented thinking. It can be said that process-oriented is a basic method. It considers the actual implementation. Generally, process-oriented is to refine step by step from top to bottom, so the most important thing for process-oriented is the modular way of thinking.

2. Object-Oriented Programming

Object-oriented is to decompose the transaction that constitutes the problem into various objects. The purpose of creating an object is not to complete a step, but to describe the behavior of a certain thing in the entire problem-solving step.

Object: An object is anything to be studied. For example, a human being is an object, but objects have attributes and methods, so height, weight, age, gender, etc., these are the characteristics that everyone has and can be summarized as attributes.

Class: A class is a template for an object. That is, a class is the definition of a group of objects with the same properties and operations, and the methods and data contained in a class describe the common properties and behaviors of a group of objects. A class is an abstraction over an object, and an object is a reification of a class, an instance of a class.

2.2.1 The basic characteristics of object-oriented encapsulation, inheritance, polymorphism, abstraction

Encapsulation: It is to privatize properties and provide public methods to access private objects.

Inheritance: When multiple classes have the same characteristics (attributes) and behaviors (methods), the same part can be extracted and put into one class as a parent class, and other classes inherit from this parent class. The inherited subclass automatically has the attributes and methods of the parent class, such as cats, dogs, and pigs. Their common features are animals, and they all run and bark.

However, it should be noted that the private properties and constructors of the parent class cannot be inherited. In addition, subclasses can write their own unique properties and methods, the purpose is to realize the expansion of functions, and subclasses can also override the methods of the parent class.

Polymorphism: Simply put, it means "one definition, many implementations". The same thing can take many forms. There are two forms of polymorphism in JAVA language: method overloading and object polymorphism.

Method overloading: In a class, multiple methods are allowed to use the same name, but the parameters of the method are different, and the completed functions are also different

Object polymorphism: subclass objects can be converted to and from the parent class object, and the completed functions are different depending on the subclass it uses

copy code

3. Interface-oriented programming

What is Interface Oriented Programming?

In an object-oriented system, the various functions of the system are completed by the cooperation of many different objects. In this case, how each object realizes itself is not so important to the system designer; and the cooperative relationship between each object becomes the key of system design. From the communication between different classes to the interaction between various modules, it is important to consider at the beginning of the system design, which is also the main work content of the system design. Interface-oriented programming refers to programming according to this idea.

2.3.1 Understanding of the interface.

From a deeper understanding of the interface, it should be the separation of definition (norm, constraint) and implementation (principle of separation of name and reality).

The interface itself reflects the system designer's abstract understanding of the system.

There should be two types of interfaces:

The first class is an abstraction of an entity, which can correspond to an abstract class;

The second category is the abstraction of a certain aspect of an entity, that is, the formation of an abstract surface (interface);

copy code

An entity may have multiple abstract faces.

There is a difference between an abstract body and an abstract surface.

Object-oriented means that when we consider a problem, we take the object as a unit and consider its properties and methods;

Process-oriented means that when we consider a problem, we take a specific process (transaction process) as a unit and consider its implementation;

Interface design and non-interface design are aimed at reuse technology, and are not a problem with object-oriented (process);

8.2 Develop with annotations

1. Interface

// Query all users
@Select("select * from user")
List<User> getUsers();

2,Mapper.xml

<!--  bind interface  -->
<mappers>
    <mapper class="com.yxy.dao.UserMapper"></mapper>
</mappers>

3. Test

@Test
public void getUsersTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.getUsers();
    for (User user : users) {
        System.out.println(user);
    }
    sqlSession.close();
}

Essence: reflection mechanism implementation

Bottom layer: dynamic proxy

[External link image transfer failed, the origin site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-7QNGHf9O-1664800617503)(C:\Users374\AppData\Roaming\Typora\typora-user-images\ 1664800433884.png)]

8.3 Mybatis detailed execution process

Outline steps:

  1. Create the mybatis-config.xml file in the src/main/resources path to create the core configuration file
  2. Write the tool class MybatisUtils.java in the src/main/java/com/qjd/utils path to read the configuration file to get the sqlsessionfactory
  3. Write the entity class User.java under the src/main/java/com/qjd/pojo path
  4. Write the interfaces UserMapper.java and UserMapper.xml under the src/main/java/com/qjd/dao path
  5. write test class

8.4 CRUD

We can automatically commit transactions when the tool class is created

1. Tools (automatically commit transactions)

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

2. Interface

public interface UserMapper {
    // Query all users
    @Select("select * from user")
    List<User> getUsers();

    // When there are multiple parameters, the @Param annotation must be added
    @Select("select * from user where id = #{id}")
    User getUserById(@Param("id") int id);

    @Insert("insert into user(id, name, pwd) values (#{id}, #{name}, #{password})")
    int addUser(User user);

    @Update("update user set name = #{name}, pwd = #{password} where id = #{id}")
    int updateUser(User user);

    @Delete("delete from user where id = #{uid}")
    int deleteUser(@Param("uid") int id);
}

8.5 About the @Param("") annotation

  • Parameters of basic type or String need to be added
  • Reference types do not need to be added
  • If there is only one basic type, it can be ignored, but it is recommended that everyone add it
  • What we refer to in sql is the attribute name set in our @Param("") here

8.6 #{} and ${}

  1. #{} is precompiled processing, ${} is string replacement ;
  2. mybatis handles two characters differently:
    1. When processing #{}, the entire #{} in sql will be replaced with a placeholder (ie:?), and the set method of PreparedStatement will be called to assign the value;
    2. When dealing with ${ }, replace ${ } with the value of the variable.
  3. If you use ${} to write SQL, it will appear: malicious SQL injection, there is no way to guarantee the data security of the database;
  4. Using #{} can effectively prevent SQL injection and improve system security.
    precompiled mechanism. Pre-compilation is to pre-compile the SQL statement in advance, and then call the SQL, and the injected parameters will not be SQL-compiled again. SQL injection occurs during the compilation process, because some special characters are maliciously injected, and finally the SQL is easily passed when compiled, resulting in data leakage. The precompile mechanism can prevent SQL injection very well.

9,Lombok

Project Lombok is a Java Libraries, which are plugins that automatically plug into editors and build tools, and Lombok provide a useful set of annotations to eliminate a lot of boilerplate code in Java classes. Only five characters (@ Data ) can replace hundreds of lines of code resulting in clean, concise, and easy-to-maintain Java classes.

Steps for usage:

  1. Install Lombok plugin in IDEA
  2. Import Lombok's jar package into the project
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>

</dependency>
  1. Annotate the entity class
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String password;
}

@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass

[illustrate]
@Data: No parameter construction, get, set, toString,hashcode,equals

@AllArgsConstructor

@NoArgsConstructor

@Getter and @Setter

@ToString

10. Many-to-one processing

basic concept

Many to one:

  • Multiple students correspond to one teacher
  • For students, association... Multiple students are associated with a teacher [many-to-one]
  • For teachers, collection... A teacher has many students [one-to-many]
    result map (resultMap)

association

– an association of a complex type; many results will be wrapped into this type

Nested result maps - associations can be resultMap elements, or references to other result maps

collection

– a collection of complex types

Nested result maps – collections can be resultMap elements, or references to other result maps

10.1 Test environment setup

SQL

New student table and teacher 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, 'Teacher Qin'); 

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', 'little red', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Xiao Zhang', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'Xiao Li', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'little king', '1');​

1. Import lombok in pom.xml (you don't need to add construction methods yourself ----- use @Data)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatis-study</artifactId>
        <groupId>com.kuang</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-06</artifactId>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

    </dependencies>

</project>

2. Create a new entity class Teacher,Student under the pojo package

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

    // Students need to be associated with a teacher
    private Teacher teacher;
}

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

3. Create Mapper interface

public interface StudentMapper {
}

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

4. Create Mapper.xml file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--core configuration file-->
<mapper namespace="com.yxy.dao.StudentMapper">

</mapper>



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--core configuration file-->
<mapper namespace="com.yxy.dao.TeacherMapper">

</mapper>

5. Bind and register our Mapper interface or file in the core configuration file [there are many ways]

<!--  bind interface  -->
<mappers>
    <!--<mapper class="com.yxy.dao.UserMapper"></mapper>-->
    <!--<mapper class="com.yxy.dao.TeacherMapper"></mapper>-->
    <mapper resource="com/yxy/dao/TeacherMapper.xml"></mapper>
    <mapper resource="com/yxy/dao/StudentMapper.xml"></mapper>
</mappers>

6. Test whether the query can be successful

10.2 Processing according to query nesting

select s.id ,s.name ,t.name from student s,teacher t where s.tid=t.id

<mapper namespace="com.yxy.dao.StudentMapper">
    <!--
        1. Query all student information
        2. According to the identified students id，find the corresponding teacher information
    -->
    <select id="getStudent" resultMap="Student_Teacher">
        select * from student;
    </select>

    <resultMap id="Student_Teacher" type="com.yxy.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <!--
            For complex properties, it needs to be handled separately, such as the current Teacher
            Object: association
            gather: collection
        -->
        <association property="teacher" column="tid" javaType="com.yxy.pojo.Teacher" select="getTeacher"></association>
    </resultMap>

    <select id="getTeacher" resultType="com.yxy.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>

</mapper>

10.3 Nested processing according to results

<mapper namespace="com.yxy.dao.StudentMapper">
    <!-- Nested processing according to results -->
    <select id="getStudent2" resultMap="Student_Teacher2">
        select  s.id sid, s.name sname, t.name tname
        from mybatis.student s, mybatis.teacher t
        where s.tid=t.id;
    </select>
    <resultMap id="Student_Teacher2" type="com.yxy.pojo.Student">
        <!-- Notice column The name corresponds to the query statement such as: sid-->
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="com.yxy.pojo.Teacher">
            <result property="name" column="tname"></result>
        </association>
    </resultMap>
</mapper>

11. One-to-many processing

For example: a teacher teaches multiple students, for teachers, it is a one-to-many relationship

11.1 Test environment setup

Entity class changes

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

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

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

    private int tid;
}

11.2 Nested processing according to results

<select id="getTeachersList" resultType="com.yxy.pojo.Teacher">
    select * from mybatis.teacher;
</select>

<select id="getTeacher" resultMap="Teacher_Student">
    select s.id sid, s.name sname, t.name tname, t.id tid
    from mybatis.teacher t, mybatis.student s
    where s.tid = t.id and t.id = #{tid};
</select>
<resultMap id="Teacher_Student" type="com.yxy.pojo.Teacher">
    <result property="id" column="tid"></result>
    <result property="name" column="tname"></result>
    <!--
        For complex properties, it needs to be handled separately, such as the current Teacher
        Object: association
        gather: collection
        javaType is the specified property type
        Generic information in the collection, using ofType Obtain
    -->
    <collection property="students" ofType="com.yxy.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="tid"></result>
    </collection>
</resultMap>

11.3 Processing according to query nesting

<select id="getTeacher2" resultMap="Teacher_Student2">
    select * from mybatis.teacher where id = #{tid};
</select>
<resultMap id="Teacher_Student2" type="com.yxy.pojo.Teacher">
    <collection property="students" javaType="ArrayList" ofType="com.yxy.pojo.Student" select="getStudentByTid" column="id"/>
</resultMap>

<select id="getStudentByTid" resultType="com.yxy.pojo.Student">
    select * from mybatis.student where tid = #{tid};
</select>

11.4 Summary

  1. Association-association [many-to-one]
  2. Collection-collection [one-to-many]
  3. javaType & ofType
    1. javaType is used to specify the type of the attribute in the entity class
    2. ofType is used to specify the pojo type mapped to the List or collection, the constraint type in the generic type
      be careful:
  • Ensure the readability of SQL and try to ensure that it is easy to understand
  • Pay attention to the problem of attribute names and fields in one-to-many and many-to-one
  • If the problem is not easy to troubleshoot errors, you can use the log, it is recommended to use log4j

12. Dynamic SQL

basic concept

What is dynamic SQL: Dynamic SQL is to generate different SQL statements according to different conditions.

Using dynamic SQL is not an easy task, but with a powerful dynamic SQL language that can be used in any SQL mapping statement, MyBatis significantly improves the ease of use of this feature.

If you've used JSTL or any XML-like language-based text processor before, dynamic SQL elements may feel familiar to you. In previous versions of MyBatis, it took time to understand a large number of elements. With the help of powerful OGNL-based expressions, MyBatis 3 replaces most of the previous elements, greatly reducing the types of elements, and now there are less than half of the elements to learn.

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

12.1 Setting up the environment

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT 'blog id',
`title` VARCHAR(100) NOT NULL COMMENT 'blog title',
`author` VARCHAR(30) NOT NULL COMMENT 'blogger',
`create_time` DATETIME NOT NULL COMMENT 'creation time',
`views` INT(30) NOT NULL COMMENT 'pageviews'
)ENGINE=INNODB DEFAULT CHARSET=utf8

Create a base project

  1. guide package
  2. write configuration file
  3. write entity class
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date   createTime;
    private int views;
}
  1. Write the Mapper interface and Mapper.xml file corresponding to the entity class
  2. test
    [expand]
private Date createTime; // Attribute name and field name do not match

CamelCase named mapping create_time ==> createTime

<settings>
    <!-- Standard log factory -->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!-- Whether to enable camel case naming rule mapping-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

12.2 if

// Query Blog
List<Blog> queryBlogs(Map map);

<select id="queryBlogs" parameterType="map" resultType="com.yxy.pojo.Blog">
    select * from mybatis.blog where 1= 1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

12.3 choose,when,otherwise

List<Blog> queryBlogChoose(Map map);

Compared to where 1=1 before

<select id="queryBlogs" parameterType="map" resultType="com.yxy.pojo.Blog">
    select * from mybatis.blog
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

[equivalent to switch-case structure]

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

12.4 trim,where,set

where: The where element only inserts a "WHERE" clause if the child element returns nothing. Also, if the clause begins with "AND" or "OR", the where element also strips them.

// update blog
int updateBlog(Map map);

The set:set element dynamically inserts the SET keyword at the beginning of the line and strips out extra commas (which are introduced when assigning values ​​to columns using conditional statements)

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

trim:

The where element will only insert a "WHERE" clause if the child element returns nothing. Also, if the clause begins with "AND" or "OR", the where element also strips them.

If the where element is not what you expect, you can also customize the function of the where element by customizing the trim element. For example, a custom trim element equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

The prefixOverrides property ignores sequences of text separated by pipes (note that the spaces are necessary in this example). The above example removes all the content specified in the prefixOverrides attribute and inserts the content specified in the prefix attribute.
The so-called dynamic SQL is essentially an SQL statement, but we can execute a logic code at the SQL level

if where set choose when

12.5 SQL Fragments

Parts of some functions are extracted for easy reuse.

1. Use sql tags to extract public parts

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

2. Use the include tag to reference where you need to use it

<select id="queryBlogs" parameterType="map" resultType="com.yxy.pojo.Blog">
    select * from mybatis.blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

Precautions:

  • It is better to define sql fragments based on single table
  • don't have where tag in fragment

12.6 foreach

select *from user where 1=1 and

<foreach item="id"  collection="ids"
        open="(" separator="or" close=")" >
          id=#{id}

(id=1 or id=2 or id=3)

Another common use case for dynamic SQL is iterating over collections (especially when building IN conditionals). for example:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

code

// Query the Blog of records No. 1, 2, and 3
List<Blog> queryBlogForeach(Map map);

<select id="queryBlogForeach" parameterType="map" resultType="com.yxy.pojo.Blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

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

    HashMap hashMap = new HashMap();
    ArrayList<Integer> ids_list = new ArrayList<Integer>();
    hashMap.put("ids", ids_list);
    ids_list.add(1);
    ids_list.add(2);
    List<Blog> blogs = mapper.queryBlogForeach(hashMap);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

summary:

Dynamic SQL is splicing SQL statements. We only need to ensure the correctness of SQL and arrange and combine them according to the format of SQL.

Suggest:

  • First write the complete SQL in Mysql and then modify it accordingly to become our dynamic SQL implementation.
  • Mysql key knowledge
    • Mysql engine
    • The underlying principle of InnoDB
    • index
    • Index optimization

13. Cache

13.1 Introduction

13.1.1. What is Cache [Cache]

  • Temporary data stored in memory
  • Put the data frequently queried by users in the cache (memory), users do not need to query the data from the disk (relational database data file), query from the cache, thereby improving query efficiency and solving the performance of high concurrent systems question

13.1.2. Why use cache?

  • Reduce the number of interactions with the database, reduce system overhead, and improve system efficiency

13.1.3. What kind of data can be cached?

  • Data that is frequently queried and infrequently changed

13.2 Mybatis cache

  • MyBatis includes a very powerful query cache feature that makes it very easy to customize and configure the cache. Caching can greatly improve query efficiency.
  • Two-level caches are defined by default in the MyBatis system: first-level cache and second-level cache
    • By default, only the first level cache is enabled. (SqlSession level cache, also known as local cache)
    • The second-level cache needs to be manually enabled and configured, and it is based on the namespace-level cache.
    • In order to improve scalability, MyBatis defines the cache interface Cache. We can customize the second level cache by implementing the Cache interface

13.3 L1 Cache

  • The first level cache is also called the local cache:
  • Data queried during the same session with the database is placed in the local cache.
  • If you need to get the same data in the future, you can get it directly from the cache, and you don't have to query the database again;

test steps

  1. enable log
  2. Test querying the same record twice in the Session
public class MyTest {
    @Test
    public void getUserByIdTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user1 = mapper.getUserById(101);
        System.out.println(user1);

        User user2 = mapper.getUserById(102);
        System.out.println(user2);

        sqlSession.close();
    }
}
  1. View log output

cache invalidation

1. Inquire about different things;

2. The addition, deletion and modification operation may change the original data, so the cache must be refreshed!

3. Query different Mapper.xml

4. Manually clean the cache!

Summary: The first-level cache is enabled by default and is only valid in one SqlSession, that is, the interval between getting the connection and closing the connection (equivalent to a user constantly querying the same data, such as constantly refreshing), the first-level cache is a map

13.4 L2 Cache

  • The second-level cache is also called the global cache. The scope of the first-level cache is too low, so the second-level cache was born.
  • Based on namespace-level cache, a namespace corresponds to a second-level cache:
  • Working Mechanism
    • A session queries a piece of data, and the data will be placed in the first level cache of the current session:
    • If the current session is closed, the first-level cache corresponding to this session is gone; but what we want is that when the session is closed, the data in the first-level cache is saved to the second-level cache;
    • The new session query information, you can get the content from the second level cache:
    • The data detected by different mapperi will be placed in their corresponding cache (map);

13.4.1 Procedure

1. Turn on the global cache (settings)

<!-- Show enabled global cache -->
<setting name="cacheEnable" value="true"/>

2. Open in the Mapper that wants to use the second level cache
​ You can add no parameters, or you can customize the parameters.

<!-- in the current Mapper.xml L2 cache -->
<cache  eviction="FIFO"
        flushInterval="60000"
        size="512"
        readOnly="true"/>
<!--
    This more advanced configuration creates a FIFO cache, refreshed every 60 seconds,
    Can store up to 512 references to the resulting object or list,
    And the returned object is considered read-only,
    So modifying them may conflict with callers in different threads.
-->

The available cleanup strategies are:

  • LRU - Least Recently Used: Removes objects that have not been used for the longest time.
  • FIFO - First In First Out: Objects are removed in the order in which they entered the cache.
  • SOFT - Soft Reference: Objects are removed based on garbage collector state and soft reference rules.
  • WEAK - Weak References: Remove objects more aggressively based on garbage collector status and weak reference rules.
    3. Test
@Test
public void getUserByIdTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    SqlSession sqlSession2 = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user1 = mapper.getUserById(101);
    System.out.println(user1);
    sqlSession.close();

    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
    User user2 = mapper2.getUserById(101);
    System.out.println(user2);
    sqlSession2.close();
}

You can see that sql is only run once

question:

  1. We need to serialize the entity class (implement the Serializable interface), otherwise an error will be reported
  2. When the sqlsession is closed, it must be closed at the end. You cannot close the sqlsession first and then close the sqlsession2, which will cause Cause: org.apache.ibatis.executor.ExecutorException: Executor was closed
    4. Summary

As long as the second level cache is turned on, it is valid under the same Mapper

All data will be placed in the first level cache first

Only when the session is committed or closed will it be committed to the second level cache

13.5 Cache Principle

Cache order:

  1. First see if there is any second level cache
  2. See if there is any cache in the first level
  3. query database
    Note: There are no first-level and second-level caches, query the database, and put the data into the first-level cache after querying.

13.6 Custom cache - ehcache

introduce:

  • EhCache is a pure Java in-process caching framework with fast and lean features. It is the default CacheProvider in Hibernate.
  • Ehcache is a widely used open source Java distributed cache. Mainly for general purpose cache
    To use ehcache in a program, first import the package
<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.1.0</version>
</dependency>

Specify in mapper to use our ehcache cache implementation

<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

It is enough to understand the content of the cache. In the future, we will use the Redis database for the cache!

Tags: Java Mybatis

Posted by OpSiS on Tue, 04 Oct 2022 04:39:00 +1030

Popular Tags

©2023 Programs Wiki