Environment construction
- Create table and insert data
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 entity class
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
- Mapper interface
public interface BlogMapper {
List<Blog> getAllBlogs();
}
- Mapper mapping file
<mapper namespace="com.wcy.dao.BlogMapper">
<select id="getAllBlogs" resultType="Blog">
select *
from blog;
</select>
</mapper>
- mybatis configuration file
<configuration>
<!-- External profile -->
<properties resource="db.properties"/>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- alias -->
<typeAliases>
<typeAlias type="com.wcy.pojo.Blog" alias="Blog"/>
</typeAliases>
<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>
<!-- I wrote it myself Mapper Interface corresponding mapper.xml -->
<mappers>
<mapper resource="com/wcy/dao/BlogMapper.xml"/>
</mappers>
</configuration>
- test
@Test
public void getAllBlogs(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<Blog> allBlogs = mapper.getAllBlogs();
for (Blog blog : allBlogs) {
System.out.println(blog);
}
session.close();
}
The null in create is caused by the mismatch between the fields in the database and the attributes in the entity class. The difference here is that they are not named with humps. We only need to add a setting
<setting name="mapUnderscoreToCamelCase" value="true"/>
if statement
Using if statement to realize dynamic query
List<Blog> getBlogsByIf(Map<String, Object> map);
<select id="getBlogsByIf" resultType="Blog" parameterType="map">
select * from blog where 1=1
<!--If title If the condition exists, continue to add the following condition-->
<if test="title != null">
and title=#{title}
</if>
<!--If author If the condition exists, continue to add the following condition-->
<if test="author != null">
and author=#{author}
</if>
</select>
@Test
public void getBlogsByIf(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
List<Blog> blogs = mapper.getBlogsByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
At this point, if you do not pass in any parameters in the map, you can query all the information
If you want to query qualified information, you only need to pass in the corresponding kv key value pair
@Test
public void getBlogsByIf(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("title", "python");
map.put("author", "WangTwo ");
List<Blog> blogs = mapper.getBlogsByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
You can see that the corresponding sql is restricted
where statement
In the above example, we added a condition 1 = 1 at the end of the basic sql statement so that we can splice the statements correctly when using the if statement splicing, because if we do not write the condition 1 = 1, when all the if conditions do not match, the sql statement will look like this
select * from blog where
In this way, the sql statement will report an error, so add the condition of 1 = 1 to prevent the sql statement from making an error
However, in the actual development, we will not add redundant conditions, so the where element is specially used to solve this problem. Let's see the official description
The WHERE element inserts the "WHERE" clause only if the child element returns anything. Also, if clause starts with the "AND" OR ", WHERE element removes them
Use an example to illustrate. Continue with the above example
List<Blog> getBlogsByIf(Map<String, Object> map);
Change the mapper
<select id="getBlogsByIf" resultType="Blog" parameterType="map">
select * from blog
<!--If where Only when there is a valid option in the label can it be spliced where condition-->
<where>
<!--If title If the condition exists, continue to add the following condition-->
<if test="title != null">
and title=#{title}
</if>
<!--If author If the condition exists, continue to add the following condition-->
<if test="author != null">
and author=#{author}
</if>
</where>
</select>
Next test
- When the first condition is met
@Test
public void getBlogsByIf(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
// Pass in the first condition
map.put("title", "python");
List<Blog> blogs = mapper.getBlogsByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
You can see that the sql statement splices where, and we actually add and in front of each condition, but in the actual sql statement, and has been removed, which is the advantage of where and confirms the official words
- Two conditions are met
@Test
public void getBlogsByIf(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
// Pass in the first condition and the second condition
map.put("title", "python");
map.put("author", "WangTwo ");
List<Blog> blogs = mapper.getBlogsByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
As expected, the sql statements were spliced correctly
- When there are no conditions
@Test
public void getBlogsByIf(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
List<Blog> blogs = mapper.getBlogsByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
You can see that when there are no conditions, the where keyword is not spliced, and all the information is queried
set statement
The usage of set is similar to that of where
The SET element dynamically inserts the SET keyword at the beginning of the line and deletes additional commas (which are introduced when using conditional statements to assign values to columns)
int updateBlog(Map<String, Object> map);
<update id="updateBlog" parameterType="map">
update blog
<!--Dynamic splicing set field-->
<set>
<if test="title != null">
title=#{title}
</if>
<if test="author != null">
author=#{author}
</if>
<if test="views != null">
views=#{views}
</if>
</set>
where id=#{id}
</update>
trim statement
You can customize where and set statements
If the where element is different from what you expect, you can also customize the function of the where element by customizing the trim element. For example, the user-defined trim element equivalent to the where element is:
<!--and and or There are spaces after it--> <trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
The prefixOverrides property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example removes all the contents specified in the prefixOverrides property and inserts the contents specified in the prefix property.
Take a look at the custom trim element equivalent to the set element:
<trim prefix="SET" suffixOverrides=","> ... </trim>
Note that we override the suffix value to "," and customize the prefix value to set
choose statement
Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides a choose element, which is a bit like a switch statement in Java.
Write a conditional query to try
ist<Blog> getBlogsByChoose(Map<String, Object> map);
<select id="getBlogsByChoose" parameterType="map" resultType="Blog">
select * from blog
<where>
<!--
choose Only one option in the can be executed
When when It will be spliced when the conditions in are met
When when Splicing occurs when none of the conditions in are met otherwise Statements in
-->
<choose>
<when test="id != null">
id=#{id}
</when>
<when test="title != null">
title=#{title}
</when>
<when test="author != null">
author=#{author}
</when>
<otherwise>
<!--Nothing is done here. Query all without conditions-->
</otherwise>
</choose>
</where>
</select>
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("views", 6666);
map.put("id", 1);
int i = mapper.updateBlog(map);
System.out.println(i);
session.close();
You can see that after the id is passed in, the first condition is met without adding the views condition
Take a look at the situation where nothing is transmitted
@Test
public void getBlogsByChoose(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<>();
List<Blog> blogs = mapper.getBlogsByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
Indeed, no conditions have been added, and all the information has been queried
sql fragment
Sometimes we may use a certain sql statement too much. In order to increase the reusability of the code and simplify the code, we need to extract these codes and call them directly when using them. This is done using the sql tag
<select id="getBlogsByIf" resultType="Blog" parameterType="map">
select * from blog
<!--If where Only when there is a valid option in the label can it be spliced where condition-->
<where>
<!--use include To introduce sql fragment-->
<include refid="condition"/>
</where>
</select>
<!-- use sql Label extraction sql fragment -->
<sql id="condition">
<!--If title If the condition exists, continue to add the following condition-->
<if test="title != null">
and title=#{title}
</if>
<!--If author If the condition exists, continue to add the following condition-->
<if test="author != null">
and author=#{author}
</if>
</sql>
foreach statement
The foreach element is very powerful. It allows you to specify a collection and declare the collection items and index variables that can be used in the element body. It also allows you to specify the separators between the beginning and end strings and the iteration of collection items. This element will not add redundant separators by mistake. See how smart it is!
Tip: you can pass any iteratable object (such as List, Set, etc.), Map object or array object as a Set parameter to foreach. 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.
For example, this example is implemented with a multi condition query
select * from blog where (id=1 or id=2 or id=3)
In this sql statement, we use foreach to dynamically add conditions in parentheses. We only need to pass in an id array
List<Blog> getBlogsByForeach(Map<String, Object> map);
<select id="getBlogsByForeach" parameterType="map" resultType="Blog">
select * from blog
<where>
<!--
collection->The traversed set means that we need to map in put One named ids Collection of
item->The name of each traversed item
index->Index name traversed
open->Start symbol
separator->Separator
close->Ending symbol
-->
<foreach collection="ids" item="id" index="index" open="(" separator="or" close=")">
id=#{id}
</foreach>
</where>
</select>
When a parameter is passed in, the splice condition
@Test
public void getBlogsByForeach(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
map.put("ids", ids);
List<Blog> blogs = mapper.getBlogsByForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
When an empty list is passed in, all items are queried by default
@Test
public void getBlogsByForeach(){
SqlSession session = MybatisUtil.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
map.put("ids", ids);
List<Blog> blogs = mapper.getBlogsByForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
