# 新建/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：真实支付
- 项目上线运行
- 项目代码下载