时间关系,不做太多解释,sqlite 是一个很轻量级的数据库,可以满足日常 sql 的需求,如果不需要使用像 mysql 那样重量级的数据库可以考虑使用 sqllite。
SQLite 一般用于处理小到中型数据存储,对于高并发高流量的应用不适用。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
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>
<parent>
<groupId>org.example</groupId>
<artifactId>springboot-learning-parents</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<groupId>springboot-demo</groupId>
<artifactId>springboot-sqlite</artifactId>
<version>1.0-SNAPSHOT</version>
<name>springboot-sqlite</name>
<description>springboot集成sqlite</description>
<properties>
<start-class>com.demo.SpringbootSqliteApplication</start-class>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatisplus.version>3.5.1</mybatisplus.version>
<freemaker.version>2.3.31</freemaker.version>
<mysql.version>8.0.28</mysql.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- mybatis-plus 所需依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.31</version>
</dependency>
<!-- sqlite 依赖包 -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.36.0.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
#端口,项目上下文
server:
port: 8080
servlet:
context-path: /springboot-sqlite
spring:
datasource:
username: leo825
password: 1WSX@357wj
url: jdbc:sqlite:D:/program/sqlite3/db/pro.db
driver-class-name: org.sqlite.JDBC
# 初始化表
schema: classpath:schema.sql
# 初始化数据
data: classpath:data.sql
initialization-mode: always
continue-on-error: true
# mybatis-plus 配置
mybatis-plus:
mapper-locations: classpath*:/mapper/**/*.xml
#实体扫描,多个package用逗号或者分号分隔
typeAliasesPackage: com.dmo.entity
global-config:
#数据库相关配置
db-config:
#主键类型 AUTO:"数据库ID自增", INPUT:"用户输入ID", ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
id-type: AUTO
#字段策略 IGNORED:"忽略判断",NOT_NULL:"非 NULL 判断"),NOT_EMPTY:"非空判断"
field-strategy: NOT_NULL
#驼峰下划线转换
column-underline: true
logic-delete-value: -1
logic-not-delete-value: 0
banner: false
#原生配置
configuration:
# 打印sql
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
map-underscore-to-camel-case: true
cache-enabled: false
call-setters-on-nulls: true
jdbc-type-for-null: 'null'
# 日志输出配置
logging:
level:
root: INFO
org:
springframework:
security: WARN
web: ERROR
file:
path: ./logs
name: './logs/sqlite-demo.log'
pattern:
file: '%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}:%L - %msg%n'
console: '%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}:%L - %msg%n'
create table if not exists t_user (
`id` int primary key not null ,
`username` char (50) not null,
`pwd` char(50) not null,
`create_time` datetime not null,
`update_time` datetime
) ;
insert into t_user(id, username, pwd, create_time, update_time) values (1,'zhhangsan','1222', datetime('2022-07-05 20:35:30.30'), NULL)
package com.demo.service.impl;
import com.demo.entity.User;
import com.demo.mapper.UserMapper;
import com.demo.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
/**
* <p>
* 用户表 服务实现类
* </p>
*
* @author Leo825
* @since 2022-07-05
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
package com.demo.service;
import com.demo.entity.User;
import com.baomidou.mybatisplus.extension.service.IService;
/**
* <p>
* 用户表 服务类
* </p>
*
* @author Leo825
* @since 2022-07-05
*/
public interface IUserService extends IService<User> {
}
impl 实现类:
package com.demo.service.impl;
import com.demo.entity.User;
import com.demo.mapper.UserMapper;
import com.demo.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
/**
* <p>
* 用户表 服务实现类
* </p>
*
* @author Leo825
* @since 2022-07-05
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
package com.demo.controller;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.demo.base.Result;
import com.demo.entity.User;
import com.demo.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* controller 测试
*
*/
@RestController
@RequestMapping("/user")
public class UserController {
/**
* 构造方法注入
*/
@Autowired
IUserService userService;
/**
* 保存数据
*
* @return
*/
@GetMapping("/save")
public Result save() {
User user = new User();
user.setId(10);
user.setUsername("miaolinlin");
user.setPwd("121212");
userService.save(user);
return new Result().ok(user);
}
/**
* 修改数据
*
* @param id
* @return
*/
@GetMapping("/update")
public Result update(Integer id) {
User user = new User();
user.setId(id);
user.setPwd("1111111111");
userService.updateById(user);
return new Result().ok("{}");
}
@GetMapping("/list")
public Result list() {
// 返回所有
List<User> list = userService.list();
return new Result().ok(list);
}
@GetMapping("/listByContion")
public Result listByContion() {
/**
* 条件查询, 通过QueryWrapper来实现查询的条件:
* eq: 代表相等
* like: 模糊匹配
* orderBy: 排序
* in, notin
* 大于,小于,between等
*/
List<User> list = userService.list(new LambdaQueryWrapper<User>()
// 查询年龄=11的
.eq(User::getUsername, "miao")
// 模糊匹配
.like(User::getPwd, "%111%")
// 排序,按照创建时间
.orderByDesc(User::getCreateTime)
);
return new Result().ok(list);
}
/**
* 根据id获取数据
*
* @param id
* @return
*/
@GetMapping("/getById")
public Result getById(Integer id) {
User user = userService.getById(id);
return new Result().ok(user);
}
/**
* 删除数据
*
* @param id
* @return
*/
@GetMapping("/delete")
public Result delete(Integer id) {
userService.removeById(id);
return new Result().ok("success");
}
/**
* 分页查询
*
* @param pageNum
* @param pageSize
* @param name
* @return
*/
@GetMapping("/page")
public Result page(int pageNum, int pageSize, String name) {
IPage<User> page = new Page<>(pageNum, pageSize);
IPage<User> page1 = userService.page(page, new LambdaQueryWrapper<User>()
// 主要演示这里可以加条件。在name不为空的时候执行
.like(StringUtils.isNotEmpty(name), User::getUsername, "%" + name + "%"));
return new Result().ok(page1);
}
}
springboot 整合 sqllite 和 mysql 差不多,代码库地址如下:
https://gitee.com/leo825/springboot-learning-parents.git