nodejs之mysql连接池

参考：http://www.oschina.net/code/snippet_95289_25000      github地址： https://github.com/obullxl/osnode-site

1、配置config.json

{
    "dbhost": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "db": "nodejs",
    "maxConnLimit": 60
}

2、db.js

/**
 * 数据库模块
 */
var config = require("../config");

var options = {
    'host': config.dbhost,
    'port': config.port,
    'user': config.user,
    'password': config.password,
    'database': config.db,
    //'charset': config.charset,
    'connectionLimit': config.maxConnLimit,
    'supportBigNumbers': true,
    'bigNumberStrings': true
};

var mysql = require('mysql');
var pool = mysql.createPool(options);

/**
 * 释放数据库连接
 */
exports.release = function(connection) {
    connection.end(function(error) {
        console.log('Connection closed');
    });
};

/**
 * 执行查询
 */
exports.execQuery = function(options) {
    pool.getConnection(function(error, connection) {
        if(error) {
            console.log('DB-获取数据库连接异常！');
            throw error;
        }

        /*
         * connection.query('USE ' + config.db, function(error, results) { if(error) { console.log('DB-选择数据库异常！'); connection.end(); throw error; } });
         */

        // 查询参数
        var sql = options['sql'];
        var args = options['args'];
        var handler = options['handler'];

        // 执行查询
        if(!args) {
            var query = connection.query(sql, function(error, results) {
                if(error) {
                    console.log('DB-执行查询语句异常！');
                    throw error;
                }

                // 处理结果
                handler(results);
            });

            console.log(query.sql);
        } else {
            var query = connection.query(sql, args, function(error, results) {
                if(error) {
                    console.log('DB-执行查询语句异常！');
                    throw error;
                }

                // 处理结果
                handler(results);
            });

            console.log(query.sql);
        }

        // 返回连接池
        connection.release(function(error) {
            if(error) {
                console.log('DB-关闭数据库连接异常！');
                throw error;
            }
        });
    });
};

3、测试：

var db = require("./db");

var options = {
    'sql':'SELECT * FROM test',
    'handler':function(results) {
        var data = '';
        for (var i=0; i<results.length; i++) {
            var firstResult = results[i];
            data += 'id: ' + firstResult['id']+'text: ' + firstResult['text'];
        }
        console.log(data);
    }
}

db.execQuery(options);