Dynamic SQL statements in mybatis framework

01? 2022-02-13 07:30:01 阅读数:375

dynamic sql statements mybatis framework

Create a database of test cases blog

CREATE TABLE `blog` (
`id` varchar(50) ,
`title` varchar(100) ,
`author` varchar(50) ,
`create_time` datetime,
`views` int
)

Create the corresponding blog Entity class

package pojo;
import lombok.Data;
import java.util.Date;
// Generate set,get Method notes
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;// Property is inconsistent with field
private int views;
}

establish BlogMapper Interface

package dao;
import pojo.Blog;
import java.util.List;
import java.util.Map;
public interface blogMapper {
// Search blogs (if)
List<Blog> queryBlogIF(Map map);
// Search blogs (choose)
List<Blog> queryBlogChoose(Map map);
// Update blog
int updateBlog(Map map);
// Inquire about 1,2 No. record blog
List<Blog> queryBlogForeach(Map map);
}

establish BlogMapper.xml

1、queryBlogIF(Map map) Method sql sentence ,where Labels can automatically help us remove and Or add and

Don't pass on the author and title , Check all blogs , Pass in the article title , Then find the article with the corresponding title , Incoming author , Then find the corresponding author's article

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

Corresponding test cases

@org.junit.Test
public void test2(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
blogMapper mapper = sqlSession.getMapper(blogMapper.class);
HashMap map=new HashMap<>();
// Pass in author and title
map.put("title"," Micro service is so simple ");
map.put("author"," Xiao Ming ");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}

 Wechat screenshot _20211223223136.png

2、queryBlogChoose(Map map) Method sql sentence

Don't pass on the author and title , Check all blogs , Pass in the article title , Then find the article with the corresponding title , Incoming author , Then find the corresponding author's article , Incoming article reading , Then find the article with the corresponding reading amount ,choose And when Need to be used together ,when Statements can be added or deleted automatically as needed and

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

Test cases

@org.junit.Test
public void test2(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
blogMapper mapper = sqlSession.getMapper(blogMapper.class);
HashMap map=new HashMap<>();
map.put("author"," Xiao Ming ");
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}

 Wechat screenshot _20211223223606.png

3、updateBlog(Map map) Method sql sentence ,set Labels will be added automatically and added as needed and

<!-- Extract public part , Improve code efficiency
matters needing attention : It's best to define in a single table sql fragment
Don't exist in it where label -->
<sql id="if-title-author">
<if test="title!=null">
title=#{title},
</if>
<if test="author!=null">
author=#{author},
</if>
</sql>
<update id="updateBlog" parameterType="map">
update blog
<set>
<!-- quote sql, Code reuse -->
<include refid="if-title-author"/>
</set>
where id=#{id}
</update>

Test cases

@org.junit.Test
public void test3(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
blogMapper mapper = sqlSession.getMapper(blogMapper.class);
HashMap map=new HashMap<>();
map.put("title","MyBatis It's so simple 123");
//map.put("author"," Xiao Ming ");
map.put("id","1");
mapper.updateBlog(map);
sqlSession.close();
}

 Wechat screenshot _20211223224022.png

4、queryBlogForeach(Map map) Method sql sentence

Inquire about 1,2 Article No , Application foreach,collection Is the name of the corresponding incoming parameter collection ,open by where After character 、item Is the condition value 、close For the last character ,separator Is the conditional middle separator

<!-- collection Now pass the omnipotent map,map There is a set -->
<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>

Test cases

@org.junit.Test
public void test4(){
SqlSession sqlSession = MyBatisUntil.getSqlSession();
blogMapper mapper = sqlSession.getMapper(blogMapper.class);
HashMap map=new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
//ids And with collection The values of should be consistent
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}

 Wechat screenshot _20211223224802.png

copyright:author[01?],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729586763.html