nodejs使用mysql连接池,并封装mysql模块
mysql连接池的封装
pool.js文件内容:
var mysql = require('mysql');
/*
* 创建连接池。
*/
var create = function(config) {
var pool = mysql.createPool({
connectionLimit: 200,
acquireTimeout: 30000,
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
});
return pool;
};
exports.create = create;
连接池的使用:
var pool= require('./pool.js');
var config = {
host: "127.0.0.1",
port: "3306",
user: "root",
password: "123456",
database: "million",
}
var pool = pool.create(config);
mysql模块的封装
dbUtils.js文件内容:
var mysql = require('mysql');
var dbClient = function(pool){
/*
* 条件获取的方法。
*/
function getCnd(cnd, args, cb){
if(arguments.length > 3) return null;
if(arguments.length == 1){
return {where : " where 1 = 1 ", args : [], cb : arguments[0]}
}else if(arguments.length == 2){
var info = {args : [], cb : args};
args = cnd;
cnd = " where 1 = 1 ";
if(args){
for (var key in args) {
if(!key || key == "_orderBy" || typeof args[key] == "function") continue;
cnd += " and " + key + " = ? ";
info.args.push(args[key]);
}
var orderByArr = args._orderBy;
if(orderByArr && orderByArr.length > 0){
cnd += " order by "
for (var i = 0, li = orderByArr.length; i < li; i++) {
cnd += " " + orderByArr[i] + " ";
if(i < li -1) cnd += ","
}
}
}
info.where = cnd;
return info;
}
cnd = cnd || "";
if(cnd.trim() != "" && cnd.search(/^[\s]*where[\s]/) != 0){
cnd = " where " + cnd
}
return {where : cnd, args : args, cb : cb};
};
/*
* 请求执行。
*/
function query(sql, args, cb){
var arr = Array.prototype.slice.apply(arguments);
var cb = arr.pop();
pool.getConnection(function(err, connection) {
if(err) return cb(err);
arr.push(function(err, results){
connection.release();
err ? cb(err) : cb(null, results);
});
connection.query.apply(connection, arr);
});
};
/*
* 获取单条数据操作。
*/
function select(tableName, cnd, args, cb){
var cnd = getCnd.apply(this, Array.prototype.slice.call(arguments, 1));
var strSql = "select * from " + tableName + cnd.where +" limit 1";
this.query(strSql, cnd.args, function (err, result) {
err ? cnd.cb(err, null) : cnd.cb(null, result.length > 0 ? result[0] : null);
});
};
this.query = query;
this.select = select;
}
exports.dbClient = dbClient;
使用方法:
var dbUtils = require('./dbUtils');
var dbClient = new dbUtils.dbClient(pool);
//选择单条数据
dbClient.select('test', 'id' ,'1' ,function(err, res){
console.log(res);
})
dbClient.select('test', {'id': '2','title': 'b'}, function(err, res){
console.log(res);
})
另附带百万测试数据以及更多的mysql使用方法的封装,包含mysql数据库的增删改查以及测试方法。
https://download.csdn.net/download/mdsdnw/12693804