Quickly master MyBatis framework

Article catalogue

1, Foreword

Quickly master MyBatis framework (I)

1.1 database and table

create database if not exists library;
use library;

drop table if exists book;
create table book(
    id int primary key auto_increment,  -- book Id
    bookName varchar(100) not null,     -- title
    content varchar(1024) not null,     -- Book content
    authorId int not null,              -- author Id
    `state` int default 1               -- Lending status, default to 1, not lent
);
drop table if exists author;
create table author(
    id int primary key auto_increment,  -- author Id
    authorName varchar(100) not null,   -- Author's name
    age int,                            -- Author age
    nationality varchar(250)            -- Nationality of the author
);

1.2 entity class

//Books
@Data
public class Book {
    private Integer id;
    private String bookName;
    private String content;
    private Integer authorId;
    private Integer state;
    private Author author;
}


//Author class
@Data
public class Author {
    private Integer id;
    private String authorName;
    private Integer age;
    private String nationality;
    private List<Book> books;
}

1.3 MyBatisX plug-in

When writing MyBatis code, there is a very useful plug-in - MyBatisX.

Select file->settings, and then perform the following operations to install the plug-in

The plug-in can jump between the interface code and the code in the corresponding XML file

And after writing an interface method, the code can be automatically generated in the corresponding XML file (red underline appears, Alt+Enter, select the first option). Of course, the label generated in this way is not necessarily what we want


1.4 SQL log view configuration

To view the written SQL, you can configure it in the configuration file, so that you can view the SQL log on the console

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2, Multi table query

In the previous article, we talked about the operations of inserting, deleting, updating and selecting databases

The return value of these three operations is the number of affected rows, so it is not necessary to specify the return type when writing SQL XML, but if it is a query operation, you need to set the return type through resultType. Even if it is a String type, the return value should also be set to resultType = "java.lang.String"

However, if you query multiple tables, one attribute in the entity class will be another entity class. For example, in the above entity class definition, a book corresponds to an author, and you want to put the complete information of the author in the book class. An author can write many books. You want to put the information of all the books written by this author in a List. In this case, there is no way to realize it simply by using resultType. If you only use resultType, you will find that the property value of the corresponding class is null (the value of variable author and variable books is null)

At this point, you need to use resultMap to return a dictionary map

2.1 one to one

The situation of a Book corresponding to an author

interface

@Mapper
public interface BookMapper {
    public Book getBookById(Integer id);
}

XML

BookMapper.xml

<?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.example.demo.Mapper.BookMapper">

    <resultMap id="BaseMap" type="com.example.demo.model.Book">
        <id property="id" column="id"></id>
        <result property="bookName" column="bookName"></result>
        <result property="content" column="content"></result>
        <result property="authorId" column="authorId"></result>
        <result property="state" column="state"></result>
        <association property="author"
                     resultMap="com.example.demo.Mapper.AuthorMapper.BaseMap"
                     columnPrefix="a_">
        </association>
    </resultMap>
    <select id="getBookById" resultMap="BaseMap">
        select b.*,a.id a_id,a.authorName a_authorName,a.age a_age,a.nationality a_nationality
        from book b left join author a
        on b.authorId = a.id where b.id = #{id}
    </select>
</mapper>

Explanation:

  1. The value "BaseMap" of the resultMap in the select tag is an identity that corresponds to the id attribute value "BaseMap" in the resultMap tag above. Any name is OK,
  2. The value of the type attribute in the resultMap tag is the package name plus the class name of the mapped entity class
  3. The id tag refers to the primary key, and the result tag refers to the common column
  4. The property property property refers to the property name in the program, and the column property refers to the field name in the database. Therefore, it doesn't matter if the attribute name in the program is inconsistent with the field name in the database, and there will be no error through such mapping
  5. Because it is a one-to-one multi table query, the association tag is used.
    • The property attribute refers to the variable of the associated author class in the Book class, that is, author;
    • The resultMap attribute refers to the specified associated result set mapping, which will organize user data based on the mapping configuration. Here, the associated is the BaseMap in AuthorMapper (that is, the following code);
    • The columnPrefix attribute refers to adding a prefix to the column in the associated database (if the prefix is not added, when there are id fields in the author table and book table at the same time, the query results will be overwritten, so that the values of the two IDs are the same);

