Express (Node) 与 Oracle DB 做接口—池

oracle.conf.js

module.exports = {
    oracledb: {
        queueRequests: true,
        autoCommit: false,
        poolIncrement: 1,
        poolMax: 4,
        poolMin: 0,
        poolPingInterval: 60,
        poolTimeout: 60,
        queueTimeout: 0
    },
    poolAttrs: {
        connectionString: 'ip/port/orcl',
        user: '***',
        password: '***',
        poolIncrement: 1,
        homogeneous: true
    }
};

oracle.connector.js

const oracleConf = require('../conf/oracle.conf');
const oracledb = require('oracledb');
let pool = null;
const subDbConf = oracleConf.oracledb;
const subPoolConf = oracleConf.poolAttrs;
const formalParams = [':0', ':1', ':2', ':3', ':4', ':5', ':6', ':7',
    ':8', ':9', '10', '11', '12', '13', '14', '15'];
for (const key in subDbConf) {
    oracledb['key'] = subDbConf['key'];
}
oracledb.createPool(
    subPoolConf,
    (err, _pool) => {
        if (err) {
            console.error('ORACLE POOL INITIAL FAILED');
            throw err;
        }
        pool = _pool;
    });
const randySQLQuery = (paramsObj, res, procName, preHandler, sufHandler, totalRs) => {
    preHandler(paramsObj)
        .then(paraArray => {
            let subFormalParams = [];
            const finalLength = paraArray.length + totalRs;
            if (finalLength <= formalParams.length) {
                subFormalParams = formalParams.slice(0, finalLength);
            } else {
                subFormalParams = [...formalParams];
                for (let count = formalParams.length, len = finalLength; count < len; count++) {
                    subFormalParams.push(':' + count);
                }
            }
            const plsql = `BEGIN ${procName}(${subFormalParams.join(',')}); END;`;
            console.log(plsql);
            console.log(paraArray);
            pool.getConnection(
                (err, connection) => {
                    if (err) {
                        throw err;
                    }
                    connection.execute(plsql, paraArray.concat(Array(totalRs).fill({
                            dir: oracledb.BIND_OUT,
                            type: oracledb.CURSOR
                        })),
                        (err, result) => {
                            if (err) {
                                console.log(err);
                                throw err;
                            }
                            return sufHandler(res, result.outBinds).then(respData => {
                                res.json(respData);
                            }).then(() => {
                                connection.release(err => {
                                    err && console.log(err);
                                })
                            });
                        });
                });
        })
};

const doGetQuery = (req, res, procName, preHandler, sufHandler, totalRs) => {
    return randySQLQuery(req.query, res, procName, preHandler, sufHandler, totalRs);
};
const doPostQuery = (req, res, procName, preHandler, sufHandler, totalRs) => {
    return randySQLQuery(req.body, res, procName, preHandler, sufHandler, totalRs);
};
module.exports = {doGetQuery, doPostQuery};

req.template-oracle.js

const express = require('express');
const router = express.Router(null);
const connection = require('../../connector/oracle.connector');
router.get('/oracle-test', (req, res) => {
    const procName = "pkg_medicare_payment.prc_getpersoninfo";
    const preHandler = paraObj => {
        const paraArray = [];
        paraArray.push(paraObj['name']);
        paraArray.push(paraObj['id']);
        // Do what you want above and pass a parameter array to the next promise.
        return Promise.resolve(paraArray);
    };
    const sufHandler = (res, rs) => {
        /* The return value has to be formatted like this:
         * The outter JSON has 3 keys.
         * respResultset: An array returned from mysql, each element is a JOSN-like object.
         * respCode: Maybe a status code which you can define yourself.
         * respData: Other values you want to pass to the front end.
         * respCode is necessary.
         * respResultset and respData are optional.
         */
        return new Promise(resolve => {
            rs[1].getRows(10).then(data => {
                console.log(data);
                rs[0].getRows(10, (err, rows) => {
                    console.log(rows);
                    resolve({
                        respData: {
                            foo: 'bar'
                        },
                        respResultset: rows,
                        respCode: '123'
                    });
                })
            });
        });
    };
    connection.doGetQuery(req, res, procName, preHandler, sufHandler, 2);
});
router.post('/oracle-test', (req, res) => {
    const procName = "pkg_medicare_payment.prc_getpersoninfo";
    const preHandler = paraObj => {
        const paraArray = [];
        paraArray.push(paraObj['name']);
        paraArray.push(paraObj['id']);
        // Do what you want above and pass a parameter array to the next promise.
        return Promise.resolve(paraArray);
    };
    const sufHandler = (res, rs) => {
        /* The return value has to be formatted like this:
         * The outter JSON has 3 keys.
         * respResultset: An array returned from mysql, each element is a JOSN-like object.
         * respCode: Maybe a status code which you can define yourself.
         * respData: Other values you want to pass to the front end.
         * respCode is necessary.
         * respResultset and respData are optional.
         */
        return new Promise(resolve => {
            rs[1].getRows(10).then(data => {
                console.log(data);
                rs[0].getRows(10, (err, rows) => {
                    console.log(rows);
                    resolve({
                        respData: {
                            foo: 'bar'
                        },
                        respResultset: rows,
                        respCode: '123'
                    });
                })
            });
        });
    };
    connection.doPostQuery(req, res, procName, preHandler, sufHandler, 2);
});
module.exports = router;