若依-更换数据库-sqlite

发布时间：2023-02-02 文章分类：编程知识投稿人：王小丽字号：默认 | 大 | 超大打印

基础

我是在ruoyi-vue已经安装了mybatis-plus的基础上进行的修改

关于SQLite

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。
SQLite主页：https://www.sqlite.org/index.html

1. pom.xml

<!-- web启动插件 -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--lombok插件-->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>
<!-- sqlite3驱动包 -->
<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.21.0.1</version>
</dependency>
<!--mybatis-plus插件-->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.4.3.1</version>
</dependency>

2.application-druid.yml

2.1 配置驱动

# 数据源配置
spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: org.sqlite.JDBC
        druid:
            # 主库数据源
            master:
                url: jdbc:sqlite:db\\db.sqlite3?date_string_format=yyyy-MM-dd HH:mm:ss
                username:
                password:

注意： date_string_format=yyyy-MM-dd HH:mm:ss 必须添加否则有时会导致查询是报错

13:53:38.671 [restartedMain] ERROR o.s.b.SpringApplication - [reportFailure,870] - Application run failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'captchaController': Unsatisfied dependency expressed through field 'configService'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sysConfigServiceImpl': Invocation of init method failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: Error attempting to get column 'create_time' from result set.  Cause: java.sql.SQLException: Error parsing time stamp
; uncategorized SQLException; SQL state [null]; error code [0]; Error parsing time stamp; nested exception is java.sql.SQLException: Error parsing time stamp
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:659)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:639)
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:119)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1431)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:619)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:953)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:780)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:453)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:343)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1370)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1359)
	at com.ruoyi.RuoYiApplication.main(RuoYiApplication.java:18)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

Caused by: java.sql.SQLException: Error parsing time stamp

2.2 修改初始化时校验是否连接成功查询

	# 配置检测连接是否有效
	validationQuery: SELECT 1 FROM sys_config

3.MybatisPlusConfig

	/**
	 * 分页插件，自动识别数据库类型 https://baomidou.com/guide/interceptor-pagination.html
	 */
	public PaginationInnerInterceptor paginationInnerInterceptor()
	{
		PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
		// 设置数据库类型为mysql
		paginationInnerInterceptor.setDbType(DbType.SQLITE);
		// 设置最大单页限制数量，默认 500 条，-1 不受限制
		paginationInnerInterceptor.setMaxLimit(-1L);
		return paginationInnerInterceptor;
	}

4. 修改因为mapper中使用sysdate() 报错问题

4.1 BaseEntity

    /** 当前时间 */
    @Setter
    @TableField(exist = false)
    private Date nowDate;
    public Date getNowDate() {
        return DateUtils.getNowDate();
    }

4.2 修改mapper

SysLogininforMapper.xml
<insert id="insertLogininfor" parameterType="SysLogininfor">
insert into sys_logininfor (user_name, status, ipaddr, login_location, browser, os, msg, login_time) 
values (#{userName}, #{status}, #{ipaddr}, #{loginLocation}, #{browser}, #{os}, #{msg}, #{nowDate})
SysUserMapper.xml
<update id="updateUser" parameterType="SysUser">
	<if test="remark != null">remark = #{remark},</if>
	update_time = #{nowDate}
</set>
where user_id = #{userId}

5.mysql表数据结构转移为sqlite表结构及数据

我在这里分享一份我整理好的 可能版本会有点旧 到时候缺字段在补充吧
链接：https://pan.baidu.com/s/19UFOhYv0qTpRZgoAsjFj2g 
提取码：pwc2

标签 mysql, select, 修改, 数据