AuthorMapper.xml

<?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.example.demo.Mapper.AuthorMapper">
    <resultMap id="BaseMap" type="com.example.demo.model.Author">
        <id property="id" column="id"></id>
        <result property="authorName" column="authorName"></result>
        <result property="age" column="age"></result>
        <result property="nationality" column="nationality"></result>
    </resultMap>
</mapper>

Unit test code

@SpringBootTest
class BookMapperTest {
    @Autowired
    private BookMapper mapper;
    @Test
    void getBookById() {
        System.out.println(mapper.getBookById(1));
    }
}

Result display

2.2 one to many

One to many and one to one are written in the same way. The difference is that one to one uses the association tag and one to many uses the collection tag

interface

public List<Author> getAuthor(Integer id);

XML

<?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.example.demo.Mapper.AuthorMapper">
    <resultMap id="BaseMap" type="com.example.demo.model.Author">
        <id property="id" column="id"></id>
        <result property="authorName" column="authorName"></result>
        <result property="age" column="age"></result>
        <result property="nationality" column="nationality"></result>
        <collection property="books"
                    resultMap="com.example.demo.Mapper.BookMapper.BaseMap"
                    columnPrefix="b_">
        </collection>
    </resultMap>
    <select id="getAuthor" resultMap="BaseMap">
        select a.*,b.id b_id,b.bookName b_bookName,b.content b_content
        from author a left join book b
        on a.id = b.authorId where a.id = #{id}
    </select>
</mapper> 

The BaseMap in the associated BookMapper has been written in the above one-to-one code

Unit test code

@Test
void getAuthor() {
    List<Author> list = mapper.getAuthor(1);
    list.stream().forEach(n-> System.out.println(n));
}

Result display

3, Dynamic SQL usage

In order to meet various needs, dynamic SQL is needed to complete different SQL splicing under different conditions

3.1 < if > label

When perfecting information, some information must be filled in, and some are not. Just like the author's information in the author table, only authorName must be filled in, and age and nationality are not required. When inserting data, you need to deal with various information insertion situations, and you need to use the < if > tag to judge whether the passed value is null. If so, the content will not be spliced into SQL

interface

public int setAuthor3(Author author);

XML

<insert id="setAuthor3" >
    insert into author(authorName
    <if test="age != null">
        ,age
    </if>
    <if test="nationality != null">
        ,nationality
    </if>
    ) values(#{authorName}
    <if test="age != null">
        ,#{age}
    </if>
    <if test="nationality != null">
        ,#{nationality}
    </if>
    )
</insert>

Explanation:

  1. Determine whether to splice it into the SQL statement by whether the content in the test attribute in the if tag (the attribute in the object) is empty
  2. You need to pay attention to the commas and parentheses when splicing to ensure that you can assemble correct SQL statements in any case
  3. Due to the need to judge null, it is best to use wrapper classes when creating entity classes, such as Integer types instead of int types. Because the default value of int type is 0, it will not be null, and there is still a great difference between 0 and null. Using int type will have the risk of error reporting

Test code display

@Test
void setAuthor3() {
    Author author = new Author();
    author.setAuthorName("Qian Qi");
    author.setNationality("Malaysia");
    System.out.println("Number of data pieces updated:"+mapper.setAuthor3(author));
}

Result display

3.2 < trim > label

This tag is used in conjunction with the if tag. In extreme cases, all parameters do not have to be passed. If you do not know which parameter is the first and which parameter is the last, there must be more commas. The trim tag can solve this problem

