Node.js+SQLite后端数据库的读写和操作展示
SQLite数据的封装体
var sqlite3 = require('sqlite3').verbose();
// sqlites数据库地址
var path = require('path');
var sqliteDbPath = path.resolve(__dirname, './sqlite3.db');
// 打开sqlites数据库
var sqlite3Connection = new sqlite3.Database(sqliteDbPath, (err: any) => {
if (err) throw err;
});
type Istrings = string | string[] | undefined;
interface Idb {
type: string;
table: string;
field?: Istrings;
value?: Istrings;
set?: Istrings;
where?: Istrings;
order?: Istrings;
limit?: number;
}
class DbSQLite3 {
pool: any;
constructor(pool: any) {
this.pool = pool;
}
joinSql(sql: Idb, type: 'where' | 'order' | 'value' | 'set' | 'field') {
var val = sql[type];
if (Array.isArray(val)) {
if (val.length) {
if (type === 'where') {
return ' where ' val.join(' and ');
} else if (type === 'order') {
return ' order by ' val.join(',');
} else if (type === 'value') {
return ' values (' val.join('),(') ')';
} else if (type === 'set') {
return ' set ' val.join(',');
} else if (type === 'field') {
return ' (' val.join(',') ')';
}
} else {
if (['value', 'set'].includes(type)) {
// 不能为空
}
}
} else if (val) {
if (type === 'where') {
return ' where ' val;
} else if (type === 'order') {
return ' order by ' val;
} else if (type === 'value') {
return ' values (' val ')';
} else if (type === 'set') {
return ' set ' val;
} else if (type === 'field') {
return ' (' val ')';
}
}
return '';
}
makeSql(sql: Idb) {
// sqlite查询sql的时候,有些指定的语句不支持order与limit
var sqls = sql.type;
if (sqls === 'insert') {
sqls = ' into';
sqls = ' ' sql.table;
sqls = this.joinSql(sql, 'field');
sqls = this.joinSql(sql, 'value');
} else if (sqls === 'delete') {
sqls = ' from ' sql.table;
sqls = this.joinSql(sql, 'where');
} else if (sqls === 'update') {
sqls = ' ' sql.table;
sqls = this.joinSql(sql, 'set');
sqls = this.joinSql(sql, 'where');
} else if (sqls === 'select') {
sqls = sql.field ? ' ' sql.field : ' *';
sqls = ' from ' sql.table;
sqls = this.joinSql(sql, 'where');
sqls = this.joinSql(sql, 'order');
sqls = sql.limit ? ' limit ' sql.limit : '';
}
console.log(666.111, sqls);
return sqls;
}
getErr(err: any) {
var errs = err?.message || null;
return errs;
}
getResult(rows: any) {
var result = rows ?? 'OK';
return result;
}
query(sql: Idb, callback: any) {
console.log(666.101, sql, callback);
if (sql.type === 'select') {
if (sql.where && (!sql.limit || sql.limit === 1) && false) {
// 直接返回一个obj
this.pool.each(this.makeSql(sql), (err: any, rows: any) => {
console.log(666.103, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
} else {
this.pool.all(this.makeSql(sql), (err: any, rows: any) => {
console.log(666.104, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
}
} else {
this.pool.run(this.makeSql(sql), (err: any, rows: any) => {
console.log(666.105, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
}
}
}
export default new DbSQLite3(sqlite3Connection);
读取示例,与上一篇的mysql保持一致
import db from './db/sqlite/db';
// 假设我们有个数据表users,有id、userName、userPassword、userType四个字段
var a = 'select';
// a = 'insert';
if (a === 'delete') {
db.query(
{
type: 'delete',
table: 'user',
where: 'userName="SSSS"',
limit: 2,
order: 'id desc',
},
(err: any, result: any) => {
if (err) {
console.log(666.201, err);
} else {
console.log(666.202, result);
if (result) {
console.log(666.2022, '删除结果:' result);
}
}
}
);
} else if (a === 'update') {
db.query(
{
type: 'update',
table: 'user',
// set: 'userName="GGGG"',
set: ['userName="SSSS"', 'userPassword="FFFF"'],
where: 'id=10',
limit: 3,
order: 'id desc',
},
(err: any, result: any) => {
if (err) {
console.log(666.201, err);
} else {
console.log(666.202, result);
if (result) {
console.log(666.2022, '修改结果:' result);
}
}
}
);
} else if (a === 'insert') {
db.query(
{
type: 'insert',
table: 'user',
field: 'userName,userPassword,userType',
// value: '"asai","123456",8',
value: ['"asai","123456",8', '"阿萨","分sss",88', '"阿萨","分sss",88'],
},
(err: any, result: any) => {
if (err) {
console.log(666.201, err);
} else {
console.log(666.202, result);
if (result) {
console.log(666.2022, '增加结果:' result);
}
}
}
);
} else if (a === 'select') {
db.query(
{
type: 'select',
table: 'user',
field: '*',
where: 'id>5',
limit: 1,
order: 'id desc',
},
(err: any, result: any) => {
if (err) {
console.log(666.201, err);
} else {
console.log(666.202, result?.length, result);
if (result && result.length) {
result.forEach((element: any) => {
console.log(666.2022, element?.id, element);
});
}
}
}
);
}
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhggaeah
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01