Mybatis

Mybatis help documentation: mybatis – MyBatis 3 | Introduction

Mybatis download address: GitHub - mybatis/mybatis-3: MyBatis SQL mapper framework for Java

What is Mybatis

brief introduction

What is MyBatis?

• MyBatis is an excellent persistence layer framework, which supports custom SQL, stored procedures and advanced mapping.

• MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.

• MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.

• MyBatis was originally a part of apache Open source project iBatis, 2010 project Migrated from apache software foundation to [Google Code]( https://baike.baidu.com/item/google Code / 2346604) and renamed MyBatis. Moved to in November 2013 Github.

• Mybatis relies on jar packages

<--Mybatis rely on jar package-->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>

The first Mybatis program

Build environment

1. Build 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=utf8;
   
   insert into `user`(`id`,`name`,`pwd`) values
   (1,'Zhang San','123456'),(2,'Li Si','123456'),(3,'Wang Wu','222222')
   
   

   1. Create a normal maven project

   2. Delete the following src directory

   3. Import the dependent jar package in the pom file

   <!--Import dependency-->
       <dependencies>
           <!--mysql drive-->
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <version>5.1.47</version>
           </dependency>	
           
           <!--mybatis-->
           <dependency>
               <groupId>org.mybatis</groupId>
               <artifactId>mybatis</artifactId>
               <version>3.5.2</version>
           </dependency>
           
           <!--junit-->
           <dependency>
               <groupId>junit</groupId>
               <artifactId>junit</artifactId>
               <version>4.12</version>
           </dependency>
       </dependencies>
   

   1. Configure resource export settings in pom file to prevent resource export failure

   <!--stay Build Medium configuration resources,To prevent resource export failure-->
       <build>
           <resources>
               <resource>
                   <directory>src/main/resources</directory>
                   <includes>
                       <include>**/*.properties</include>
                       <include>**/*.xml</include>
                   </includes>
                   <filtering>true</filtering>
               </resource>
               <resource>
                   <directory>src/main/java</directory>
                   <includes>
                       <include>**/*.properties</include>
                       <include>**/*.xml</include>
                   </includes>
                   <filtering>true</filtering>
               </resource>
           </resources>
       </build>
   

Create module

1. Write core configuration file

   Create an xml folder in the resource directory

   [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-SMKGEGBl-1638097754729)(... /... / appdata / roaming / typora / typora user images / image-20211123172221272. PNG)]

Write code:

<?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 Core profile-->
<configuration>
    <environments default="development"><!--environment-->
        <environment id="development">
            <transactionManager type="JDBC"/><!--transaction management-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/> <!--drive-->
                <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>

</configuration>

1. Write Mybatis tool class

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {

        try {
            //Using mybatis, the first step is to obtain 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, we can get an instance of SqlSession from it
    //SqlSession provides all the methods required to execute SQL commands in the database
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

Write code

1. Entity class

   //Annotate the entity class to generate the corresponding get/set method
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class User {
       private Integer id;
       private String name;
       private String pwd;
   
   
   }
   

   2.doa interface

public interface UserDao {
    //Interface to get all User data
    List<User> getUserList();
}

1. The interface implementation class is transformed from the original UserDaoImpl to a Mapper configuration file

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-hRLXHA64-1638097754731)(... /... / appdata / roaming / typora / typora user images / image-20211123184617776. PNG)]

<?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(Namespace)=Bind a corresponding Dao/mappr Interface-->
<mapper namespace="com.hua.dao.UserDao">

    <select id="getUserList" resultType="com.hua.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

test

Note: org.apache.ibatis.binding.BindingException: Type interface com.hua.dao.UserDao is not known to the MapperRegistry

This error is that Mapper is not registered!!

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-QfyIo0zQ-1638097754732)(... /... /... / appdata / roaming / typora / typora user images / image-20211123203437549. PNG)]

public class UserText {

