Springboot + Sqlite实战(离线部署成功)

最近有个需求，是手机软件离线使用，
用的springboot + mybatis-plus + mysql，无法实现，于是考虑使用内嵌式轻量级的数据库SQLlite

1. 引入依赖

		<dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.36.0.3</version>
        </dependency>
1
2
3
4
5

2. 修改配置文件

server:
  port: 9090
spring:
  datasource:
    url: jdbc:sqlite:sqlite.db
    driver-class-name: org.sqlite.JDBC
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto
  type-aliases-package: com.example.sqlite.entity
1
2
3
4
5
6
7
8
9
10
11
12
13
14

此处的url折磨了一晚上，网上各种百度加resource目录下什么的，最后直接这样，启动后会在根目录下自动生成一个db文件。

3. 铺底表结构及数据
   预先 生成表结构以及铺地数据，百度了很久并不能通过预先放好sql，然后启动时候动态执行生成。
   最后只能自己在本系统中手动创建好，以及执行好sql，项目打包之后该数据库及铺底数据会内嵌其中

1). 建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`
(
    `id`          INTEGER NOT NULL,
    `username`    TEXT,
    `password`    TEXT,
    PRIMARY KEY (`id`)
)

1
2
3
4
5
6
7
8
9

sqllite的数据类型和mysql不同,但同时也包容（不确定）
简单来说
整数用 INTEGER
字符串用 TEXT
浮点数 REAL

具体可参考该链接： SQLLite基本数据类型

2). 铺地数据

INSERT INTO `user` VALUES (1, '范大', '11111');
1

将这两个sql放在项目里的文件夹

3). 添加Sqllite


建议方框内的值直接填写生成的db名即可，同时别忘记点击一个test Connection测试一下。

ok,生成了db。

4). 执行sql
双击该数据库，

选择之前的sql执行，可以看日志

两个sql都run一下

OK, 我这边都成功了

5. 简单写个demo
   Controller

package com.example.sqlite.controller;

import com.example.sqlite.entity.User;
import com.example.sqlite.service.UserService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/user")
@RequiredArgsConstructor
public class UserController {

    private final UserService userService;

    @GetMapping("/get/{id}")
    public User findOne(@PathVariable Integer id) {
        return userService.getById(id);
    }


    @GetMapping("/save/{id}")
    public void save(@PathVariable Integer id) {
        User user = new User();
        user.setId(id);
        user.setUsername(id+":哈哈哈哈");
        userService.save(user);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

service

package com.example.sqlite.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.sqlite.entity.User;

public interface UserService extends IService<User> {
}
1
2
3
4
5
6
7

serviceImpl

package com.example.sqlite.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.sqlite.entity.User;
import com.example.sqlite.mapper.UserMapper;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService{
}
1
2
3
4
5
6
7
8
9
10

Mapper

package com.example.sqlite.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.sqlite.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

1
2
3
4
5
6
7
8

Entity

package com.example.sqlite.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("user")
public class User {

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String username;

    private String password;


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

UserMapper.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.example.sqlite.mapper.UserMapper">

</mapper>
1
2
3
4
5

MybatisPlusConfig

package com.example.sqlite.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;

//注入配置springboot
@Configuration
@MapperScan("com.example.sqlite.mapper")
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQLITE));
        return interceptor;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

启动后，访问成功。

打个包，java -jar启动，经测试报错，并无该数据库表。说明sql并不会自动执行。
简单一点，直接项目启动的时候，我指定sql让他启动。
CustomizeDataSourceInitializer

package com.example.sqlite.config;


import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

import javax.sql.DataSource;

@Configuration
public class CustomizeDataSourceInitializer {
    @Value("classpath:db/create.sql")
    private Resource sqlScriptSchema;

    @Value("classpath:db/data.sql")
    private Resource sqlScriptData;


    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(dataSource);
        dataSourceInitializer.setDatabasePopulator(databasePopulator());
        return dataSourceInitializer;
    }

    private DatabasePopulator databasePopulator() {
        ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(sqlScriptSchema);
        resourceDatabasePopulator.addScript(sqlScriptData);
        resourceDatabasePopulator.setSeparator(";");
        return resourceDatabasePopulator;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

重新打包，java -jar启动后，正常访问。

OK！