使用 ApiTable 连接 SQLite

入门示例

创建数据表并插入数据

SQLite 是一种轻量级嵌入式数据库。使用 Navicat Premium 可以轻松创建 SQLite 数据库，数据库文件将保存在本地。

以下是一个创建表并插入数据的示例：

CREATE TABLE "student" (
  "id" integer NOT NULL,
  "name" text NOT NULL,
  "grade" text NOT NULL,
  PRIMARY KEY ("id")
);

INSERT INTO "student" VALUES (1, '沈', '一年级');

整合 SQLite

在项目中整合 SQLite，首先需要添加以下依赖：

<!-- https://central.sonatype.com/artifact/com.litongjava/api-table -->
<dependency>
  <groupId>com.litongjava</groupId>
  <artifactId>api-table</artifactId>
  <version>${api-table.version}</version>
</dependency>

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.23</version>
</dependency>

<!-- sqlite-jdbc -->
<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.7.2</version>
</dependency>

在 app.properties 配置文件中添加以下配置：

# SQLite 数据库配置
jdbc.driverClass=org.sqlite.JDBC
jdbc.url=jdbc:sqlite:D:/sqlite/student.db
jdbc.user=
jdbc.pswd=
jdbc.showSql=true
jdbc.validationQuery=select 1

配置类的创建

以下是配置 SQLite 数据源和 ActiveRecord 插件的示例代码：

package com.litongjava.address.config;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.jfinal.template.Engine;
import com.jfinal.template.source.ClassPathSourceFactory;
import com.litongjava.annotation.AConfiguration;
import com.litongjava.annotation.AInitialization;
import com.litongjava.db.activerecord.ActiveRecordPlugin;
import com.litongjava.db.activerecord.OrderedFieldContainerFactory;
import com.litongjava.db.activerecord.dialect.Sqlite3Dialect;
import com.litongjava.db.hikaricp.DsContainer;
import com.litongjava.tio.boot.server.TioBootServer;
import com.litongjava.tio.utils.environment.EnvUtils;

import lombok.extern.slf4j.Slf4j;

@AConfiguration
@Slf4j
public class DbConfig {

  public DataSource dataSource() {
    String jdbcUrl = EnvUtils.get("jdbc.url");
    String jdbcUser = EnvUtils.get("jdbc.user");
    String jdbcPswd = EnvUtils.get("jdbc.pswd");
    String jdbcValidationQuery = EnvUtils.get("jdbc.validationQuery");

    log.info("jdbcUrl:{}", jdbcUrl);

    // 检查并创建 SQLite 数据库路径
    try {
      if (jdbcUrl != null && jdbcUrl.startsWith("jdbc:sqlite:")) {
        String dbPath = jdbcUrl.substring("jdbc:sqlite:".length());
        Path path = Paths.get(dbPath).getParent();
        if (path != null && !Files.exists(path)) {
          Files.createDirectories(path);
          log.info("已创建 SQLite 数据库目录: {}", path);
        }
      }
    } catch (IOException e) {
      log.error("创建 SQLite 数据库目录失败", e);
      throw new RuntimeException(e);
    }

    // 创建数据源
    DruidDataSource druidDataSource = new DruidDataSource();
    druidDataSource.setUrl(jdbcUrl);
    druidDataSource.setUsername(jdbcUser);
    druidDataSource.setPassword(jdbcPswd);
    druidDataSource.setValidationQuery(jdbcValidationQuery);

    // 保存数据源
    DsContainer.setDataSource(druidDataSource);

    // 关闭服务器时关闭数据源
    TioBootServer.me().addDestroyMethod(druidDataSource::close);
    return druidDataSource;
  }

  @Initialization
  public void activeRecordPlugin() {
    boolean dev = EnvUtils.isDev();
    boolean jdbcShowSql = EnvUtils.getBoolean("jdbc.showSql", false);

    // 创建 ActiveRecord 插件
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dataSource());
    arp.setDialect(new Sqlite3Dialect());
    arp.setContainerFactory(new OrderedFieldContainerFactory());
    arp.setShowSql(jdbcShowSql);

    if (dev) {
      arp.setDevMode(true);
    }

    // 配置模板引擎
    Engine engine = arp.getEngine();
    engine.setSourceFactory(new ClassPathSourceFactory());
    engine.setCompressorOn(' ');
    engine.setCompressorOn('\n');

    // 启动插件
    arp.start();

    // 关闭服务器时关闭插件
    TioBootServer.me().addDestroyMethod(arp::stop);
  }
}

启动类的创建

以下是启动类的示例代码：

import com.litongjava.hotswap.wrapper.tio.boot.TioApplicationWrapper;
import com.litongjava.jfinal.aop.annotation.AComponentScan;

@AComponentScan
public class TioBootSqlLiteApp {

  public static void main(String[] args) {
    long start = System.currentTimeMillis();
    TioApplicationWrapper.run(TioBootSqlLiteApp.class, args);
    long end = System.currentTimeMillis();
    System.out.println((end - start) + "ms");
  }
}