    @Test
    public void test(){
        //Step 1: obtain the sqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Method 1: getmapper
       UserDao mapper = sqlSession.getMapper(UserDao.class);
       List<User> userList = mapper.getUserList();

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

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

        //Close sqlSession
        sqlSession.close();
    }

}

CRUD

Realization

• namespace The package name should be consistent with that of the interface
  

• id: is the method name in the corresponding namespace

• Resulttype: return value of SQL statement execution

• parameterType: parameter type

1. Write interface

public interface UserDao {
    //Query all users
    List<User> getUserList();

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

    //Add user
    int getAddUser(User user);

    //Change user
    int updateUser(User user);

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

1. Write corresponding 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">

<mapper namespace="com.hua.dao.UserDao">
<!--Query all users-->
    <select id="getUserList" resultType="com.hua.pojo.User">
        select * from mybatis.user
    </select>
<!--according to ID View user-->
    <select id="getUserById" parameterType="int" resultType="com.hua.pojo.User">
        select * from mybatis.user where `id` = #{id}
    </select>
<!--Add a user-->
    <!---->
    <insert id="getAddUser" parameterType="com.hua.pojo.User">
        insert into mybatis.user(id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>
<!--Modify a user-->
    <update id="updateUser" parameterType="com.hua.pojo.User" >
        update mybatis.user set `name` = #{name} ,pwd=#{pwd} where id = #{id}
    </update>
<!--Delete a user-->
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id = #{id}
    </delete>
</mapper>

1. Test function

public class UserText {

    @Test
    public void test() {

        //Step 1: obtain the sqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
            //Method 1: getmapper
            //      UserDao mapper = sqlSession.getMapper(UserDao.class);
            //        List<User> userList = mapper.getUserList();
            //Mode 2:
            List<User> userList = sqlSession.selectList("com.hua.dao.UserDao.getUserList");
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {

        } finally {
            //Close sqlSession
            sqlSession.close();
        }
    }

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

        User userById = mapper.getUserById(2);
        System.out.println(userById);

        sqlSession.close();

    }

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

        UserDao mapper = sqlSession.getMapper(UserDao.class);

        int adduser = mapper.getAddUser(new User(10, "Zhao Tai", "999999"));

        sqlSession.commit();

        if (adduser > 0) {
            System.out.println("Insert successful");
        }

        sqlSession.close();


    }

    @Test
    public void updateUser() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int updateUser = mapper.updateUser(new User(5, "Zhang San", "111111"));

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

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

        mapper.deleteUser(0);

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

Universal Map

Assuming that there are too many entity classes or tables, fields or parameters in the database, we can consider using Map

• Map passes parameters and directly takes the key from sql [parameterType = "map"]
• Pass parameters to the object and directly get the attribute of the object in sql [parameterType = "int"]
• If there is only one basic type, you can get it directly in sql
• Map or annotation is used for multiple parameters

 User addUser2(Map<String,Object> map);  //Interface

<!--according to ID View user-->
    <select id="getUserById2" parameterType="map" resultType="com.hua.pojo.User">
        select * from mybatis.user where `id` = #{id} and `name` = #{name}
    </select>

 //<!-- View users by ID -- >
    public void getUserById2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id",4);
        mapper.addUser((User) map);

        sqlSession.close();
    }

Fuzzy query

1. When executing Java code, pass wildcard%%
2. Using wildcards in sql splicing

//Interface 
//Fuzzy query  
    List<User> getUserLike(String value); 

 //query
 <select id="getUserLike" resultType="com.hua.pojo.User" >
        SELECT * FROM mybatis.user WHERE `name` like "%"#{value}"%"
    </select>

 //test 
@Test
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = mapper.getUserLike("%w%");
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

Configuration function

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

Environment configurations

• MyBatis can be configured to adapt to a variety of environments
• However, only one environment can be selected for each SqlSessionFactory instance

Learn to use multiple configuration environments

The default transaction manager of mybatis is JDBC and connection pool is POOLED

