SpringBoot集成SQLite和Mybatis-Plus

本文最后更新于2023.04.28-13:26，某些文章具有时效性，若有错误或已失效，请在下方留言或联系涛哥。

简介

什么是SQLite

SQLite是一款非常轻量级的关系数据库系统，支持多数SQL92标准。它不需要单独安装，类似于一个文本文件，又可以放置在项目内作为持久化数据的内嵌式数据库使用。使用SQLite一般只需要带上一个dll，就可以使用它的全部功能。

SQLite应用场景

存储手机App应用、小型的Web项目或者桌面应用的持久化数据

SQLite安装

Windows安装SQLite

1. 官网下载地址: https://www.sqlite.org/download.html

2. 下载如下两个压缩文件(根据系统不同下载文件略有差别)

   

3. 将文件解压到本地

   

4. 配置系统变量 右键计算机》属性》打开高级系统设置》找到系统变量的path添加安装目录

   

5. cmd命令输入 sqlite3 test.db 》.databases 创建并进入数据库

   sqlite3 test.db

   .databases

   

安装完毕

集成SpringBoot和MybatisPlus

创建springboot项目

配置pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>SpringBoot_SQLite3</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringBoot_SQLite3</name>
    <description>SpringBoot_SQLite3</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.4.2</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
​
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--swagger-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>
        <!--  sqlite3-jdbc      -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.40.1.0</version>
        </dependency>
        <!-- MybatisPlus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!--        MybatisPlus代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.29</version>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
​
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.example.sqlite3.SpringBootSqLite3Application</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
​
</project>

配置Mybatis-Plus插件和Mybatis-Plus-generator

package com.example.sqlite3.config;
​
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
​
/**
 * @Program: admin
 * @ClassName MybatisPlusConfig
 * @Author: liutao
 * @Description: Mybatisplus插件
 * @Create: 2023-03-12 13:07
 * @Version 1.0
 **/
​
@Configuration
@MapperScan("com.example.sqlite3.mapper")
public class MybatisPlusConfig {
​
    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQLITE));
        return interceptor;
    }
​
}

启动生成代码

package com.example.sqlite3.utils;
​
import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.config.OutputFile;
import com.baomidou.mybatisplus.generator.engine.VelocityTemplateEngine;
import java.util.Collections;
​
/**
 * @Program: admin
 * @ClassName CodeGenerator
 * @Author: liutao
 * @Description: 代码生成器工具类
 * @Create: 2023-03-13 19:01
 * @Version 1.0
 **/
​
public class CodeGenerator {
​
​
    public static void main(String[] args) {
        generate();
    }
​
    private static void generate() {
        FastAutoGenerator.create("jdbc:sqlite:db/test.db", "", "")
                .globalConfig(builder -> {
                    builder.author("liutao") // 设置作者
                            .enableSwagger() // 开启 swagger 模式
                            .fileOverride() // 覆盖已生成文件
                            .outputDir("F:\\项目\\SpringBoot_SQLite3\\src\\main\\java"); // 指定输出目录
                })
                .packageConfig(builder -> {
                    builder.parent("com.example.sqlite3") // 设置父包名
                            .moduleName(null) // 设置父包模块名
                            .pathInfo(Collections.singletonMap(OutputFile.xml, "F:\\项目\\SpringBoot_SQLite3\\src\\main\\resources\\mapper")); // 设置mapperXml生成路径
                })
                .strategyConfig(builder -> {
                    builder.entityBuilder().enableLombok(); //开启lombok
                    builder.mapperBuilder().enableMapperAnnotation().build(); //加入@Mapper注解
                    builder.controllerBuilder().enableHyphenStyle()  // 开启驼峰转连字符
                            .enableRestStyle();  // 开启生成@RestController 控制器
                    builder.addInclude("user") // 设置需要生成的表名
                            .addTablePrefix("t_", "c_", "sys_"); // 设置过滤表前缀
                })// 使用Freemarker引擎模板，默认的是Velocity引擎模板
                .templateEngine(new VelocityTemplateEngine())
                .execute();
    }
​
}

application.yml配置

server:
    port: 8080
spring:
    datasource:
        url: jdbc:sqlite:db/test.db
        driver-class-name: org.sqlite.JDBC
        username:
        password:

编写接口

package com.example.sqlite3.controller;
​
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.sqlite3.common.Result;
import com.example.sqlite3.entity.User;
import com.example.sqlite3.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
​
/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author liutao
 * @since 2023-03-26
 */
@RestController
@Api(tags = "用户接口")
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;
​
    @ApiOperation("查询所有用户")
    @GetMapping
    public Result findAll(){
        return Result.success(userService.list());
    }
​
    @ApiOperation("分页查询所有用户")
    @GetMapping("/page")
    public Result findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        return Result.success(userService.page(new Page<>(pageNum,pageSize),queryWrapper));
    }
​
    @ApiOperation("更新或新增用户")
    @PostMapping
    public Result save(@RequestBody User user){
        return Result.success(userService.saveOrUpdate(user));
    }
    @ApiOperation("根据id删除用户")
    @DeleteMapping("/{id}")
    public Result del(@PathVariable Integer id){
        return Result.success(userService.removeById(id));
    }
​
}

启动服务调用接口进行测试