创建 Controller

以下是一个简单的 Controller，用于从数据库中检索数据并返回：

import java.util.List;

import com.litongjava.data.model.DbJsonBean;
import com.litongjava.data.services.DbJsonService;
import com.litongjava.jfinal.aop.annotation.AAutowired;
import com.litongjava.jfinal.plugin.activerecord.Record;
import com.litongjava.annotation.RequestPath;

@RequestPath("/api")
public class TestController {

  @AAutowired
  private DbJsonService dbJsonService;

  @RequestPath("/list")
  public DbJsonBean<List<Record>> list() {
    return dbJsonService.listAll("student");
  }
}

访问测试

启动应用后，访问以下 URL 来测试数据库连接和数据查询功能：

http://localhost/listopen in new window

测试代码可在 GitHub 上找到：

GitHub 项目地址open in new window

自动创建数据库和表

SQLite 会自动创建数据库文件，但不会创建不存在的目录。在上面的 DbConfig 类的 dataSource 方法中，已经包含了创建目录的逻辑。除此之外，我们还需要手动编写创建表的代码。

以下是一个示例代码，用于在数据库中自动创建表：

package com.litongjava.address.config;

import java.net.URL;
import java.util.List;

import com.litongjava.db.activerecord.Db;
import com.litongjava.tio.utils.hutool.FileUtil;
import com.litongjava.tio.utils.hutool.ResourceUtil;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DbTables {

  public static void init() {
    String userTableName = "tio_boot_admin_system_users";

    boolean created = createTable(userTableName);
    if (created) {
      URL url = ResourceUtil.getResource("sql/tio_boot_admin_system_users_init.sql");
      StringBuilder stringBuilder = FileUtil.readURLAsString(url);
      int update = Db.update(stringBuilder.toString());
      log.info("add user:{},{}", userTableName, update);
    }

  }

  private static boolean createTable(String userTableName) {
    String sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
    List<String> tables = Db.queryListString(sql, userTableName);
    int size = tables.size();
    if (size < 1) {
      URL url = ResourceUtil.getResource("sql/" + userTableName + ".sql");
      StringBuilder stringBuilder = FileUtil.readURLAsString(url);
      int update = Db.update(stringBuilder.toString());
      log.info("created:{},{}", userTableName, update);
      return true;
    }
    return false;
  }
}

src\main\resources\sql\tio_boot_admin_system_users.sql 文件是实际执行的 SQL 文件，用于创建所需的数据库表。

CREATE TABLE tio_boot_admin_system_users (
    id BIGINT NOT NULL,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(100) NOT NULL DEFAULT '',
    nickname VARCHAR(30) NOT NULL,
    signature VARCHAR(200),
    title VARCHAR(50),
    group_name VARCHAR(50),
    tags JSON,
    notify_count INT DEFAULT 0,
    unread_count INT DEFAULT 0,
    country VARCHAR(50),
    access VARCHAR(20),
    geographic JSON,
    address VARCHAR(200),
    remark VARCHAR(500),
    dept_id BIGINT,
    post_ids VARCHAR(255),
    email VARCHAR(50) DEFAULT '',
    phone VARCHAR(11) DEFAULT '',
    sex SMALLINT DEFAULT 0,
    avatar VARCHAR(512) DEFAULT '',
    status SMALLINT NOT NULL DEFAULT 0,
    login_ip VARCHAR(50) DEFAULT '',
    login_date TIMESTAMP WITHOUT TIME ZONE,
    creator VARCHAR(64) DEFAULT '',
    create_time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) DEFAULT '',
    update_time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted SMALLINT  NOT NULL DEFAULT 0,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE (username)
);

src\main\resources\sql\tio_boot_admin_system_users_init.sql

INSERT INTO tio_boot_admin_system_users (
    id, username, password, nickname, signature, title, group_name, tags, notify_count, unread_count, country, access, geographic, address, remark, dept_id, post_ids, email, phone, sex, avatar, status, login_ip, login_date, creator, create_time, updater, update_time, deleted, tenant_id
) VALUES (
    1, 'admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', 'admin', 'This is a signature', 'Admin', 'Administrators', '{"tags": [{"key": "tag1", "label": "Tag 1"}, {"key": "tag2", "label": "Tag 2"}]}', 10, 5, 'United States', 'admin', '{"province": {"label": "California", "key": "CA"}, "city": {"label": "San Francisco", "key": "SF"}}', '123 Main St, San Francisco, CA 94122', '管理员', 103, '[1]', 'aoteman@126.com', '15612345678', 1, 'http://127.0.0.1:48080/admin-api/infra/file/4/get/37e56010ecbee472cdd821ac4b608e151e62a74d9633f15d085aee026eedeb60.png', 0, '127.0.0.1', '2023-11-30 09:16:00', 'admin', '2021-01-05 17:03:47', NULL, '2024-03-23 08:49:55', 0, 1
);

在 DbConfig 中执行初始化方法

DbTables.init();