前期工作
- 在
manifest.json
中打开 SQLite
数据库
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/c7fbd0c9ad53421cab8195030b4af706.png)
- 创建数据库存储文件
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/7f47e4257f774f4ba4d5e603d06c9d28.png)
开始封装 sqlite.js
module.exports = {
dbName: 'chat',
dbPath: '_doc/chat.db',
isOpen() {
let open = plus.sqlite.isOpenDatabase({
name: this.dbName,
path: this.dbPath
})
return open
},
openSqlite() {
return new Promise((resolve, reject) => {
plus.sqlite.openDatabase({
name: this.dbName,
path: this.dbPath,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
},
closeSqlite() {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: this.dbName,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
},
createTable(dbTable, data) {
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql: `CREATE TABLE IF NOT EXISTS ${dbTable}(${data})`,
success: (res) => {
console.log('创建数据库表', res);
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
},
dropTable(dbTable) {
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql: `DROP TABLE ${dbTable}`,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
},
insertTableData(dbTable, data, condition) {
if (dbTable !== void 0 && data !== void 0) {
let bol = (JSON.stringify(data) == '{}')
if (!bol) {
let sql = condition == void 0 ? `INSERT INTO ${dbTable} VALUES('${data}')` :
`INSERT INTO ${dbTable} (${condition}) VALUES(${data})`
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
} else {
return new Promise((resolve, reject) => {
reject('错误添加')
})
}
} else {
return new Promise((resolve, reject) => {
reject('错误添加')
})
}
},
insertOrReplaceData(dbTable, data, condition) {
if (dbTable !== void 0 && data !== void 0) {
let sql = condition == void 0 ? `INSERT OR REPLACE INTO ${dbTable} VALUES('${data}')` :
`INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data})`;
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
} else {
return new Promise((resolve, reject) => {
reject('错误添加')
})
}
},
selectTableData(dbTable, uname, namevalue, upass, passvalue, urrn, rrnvalue) {
if (dbTable !== undefined) {
if (uname !== undefined && upass !== undefined && urrn !== undefined) {
var sql =
`SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}' AND ${urrn}='${rrnvalue}'`;
}
if (uname !== undefined && upass !== undefined && urrn == undefined) {
var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}'`;
}
if (uname !== undefined && upass == undefined && urrn == undefined) {
var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}'`;
}
if (uname == undefined) {
var sql = `SELECT * FROM ${dbTable}`;
}
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject('错误查询')
});
}
},
deleteTableData(dbTable, lname, lvalue, ww, ee) {
if (dbTable !== void 0) {
if (lname == void 0) {
var sql = `DELETE FROM ${dbTable}`;
} else {
var sql = ww !== void 0 ? `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'` :
`DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;
}
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
} else {
return new Promise((resolve, reject) => {
reject('错误删除')
})
}
},
updateTableData(dbTable, data, lname, lvalue) {
let sql = lname == void 0 ? `UPDATE ${dbTable} SET ${data}` :
`UPDATE ${dbTable} SET ${data} WHERE ${lname} = '${lvalue}'`;
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql,
success: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
},
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: (res) => {
resolve(res)
},
fail: (err) => {
reject(err)
}
})
})
}
}
使用 SQLite
import DB from '@/utils/sqlite.js'
openSQL() {
let open = DB.isOpen();
if (!open) {
DB.openSqlite()
.then(() => {
console.log('数据库打开')
})
.catch(() => {
console.log('数据库关闭')
})
}
}
closeSQL() {
let open = DB.isOpen();
if (open) {
DB.closeSqlite()
.then(() => {
console.log("数据库已关闭");
})
.catch(() => {
console.log("数据库关闭失败");
})
}
}
createTable() {
let open = DB.isOpen();
if (open) {
this.openSQL();
let sql = '"id" INTEGER PRIMARY KEY AUTOINCREMENT,"name" text,"content" text,"time" text';
DB.createTable('chat', sql)
.then(() => {
console.log('创建 chat 表成功')
})
.catch(() => {
console.log('创建表失败')
})
} else {
console.log('数据库未打开')
}
},
detaleTable() {
let open = DB.isOpen();
if (open) {
DB.dropTable('chat')
.then(res => {
console.log('删除表成功', res);
})
.catch(err => {
console.log('删除表失败');
})
}
}
selectTableData() {
let open = DB.isOpen();
if (open) {
DB.selectTableData('chat')
.then(res => {
console.log('contact', res);
})
.catch(err => {
console.log('查询失败');
})
} else {
console.log('数据库未打开');
}
}
insertTableData() {
let open = DB.isOpen();
if (open) {
let arr = [
{ name: '小明', content: "你好呀" },
{ name: '小红', content: "HI" }
]
arr.map(v => {
let time = new Date().getTime();
let sql = `'${v.name}','${v.content}','${time}'`;
let condition = "'name','content','time'";
DB.insertOrReplaceData('chat', sql, condition)
.then(res => {
console.log('添加成功', res);
this.selectTableData()
})
.catch(err => {
console.log('添加失败');
})
} else {
console.log('数据库未打开');
}
}
- 根据条件向表格里添加数据 有数据更新、无数据插入
- 需要更新数据则需要传主键 id
- 不传主键 id 则继续向后添加
insertOrReplaceData() {
let open = DB.isOpen()
if (open) {
let arr = [
{id: 1, name: '小明呀!', content: "你好呀!" },
]
arr.map(v => {
let time = new Date().getTime();
let sql = `'${v.id}','${v.name}','${v.content}','${time}'`;
let condition = "'id','name','content','time'";
DB.insertOrReplaceData('chat', sql, condition)
.then(res => {
console.log('新增成功', res);
this.selectTableData()
})
.catch(err => {
console.log('新增失败');
})
})
} else {
console.log('数据库未打开');
}
},
deleteTableData() {
let open = DB.isOpen()
if (open) {
DB.deleteTableData('chat', 'name', '小明')
.then(res => {
console.log('删除表数据成功');
})
.catch(err => {
console.log('删除失败');
})
} else {
console.log('数据库未打开');
}
},
updateTableData() {
let open = DB.isOpen()
if (open) {
let time = new Date().getTime();
let data = `content = '我被修改了',time = '${time}'`;
DB.updateTableData('chat', data, 'name', '小明')
.then(res => {
console.log('数据库更新成功');
})
.catch(err => {
console.log('数据库更新失败');
})
} else {
console.log('数据库未打开');
}
},
pullSQL() {
let open = DB.isOpen();
if (open) {
DB.pullSQL('chat', 'id', 1)
.then(res => {
console.log(res, '@@@');
})
.catch(err => {
console.log(err, '!!!');
})
}
},