准备工作:
已安装mysql 的windows版本
先安装依赖库
npm install mysql
npm install generic-pool
创建文件:
250bede596931142bef0ab31e7aa08c0.png
如图中所示建立shared文件夹,在shared文件夹下建立config文件夹,在config文件夹下创建mysql.json数据库配置文件,在shared文件夹下创建mysql.js和sqlPool.js两个脚本,脚本内容在下面。
mysql.json配置
{
"development": {
"host" : "127.0.0.1",//ip
"port" : "3306",//端口
"database" : "user",//数据库名称
"user" : "dev",//帐号
"password" : "123456"//密码
},
"production": {
"host" : "127.0.0.1",
"port" : "3306",
"database" : "user",
"user" : "dev",
"password" : "123456"
}
}
mysql.js代码
var sqlclient = module.exports;
var _pool;
var NND = {};
/*
* Init sql connection pool
* @param {Object} app The app for the server.
*/
NND.init = function(app){
_pool = require('./sqlPool').createMysqlPool(app);
};
/**
* Excute sql statement
* @param {String} sql Statement The sql need to excute.
* @param {Object} args The args for the sql.
* @param {fuction} cb Callback function.
*
*/
NND.query = function (sql, args, cb) {
const resourcePromise = _pool.acquire();
resourcePromise.then(function (client) {
client.query(sql, args, function (err, res) {
_pool.release(client);
cb(err, res);
});
}).catch(function (err) {
// handle error - this is generally a timeout or maxWaitingClients
// error
console.log("errr... query", err);
cb(err);
});
};
/**
* Close connection pool.
*/
NND.shutdown = function(){
_pool.destroyAllNow();
};
/**
* init database
*/
sqlclient.init = function(app) {
if (!!_pool){
return sqlclient;
} else {
NND.init(app);
sqlclient.insert = NND.query;
sqlclient.update = NND.query;
sqlclient.delete = NND.query;
sqlclient.query = NND.query;
return sqlclient;
}
};
/**
* shutdown database
*/
sqlclient.shutdown = function(app) {
NND.shutdown(app);
};
sqlPool.js代码
//sqlPool.js
var genericPool = require('generic-pool');
var mysql = require("mysql");
/*
* Create mysql connection pool.
*/
var createMysqlPool = function (app) {
var mysqlConfig = app.get('mysql');
const factory = {
create: function () {
return new Promise(function (resolve, reject) {
var client = mysql.createConnection({
host: mysqlConfig.host,
user: mysqlConfig.user,
password: mysqlConfig.password,
database: mysqlConfig.database
});
client.connect();
resolve(client);
});
},
destroy: function (client) {
return new Promise(function (resolve) {
client.on( function () {
resolve();
});
client.disconnect();
})
}
};
var opts = {
max: 10, // maximum size of the pool
min: 1 // minimum size of the pool
};
return genericPool.createPool(factory, opts);
};
exports.createMysqlPool = createMysqlPool;
在game-server的app.js中添加如下配置
app.loadConfig("mysql", app.getBase() + "/shared/config/mysql.json"); // 添加配置
app.configure('production|development', "connector|game", function () {
var dbclient = require("./shared/mysql.js").init(app); // 初始化dbclient
app.set("dbclient", dbclient);// dbclient 为外部数据库接口,app,get("dbclient") 来使用
})
调用方法如下
var sql = " insert into user (account, password) VALUES(?, ?)";
var args = [msg.account, msg.password];
var dbclient = pomelo.app.get('dbclient');//获取全局mysql client
dbclient.query(sql, args, function (err, res) {//执行sql语句 函数insert和query等效
next(null, {
code: 0
});
});