autojs使用nodejs調用sqlite資料庫

牙叔教程 簡單易懂

依賴

"nodejs";
require("rhino").install();
const { device } = require("device");
const path = require("path");
const fs = require("fs");
const util = require("util");
const SQLiteDatabase = android.database.sqlite.SQLiteDatabase;           

建立資料庫

let dir = device.externalStorageDirectory; // /storage/emulated/0
let dbPath = path.join(dir, "bullet_comment.db"); // /storage/emulated/0/bullet_comment.db
let db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
           

建立表

async function createTable(db) {
  let CREATE_Table = `CREATE TABLE IF NOT EXISTS dou_yin_bullet_comment_table(
  'id' INTEGER PRIMARY KEY AUTOINCREMENT,
  'name' TEXT NOT NULL,
  'comment' TEXT,
  'level' INTEGER,
  'complete_comment' TEXT UNIQUE,
  'ts' TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) `;
  db.execSQL(CREATE_Table); //建立使用者表
}
           

增加一條資料

function insertItem(db, item) {
  let sql =
    "INSERT OR REPLACE INTO dou_yin_bullet_comment_table (name, comment, level, complete_comment) VALUES (?, ?, ?, ?)";
  db.beginTransaction();
  db.execSQL(sql, [item.name, item.comment, item.level, item.complete_comment]);
  db.setTransactionSuccessful();
  db.endTransaction();
}           

增加多條資料

async function insertItems(db, items) {
  for (let i = 0; i < items.length; i++) {
    console.log("i = " + i);
    insertItem(db, items[i]);
    await sleep(1000);
  }
}           

增加多條資料2

async function insertItemsByStatement(db, items) {
  let sql =
    "INSERT OR REPLACE INTO dou_yin_bullet_comment_table (name, comment, level, complete_comment) VALUES (?, ?, ?, ?)";
  db.beginTransaction();
  let statement = db.compileStatement(sql);
  for (let i = 0; i < items.length; i++) {
    let item = items[i];
    statement.bindString(1, item.name);
    statement.bindString(2, item.comment);
    statement.bindLong(3, item.level);
    statement.bindString(4, item.complete_comment);
    statement.execute();
    statement.clearBindings();
  }

  db.setTransactionSuccessful(); //設定事務處理成功，不設定會自動復原不送出。
  db.endTransaction();
}           

查詢所有資料

function queryItems(db) {
  let query = "SELECT * FROM dou_yin_bullet_comment_table";
  let cursor = db.rawQuery(query, null);
  cursor.moveToFirst();
  let bulletComments = [];
  while (!cursor.isAfterLast()) {
    let bulletComment = {
      id: cursor.getInt(0),
      name: cursor.getString(1),
      comment: cursor.getString(2),
      level: cursor.getInt(3),
      complete_comment: cursor.getString(4),
      ts: cursor.getString(5),
    };
    bulletComments.push(bulletComment);
    cursor.moveToNext();
  }
  cursor.close();
  return bulletComments;
}
           

查詢指定資料

function queryItemsByTs(db, count) {
  let sql = "SELECT * FROM dou_yin_bullet_comment_table ORDER BY ts DESC LIMIT " + count;
  db.beginTransaction();
  let cursor = db.rawQuery(sql, null);
  cursor.moveToFirst();
  let bulletComments = [];
  while (!cursor.isAfterLast()) {
    let bulletComment = {
      id: cursor.getInt(0),
      name: cursor.getString(1),
      comment: cursor.getString(2),
      level: cursor.getInt(3),
      complete_comment: cursor.getString(4),
      ts: cursor.getString(5),
    };
    bulletComments.push(bulletComment);
    cursor.moveToNext();
  }
  cursor.close();
  return bulletComments;
}           

環境

裝置: 小米11pro

Android版本: 12

Autojs版本: 9.3.11

名人名言

思路是最重要的, 其他的百度, bing, stackoverflow, github, 安卓文檔, autojs文檔, 最後才是群裡問問 --- 牙叔教程

聲明

部分内容來自網絡 本教程僅用于學習, 禁止用于其他用途