properties

We can refer to the configuration file through the properties property

1. Write a db.porperties configuration file

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

This means that (properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?) "must be written in this prompt order

1. Import in core configuration file

<!--Import external profile-->
    <properties resource="db.properties"/>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

• You can import external files directly
• You can add some attribute configurations

Type aliases

• Type alias sets 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

<!--Alias an entity class-->
    <typeAliases>
        <typeAlias type="com.hua.pojo.User" alias="User"/>
    </typeAliases>

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xv6D2nsp-1638097754734)(... /... /... / appdata / roaming / typora / typora user images / image-20211125160342529. PNG)]

You can also specify a package name. Mybatis will search for the required Java beans under the package name. For example, scan the package of entity class. Its default alias is the class name of this class, with the initial lowercase user.

<!--Alias-->
<typeAliases>
    <package name="com.hua.pojo.User"/>
</typeAliases>

The first method is used when there are few entity classes.

If there are many entity classes, use the second one

The first one can customize the alias, but the second one can't. If you have to change it, you need to add annotations on the entity

//Alias annotation
@Alias("User")
public class User {}

settings

• These are extremely important tuning settings in MyBatis, which change the runtime behavior of MyBatis

Details: Mybatis – MyBatis 3 | configuration

mappers

Used to register and bind our Mapper file

It has four ways:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-vfUMbf6o-1638097754735)(... /... /... / appdata / roaming / typora / typora user images / image-20211125180725483. PNG)]

Method 1: restore [recommended]

 <!--register mapper-->
    <mappers>
        <mapper resource="com/hua/dao/UserMappr.xml"/>
    </mappers>

Method 2: register with Class file binding

<!--register mapper-->
<mappers>
    <mapper class="com.hua.dao.UserMapper"/>
</mappers>

Note:

• Interface and its mapper configuration file must have the same name
• The interface and configuration file must be under the same package

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-6AJJkkX4-1638097754735)(... /... /... / appdata / roaming / typora / typora user images / image-20211125182144677. PNG)]

Method 3: directly scan the package for binding

<!--register mapper-->
    <mappers>
     <package name="com.hua.dao"/>
    </mappers>

Note:

• Interface and its mapper configuration file must have the same name
• The interface and configuration file must be under the same package

Solve the inconsistency between field name and attribute

1. Question

Field in the database: [external chain image transfer failed, and the source station may have anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-Hym2lcmB-1638097754736)(... /... /... / appdata / roaming / typora / typora user images / image-20211126140612854. PNG)]

Create a new project and test the inconsistency of entity class fields before copying

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-Kb3Jkq1H-1638097754736)(... /... /... / appdata / roaming / typora / typora user images / image-20211126140802234. PNG)]

Test query result: pwd display is null

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (IMG dnnznxmx-1638097754737) (... /... /... / appdata / roaming / typora / typora user images / image-20211126140855492. PNG)]

Original query statement:

<!--query-->
    <select id="getUser" parameterType="int" resultType="User">
        select id,name,pwd  from mybatis.user where id = #{id}
    </select>

Solution 1: alias the pwd field

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

2,resultMap

Same question:

Result set mapping

id  name  pwd
id  name  password

 <!--type=User Alias of the entity class represented-->
    <resultMap id="UserMap" type="User">
        <!--column Fields in the database, property Properties in entity classes-->
        <result column="pwd" property="password"/>
        <result column="id" property="id"/>
        <result column="name" property="name"/>
    </resultMap>
    <!--resultMap=userMap above Map-->
    <select id="getUser" resultMap="UserMap" parameterType="int">
        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. For more complex statements, you only need to describe the relationship between statements.

journal

Factory log

If an exception occurs in a database operation, we need to troubleshoot it. The log is the best assistant!

Once: South, debug

Now: log factory

logImpl	Specify the specific implementation of the log used by MyBatis. If it is not specified, it will be found automatically.	SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING

