pomelo链接mysql

本文基于上一篇博文（pomelo新建一个服务类型）

需求：玩家购买物品够添加到数据库中，以保存数据

准备工作：

已安装mysql 的windows版本

先安装依赖库
npm install mysql
npm install generic-pool

在app目录下新建dao/mysql/目录，在该目录下新建2个文件

新建文件dao-pool.js
var _poolModule = require('generic-pool');

/*
 * Create mysql connection pool.
 */
var createMysqlPool = function(app) {
	var mysqlConfig = app.get('mysql');
	return _poolModule.Pool({
		name: 'mysql',
		create: function(callback) {
			var mysql = require('mysql');
			var client = mysql.createConnection({
				host: mysqlConfig.host,
				user: mysqlConfig.user,
				password: mysqlConfig.password,
				database: mysqlConfig.database
			});
			callback(null, client);
		},
		destroy: function(client) {
			client.end();
		},
		max: 10,
		idleTimeoutMillis : 30000,
		log : false
	});
};

exports.createMysqlPool = createMysqlPool;




新建文件mysql.js
// mysql CRUD
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('./dao-pool').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){
	_pool.acquire(function(err, client) {
		if (!!err) {
			console.error('[sqlqueryErr] '+err.stack);
			return;
		}
		client.query(sql, args, function(err, res) {
			_pool.release(client);
			cb(err, res);
		});
	});
};

/**
 * 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);
};

新建配置文件 

新建文件mysql.json
{
	"development": {
	  "host" : "127.0.0.1",
		"port" : "3306",
		"database" : "pomelo",
		"user" : "root",
		"password" : ""
	},

	"production": {
	  "host" : "127.0.0.1",
		"port" : "3306",
		"database" : "pomelo",
		"user" : "root",
		"password" : ""
	}
}

准备工作完毕。/******************************************************************************/////

app.js添加以下代码

app.loadConfig("mysql", app.getBase() + "/config/mysql.json"); // 添加配置
app.configure('production|development', "game", function () {
    var dbclient = require("./app/dao/mysql/mysql.js").init(app); // 初始化dbclient
    app.set("dbclient", dbclient);// dbclient 为外部数据库接口，app,get("dbclient") 来使用
})

修改gameHandler.js的购买接口  buyGoods  函数

handler.buyGoods=function(msg,session,next){
	var id=msg.id;
    var count = msg.count;
    
    ///////////////////////////////

	console.log("购买物品玩家 uid:" + session.uid);

    if (id == "100" && count == 1) {//验证购买条件
        
        //允许购买
        var sql = " insert into `goods` (`id`, `owner`) VALUES(?, ?)";
        
        var args = [id, session.uid];
        var dbclient = pomelo.app.get('dbclient');//获取全局mysql client

        console.log(dbclient);
        dbclient.query(sql, args, function (err, res) {//执行sql语句 函数insert和query等效
              
            console.log("......................");
            console.log(err + "            " + JSON.stringify(res));
            
            
            console.log("......................");
            if (err) { // 数据库操作失败
                next(null, { msg: "购买失败，服务器错误！", code: 200 });

            }
            else {//购买成功
                next(null, { msg: "购买物品:#活血丹 成功", code: 200 });

            }
        }
    
        );

	}else{ // 不允许购买
				next(null,{msg:"你的金币不足，购买失败",code:200});
	}
}

运行测试，查看数据库

服务端日志：