Auto.js封装SQLite数据库操作的示例类

这里是一个使用Auto.js封装SQLite数据库操作的示例类。这个类包含了创建数据库、表格操作、插入数据、查询数据、更新数据和删除数据等方法，可以方便地调用这些操作。

// SQLite数据库封装类

function DatabaseHelper(dbName) {
    // 设置数据库路径
    this.dbPath = "/sdcard/xxxx/ " + dbName + ".db";
    this.db = sqlite.open(this.dbPath);
    // 创建表
    this.createTable = function (tableName, columns) {
        var columnsDef = [];
        for (var i = 0; i < columns.length; i++) {
            columnsDef.push(columns[i].name + " " + columns[i].type);
        }
        var sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + columnsDef.join(", ") + ");";
        this.db.execSQL(sql);
    };

    // 查询表是否存在
    this.isTableExists = function (tableName) {
        var sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?";
        var cursor = this.db.rawQuery(sql, [tableName]);
        var exists = false;
        if (cursor.moveToFirst()) {
            exists = cursor.getInt(0) > 0;
        }
        cursor.close();
        return exists;
    };

    // 插入数据
    this.insert = function (tableName, values) {
        var columns = [];
        var placeholders = [];
        var valuesArr = [];
        for (var key in values) {
            if (values.hasOwnProperty(key)) {
                columns.push(key);
                placeholders.push("?");
                valuesArr.push(values[key]);
            }
        }
        var sql = "INSERT INTO " + tableName + " (" + columns.join(", ") + ") VALUES (" + placeholders.join(", ") + ");";
        this.db.execSQL(sql, valuesArr);
    };

    // 查询数据（带分页功能）
    this.query = function (tableName, columns, whereClause, whereArgs, limit, offset) {
        columns = columns || ["*"];
        whereClause = whereClause || "";
        whereArgs = whereArgs || [];
        limit = limit || null;
        offset = offset || null;

        var sql = "SELECT " + columns.join(", ") + " FROM " + tableName;
        if (whereClause) sql += " WHERE " + whereClause;
        if (limit !== null) sql += " LIMIT " + limit;
        if (offset !== null) sql += " OFFSET " + offset;

        var cursor = this.db.rawQuery(sql, whereArgs);
        var results = [];
        while (cursor.moveToNext()) {
            var row = {};
            for (var i = 0; i < columns.length; i++) {
                row[columns[i]] = cursor.getString(i);
            }
            results.push(row);
        }
        cursor.close();
        return results;
    };

    // 更新数据
    this.update = function (tableName, values, whereClause, whereArgs) {
        var columns = [];
        var valuesArr = [];
        for (var key in values) {
            if (values.hasOwnProperty(key)) {
                columns.push(key + " = ?");
                valuesArr.push(values[key]);
            }
        }
        var sql = "UPDATE " + tableName + " SET " + columns.join(", ") + " WHERE " + whereClause;
        this.db.execSQL(sql, valuesArr.concat(whereArgs));
    };

    // 删除数据
    this.delete = function (tableName, whereClause, whereArgs) {
        var sql = "DELETE FROM " + tableName + " WHERE " + whereClause;
        this.db.execSQL(sql, whereArgs);
    };

    // 删除数据库文件
    this.deleteDatabase = function () {
        this.close();
        var file = new java.io.File(this.dbPath);
        return file.exists() && file.delete();
    };

    // 关闭数据库
    this.close = function () {
        this.db.close();
    };
}

// 使用示例
(function () {
    var dbHelper = new DatabaseHelper("my_database");

    // 检查表是否存在，若不存在则创建
    if (!dbHelper.isTableExists("users")) {
        dbHelper.createTable("users", [
            { name: "id", type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
            { name: "name", type: "TEXT" },
            { name: "age", type: "INTEGER" }
        ]);
        console.log("创建表: users");
    }

    // 插入数据
    dbHelper.insert("users", { name: "Alice", age: 25 });
    dbHelper.insert("users", { name: "Bob", age: 30 });
    dbHelper.insert("users", { name: "Charlie", age: 35 });
    console.log("插入数据完成");

    // 查询数据，分页查询：从第0条记录开始，获取2条数据
    var users = dbHelper.query("users", ["id", "name", "age"], "", [], 2, 0);
    console.log("查询数据:");
    for (var i = 0; i < users.length; i++) {
        console.log(users[i]);
    }

    // 更新数据
    dbHelper.update("users", { age: 28 }, "name = ?", ["Alice"]);
    console.log("更新数据完成");

    // 查询更新后的数据
    users = dbHelper.query("users", ["id", "name", "age"]);
    console.log("更新后查询所有数据:");
    for (var i = 0; i < users.length; i++) {
        console.log(users[i]);
    }

    // 删除一条数据
    dbHelper.delete("users", "name = ?", ["Bob"]);
    console.log("删除一条数据完成");

    // 查询删除后的数据
    users = dbHelper.query("users", ["id", "name", "age"]);
    console.log("删除后查询所有数据:");
    for (var i = 0; i < users.length; i++) {
        console.log(users[i]);
    }

    // 删除数据库
    var isDeleted = dbHelper.deleteDatabase();
    console.log("数据库删除成功: " + isDeleted);
})();

Alt

海报 链接