Mybatis动态SQL

最新推荐文章于 2024-12-26 10:27:59 发布

~故事的小黄花~

最新推荐文章于 2024-12-26 10:27:59 发布

阅读量172

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mybatis 文章标签： mybatis 动态SQL

本文链接：https://blog.youkuaiyun.com/weixin_45802810/article/details/118635882

Mybatis 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了如何在数据库中创建'blog'表，实现Pojo实体类Blog，配置Mybatis并编写相关Mapper接口及XML映射文件。涵盖了if、choose、set等条件语句的使用实例，适合初学者理解数据库操作和ORM映射。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前期准备

创建数据库中的表

create table `blog`(
	`id` varchar(50) not null comment '博客id',
    `title` varchar(100) not null comment '博客标题',
    `author` varchar(30) not null comment '博客作者',
    `create_time` datetime not null comment '创建时间',
    `views` int(30) not null comment '浏览量'
)ENGINE=InnoDB DEFAULT CHARSET=utf8

创建实体类

package com.pojo;

import java.util.Date;

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;

    @Override
    public String toString() {
        return "Blog{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public Blog() {
    }
}

在Mybatis配置文件中添加settings代码，原因详情可见往期博客

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<settings>
		<!--
        是否开启驼峰命名自动映射，
        即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。
        -->
	    <setting name="mapUnderscoreToCamelCase" value="true"/>
	</settings>
</configuration>

创建接口BlogMapper

package com.dao;

import com.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
    int addBlog(Blog blog);
    List<Blog> queryBlogIf(Map map);
    List<Blog> queryBlogChoose(Map map);
    int updateBlog(Map map);
    List<Blog> queryBlogForeach(Map map);
}

创建BlogMapper.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.dao.BlogMapper">
    ......
</mapper>

if语句（常用于网页模糊查询）

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

测试：

public class Test{
	@Test
    public void addBlog(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog=new Blog();
        blog.setId("1");
        blog.setAuthor("李华");
        blog.setCreateTime(new Date());
        blog.setViews(999);
        blog.setTitle("asd");
        mapper.addBlog(blog);

        sqlSession.close();
    }
}

choose、when、otherwise语句（类似于Java中的switch-case-default，常用于枚举）

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

测试：

@Test
public class Test{
    public void queryBlogIf(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map=new HashMap();
        //map.put("title","second");
        map.put("author","张三");
        List<Blog> blogList = mapper.queryBlogIf(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
}

set语句（常用于更新语句）

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

测试：

@Test
public class Test{
    public void updateBlog(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map=new HashMap();
        map.put("id","1");
        //map.put("title","second");
        map.put("author","李四");
        int i = mapper.updateBlog(map);
        if(i>0){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
        sqlSession.close();
    }
}

SQL片段，常用于提取if语句公共代码，调用时用include语句包含进来即可

<sql id="if-title-author">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryBlogIF" parameterType="map" resultType="Blog">
    select * from mybatis.blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

for-each语句

<select id="queryBlogForeach" parameterType="map" resultType="Blog">
	select * from mybatis.blog
	<where>
	    <foreach collection="ids" item="id" open="and (" close=")" separator="or">
	        id=#{id}
	    </foreach>
	</where>
</select>

说明：collections对应的内容为传递过来的参数的名称（测试类传进来的map参数名称为ids，注意要对应好），item为该集合当中的每一个单一的值，open表示以什么符号开始，close表示以什么符号结束，seperator表示分隔符，最后在数据库中查询时呈现的代码就是：select * from mybatis.blog WHERE ( id=? or id=? )

这个语句比较难懂，接下来用实例解释一下：

public class Test{
	@Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        ArrayList<String> ids=new ArrayList<String>();
        ids.add("1");
        ids.add("2");
        map.put("ids",ids);
        //查询id为1和2的blog对象
        List<Blog> list=blogMapper.queryBlogForeach(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
}

解释：上述的测试就是将要查询的对象的id放在一个集合当中，并查询数据库是否有数据在这个集合中，类似于数据库的in(id1,id2)语句，按照mapper文件中最好的理解就是：select * from mybatis.blog WHERE ( id=? or id=? )，这样也同样能完成in语句的作用。
此处的open就是左括号(，close就是右括号)，seperator就是or，item就是1、2这两个值，这个时候再去理解for-each语句就更清晰了，本质上就是in语句的另一种表达。