Autojs: 使用 SQLite

例子

let db = new SQLiteUtil("/sdcard/A_My_DB/sqlite.db");db.fastCreateTable("user_table",{name: "",online: false,},["name"] // 设置 name 为唯一, 重复项 不会添加成功
);// 新增数据的 ID
let row_id = db.insert("user_table", {name: "小明5",online: true,
});if (row_id == -1) {console.log("新增失败");
}
console.log("新数据的ID", row_id);// 查询一条
// arr = db.find("select * from user_table limit 1");// for (let i = 0; i < arr.length; i++) {
//   let item = arr[i];
//   console.log(item);
// }// 其他都类似, 需要把一条标准 sql 的几个部分拆开,分别当做参数传入
let r2 = db.update("user_table", { online: false }, "name=? and online = ?", ["小明5", 1]);
if (r2 == 0) {console.log("修改失败");
}
console.log("受影响的行数", r2);// 拼接字符串时, 要注意 "文本" 类型要加双引号
// db.execSQL("update user_table set online = 0 where name='小明2'");find_all();db.deleteDbFile()function find_all() {let arr = db.find("select * from user_table");for (let i = 0; i < arr.length; i++) {let item = arr[i];console.log(item);}
}

代码

// https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase
// https://www.apiref.com/android-zh/android/database/sqlite/SQLiteDatabase.html
/*** sqlite for auto.js** 如果封装的无法满足需求,可以在你的代码中获取 db.getDBConnection(), 自己实现* @example* let db = new SQLiteUtil("/sdcard/A_My_DB/sqlite.db");* @param {*} filePath 地址*/
var SQLiteUtil = /** @class */ (function () {function SQLiteUtil(file_path) {// 导入必要的 java classimportClass(android.database.sqlite.SQLiteDatabase);importClass(android.content.ContentValues);this._db_file_path = file_path;}/** 根据 json 键值 快速建表** (简便函数,如果你的需求很复杂,可以自己调用 execSQL)* @param {string} tableName 表名* @param {object} data json 数据 ,值没有实际意义, 只是用来判断类型, 例子: { name: '', age : 0, online : false}* @param {string[] | undefined} uniqueArr 唯一, 例子: ["name"]*/SQLiteUtil.prototype.fastCreateTable = function (tableName, data, uniqueArr) {var columnArr = [];var itemName = "";// 拼接数据类型for (var key in data) {//this._log((typeof data[key])  + data[key])// 如果有其他类型需求, 请使用 createTableswitch (typeof data[key]) {case "number":itemName = key + " INTEGER DEFAULT 0";break;case "boolean":itemName = key + " INTEGER DEFAULT 0";break;default:itemName = key + " TEXT DEFAULT ''";break;}if (uniqueArr != undefined && uniqueArr.indexOf(key) > -1) {itemName += " unique";}columnArr.push(itemName);}// 创建this.createTable(tableName, columnArr);};/** 创建表** (简便函数,如果你的需求很复杂,可以自己调用 execSQL)** (默认添加一个 自增的 id 属性)* @param {string} tableName 表名* @param {string[]} columns 参数列名 数组, 源码中会用 逗号拼接* @example* ("user_table",["name TEXT DEFAULT '' unique","count INTEGER DEFAULT 0"])*/SQLiteUtil.prototype.createTable = function (tableName, columns) {this._log("init", columns);try {// 创建目录files.ensureDir(this._db_file_path);// 创建表this.execSQL("create table IF NOT EXISTS " + tableName + "(id integer primary key autoincrement," + columns.join(",") + ")");}catch (e) {this._error("createTable error: " + e);throw e;}};/** 查询** (查询一条,请拼接 limit 1)* @example* db.find("select * from " + tableName + " where name='123'");* @param {string} sqlStr sql 字符串* @returns {object[]} json 数组*/SQLiteUtil.prototype.find = function (sqlStr) {this._log("find");var res = [];var db;try {db = this.getDBConnection();// 执行 sql ,并通过 游标 取出所有数据 ( 安卓返回值只有游标,不会直接返回数据 )res = this._getCursorDataArr(db.rawQuery(sqlStr, null));}catch (e) {this._error("find error: " + e);throw e;}finally {db && db.close();}return res;};/** 插入** @param {string} tableName 表名* @param {object} jsonObj json 数据对象, 例子: { name : "123", age : 1 }* @param {string?} nullColumnHack  可以不传,一般为 null 即可   https://www.iteye.com/blog/mofan-1412262* @returns {number} 插入的行 ID ；如果发生错误，则返回-1*/SQLiteUtil.prototype.insert = function (tableName, jsonObj, nullColumnHack) {this._log("insert");nullColumnHack = nullColumnHack || undefined;var res = -1;var db;try {db = this.getDBConnection();db.beginTransaction();res = db.insert(tableName, nullColumnHack, this._getContentValues(jsonObj));db.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交。}catch (e) {this._error("insert error: " + e);throw e;}finally {db && db.endTransaction();db && db.close();}return res;};/** 删除* @example* db.delete(tableName,"name=?",["1名字"])* @param {string} tableName 表名* @param {string} whereClause where 条件* @param {any[]} whereArgs where 条件的参数* @returns {number} 如果传入 whereClause，则受影响的行数，否则为 0 。要删除所有行并获得计数，请将“1”作为 whereClause。*/SQLiteUtil.prototype.delete = function (tableName, whereClause, whereArgs) {this._log("delete");var res = 0;var db;try {db = this.getDBConnection();db.beginTransaction();res = db.delete(tableName, whereClause, whereArgs);db.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交。}catch (e) {this._error("delete error: " + e);throw e;}finally {db && db.endTransaction();db && db.close();}return res;};/** 更新* @example* db.update(tableName,{"address":"哈哈哈"},"user_name=?",["13235919724"])* @param tableName 表名* @param jsonObj json 对象* @param whereClause where 条件* @param whereArgs where 条件的参数* @returns {number} 受影响的行数*/SQLiteUtil.prototype.update = function (tableName, jsonObj, whereClause, whereArgs) {this._log("update");var res = 0;var db;try {db = this.getDBConnection();res = db.update(tableName, this._getContentValues(jsonObj), whereClause, whereArgs);}catch (e) {this._error("update error: " + e);throw e;}finally {db && db.close();}return res;};/** 替换数据库中一行的便捷函数。 如果行不存在，则插入新行。** !!!! 当表有一个 PRIMARY KEY 或 UNIQUE 索引才有意义* @example* https://blog.csdn.net/wangyanguiyiyang/article/details/51126590* @param {string} tableName 表名* @param {object} jsonObj json 对象* @param {string?} nullColumnHack 一般为null即可   https://www.iteye.com/blog/mofan-1412262* @returns {number} 新插入的行的行ID；如果发生错误，则返回-1*/SQLiteUtil.prototype.replace = function (tableName, jsonObj, nullColumnHack) {nullColumnHack = nullColumnHack || undefined;var res = -1;var db;try {db = this.getDBConnection();res = db.replace(tableName, nullColumnHack, this._getContentValues(jsonObj));}catch (e) {this._error("replace error: " + e);throw e;}finally {db && db.close();}return res;};/** 删除表* @param {string} tableName 表名*/SQLiteUtil.prototype.dropTable = function (tableName) {try {this.execSQL("drop table if exists " + tableName);}catch (e) {this._error("dropTable error: " + e);throw e;}};/** 清空表* @param {string} tableName 表名*/SQLiteUtil.prototype.clearTable = function (tableName) {try {this.execSQL("delete from " + tableName);}catch (e) {this._error("clearTable error: " + e);throw e;}};/** 表索引序列归0* @param {string} tableName 表名*/SQLiteUtil.prototype.resetTableSequence = function (tableName) {try {this.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name = '" + tableName + "'");}catch (e) {this._error("resetTableSequence error: " + e);throw e;}};/** 执行sql** (无返回值,需要获取 数据、受影响行数、新增数据的id、等,请使用 find insert update delete )* @param {string} sqlStr* @returns {void}*/SQLiteUtil.prototype.execSQL = function (sqlStr) {var db;try {db = this.getDBConnection();db.execSQL(sqlStr);}catch (e) {this._error("execSQL error: " + e);throw e;}finally {db && db.close();}};/*** 执行 sql 并获取数量** @example* select count(*) from user_table where id < 5* @param sql* @returns*/SQLiteUtil.prototype.getCount = function (sql) {var db;var cursor;try {db = this.getDBConnection();cursor = db.rawQuery(sql, null);cursor.moveToFirst();var count = cursor.getLong(0);return count;}catch (e) {this._error("getCount error: " + e);throw e;}finally {db && db.close();cursor && cursor.close();}};/** 需要升级* @param {number} newVersion  版本号 数字* @returns {boolean} 如果新版本代码大于当前数据库版本，则返回true。*/SQLiteUtil.prototype.needUpgrade = function (newVersion) {var res = false;var db;try {db = this.getDBConnection();res = db.needUpgrade(newVersion);}catch (e) {this._error("needUpgrade error:" + e);throw e;}finally {db && db.close();}return res;};/** 删除数据库文件*/SQLiteUtil.prototype.deleteDbFile = function () {if (files.exists(this._db_file_path)) {files.remove(this._db_file_path);this._log("数据库删除成功,地址:" + this._db_file_path);}};/** 获取 游标里的 数据** @private* @param {*} cursor 游标* @returns {object[]} json 数组*/SQLiteUtil.prototype._getCursorDataArr = function (cursor) {var res = [];if (cursor) {try {cursor.moveToFirst();this._log("cursor count: " + cursor.getCount());var columnNameArr = cursor.getColumnNames();if (cursor.getCount() > 0) {do {var resItem = {};for (var nameIndex = 0; nameIndex < columnNameArr.length; nameIndex++) {var nameItem = columnNameArr[nameIndex];var columnIndex = cursor.getColumnIndex(nameItem);if (columnIndex > -1) {var itemValue = void 0;switch (cursor.getType(columnIndex)) {case 0: // FIELD_TYPE_NULL 0itemValue = null;break;case 1: // FIELD_TYPE_INTEGER 1itemValue = cursor.getInt(columnIndex);break;case 2: // FIELD_TYPE_FLOAT 2itemValue = cursor.getFloat(columnIndex);break;case 3: // FIELD_TYPE_STRING 3itemValue = cursor.getString(columnIndex);break;case 4: // FIELD_TYPE_BLOB 4itemValue = cursor.getBlob(columnIndex);break;default:itemValue = cursor.getString(columnIndex);break;}resItem[nameItem] = itemValue;}}res.push(resItem);} while (cursor.moveToNext());}}catch (e) {this._error("_getCursorDataArr error: " + e);throw e;}finally {cursor && cursor.close();}}return res;};/** 获取 contentValues ( json 转 contentValues )** @private* @param {object} jsonObj json对象* @returns ContentValues 对象*/SQLiteUtil.prototype._getContentValues = function (jsonObj) {// @ts-ignorevar cv = new ContentValues();if (jsonObj) {for (var key in jsonObj) {var item = jsonObj[key];switch (typeof item) {case "number":cv.put(key, java.lang.Integer(item));break;case "boolean":cv.put(key, java.lang.Boolean(item));break;case "boolean":cv.put(key, java.lang.Boolean(item));break;default:cv.put(key, java.lang.String(item));break;}}}/**void put(java.lang.String,java.lang.Long)void put(java.lang.String,java.lang.Byte)void put(java.lang.String,java.lang.Double)void put(java.lang.String,java.lang.Float)void put(java.lang.String,java.lang.Integer)void put(java.lang.String,java.lang.Short)*/return cv;};/** log 日志** @private* @param {*} msg*/SQLiteUtil.prototype._log = function () {var data = [];for (var _i = 0; _i < arguments.length; _i++) {data[_i] = arguments[_i];}// 注释后,可以去掉所有的 log 输出var msg = [];for (var i = 0; i < arguments.length; i++) {var item = arguments[i];msg.push(item);}console.log(msg.join(" "));};/** error 日志** @private* @param {any} msg*/SQLiteUtil.prototype._error = function (msg) {console.error(msg);};/** 获取 db连接对象** @private*/SQLiteUtil.prototype.getDBConnection = function () {// @ts-ignorereturn SQLiteDatabase.openOrCreateDatabase(this._db_file_path, null);};return SQLiteUtil;
}());