1. 安装mysql包
npm i mysql -s
2. 创建一个 database.config.js 用于mysql配置
/**
* Database config.
*
*/
module.exports = {
host: 'localhost',
port: '3306',
user: 'root',
password: 'xxxxxxx.',
database: ''
...
}
详细配置请参考:https://www.npmjs.com/package/mysql
3.创建一个db.js 写封装的逻辑
const mysql = require('mysql'); //mysql包
const config = require('./config/database.config'); //数据库配置
class CustomDB {
/**
* config一些配置文件,例如主机、端口、用户、密码....
*/
constructor(config={}) {
this.config = config;
}
/**
* 执行sql
*/
query(sql, params) {
let me = this;
return new Promise((resolve, reject) => {
const connection = mysql.createConnection(me.config);
connection.connect(err => {
if (err) reject('DATABASE ERROR:connection failed!')
});
//Execute SQL statement
connection.query(sql, params, (err, results, fileds) => {
if (err) reject(`DATABASE ERROR:${err.message}`);
resolve(results, fileds);
//Close the connection
connection.end(err => {
reject('DATABASE ERROR:close connection failed!')
});
});
});
}
/**
* 插入数据,单行插入
* @param table
* @param datas
*/
insert({table, datas}) {
return new Promise(async(resolve, reject) => {
try {
let fields = ''; //字段名
let values = []; //字段值
for(let key in datas) {
fields += key + ','
values.push(datas[key]);
}
fields = fields.slice(0, -1);
values = JSON.stringify(values).slice(1, -1);
const insertSql = `INSERT INTO ${table} (${fields}) VALUES (${values})`;
let insertResult = await this.query(insertSql); //写入结果
//是否成功写入
if (insertResult.affectedRows > 0) {
resolve(insertResult)
} else {
reject(`INSERT ERROR: ${insertResult.message}`);
}
} catch (error) {
reject(typeof error === 'object' ? `ABNORMAL PRGORAM:${error.message}` : error);
}
});
}
/**
* 更新
*/
update({table, sets, where}) {
return new Promise(async(resolve, reject) => {
try {
let setData = '';
for (let key in sets) {
let value = typeof sets[key] === 'string' ? `"${sets[key]}"` : `${sets[key]}`;
setData += `${key}=${value}, `;
}
setData = setData.slice(0, -2);
const updateSql = `UPDATE ${table} SET ${setData} WHERE ${where}`;
console.log(updateSql);
let updateResult = await this.query(updateSql);
if (updateResult.changedRows === 0) {
reject(`UPDATE ERROR: ${updateResult.message}`, updateResult);
} else {
resolve(updateResult);
}
} catch (error) {
reject && reject(typeof error === 'object' ? `ABNORMAL PRGORAM:${error.message}` : error);
}
});
}
/**
* 删除
* @param table
*/
delete({table, where}) {
return new Promise(async (resolve, reject) => {
try {
const deleteSql = `DELETE FROM ${table} WHERE ${where}`;
let deleteResult = await this.query(deleteSql);
if (deleteResult.affectedRows === 0) {
reject(`DELETE ERROR: ${deleteResult.message}`, deleteResult);
} else {
resolve(deleteResult);
}
} catch (error) {
console.log(error);
reject && reject(typeof error === 'object' ? `ABNORMAL PRGORAM:${error.message}` : error);
}
});
}
}
//导出模块
module.exports = new CustomDB(config); //初始化实例,传入配置文件
4. 外部调用
const db = require('./libs/db.js'); //引入封装的db模块
//新增一个用户
let userData = {
table: 'sys_user',
datas: {
ID: 'b57ce5e8-a2ff-45f8-b92e-97e136d9187d',
ACCOUNT: 'admin1',
PASSWORD: '123456',
NICK_NAME: '管理员1',
SYS_ID: 'b67ce5e8-a2ff-45f8-b92e-97e136d9187d',
STATUS: 1,
CREATE_TIME: '2019-07-06 14:20:30'
}
};
db.insert(userData).then(res => {
console.log(res);
}).catch(err => {
console.log(err);
});
OR
let addUser = async() => {
try {
let addResult = await db.insert(userData);
//success do sth...
} catch (error) {
//error do sth...
}
}
Tip: 由于是基于Promise封装,代码调用中会出现 .then().catch(), 作者建议使用 async,await 来处理回调问题哦~