This article will use a simple login comment function to fully introduce the use of mybatis framework.
Functions: add, delete, modify, query, multi table query, fuzzy query, dynamic query

This article uses the environment / tools:
Database: mysql 5.7
JDK:8
IDEA:2020.1
Database visualization tool: Navicat

1. What is MyBatis?

2. Advantages of mybatis

Here's the introduction of MyBatis. Let's teach you how to use MyBatis to build a user login registration system (including adding, deleting, modifying and checking)

3. Create tables with MySQL

We first open Navicat to create a mybatis database, and then create a user table to set the primary key uuid to automatically increase from 1000. The settings are as follows:

All fields are as follows

4. Create a Mybatis project

To open IDEA, you need to set the following content and check the functions on the right.

If not, you can create a simple java web program and import the following dependencies in pom.xml. After the above project is completed, you need to check whether you want to import the top three dependencies

 <dependencies>
 
         <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.14</version>
        </dependency>
        
         <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
      
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>
    

The application.properties configuration file is as follows. Since I use the thmeleaf template, I have the above code. If you don't want to use the template, you can delete it.
MySQL database connection configuration needs to be modified according to your own database, such as database login password. If the database name is different from mine, mybayis in the first line needs to be modified to your database name

#Template cache: on
spring.thymeleaf.cache=false
#Template code
spring.thymeleaf.encoding=UTF-8
#Template style
spring.thymeleaf.mode=HTML5
#Specify template page storage path
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html


# MySQL database connection configuration
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false
spring.datasource.username=root
spring.datasource.password=123123

#Add and configure a third-party data source druid
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=20
spring.datasource.minIdle=10
spring.datasource.maxActive=100