• SLF4J
• LOG4J [Master]
• LOG4J2
• JDK_LOGGING java's own log output
• COMMONS_LOGGING Toolkit
• STDOUT_LOGGING console output [Master]
• NO_LOGGING has no log output

Which log implementation to use in mybatis needs to be set in the core configuration file settings!

STDOUT_LOGGING standard log output

Logs need to be configured in the core configuration file:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-OgTdh3QB-1638097754738)(... /... /... / appdata / roaming / typora / typora user images / image-20211126145215950. PNG)]

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

After running the program, the content will be output in the form of log:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-lSktrAWz-1638097754738)(... /... /... / appdata / roaming / typora / typora user images / image-20211126150246914. PNG)]

Implementation of LOG4J log

• Log4j yes Apache By using Log4j, we can control the destination of log information delivery Console , documents GUI assembly

• 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

• Through a configuration file To flexibly configure without modifying the application code

1. Import the dependency of LOG4J

<!--log4j rely on-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

1. Write log4j.properties

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

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

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

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

1. Configure log4j as the implementation of log

 <settings>
        <!--LOG4J Log factory implementation-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>

1. The use of Log4J runs directly!

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-oThaUG9i-1638097754739)(... /... /... / appdata / roaming / typora / typora user images / image-20211126153933075. PNG)]

Simple use

1. In the class to use Log4j, import the package import org.apache.log4.Logger;
2. Log object. The parameter is the Class of the current Class

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

1. Three commonly used levels

 @Test
    public void testlog4j(){
        logger.info("info:Entered testlog4j method");
        logger.debug("debug:Entered testlog4j method");
        logger.error("error:Entered testlog4j method");

    }

paging

Why pagination:

When the amount of data is too large, it can reduce the processing of data

Paging with Limit

Syntax: select * from user limit startIndex,pageSize;  startIndex : Starting value pageSize :  Maximum

Use mybatis to realize paging and core sql

1. Interface

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

2. Write sql

<!--type=User Alias of the entity class represented-->
    <resultMap id="UserMap" type="User">
        <!--column Fields in the database, property Properties in entity classes-->
        <result column="pwd" property="password"/>
    </resultMap>

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

1. test

@Test
//Paging test
    public void getLimit(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        Map<String, Integer> map = new HashMap<>();
        map.put("startIndex",0);
        map.put("pageSize",2);

        List<User> limit = mapper.getLimit(map);
        for (User user : limit) {
            System.out.println(user);
        }

        sqlSession.close();
    }

Using annotation development

1. Annotations are implemented on the interface

@Select("select * from user")
    List<User> getUser();

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

<mappers>
        <mapper class="com.hua.dao.UserMapper"/>
    </mappers>

1. test

   @Test
    public void getUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> user = mapper.getUser();

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

        sqlSession.close();
    }

CRUD

To use annotations, you must bind the interface to the core configuration file

 <mappers>
        <mapper class="com.hua.dao.UserMapper"/>
    </mappers>

You can automatically submit transactions when the tool class is created!

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

1. Write interface

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

    //Query user by id
    @Select("select * from user where id=#{id}")
    User getUserById(@Param("id") int id);

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

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

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

}

1. test

public class UserText {

    //Test: query all users
    @Test
    public void getUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> user = mapper.getUser();
        for (User user1 : user) {
            System.out.println(user1);
        }
    }
    //Test: query users by id
    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.getUserById(3);
        System.out.println(userById);
        sqlSession.close();
    }
    //Testing: inserting a user
    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(2, "ttttt", "1234532"));
        if (i==1){
            System.out.println("Insert successful");
        }

        sqlSession.close();
    }
    //Test: delete a user
    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(4);
    }
    //Testing: modifying users
    @Test
    public void updeteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(1, "wa", "222222"));

    }

}

Many to one processing

A group of students corresponds to a teacher:

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, 'Miss 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, 'Xiao Hong', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, 'Xiao Zhang', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, 'petty thief', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, 'Xiao Wang', 1);

