const Pool = require('./pool');
const pool = Pool.init();
/**
* 数据库模型
*/
class DB {
/**
* 构造方法
*/
constructor(tableName) {
this.tableName = tableName;
this.pool = pool;
}
/**
* 数据查询接口
* @param tableName
* @param idJson
* @returns {Promise<any>}
*/
fetchRow(idJson) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `SELECT * FROM ${tableName} WHERE ?`
pool.query(sqlMod, idJson, function(error, results) {
if (error) {
reject(error)
} else {
if (results) {
resolve(results.pop())
} else {
resolve(results)
}
}
})
})
}
/**
* 取数据集合
* @param idJson
* @returns {Promise<any>}
*/
fetchRows(idJson) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `SELECT * FROM ${tableName} WHERE ?`
pool.query(sqlMod, idJson, function (error, results) {
if (error) {
reject(error)
} else resolve(results)
})
})
}
/**
* 数据插入接口
* @param tableName
* @param rowInfo
* @returns {Promise<any>}
*/
insert(rowInfo) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `INSERT INTO ${tableName} SET ?`
pool.query(sqlMod, rowInfo, function(error, result) {
if (error) reject(error)
else resolve(result)
})
})
}
/**
* 数据修改接口
* @param tableName
* @param idJson
* @param rowInfo
* @returns {Promise<any>}
*/
update(idJson, rowInfo) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `UPDATE ${tableName} SET ? WHERE ?`
pool.query(sqlMod, [rowInfo, idJson], function (error, result) {
if (error) reject(error)
else resolve(result)
})
})
}
/**
* 数据删除接口
* @param tableName
* @param idJson
* @returns {Promise<any>}
*/
remove(idJson) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `DELETE FROM ${tableName} WHERE ?`
pool.query(sqlMod, idJson, function (error, result) {
if (error) reject(error)
else resolve(result)
})
})
}
/**
* 统计
* @param idJson
* @returns {Promise<any>}
*/
count(idJson) {
const { tableName, pool } = this
return new Promise((resolve, reject) => {
const sqlMod = `SELECT COUNT(*) as count FROM ${tableName} WHERE ?`
pool.query(sqlMod, idJson, function (error, result) {
if (error) reject(error)
else resolve(result.pop())
})
})
}
/**
* 自定义查询
* @param sql
* @returns {Promise<any>}
*/
queryStr(sqlMod) {
const { pool } = this
return new Promise((resolve, reject) => {
pool.query(sqlMod, function (error, result) {
if (error) {
reject(error)
} else {
resolve(result)
}
})
})
}
/**
* 复合查询
* @param tableName
* @param whereJson
* @param orderByJson
* @param limitArr
* @param selectStr
* @returns {Promise<any>}
*/
fetchAll(tableName, selectStr, whereJson, orderByJson = '', limitArr = '') {
const andWhere = whereJson['and']
const orWhere = whereJson['or']
const betArr = whereJson['between']
const andArr = []
const orArr = []
for(const key in andWhere) {
const snap = typeof andWhere[key] === 'string' ? '\"' : ''
andArr.push(`\`${key}\` = ${snap}${andWhere[key]}${snap}`)
}
for(const key in orWhere) {
const snap = typeof andWhere[key] === 'string' ? '\"' : ''
orArr.push(`\`${key}\` = ${snap}${orWhere[key]}${snap}`)
}
const andStr = andArr.join(' and ')
const orStr = orArr.join(' or ')
const betStr = betArr ? `AND ${betArr[0]} BETWEEN ${betArr[1]} AND ${betArr[2]}` : ''
const orderStr = orderByJson['type'] ? `order by ${orderByJson['key']} ${orderByJson['type']}` : ''
const limitStr = limitArr.length > 0 ? `limit ${limitArr.join(',')}` : ''
const sqlMod = `SELECT ${selectStr} FROM ${tableName} WHERE ${andStr} ${orStr} ${betStr} ${orderStr} ${limitStr}`
return new Promise((resolve, reject) => {
pool.query(sqlMod, function (error, results) {
if (error) {
reject(error)
} else resolve(results)
})
})
}
}
module.exports = DB
上面引用的pool.js
const mysql = require('mysql')
const config = require('../config/mysql')
class Pool {
constructor() {
this.pool = this.init()
}
init() {
return mysql.createPool(config)
}
}
module.exports = new Pool(config)
以上是在线运行项目,特意分享,网上大多都是2015年的文章,且没有新的方法,可以查看官方文档 mysql - npm
用pool.query 不用自己再去query 封装一层,去先建connect, 再查,再释放。
如要不相信可以下我的项目,自己部署跑一下,项目地址 新上线对应的后台接口Api Resetful 平台 wapman-admin https://github.com/xiejunping/wapman-admin
如果运行报错,随时可以在群里提问 QQ群 607631721
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。