Properties of trim tag

  • Prefix: indicates that the entire statement block is prefixed with the value of prefix
  • Suffix: indicates that the whole statement block takes the value of suffix as the suffix
  • prefixOverrides: indicates the prefix to be removed from the entire statement block
  • suffixOverrides: indicates the suffix to be removed from the entire statement block

interface

public int setAuthor4(Author author);

XML

<insert id="setAuthor4">
    insert into author
    <trim prefix="(" suffix=")" prefixOverrides=",">
        <if test="authorName != null">
            ,authorName
        </if>
        <if test="age != null">
            ,age
        </if>
        <if test="nationality != null">
            ,nationality
        </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="authorName != null">
            #{authorName},
        </if>
        <if test="age != null">
            #{age},
        </if>
        <if test="nationality != null">
            #{nationality},
        </if>
    </trim>
</insert>

Note: if none of the conditions in the trim tag is true, the contents of the trim statement will not be executed, and its attributes will not take effect. Therefore, if there are mandatory parameters, add them to the trim tag; If there are no required parameters, the prefix and suffix related attributes are set in trim

Unit test code

@Test
void setAuthor4() {
    Author author = new Author();
    author.setAuthorName("porkchop ");
    author.setAge(66);
    System.out.println("Number of data pieces updated:"+mapper.setAuthor4(author));
}

Result display

3.3 < where > label

Pass in an object and query the where condition according to the attribute. As long as the attribute in the object is not null, it is the query condition

interface

public List<Book> getBookByIdOrAuthorId(Book book);

XML

<select id="getBookByIdOrAuthorId" resultType="com.example.demo.model.Book">
    select * from book
    <where>
        <if test="id != null">
            id=#{id}
        </if>
        <if test="authorId != null">
            and authorId=#{authorId}
        </if>
    </where>
</select>

Explanation:

  1. The where tag has its own where keyword, and it will automatically remove redundant and
  2. You can use < trim prefix= "where" prefixOverrides= "and" > to replace

Unit test code

@Test
void getBookByIdOrAuthorId() {
    Book book = new Book();
    book.setAuthorId(1);
    List<Book> list = mapper.getBookByIdOrAuthorId(book);
    list.stream().forEach(n-> System.out.println(n));
}

Result display

3.4 < set > label

Pass in an object and update the user's data according to the attribute, such as modifying other non null attributes according to the id of the passed in object

interface

public int updateBook(Book book);

XML

<update id="updateBook">
    update book 
    <set>
        <if test="bookName != null">
            bookName=#{bookName},
        </if>
        <if test="content != null">
            content=#{content},
        </if>
        <if test="authorId != null">
            authorId=#{authorId}
        </if>
    </set>
    where id=#{id}
</update>

Explanation:

  1. The set tag has its own set keyword, which will automatically remove redundant commas
  2. You can use < trim prefix= "set" suffixOverrides= "," > to replace

Unit test code

@Test
void updateBook() {
    Book book = new Book();
    book.setId(1);
    book.setContent("Update content~");
    System.out.println("Number of updated data:"+mapper.updateBook(book));
}

Result display

3.5 < foreach > label

Use this tag when traversing a collection

foreach tag attribute

  • Collection: the collection in the binding method parameters (List, Set, Map, array...)
  • item: used to specify each object during traversal
  • open: the string at the beginning of the entire statement block
  • close: the string at the end of the entire statement block
  • separator: a string that traverses the interval between elements

interface

public int deleteByIdList(List<Integer> list);

XML

<delete id="deleteByIdList">
    delete from book where id in
    <foreach collection="list" item="bookId" open="(" close=")" separator=",">
        #{bookId}
    </foreach>
</delete>

Unit test code

@Test
void deleteByIdList() {
    List<Integer> list = new ArrayList<>();
    list.add(1);
    list.add(3);
    System.out.println("Number of updated data:"+mapper.deleteByIdList(list));
}

Result display

End~~~

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials

Tags: Front-end Android Back-end Interview

Posted by mithras on Sat, 30 Jul 2022 21:38:10 +0300