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:
- 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,
- The value of the type attribute in the resultMap tag is the package name plus the class name of the mapped entity class
- The id tag refers to the primary key, and the result tag refers to the common column
- 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
- 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:
- 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
- You need to pay attention to the commas and parentheses when splicing to ensure that you can assemble correct SQL statements in any case
- 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:
- The where tag has its own where keyword, and it will automatically remove redundant and
- 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:
- The set tag has its own set keyword, which will automatically remove redundant commas
- 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