#Configure the xml profile path for MyBatis
mybatis.mapper-locations=classpath:mapper/*.xml
#Configure the entity class alias path specified in the XML Mapping File
mybatis.type-aliases-package=com.mybatis.demo.domain
#Turn on hump name matching mapping
mybatis.configuration.map-underscore-to-camel-case=true

At this time, a simple preparatory work for Mybatis is completed

5. Realize login function based on annotation (database search)

5.1 create a login html interface

as follows

Of course, you want to be simple. The following can also be used

5.2 control layer – > get request

The old way of springboot is to create a control layer. When the user enters / login in the browser, he will jump to the login interface.
I won't show you the basic springboot configuration jump settings. After all, it's a little off my topic

@Controller
@RequestMapping("/")
public class loginController {
    @GetMapping("/login")
    public String toLogin(){
        return "login";
    }
}

Now that you can enter the login interface, you need to take the value from the page to the control layer to interact with the database to determine whether there is a user. Please proceed to the following steps to complete the operation

5.3 create user entity class

Create a user class as shown in the figure below. Here we need to create corresponding attributes according to our database user table. The names can be different (we will solve the problem later, but the names can correspond to the attributes)

The code is as follows

import lombok.Data;

import java.util.Date;
//@Data note: remember to install lombok plug-in and remove pom.xml to import lombok dependency
@Data
public class user {
    private Long id;					//User UUID
    private String username;			//User account
    private String password;			//User password
    private Date createTime;			//Account creation time
    private String registerIp;			//Registered ip address
    private int role;					//User gender
}

5.4 create Mapper interface

Now that the user classes have been created, we need to write the corresponding interface to complete the operation on the database
We use @ Select annotation here to complete the query operation. If you have ever started sql, you can guess
@Insert(… )Is the insert operation @ Delete( )Delete operation @ Update( )Update operation
The code is shown in the figure below

@Param("username"), @ Param("password") are used to obtain the account and password of the page respectively. The parameter in double quotation marks is naturally the name in the input input input box of the front-end page

import com.zsc.mybatis.demo.domain.user;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
//Make sure to add the mapper annotation, otherwise the interface will not be scanned
@Mapper
public interface userMapper {
    @Select("select * from user as u where u.username=#{username} and u.password=#{password}")
    //@Param("username") and @ Param("password") are used to obtain the account and password of the page respectively
    public List<user> getUser(@Param("username")String username ,@Param("password") String password);
}

This is the front page. You can clearly see the corresponding contents in name

  <input type="text" name="username" required  lay-verify="required" placeholder="Please enter your account number" autocomplete="off" class="layui-input"> </br>
  <input type="text" name="password" required  lay-verify="required" placeholder="Please input a password" autocomplete="off" class="layui-input"> </br>
         

5.4 control layer > post request

Now that the entity class has been created and the mapper interface has been written, let's control layer test whether we have written it right or not
The code is as follows:

	//Injection if there is an error reported by userMapper, it can be ignored. In fact, it is true
	@Autowired
    userMapper userMapper;
	//Let's not make a fuss here. Let's go back to a simple josn
	@ResponseBody
    @PostMapping("/login")
    public String dellogin(String username,String password){
        try {
            List<user> list=userMapper.getUser(username,password);
            if (list.size()==0)
                return "Login failure no such user found";
            else
                System.out.println(list);
                return "Login successful";
        }catch (Exception e){
            System.out.println(e.toString());
        }

        return "Login failed, please check the error";
    }

Now that the code has been written, let's test it. There is a piece of data in my database

Let's go to page test
Login failed:


Login successful


Data found:

We will certainly have a question, why the properties in the user entity class are not equal to the fields in the database, but why can we still get the data?
The reason is very simple. We set the hump name matching map in the previous configuration

#Turn on hump name matching mapping
mybatis.configuration.map-underscore-to-camel-case=true

So far, we have completed the query operation of the database. In order to speed up the learning, we directly use the test class to complete the remaining addition, deletion and modification

6. Add, delete, modify and query database based on configuration file

6.1 create test class

First, create a test class, as shown in the following figure

6.2 create configuration file to add, delete, modify and query

After creation, start to create the userMapper.xml file in the following directory and write the configuration file

Namespace represents a namespace. It must be under the corresponding Mapper. It can't be copied. Here are all the operation codes for adding, deleting, modifying and querying

<?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 Represent namespace -->
<mapper namespace="com.zsc.mybatis.demo.mapper.userMapper">

    <!--Query operation -->
	<!--    id It's the function name, parameterType Is the parameter type passed in, resultType This is the return value type. If I want to return all the information of the user, I will return user object-->
   <select id="getUser" parameterType="String" resultType="user">
        select * from user where username=#{username} and password=#{password}
   </select>
   
	<!--    Insert operation-->
<!--    &lt;!&ndash; Add user &ndash;&gt;     Return recorded uuid   useGeneratedKeys="true" keyProperty="uuid"-->
    <insert id="plususer" parameterType="user" useGeneratedKeys="true" keyProperty="uuid">
        insert into user(username,password,create_time,register_ip,role)
        value (#{user.username},#{user.password},#{user.createTime},#{user.registerIp},#{user.role})
    </insert>
    
	<!--    Delete operation-->
    <delete id="deluser" parameterType="String">
        delete from user where username=#{username} and password=#{password}
    </delete>
    
	<!--    update operation-->
    <update id="updateuser" parameterType="user">
        update user set username=#{user.username},password=#{user.password},create_time=#{user.createTime},
        register_ip=#{user.registerIp},role=#{user.role} where uuid=#{user.uuid}
    </update>

</mapper>

6.5 writing the userMapper interface

It's easy to see that the annotation of our sql statement is gone, because it will be later

@Mapper
public interface userMapper {

    //Query based on configuration file
    //Query user action
    public List<user> getUser(@Param("username")String username ,@Param("password") String password);
	
	//Add user action
    public void plususer(@Param("user")user user);
	
	//delete
    public void deluser(@Param("username")String username,@Param("password") String password);
	
	//Modify operation
    public void updateuser(@Param("user") user user);
}

6.4 writing test cases

The following is basically the same as my writing at the control level

@SpringBootTest
@RunWith(SpringRunner.class)
public class userMapperTest {
    //injection
    @Autowired
    userMapper userMapper;

    //Query operation
    @Test
    public void getUser(){
        List<user> list=userMapper.getUser("admin","123");
        System.out.println(list);
    }

    //Insert operation
    @Test
    public void plususer(){
        Date date=new Date();
        user user=new user();
        user.setUsername("No one is allowed to move my bricks");
        user.setPassword("Spicy chicken");
		// user.setRegisterIp(request.getRemoteHost()); this sentence can get the registered ip address
        user.setCreateTime(date);
        user.setRegisterIp("0.0.0.0");
        user.setRole(1);
        System.out.println(user.toString());
        userMapper.plususer(user);
        System.out.println(user.toString());
    }

    //Delete test
    @Test
    public void deluser(){
        userMapper.deluser("Li Si","456");
    }

    //Update test
    @Test
    public void uodateuser(){
        //Get the current user name = "Cai Xukun", password="985"
        user user=new user();
        List<user> list;
        list=userMapper.getUser("Cai Xukun","985");
        //Assign to user after obtaining
        user=list.get(0);
        user.setUsername("Cai Xukun is so handsome");
        user.setPassword("Really?");
        //Next, we will not modify it any more. We will modify the corresponding properties according to our own needs
        userMapper.updateuser(user);


    }

So the test results are as follows:
Query data operation performed successfully

Insert successfully
Careful students can surely see why uuid is not null after insertion? Because we look at the profile code inserted earlier

//useGeneratedKeys="true" keyProperty="uuid" here is the uuid returned after the insert operation, so we can see that the uuid returned after the insert operation
 <insert id="plususer" parameterType="user" useGeneratedKeys="true" keyProperty="uuid">

Delete successfully

Modification succeeded
Since the query is performed once and then the modification is performed once, the sql statement is executed twice

7. Use ResutlMap to realize one to many, many to many object association.

Here we actually demonstrate the operation of a common user comment
Note: because the relationship between users and comments is one to many, and users will attach some attachments (files) or pictures to comments, so when we get the content of users' comments, we also get the associated user objects and attachments, which can be realized by referring to my next method.
ER diagram is as follows:

7.1 write corresponding comments and attachments. Create comment database, attachment database

Comments

Comment database:

File class. File is the file image uploaded below the comment

File database

7.2 write commentMapper interface

This is still the old pattern above. Write the interface, and then complete the corresponding sql statements below

<mapper namespace="com.zsc.mybatis.demo.mapper.commentMapper">
	<!--    resultMap: Corresponding to the following statement-->
    <select id="getCommentDetails" parameterType="Long" resultMap="user_comment_file">
        select * from comment where comment_id=#{commentId}
    </select>

   

<!--    user_comment_file,It means that when we query, we will query its associated objects together,-->
<!--    type="comment"Indicates that the returned result is comment Type.-->
		
  		<resultMap id="user_comment_file" type="comment">
  		
<!--        commentId Comment id    comment_id express comment Primary Key -->
<id property="commentId" column="comment_id"/>
        
<!--        User object 1 to 1 Association-->
        <association property="user" column="uuid"
                     javaType="user"
                     select="com.zsc.mybatis.demo.mapper.userMapper.getUserById"/>
<!--        enclosure/File object 1 to many Association-->
<!--        use collection,One comment corresponds to more than one file，So use collection Associate with attachment object.-->
<!--        Property by comment Associated in object file Property, and column Indicates association to file The foreign key of the table.-->
<!--        ofType Indicates that the associated object type is fileList，select Represents the calling interface listByCommentId query file Object.-->
        <collection property="fileList" column="comment_id"
                    ofType="fileList"
                    select="com.zsc.mybatis.demo.mapper.fileMapper.listByCommentId"/>
    </resultMap>

7.3 write test interface

test result

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ecec90d] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@27d33393] will not be managed by Spring
==>  Preparing: select * from comment where comment_id=? 
==> Parameters: 1000(Long)
<==    Columns: comment_id, uuid, comment, create_time
<==        Row: 1000, 101, No one is allowed to move my bricks, 2020-05-12 11:11:32
====>  Preparing: select * from user where uuid=? 
====> Parameters: 101(Long)
<====    Columns: uuid, username, password, create_time, register_ip, role
<====        Row: 101, Zhang San, 123, null, 192.168.1.1, 1
<====      Total: 1
====>  Preparing: select * from file where comment_id=? 
====> Parameters: 1000(Long)
<====    Columns: file_id, file, comment_id
<====        Row: 1000, Brick 1, 1000
<====        Row: 1001, Brick 2, 1000
<====        Row: 1002, Brick 3, 1000
<====        Row: 1005, csdn, 1000
<====      Total: 4
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ecec90d]
[comment(commentId=1000, uuid=null, comment=No one is allowed to move my bricks, createTime=Tue May 12 19:11:32 CST 2020, fileList=[file(fileId=1000, file=Brick 1, commentId=1000), file(fileId=1001, file=Brick 2, commentId=1000), file(fileId=1002, file=Brick 3, commentId=1000), file(fileId=1005, file=csdn, commentId=1000)], user=user(uuid=101, username=Zhang San, password=123, createTime=null, registerIp=192.168.1.1, role=1))]

Data from my database:
Look at the figure below. You can see that the query result is OK
So it realizes the multi table query of database and the object association of one to many and many to many

8. Fuzzy query of database

It's still the same old pattern. Write the mapper interface, write the xml configuration file, and then use the test class to complete the test, and put the code directly
commentMapper.xml

<!--If you can't remember the function of parameters here, please look at the above content again-->
 <select id="listLikeComment" parameterType="String" resultType="comment">
        select * from comment where comment like concat('%' #{comment},'%')
 </select>

commentMapper.java

	//Fuzzy query
	public List<comment> listLikeComment(@Param("comment") String comment);

Writing of test class commentMapperTest.java

 	@Test
    public void listLikeComment(){
        List<comment> commentsList=commentMapper.listLikeComment("Just");
        System.out.println(commentsList);
    }

Test results:

Database data:

9. Dynamic query, dynamic splicing of SQL query statements

The basic statement is as follows. I will not test it any more. In general, I will query the ticket table. If the field subject and type are not empty, then I will fuzzy query the subject and specify the type of the content

The above content is the main content of mybatis framework. If you think it's well written, can you give me some likes or collections.