Express与MySQL直接的连接与数据流

这几天懒，双拼用得也不溜，加之还在弄别的，就攒了很多要写的，喜闻乐见。

总体上讲，架构是用了Angular+Express+MySQL这个技术栈，下面主要是讲Express连接MySQL的部分，已经怎样调用MySQL存储过程。

数据库连接池用的是Generic Pool，github上的地址是这里，大概看了下，应该包含的不仅仅是指连接MySQL的，只要用适当的驱动替换，应该可以充当大部分数据库的连接池，时间紧，没具体试，不敢乱说。

安装方法如下：

https://github.com/coopernurse/node-pool/blob/master/README.md

原始的例子并没有具体讲哪一个数据库产品，所以在具体用的时候需要将里面调用数据库驱动部分的语句(参照version3的generic-pool问题)替换成你所用的数据库产品的接口，我用的MySQL的语句如下：

var genericPool = require('generic-pool');
var DbDriver = require('mysql');
var settings = require('./settings');

/**
 * Step 1 - Create pool using a factory object
 */
const factory = {
    create: function(){
        return new Promise(function(resolve, reject){
            var client = DbDriver.createConnection({
                host: settings.mysql.host,
                port: settings.mysql.port,
                database: settings.mysql.database,
                user: settings.mysql.user,
                password: settings.mysql.password,
                charset: settings.mysql.charset
            });
            client.connect(function (err) {
                if (err) {
                    console.log("SQL CONNECT ERROR: ", err);
                } else {
                    console.log("SQL CONNECT SUCCESSFUL.");
                    resolve(client)
                }
            });
        })
    },
    destroy: function(client){
        return new Promise(function(resolve){
            client.on('end', function(){
                resolve()
            });
            client.disconnect()
        })
    }
};

var opts = {
    max: 10, // maximum size of the pool
    min: 5 // minimum size of the pool
};

var myPool = genericPool.createPool(factory, opts);

/**
 * Step 2 - Use pool in your code to acquire/release resources
 */

// acquire connection - Promise is resolved
// once a resource becomes available
const resourcePromise = myPool.acquire();
module.exports = {
    doGetQuery:function(res, procName, handler) {
        var paraArray = [];
        var stmt = "CALL " + procName + "()";
        console.info("stmt:" + stmt);
        resourcePromise.then(function(client) {
            client.query(stmt, paraArray, function(err, rows, fields) {
                if (err) {
                    throw err;
                }
                handler(res, rows);
                //myPool.release(client);
            });
        })
            .catch(function(err){
                // handle error - this is generally a timeout or maxWaitingClients
                // error
            });
    },
    doPostQuery : function(req, res, procName, handler){
        var params = JSON.parse(req.body.params);
        var paraArray = [];
        var stmt = "CALL " + procName + "(";
        for(var param in params){
            stmt = stmt + "?, ";
            paraArray.push(params[param]);
        }
        stmt = stmt.substring(0, stmt.length - 2) + ")";
        console.info("stmt:" + stmt);
        resourcePromise.then(function(client) {
            client.query(stmt, paraArray, function(err, rows, fields) {
                if (err) {
                    throw err;
                }
                handler(res, rows);
                //myPool.release(client);
            });
        })
            .catch(function(err){
                // handle error - this is generally a timeout or maxWaitingClients
                // error
            });
    }
};

/**
 * Step 3 - Drain pool during shutdown (optional)
 */
// Only call this once in your application -- at the point you want
// to shutdown and stop using this pool.
myPool.drain().then(function() {
    myPool.clear();
});

代码大致一样，改的只是数据库产品的驱动连接的部分。那两个导出函数分别对应的是前端的POST提交和GET提交的与数据库的交互。至于MySQL存储过程参数传递的问题，我是拼了输入，然后输出不管是参数还是结果集，全部返回结果集。