springboot通过mybatis整合sqlite3

首先你可以下载一个客户端，用于操作sqlite
地址：https://www.sqlite.org/download.html
找到对应版本，可以通过客户端创建表
教程：https://www.runoob.com/sqlite/sqlite-installation.html

引入pom

 <!--sqlite的必须依赖-->
        <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.32.3.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

application.properties配置文件

spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:C:/Users/10710/Desktop/sqllite/consumer.db
spring.datasource.username=root
spring.datasource.password=root

config配置

package com.dbapp.fly.sqlite.Config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.sqlite.SQLiteDataSource;

import javax.sql.DataSource;

/**
 * @ClassName: MyBatisConfig
 * @Author: yongtao.ding on 2020/8/08 16:05
 */
@Configuration
public class MyBatisConfig {
    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Bean(name="dataSource")
    public DataSource dataSource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        dataSource.setUrl(dataSourceProperties.getUrl());
        return dataSource;
    }
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) {
        SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
        try {
            sqlSessionFactoryBean.setDataSource(dataSource);
            // 设置别名包（实体类）
            sqlSessionFactoryBean.setTypeAliasesPackage("com.dbapp.fly.sqlite.model");
            // 设置mybatis的主配置文件
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            //设置sql配置文件路径
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:/mapper/*.xml"));
            return sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

}

package com.dbapp.fly.sqlite.Config;

import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @ClassName: MyBatisConfig
 * @Author: yongtao.ding on 2020/8/08 16:05
 */
@Configuration
public class MyBatisMapperScannerConfig {
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");

        //com.example.demo.dal.mapper 这个包名是所有的Mapper.java文件所在的路径，该包下面的子包里面的文件同样会扫描到。
        //此包名与具体的应用的名称相关
        mapperScannerConfigurer.setBasePackage("com.dbapp.fly.sqlite.mapper");

        return mapperScannerConfigurer;
    }

}

entity

/*
 * Powered By dbappsecurity
 * Since 2016 - 2018
 */

package com.dbapp.fly.sqlite.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.TimeZone;

/**
 * @version 1.0
 */
@ApiModel("")
@Data
public class SysLogDTO implements Serializable {
	//columns START
	/**
	 * id  db_column:id
	 */
	@ApiModelProperty("id")
	private Long id;
	/**
	 * 日志内容  db_column:content
	 */
	@ApiModelProperty("日志内容")
	private String content;
	/**
	 * 用户名  db_column:user_name
	 */
	@ApiModelProperty("用户名")
	private String userName;
	/**
	 * 用户行为  db_column:operation
	 */
	@ApiModelProperty("用户行为")
	private String operation;
	/**
	 * 用户ip  db_column:ip
	 */
	@ApiModelProperty("用户ip")
	private String ip;
	/**
	 * 创建时间   db_column:createDate
	 */
	@ApiModelProperty("创建时间 ")
	@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
	private Date createDate;



}

package com.dbapp.fly.sqlite.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.util.Date;

@ApiModel("日志信息")
@Data
public class SysLogVm implements Serializable {
    /**
     * 内容  db_column:content
     */
    @ApiModelProperty("内容")
    private String content;

    @ApiModelProperty("起始时间 ")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date createDateBegin;

    @ApiModelProperty("终止时间 ")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date  createDateEnd;


}

mapper

package com.dbapp.fly.sqlite.mapper;

import com.dbapp.fly.sqlite.model.SysConsumerLogModel;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

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

@Mapper
@Component
public interface SysConsumerLogMapper {

    // 插入 并查询id 赋给传入的对象
    @Insert("INSERT INTO sysConsumerLog(key, value) VALUES(#{key}, #{value})")
    @SelectKey(statement = "SELECT seq id FROM sqlite_sequence WHERE (name = 'sysConsumerLog')", before = false, keyProperty = "id", resultType = int.class)
    int insert(SysConsumerLogModel model);

    // 根据 ID 查询
    @Select("SELECT * FROM sysConsumerLog WHERE id=#{id}")
    SysConsumerLogModel select(int id);

    //分页查询
//    @Select("SELECT * FROM sysConsumerLog2  ORDER BY createDate DESC LIMIT #{offset} , #{recordNum}")
//    @Select("SELECT id,content,userName,operation,ip,createDate FROM sysConsumerLog2 WHERE createDate >= strftime('%Y-%m-%d %H:%M:%f',?,'localtime') and createDate <= strftime('%Y-%m-%d %H:%M:%f', ?,'localtime') and id in ( select id from sysConsumerLog2 ) order by createDate desc limit 0,10;")
//    @Select("SELECT id,content,userName,operation,ip,createDate FROM sysConsumerLog2 WHERE  createDate >=#{createDateBegin}  and createDate <= #{createDateEnd} and id in ( select id from sysConsumerLog2 ) ")
    List<SysConsumerLogModel> findSysLogList(Map map);

    // 查询全部
    @Select("SELECT * FROM sysConsumerLog2")
    List<SysConsumerLogModel> selectAll();

    // 更新 value
    @Update("UPDATE sysConsumerLog SET value=#{value} WHERE id=#{id}")
    int updateValue(SysConsumerLogModel model);

    // 根据 ID 删除
    @Delete("DELETE FROM sysConsumerLog WHERE id=#{id}")
    int delete(Integer id);

    /**
     * 查询数量
     * @param map
     * @return
     */

    Integer findCount(Map map);

}

model

package com.dbapp.fly.sqlite.model;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @ClassName: SysLog
 * @Author: yongtao.ding on 2020/8/7 17:53
 */
@Data
public class SysConsumerLogModel {


	private Long id;

	@ApiModelProperty(value = "日志内容")
	private String content;

	@ApiModelProperty(value = "用户名")
	private String userName;

	@ApiModelProperty(value = "用户行为")
	private String operation;

	@ApiModelProperty(value = "用户ip")
	private String ip;


	@ApiModelProperty(value = "应用id")
	private Long applicationId;

	@ApiModelProperty(value = "创建时间 ")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSS"	)
	private Date createDate;
}

service

package com.dbapp.fly.sqlite.service;

import com.dbapp.fly.sqlite.model.SysConsumerLogModel;
import com.dbapp.fly.sqlite.mapper.SysConsumerLogMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Slf4j
@Service
public class SysConsumerLogService {

	private final SysConsumerLogMapper dao;

	@Autowired
	public SysConsumerLogService(SysConsumerLogMapper dao) {
		this.dao = dao;
	}

	public boolean insert(SysConsumerLogModel model) {
		return dao.insert(model) > 0;
	}

	public SysConsumerLogModel select(int id) {
		return dao.select(id);
	}


	public Integer findPageSysConsumerLogCount(Map map) {
		try {
			return dao.findCount(map);
		} catch (Exception e) {
			log.error(e.toString());
		}
		return 0;
	}

	public List<SysConsumerLogModel> findPageSysConsumerLog(Map paramMap, int start, int recordNum) {
		if (paramMap == null) {
			paramMap = new HashMap<String, Object>();
		}
		paramMap.put("recordNum", recordNum);  //每次取的记录数
		paramMap.put("offset", start); //从第几条开始
		try {
//			return dao.selectAll();
			List<SysConsumerLogModel> sysLogList = dao.findSysLogList(paramMap);
			return sysLogList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<SysConsumerLogModel> selectAll() {
		return dao.selectAll();
	}

	public boolean updateValue(SysConsumerLogModel model) {
		return dao.updateValue(model) > 0;
	}

	public boolean delete(Integer id) {
		return dao.delete(id) > 0;
	}


}

controlle层

package com.dbapp.fly.controller;


import com.dbapp.fly.page.PageableVm;
import com.dbapp.fly.page.Pagination;
import com.dbapp.fly.sqlite.entity.SysLogDTO;
import com.dbapp.fly.sqlite.entity.SysLogVm;
import com.dbapp.fly.sqlite.model.SysConsumerLogModel;
import com.dbapp.fly.sqlite.service.SysConsumerLogService;
import com.dbapp.fly.web.ResponseCode;
import com.dbapp.fly.web.ResponseModel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Title: SysLogController
 * @version V1.0
 */
@RestController
@RequestMapping("/api")
@Api(value = "SysLogController", tags = "系统日志信息管理API")
public class SysConsumerLogController {
    @Autowired
    private SysConsumerLogService sysConsumerLogService;
    @Autowired
    public SysConsumerLogController(SysConsumerLogService sysConsumerLogService){
        this.sysConsumerLogService = sysConsumerLogService;

    }

    @GetMapping("/sysLogPage")
    @ApiOperation("系统日志分页信息")
    public ResponseModel<Pagination> page(SysLogVm info, @ApiParam("分页条件") PageableVm pageable) {
        HashMap<String, Object> condition = new HashMap<>(16);
        if (info.getContent()!=null){
            condition.put("content",'%'+info.getContent()+'%');
        }
        condition.put("createDateBegin",getStringDate(info.getCreateDateBegin()));
        condition.put("createDateEnd",getStringDate(info.getCreateDateEnd()));
        Integer totalCount = sysConsumerLogService.findPageSysConsumerLogCount(condition);
        Pagination pagination = new Pagination(pageable.getPageNumber(), pageable.getPageSize(), totalCount);
        return ResponseModel.ok(pagination, ResponseCode.SUCCESS, "恭喜您，获取成功！");

    }
    @ApiOperation("根据条件获取系统日志")
    @GetMapping("/sysLog")
    public ResponseModel<List<SysLogDTO>> getSysLog(@ApiParam("查询条件") SysLogVm info, PageableVm pageVm ) {
        HashMap<String, Object> condition = new HashMap<>(16);
        if (info.getContent()!=null){
            condition.put("content",'%'+info.getContent()+'%');
        }
		condition.put("createDateBegin",getStringDate(info.getCreateDateBegin()));
		condition.put("createDateEnd",getStringDate(info.getCreateDateEnd()));
        List<SysConsumerLogModel> sysLogs=sysConsumerLogService.findPageSysConsumerLog(condition, pageVm.getOffset(), pageVm.getPageSize());
        if(sysLogs.size()==0){
            return ResponseModel.ok(new ArrayList<>(), ResponseCode.SUCCESS, "您所搜索的日志不存在！");
        }
		List<SysLogDTO> sysLogDTOS = sysLogs.stream().map(t -> {
            SysLogDTO sysLogDTO = new SysLogDTO();
            BeanUtils.copyProperties(t, sysLogDTO);
            return sysLogDTO;
        }).collect(Collectors.toList());
        return ResponseModel.ok(sysLogDTOS, ResponseCode.SUCCESS, "恭喜您，操作成功！");
    }

	/**
	 * 将时间转换为字符串型
	 * @param date
	 * @return
	 */
	public static String getStringDate(Date date) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        String dateString = formatter.format(date);
        return dateString;
    }


}

mapper.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.dbapp.fly.sqlite.mapper.SysConsumerLogMapper">
	
	<resultMap id="Base_ResultMap" type="com.dbapp.fly.sqlite.model.SysConsumerLogModel">
			<result column="id" property="id" jdbcType="INTEGER"/>
			<result column="content" property="content" jdbcType="VARCHAR"/>
			<result column="userName" property="userName" jdbcType="VARCHAR"/>
			<result column="operation" property="operation" jdbcType="VARCHAR"/>
			<result column="ip" property="ip" jdbcType="VARCHAR"/>
			<result column="createDate" property="createDate" jdbcType="TIMESTAMP"/>
    </resultMap>
	 
	<sql id="Base_Column">
		<![CDATA[
        id,content,userName,operation,ip,createDate
	    ]]>
	</sql>

	<!-- 通过参数查询集合 -->
	<select id="findSysLogList" parameterType="java.util.HashMap" resultMap="Base_ResultMap">
		SELECT <include refid="Base_Column"/> FROM sysConsumerLog2
		<where>
            <if test="id!=null">
                and   id = #{id,jdbcType=INTEGER}
            </if>
            <if test="createDateBegin!=null ">
                and   createDate >= #{createDateBegin}
            </if>
            <if test="createDateEnd!=null">
                and   createDate &lt;= #{createDateEnd}
            </if>
			and id in (
			select id from sysConsumerLog2
			<where>

                <if test="content!=null and content !=''">
                    or   content  like #{content,jdbcType=VARCHAR}
                </if>
                <if test="content!=null and content!=''">
                    or   userName  like #{content,jdbcType=VARCHAR}
                </if>
                <if test="content!=null and content!=''">
                    or   operation  like #{content,jdbcType=VARCHAR}
                </if>
                <if test="content!=null and content!=''">
                    or   ip = #{content,jdbcType=VARCHAR}
                </if>
			</where>)
		</where>
		    order by  createDate desc
		<if test="recordNum!=null and offset !=null">
			limit #{offset,jdbcType=INTEGER},#{recordNum,jdbcType=INTEGER}
		</if>
	</select>

	<!-- 通过参数查询集合 -->
	<select id="findCount" parameterType="java.util.HashMap" resultType="java.lang.Integer">
		SELECT count(id) FROM sysConsumerLog2
		<where>
			<if test="createDateBegin!=null ">
				and   createDate >= #{createDateBegin}
			</if>
			<if test="createDateEnd!=null">
				and   createDate &lt;= #{createDateEnd}
			</if>
			and id in (
			select id from sysConsumerLog2
			<where>
				<if test="content!=null and content !=''">
					or   content  like #{content,jdbcType=VARCHAR}
				</if>
				<if test="content!=null and content!=''">
					or   userName  like  #{content,jdbcType=VARCHAR}
				</if>
				<if test="content!=null and content!=''">
					or   operation  like  #{content,jdbcType=VARCHAR}
				</if>
				<if test="content!=null and content!=''">
					or   ip = #{content,jdbcType=VARCHAR}
				</if>
			</where>)
		</where>
	</select>


</mapper>

通过上边的代码基本可以完成增删改查，
在这个过程中遇到了一些坑，文章地址：https://blog.csdn.net/qq_16613311/article/details/107905483