Test environment construction

1. Import lombok

2. Create a new entity class, teacher and student

3. Establish Mapper interface

4. Create Mapper.xml file

5. Bind and register Mapper interface or file in core configuration [three methods are optional]

6. Test

Nested processing by query

 <!--Idea:
        1.Query the information of all students
        2.According to the student's tid，Find the corresponding teacher
    -->

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--Complex properties require separate processing of objects: association  Set: collection -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>

Nested processing according to results

<!--Idea:
        1.Query the information of all students
        2.According to the student's tid，Find the corresponding teacher
    -->
    <select id="getStudent" resultMap="StudentTeacher">
        select s.id sid,s.name sname,t.name tname
        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="name" column="tname"/>
        </association>
    </resultMap>

One to many processing

Entity class

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;
}

Interface

public interface TeacherMapper {

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

    Teacher getTeacherStudent(int id);

    Teacher getTeacherStudent2(int id);
}

Nested processing by query

<select id="getTeacherStudent2" resultMap="ts2">
    SELECT s.id sid,s.name sname,t.id tid,t.name tname
    FROM student s,teacher t
    WHERE s.tid = t.id and t.id = #{id}
</select>

<resultMap id="ts2" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--
        Object: association
        Set: collection
        javaType Specify the type of property,
        Generic information in the collection, using ofType obtain
    -->
    <collection property="students" javaType="List">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
    </collection>
</resultMap>

Nested processing according to results

<select id="getTeacherStudent" resultType="Teacher" resultMap="ts">
    select * from teacher where id = #{id}
</select>

<resultMap id="ts" type="Teacher">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="students" javaType="List" ofType="Student" select="getStudentByTid" column="id">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
    </collection>
</resultMap>

<select id="getStudentByTid" resultType="Student">
    select * from student where tid = #{id}
</select>

Summary:

1. association - association [many to one]

2. Set collection [one to many]

3.javaType: used to specify the type of attribute in the entity class

4.ofType: used to specify pojo types mapped to lists or collections, and constraint types in generic types

Attention

• Ensure the readability of SQL and make it easy to understand as much as possible
• Note the problem of attribute names and fields in one to many and many to one
• If the problem is difficult to troubleshoot, log can be used. Log4j is recommended

Interview frequency

• mysql engine
• innoDB underlying principle
• Indexes
• Index optimization

Dynamic SQL

Dynamic SQL is to generate different SQL according to different conditions

Build 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 'Views'
)ENGINE=INNODB DEFAULT CHARSET=utf8

Create a basic project

1. Guide Package

2. Prepare configuration file

3. Write entity class

4. Write Mapper interface and Mapper.xml file corresponding to entity class

IF

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

choose(when,otherwise)

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

trim(where,set)

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

update set

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

The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level.

SQL fragment

Sometimes, we may extract some functions for reuse!

1. Use sql tags to extract common parts.

2. Use the include tag reference where necessary

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

<select id="queryBlogIF" parameterType="map"  resultType="Blog">
    select * from blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

matters needing attention:

• It is best to define sql fragments based on a single table!
• Do not have a where tag

foreach

Another common usage scenario for dynamic SQL is traversal of collections (especially when building IN conditional statements)

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

The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the element body. It also allows you to specify the beginning and end strings and the separator between collection item iterations. This element will not add extra delimiters by mistake. See how smart it is!

Tip: you can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter. When using an iteratable object or array, index is the sequence number of the current iteration, and the value of item is the element obtained in this iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

<select id="queryBlogForeach" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>
collection: map of key
id: ids For one collection，id For each of them
open: Start with what
clse: End with what
separator: By what
select * from blog WHERE ( id = ? or id = ? ) 

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

Recommendations:

• Now the complete sql written in Mysql can be modified to become our dynamic sql and can be used for general purpose

Cache (just understand)