Spring Boot中集成Mybatis（动态拼接SQL）

最新推荐文章于 2025-04-28 09:18:43 发布

转载最新推荐文章于 2025-04-28 09:18:43 发布 · 6.1k 阅读

6 ·

CC 4.0 BY-SA版权

原文链接：http://www.pianshen.com/article/989694338/

Spring Boot 同时被 2 个专栏收录

7 篇文章

订阅专栏

Mybatis

4 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot项目中整合MyBatis，包括添加依赖、配置数据源、编写实体类、Mapper接口及SQL动态构建方法，并提供了完整的测试示例。

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

一、在pom.xml中添加以下依赖：

<dependencies>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
   </dependency>

   <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.2</version>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
   </dependency>
       <dependency>
           <groupId>javax.enterprise</groupId>
           <artifactId>cdi-api</artifactId>
           <version>1.0</version>
       </dependency>
       <dependency>
           <groupId>junit</groupId>
           <artifactId>junit</artifactId>
           <version>4.12</version>
       </dependency>
   <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.1.8.RELEASE</version>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-test</artifactId>
      <version>2.1.6.RELEASE</version>
   </dependency>
   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
   </dependency>

</dependencies>

二、使用的实体

package com.test.bean;

import java.time.LocalDateTime;

public class LearnResouce {
    private String id;
    private String author;
    private String title;
    private LocalDateTime time;
    public String getId() {
        return id;
    }

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

    public String getAuthor() {
        return author;
    }

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

    public String getTitle() {
        return title;
    }

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

    public LocalDateTime getTime() {
        return time;
    }

    public void setTime(LocalDateTime time) {
        this.time = time;
    }
}

三、配置数据源application.properties

spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.datasource.username = root
spring.datasource.password = 123456

四、编写mapper

package com.test.dao;

import com.test.bean.LearnResouce;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

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

@Component
@Mapper
public interface LearnMapper {
    //添加记录
    @Insert({"insert into learn_resource(id, author, title, time) values(#{id},#{author},#{title},#{time})"})
    int add(LearnResouce learnResouce);

    //修改记录
    //@Update("update learn_resource set id=#{id},author=#{author},title=#{title},time=#{time} where id = #{id}")
    @Update("update learn_resource set time=#{time} where id = #{id}")
    int update(LearnResouce learnResouce);

    //动态拼接删除语句
    @DeleteProvider(type = LearnSqlBuilder.class, method = "deleteByids")
    int deleteByIds(@Param("ids") String[] ids);

    //根据id查询记录
    @Select("select * from learn_resource where id = #{id}")
    @Results(id = "learnMap", value = {
            @Result(column = "id", property = "id", javaType = String.class),
            @Result(property = "author", column = "author", javaType = String.class),
            @Result(property = "title", column = "title", javaType = String.class)
    })
    LearnResouce queryLearnResouceById(@Param("id") String id);

    //动态拼接查询语句
    @SelectProvider(type = LearnSqlBuilder.class, method = "queryLearnResouceByParams")
    List<LearnResouce> queryLearnResouceList(Map<String, Object> params);

    class LearnSqlBuilder {
        //动态构造查询语句
        public String queryLearnResouceByParams(final Map<String, Object> params) {
            StringBuffer sql =new StringBuffer();
            sql.append("select * from learn_resource where 1=1");
            if(!StringUtils.isEmpty((String)params.get("author"))){
                sql.append(" and author like '%").append((String)params.get("author")).append("%'");
            }
            if(!StringUtils.isEmpty((String)params.get("title"))){
                sql.append(" and title like '%").append((String)params.get("title")).append("%'");
            }
            System.out.println("查询sql=="+sql.toString());
            return sql.toString();
        }

        //删除的方法
        public String deleteByids(@Param("ids") final String[] ids){
            StringBuffer sql =new StringBuffer();
            sql.append("DELETE FROM learn_resource WHERE id in(");
            for (int i=0;i<ids.length;i++){
                if(i==ids.length-1){
                    sql.append(ids[i]);
                }else{
                    sql.append(ids[i]).append(",");
                }
            }
            sql.append(")");
            System.out.println("删除sql=="+sql.toString());
            return sql.toString();
        }
    }
}

五、启动类中进行包扫描@MapperScan("com.test.dao")

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.test.dao")
public class DemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}

}

六、编写测试类

package com.example.demo;

import com.test.bean.LearnResouce;
import com.test.dao.LearnMapper;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestApplication {
    @Autowired
    private LearnMapper learnMapper;

    LearnResouce learnResouce = new LearnResouce();
    @Before
    public void doBefore(){
        learnResouce.setId("1");
        learnResouce.setAuthor("hdn");
        learnResouce.setTitle("Mybatis");
        learnResouce.setTime(LocalDateTime.now());
    }

    /*测试添加*/
    @Test
    public void testInsert() throws Exception {
        int count = learnMapper.add(learnResouce);
        if(count==1){
            System.out.println("添加成功");
        }
    }

    /*测试修改*/
    @Test
    public void testUpdate() throws Exception {
        int count = learnMapper.update(learnResouce);
        if(count==1){
            System.out.println("修改id为1记录的时间");
        }
    }

    /*测试删除*/
    @Test
    public void testDelete() throws Exception {
        String[] ids = new String[2];
        ids[0]="1";
        int count = learnMapper.deleteByIds(ids);
        if(count==1){
            System.out.println("删除id为1记录");
        }
    }

    /*根据id查询*/
    @Test
    public void testSelete() throws Exception {
        LearnResouce resouce = learnMapper.queryLearnResouceById("1");
        System.out.println(resouce.toString());
    }

    /*动态构造查询语句*/
    @Test
    public void testSelectParams() throws Exception {
        Map<String, Object> params = new HashMap<>();
        //params.put("author","h");
        List<LearnResouce> list = learnMapper.queryLearnResouceList(params);
        System.out.println(list.size());

    }
}

在编写mapper时，需要注意的是：简单的语句只需要使用@Insert、@Update、@Delete、@Select这四个注解即可，但是有些复杂点需要动态拼接SQL语句，就比如上面方法中根据查询条件是否有值来动态添加sql的就需要使用@InsertProvider、@UpdateProvider、@DeleteProvider、@SelectProvider等注解。

这些可选的SQL注解允许你指定一个类名和一个方法名在执行时来返回允许创建动态的SQL。Mybatis会实例化这个类，然后执行由provider指定的方法。该方法可以有选择地接受参数对象。type：类；method：方法名

例子：

根据颜色进行匹配：