springboot通过mybatis整合sqlite3

怼烎@ 2022-11-26 01:49 545阅读 0赞

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

引入pom

<dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.32.3.2</version>
            </dependency>
            
            <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 <= #{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 <= #{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