java 的 sqlite 的简单使用

发表于 分类于

介绍

SQLite
是一个极其轻量级、零配置、嵌入式的关系型数据库系统，非常适合用于小型应用、嵌入式系统、桌面和移动应用程序。它的简单性和高效性使其成为快速原型开发和小规模应用的理想选择。然而，对于需要高并发、复杂事务或分布式特性的应用，SQLite
可能不是最合适的选择。

Java项目中的使用

添加依赖

1
2
3
4
5
6
7
8
9
10
11
    <!-- Spring Boot JPA Starter -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
        <!-- SQLite JDBC Driver -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.36.0.3</version>
</dependency>

若sqlite-jdbc依赖拉不下来，就在pom中添加下面的临时仓库

1
2
3
4
5
6
7

<repositories>
    <repository>
        <id>central</id>
        <url>https://repo.maven.apache.org/maven2</url>
    </repository>
</repositories>

添加springboot配置

  • yml 文件配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    spring:
    # 数据源配置
    datasource:
      # database.db 为生成的数据库文件名，可自行修改为xxx.db
      url: jdbc:sqlite:database.db
      driver-class-name: org.sqlite.JDBC
      # sqlite没有用户名和密码，所以username和password留空
      username:
      password:
    # JPA 配置
    jpa:
      # 此为sqlite的方言配置，可以找其他作者的成品直接使用
      database-platform: com.shimu.dialect.SQLiteDialect
      hibernate:
        # 自动创建表
        ddl-auto: update
      show-sql: true

  • properties 文件配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 数据源配置
    ## database.db 为生成的数据库文件名，可自行修改为xxx.db
    spring.datasource.url=sqlite:database.db
    spring.datasource.driver-class-name=org.sqlite.JDBC
    ## sqlite没有用户名和密码，所以username和password留空
    spring.datasource.username=
    spring.datasource.password=
    # JPA 配置
    ## 此为sqlite的自定义方言配置，可以找其他作者的成品直接使用
    spring.jpa.database-platform=com.shimu.dialect.SQLiteDialect
    ## 自动创建表
    spring.jpa.hibernate.ddl-auto=update
    spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto

  • create: 每次启动应用时都会删除表并重新创建。
  • update: 每次启动应用时会尝试更新数据库表（如果需要的话），但不会删除已有数据。
  • validate: 验证数据库结构与实体类是否匹配，不做修改。
  • none: 不进行任何操作。
    使用 update 或 create 的时候，Hibernate 会根据实体类中的定义来自动创建和更新表，但无法直接处理默认值（除非使用 columnDefinition）。

创建自定义方言SQLiteDialect类

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.type.StandardBasicTypes;

import java.sql.Types;

/**
 * @Description TODO
 * @Version 1.0
 * @Author shimu
 * @Date 2025-01-17 13:59
 **/
public class SQLiteDialect extends Dialect {
    public SQLiteDialect() {
        super();
        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "timestamp");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");

        registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
        registerFunction("substring", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
    }

    public boolean supportsIdentityColumns() {
        return true;
    }

    public boolean hasDataTypeInIdentityColumn() {
        return false;
    }

    public String getIdentityColumnString() {
        return "integer";
    }

    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    public boolean supportsLimit() {
        return true;
    }

    public String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query).append(hasOffset ? " limit ? offset ?" : " limit ?")
                .toString();
    }

    public boolean supportsTemporaryTables() {
        return true;
    }

    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    public boolean supportsUnionAll() {
        return true;
    }

    public boolean hasAlterTable() {
        return false;
    }

    public boolean dropConstraints() {
        return false;
    }

    public String getAddColumnString() {
        return "add column";
    }

    public String getForUpdateString() {
        return "";
    }

    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
    }

    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable,
                                                   String[] primaryKey, boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
    }

    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
    }

    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    public boolean supportsCascadeDelete() {
        return false;
    }

    @Override
    public boolean bindLimitParametersInReverseOrder() {
        return true;
    }
}

创建实体类

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
import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.math.BigInteger;
import java.util.Date;

/**
 * @Description TODO
 * @Version 1.0
 * @Author shimu
 * @Date 2025-01-17 11:26
 **/
@Data
@Entity
public class User {
    @Id
    @GeneratedValue
    @Column(length = 20, unique = true, nullable = false)
    private BigInteger id;
    @Column(length = 50, unique = true, nullable = false)
    private String username;
    @Column(length = 50, unique = true, nullable = false)
    private String password;
    @Column(length = 50, unique = true, nullable = false)
    private String email;
    @Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP", nullable = false)
    private Date createTime;
}

注解解释

  • @Id: 表示为主键id
  • @GeneratedValue: 自动生成id的值
  • @Column
    属性 默认值 描述
    name field name 指定数据库表中列的名称
    nullable true 指定该列是否允许为 NULL
    length 255 指定字符串列的最大长度
    unique false 指定该列的值是否唯一
    insertable true 指定是否在插入时包含该列
    updatable true 指定是否在更新时修改该列
    columnDefinition null 指定列的数据库定义（例如类型、默认值）
    precision null 指定数字类型字段的总位数
    scale null 指定数字类型字段的小数位数

创建repository类

UserRepository.java

1
2
3
4

@Component
public interface UserRepository extends JpaRepository<User, BigInteger> {
}

JpaRepository

1
2
3
4
5
6
7
8
9
10
11
12
13

@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
    List<T> findAll();

    List<T> findAll(Sort sort);

    List<T> findAllById(Iterable<ID> ids);

    <S extends T> List<S> saveAll(Iterable<S> entities);

    // 省略其他api
}

其中 JpaRepository<T, ID> 的泛型 T 是实体，ID 是主键id

使用repository操作sqlite

  1. 创建 UserService
    1
    // 省略
  2. 创建 UserServiceImpl
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    @Service
    public class UserServiceImpl implements UserService{
      @Autowired
      private UserRepository userRepository;

      // 新增
      @Override
      public void save(User user) {
         userRepository.saveAndFlush(user);
      }
      
      // 省略
    }

参考文章

  1. SQLiteDialect 来源：https://blog.51cto.com/u_13706148/6031121