# 新建/libs/model.js
**model類采用了連接池進行操作，集成了增刪改查方法，數據字段自動過濾功能，sql錯誤自動記錄功能，數據查詢文件緩存功能，查詢方法可以select、find、column、value、map進行查詢**
```
var fs = require('fs');
var config = require('../config');
var tools = require(__dirname + '/tools');
var mysql = require('mysql');
//使用config的數據庫配置連接數據庫
var connection = mysql.createConnection(config.mysql);
const crypto = require('crypto');
var pool = mysql.createPool(config.mysql);
//設置sql模式
pool.on('connection', function (connection) {
connection.query("SET sql_mode=''");
});
var model = {
debug: false,
init: function () {
},
//記錄sql錯誤
log_error: function (err, data = '') {
let str = err.errno + ':' + err.sqlMessage + "\n" + err.sql + "\n" + JSON.stringify(data) + "\n";
fs.appendFile(tools.runtimePath + 'mysql.txt', str, (err) => {
console.error('sql error:', str);
});
},
//數據表字段緩存
table_fields: function (table) {
//check exist
let jsonfile = tools.tableCachePath + table + '.json';
if (fs.existsSync(jsonfile)) {
try {
return JSON.parse(fs.readFileSync(jsonfile));
} catch (e) {
return [];
}
}
return new Promise((resolve, reject) => {
pool.query("select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA=? and TABLE_NAME=?", [config.mysql.database, table], (err, results) => {
if (err) {
try {
this.log_error(err, [table]);
} catch (e) { }
resolve([]);
return;
}
let r = [];
for (let i = 0; i < results.length; i++) {
r.push(results[i]['COLUMN_NAME']);
}
try {
fs.writeFileSync(jsonfile, JSON.stringify(r));
} catch (e) {
console.error('save table jsonfile fail!');
}
resolve(r);
});
});
},
//where封裝
where: function (obj, before = '') {
if (Object.keys(obj).length == 0) return before;
let sql = [];
if (before) sql.push(before);
for (let k in obj) {
let v = obj[k];
if (v instanceof Array) {
if (v.length != 2) continue;
if (v[0].toLowerCase() == 'like') {
sql.push(` ${k} like '${v[1]}' `);
continue;
}
if (v[0].toLowerCase() == 'in') {
if (!(v[1] instanceof Array)) {
v[1] = [v[1]];
}
v[1].push(0);
v[1] = v[1].join(',');
sql.push(` ${k} in(${v[1]}) `);
continue;
}
if (v[0].toLowerCase() == 'not in') {
if (!(v[1] instanceof Array)) {
v[1] = [v[1]];
}
v[1].push(0);
v[1] = v[1].join(',');
sql.push(` ${k} not in(${v[1]}) `);
continue;
}
if (v[0].toLowerCase() == 'exp') {
sql.push(` ${v[1]} `);
continue;
}
sql.push(` ${k} ${v[0]} '${v[1]}' `);
continue;
}
sql.push(` ${k}='${v}' `);
}
return sql.join(' and ');
},
//insert
insert: function (sql, p = []) {
return new Promise((resolve, reject) => {
pool.query(sql, p, (err, results, fields) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve(false);
return;
}
resolve(results.insertId);
});
});
},
//insert into table set ?
i: async function (sql, p = {}) {
let table = sql.match(/insert into(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1];
if (!table) return false;
let fields = await this.table_fields(table);
let p2 = {};
for (var i in p) {
if (fields.indexOf(i) > -1) p2[i] = p[i];
}
return new Promise((resolve, reject) => {
pool.query(sql, p2, (err, results, fields) => {
if (err) {
try {
this.log_error(err, p2);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve(false);
return;
}
resolve(results.insertId);
});
});
},
//update
update: function (sql, p = []) {
return new Promise((resolve, reject) => {
pool.query(sql, p, (err, results, fields) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve(false);
return;
}
resolve(true);
});
});
},
//update table set ? where id=?
u: async function (sql, p = {}, where = []) {
let table = sql.match(/update(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1];
if (!table) return false;
let fields = await this.table_fields(table);
let p2 = {};
for (var i in p) {
if (fields.indexOf(i) > -1) p2[i] = p[i];
}
where.unshift(p2);
return new Promise((resolve, reject) => {
pool.query(sql, where, (err, results, fields) => {
if (err) {
try {
this.log_error(err, where);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve(false);
return;
}
resolve(true);
});
});
},
//delete
delete: function (sql, p = []) {
return new Promise((resolve, reject) => {
pool.query(sql, p, (err, results, fields) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve(false);
return;
}
resolve(true);
});
});
},
//獲取md5
getSign: function (sql, p, cacheTime) {
return cacheTime > 0 ? crypto.createHash('md5').update(`${sql}${JSON.stringify(p)}`).digest('hex') : '';
},
//讀取緩存
getCache: async function (sign, cacheTime) {
let cachePath = tools.runtimePath + 'cache/';
let subFolderA = sign.substr(0, 2);
let subFolderB = sign.substr(2, 2);
let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json';
try {
let e = await fs.promises.stat(dest);
let mTime = parseInt(e.mtimeMs / 1000);
let now = parseInt(new Date().getTime() / 1000);
if (now > mTime + cacheTime) return [false, null];
let d = JSON.parse(await fs.promises.readFile(dest, 'utf8'));
return [true, d];
} catch (err) {
if (err) return [false, null];
}
},
//設置緩存
setCache: async function (sign, data) {
let cachePath = tools.runtimePath + 'cache/';
let subFolderA = sign.substr(0, 2);
let subFolderB = sign.substr(2, 2);
let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json';
try {
await fs.promises.mkdir(cachePath + subFolderA + '/' + subFolderB + '/', { recursive: true });
await fs.promises.writeFile(dest, JSON.stringify(data));
return true;
} catch (err) {
if (err) return false;
}
},
//select返回數組
select: async function (sql, p = [], cacheTime = 0) {
let sign = this.getSign(sql, p, cacheTime);
if (cacheTime > 0) {
let d = await this.getCache(sign, cacheTime);
if (d[0] === true) return d[1];
}
return new Promise(async (resolve, reject) => {
pool.query(sql, p, async (err, results) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve([]);
return;
}
if (cacheTime > 0) {
await this.setCache(sign, results.length > 0 ? results : []);
}
resolve(results.length > 0 ? results : []);
});
});
},
//find返回object
find: async function (sql, p = [], cacheTime = 0) {
let sign = this.getSign(sql, p, cacheTime);
if (cacheTime > 0) {
let d = await this.getCache(sign, cacheTime);
if (d[0] === true) return d[1];
}
return new Promise(async (resolve, reject) => {
pool.query(sql, p, async (err, results) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve({});
return;
}
if (cacheTime > 0) {
await this.setCache(sign, results.length > 0 ? results[0] : {});
}
resolve(results.length > 0 ? results[0] : {});
});
});
},
//column返回一列數組
column: async function (sql, p = [], cacheTime = 0) {
let sign = this.getSign(sql, p, cacheTime);
if (cacheTime > 0) {
let d = await this.getCache(sign, cacheTime);
if (d[0] === true) return d[1];
}
return new Promise(async (resolve, reject) => {
pool.query(sql, p, async (err, results) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve([]);
return;
}
let r = [];
for (let i = 0; i < results.length; i++) {
for (let n in results[i]) {
r.push(results[i][n]);
break;
}
}
if (cacheTime > 0) {
await this.setCache(sign, r);
}
resolve(r);
});
});
},
//value返回一個值
value: async function (sql, p = [], cacheTime = 0) {
let sign = this.getSign(sql, p, cacheTime);
if (cacheTime > 0) {
let d = await this.getCache(sign, cacheTime);
if (d[0] === true) return d[1];
}
return new Promise(async (resolve, reject) => {
pool.query(sql, p, async (err, results) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve('');
return;
}
let r = '';
if (results.length > 0) {
for (let n in results[0]) {
r = results[0][n];
break;
}
if (cacheTime > 0) {
await this.setCache(sign, r);
}
resolve(r);
return;
}
if (cacheTime > 0) {
await this.setCache(sign, r);
}
resolve(r);
});
});
},
//map返回一列的鍵值對
map: async function (sql, p = [], cacheTime = 0) {
let sign = this.getSign(sql, p, cacheTime);
if (cacheTime > 0) {
let d = await this.getCache(sign, cacheTime);
if (d[0] === true) return d[1];
}
return new Promise(async (resolve, reject) => {
pool.query(sql, p, async (err, results) => {
if (err) {
try {
this.log_error(err, p);
} catch (e) { }
if (this.debug) console.error('sql_error:', sql);
resolve({});
return;
}
let r = {};
for (let i = 0; i < results.length; i++) {
let fds = [];
for (let n in results[i]) {
fds.push(n);
}
r[results[i][fds[0]]] = results[i][fds[1]];
}
if (cacheTime > 0) {
await this.setCache(sign, r);
}
resolve(r);
});
});
},
//自定義方法
get_users: function () {
return ['admin', 'tom', 'jack'];
}
};
model.init();
module.exports = { connection: connection, pool: pool, model: model };
```
- 課程介紹
- 開發環境搭建
- 安裝express.js框架
- 為diy自己的web框架做準備(1)
- 為diy自己的web框架做準備(2)
- 為應用綁定域名
- 封裝控制器基類base.js
- 封裝數據庫操作基類model.js
- curd操作-準備工作
- curd操作-文章列表
- curd操作-添加文章
- curd操作-編輯文章
- curd操作-刪除文章
- model文件的使用
- 文件上傳
- session實現登錄
- 郵件發送
- 文件下載
- 執行子任務
- 圖片縮放
- 圖片裁剪
- 圖片驗證碼
- Excel讀取與寫入
- 編寫計劃任務
- 工具函數使用實例
- websocket
- 集成ckeditor
- 微信公眾號開發-1：內網穿透
- 微信公眾號開發-2：自動回復
- 微信公眾號開發-3：api接口調用
- 微信公眾號開發-4：oauth登錄
- 微信公眾號開發-5：沙箱支付
- 微信公眾號開發-6：真實支付
- 項目上線運行
- 項目代碼下載