Using SQLite in UNI-App starts with learning simple SQL syntax. SQLite portal
The first job
plus.sqliteWant to be inMobile phone or emulatorTo run!- in
manifest.jsonIn theApp moduleSelect in permission configurationSQLite(database)!
The source code
encapsulatedsqlite.js
isOpenDetermine whether to open the databaseopenSqliteCreate database/open databasecloseSqliteClosing the databasecreateTableDatabase table buildingdropTableDelete table from databaseinsertTableDataDatabase insert data – no primary keyinsertOrReplaceDataDatabase inserts data – with primary keysselectTableDataQuery retrieves database datadeleteTableDataDelete table dataupdateTableDataUpdate the data in the tablepullSQLGets the specified number of data items
module.exports = {
dbName: 'chat'.// Database name
dbPath: '_doc/chat.db'.// Database address, starting with an underscore _doc/xxx.db is recommended
// Check whether the database is open
isOpen() {
// Return true if the database is open, false otherwise
var open = plus.sqlite.isOpenDatabase({
name: this.dbName, // Database name
path: this.dbPath // Database address
})
return open;
},
// Create a database or open it when it exists
openSqlite() {
return new Promise((resolve, reject) = > {
// Open the database
plus.sqlite.openDatabase({
name: this.dbName,
path: this.dbPath,
success(e) {
resolve(e); // Successful callback
},
fail(e) {
reject(e); // Failed callback}})})},// Close the database
closeSqlite() {
return new Promise((resolve, reject) = > {
plus.sqlite.closeDatabase({
name: this.dbName,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})},SQL :'CREATE TABLE IF NOT EXISTS dbTable("id" varchar(50),"name" TEXT)
// CREATE a CREATE TABLE IF NOT EXISTS. DbTable is the name of a TABLE and cannot start with a number
createTable(dbTable, data) {
return new Promise((resolve, reject) = > {
// executeSql: SQL statements that perform operations such as add, delete, and modify
plus.sqlite.executeSql({
name: this.dbName,
sql: `CREATE TABLE IF NOT EXISTS ${dbTable}(${data}) `.success(e) {
resolve(e);
},
fail(e){ reject(e); }})})},SQL :'DROP TABLE dbTable'
dropTable(dbTable) {
return new Promise((resolve, reject) = > {
plus.sqlite.executeSql({
name: this.dbName,
sql: `DROP TABLE ${dbTable}`.success(e) {
resolve(e);
},
fail(e){ reject(e); }})})},SQL :'INSERT INTO dbTable VALUES('x','x','x')
/ / or SQL: 'INSERT INTO dbTable (' x', 'x', 'x') VALUES (' x ', 'x', 'x') 'specific new
// INSERT INTO, dbTable is the name of the table, and INSERT column values according to the name of the header column
insertTableData(dbTable, data, condition) {
// Check whether the parameter is passed
if(dbTable ! = =undefined&& data ! = =undefined) {
// Check whether the passed parameter has a value
var bol = (JSON.stringify(data) == "{}");
if(! bol) {if (condition == undefined) {
var sql = `INSERT INTO ${dbTable} VALUES('${data}') `;
} else {
var sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${data}) `;
}
// console.log(sql);
return new Promise((resolve, reject) = > {
// Add data to the table
plus.sqlite.executeSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})}else {
return new Promise((resolve, reject) = > { reject("Error added")}}}else {
return new Promise((resolve, reject) = > { reject("Error added")})}},// Add data to table according to condition
For example, "roomID" vARCHar (50) PRIMARY KEY
insertOrReplaceData(dbTable, data, condition) {
// Check whether the parameter is passed
if(dbTable ! = =undefined&& data ! = =undefined) {
if (condition == undefined) {
var sql = `INSERT OR REPLACE INTO ${dbTable} VALUES('${data}') `;
} else {
var sql = `INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data}) `;
}
// console.log(sql);
return new Promise((resolve, reject) = > {
// Add data to the table
plus.sqlite.executeSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})}else {
return new Promise((resolve, reject) = > { reject("Error added")})}},SQL: SELECT * FROM dbTable WHERE lname = 'lvalue'
SELECT * FROM; dbTable; lname; lvalue
selectTableData(dbTable, lname, lvalue, cc, dd) {
if(dbTable ! = =undefined) {
// The first parameter is the form name, and the last two parameters are the list name for retrieval
if(lname ! = =undefined&& cc ! = =undefined) {
// Two search criteria
var sql = `SELECT * FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${cc} = '${dd}'`;
}
if(lname ! = =undefined && cc == undefined) {
// a search condition
var sql = `SELECT * FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;
// console.log(sql);
}
if (lname == undefined) {
var sql = `SELECT * FROM ${dbTable}`;
}
return new Promise((resolve, reject) = > {
// Table query data to execute the query SQL statement
plus.sqlite.selectSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})}else {
return new Promise((resolve, reject) = > { reject("Error query")}); }},SQL :'DELETE FROM dbTable WHERE lname = 'lvalue'
DELETE FROM and dbTable are the name of the table, WHERE search condition lname, and lvalue are the column name and value of the query condition
deleteTableData(dbTable, lname, lvalue, ww, ee) {
if(dbTable ! = =undefined) {
if (lname == undefined) {
var sql = `DELETE FROM ${dbTable}`;
} else {
if(ww ! = =undefined) {
// Two search criteria
var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'`;
} else {
// a search condition
var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`; }}return new Promise((resolve, reject) = > {
// Delete table data
plus.sqlite.executeSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})}else {
return new Promise((resolve, reject) = > { reject("Error deletion")}); }},SQL :"UPDATE dbTable SET lname = 'lvalue' WHERE lname = 'lvalue'"
UPDATE and dbTable are table names, data: column names to be modified = column values to be modified, lname and lvalue are column names and column values of the query criteria
updateTableData(dbTable, data, lname, lvalue) {
if (lname == undefined) {
var sql = `UPDATE ${dbTable} SET ${data}`;
} else {
var sql = `UPDATE ${dbTable} SET ${data} WHERE ${lname} = '${lvalue}'`;
}
// WHERE is the column name and value to be modified, followed by the column name and value of the condition
return new Promise((resolve, reject) = > {
// Modify table data
plus.sqlite.executeSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e){ reject(e); }})})},SQL :"SELECT * FROM dbTable ORDER BY 'id' DESC LIMIT 15 OFFSET 'num'"
// select * from dbTable where ORDER BY is the default ORDER and id is the condition
// OFFSET '${num}' OFFSET '${num}' LIMIT 15 OFFSET '${num}
// If num = 0, select 15 from last; if num = 15, select 15 from last; if num = 15, select 15 from last
pullSQL(dbTable, id, num) {
return new Promise((resolve, reject) = > {
plus.sqlite.selectSql({
name: this.dbName,
sql: `SELECT * FROM ${dbTable} ORDER BY '${id}' DESC LIMIT 15 OFFSET '${num}'`.success(e) {
resolve(e);
},
fail(e){ reject(e); }})})}Copy the codeOn the pageimportThe introduction ofsqlite.js
/ / introduction
import DB from "@/common/sqlite.js";
// Check whether the database is open
DB.isOpen()
// Create/start the database
DB.openSqlite()
// Close the database
DB.closeSqlite()
// create table
DB.createTable()
// Delete table from database
DB.dropTable()
// Add/insert data into the table
DB.insertTableData()
// Insert data, update, or overwrite into the table based on conditions
DB.insertOrReplaceData()
// query data in table
DB.selectTableData()
// Delete data from table
DB.deleteTableData()
// Modify data in table
DB.updateTableData()
// query data in reverse order
DB.pullSQL()
Copy the code