芙芙 | 学习笔记

Appearance

Windows 2008 Node连接Oracle11gR2数据库 ​

1. (必须)安装 Visual Studio 2013 ​

下载站

  • 安装指定版本Visual Studio 2013 (VC++ 12.0)（不再支持）

Win2008 最高支持instantclient_18,对应是 Visual Studio 2013 (VC++ 12.0)（不再支持）

2. （必须）设置OCI驱动 ​

下载链接

  • 编辑环境变量

OCI_LIB_DIR D:\environment\oracle\instantclient_18_5

OCI_INC_DIR D:\environment\oracle\instantclient_18_5\sdk\include

OCI_VERSION 18

path 新增 %OCI_LIB_DIR% %OCI_INC_DIR%

3. 安装包 ​

"oracledb": "^4.2.0"

安装指定版本oracledb npm i oracledb@4.2.0

版本太高太低连不了

最小参考示例 ​

javascript
// 导入node-oracledb模块
const oracledb = require('oracledb');

// 设置 Oracle 数据库连接信息
const dbConfig = {
    user: 'prpln',
    password: 'PLN123',
    connectString: '192.168.18.190:1521/orcl' // 例如：localhost:1521/your_service_name
};

(async () => {
    try {
        await oracledb.createPool({
            _enableStats: true,
            user: '',
            password: '',
            connectString: '192.168.18.190:1521/orcl',
            poolAlias: "prplntt1"
        });
        var connection = await oracledb.getPool('prplntt1').getConnection();
        var q1 = 'select * from pazs_receive.ew_pollution'
        // var q1 = 'select * from ew_pollution'
        var result = await connection.execute(q1);

        
        await connection.close();
        console.log(result)
    } catch (err) {
        console.log(err.message)
    }
})();

单连接池示例 ​

单连接池完整示例
js
import oracledb from 'oracledb'

// 配置信息（建议抽到 config.js 或 .env 中）
const dbConfig = {
    user: 'prpln',
    password: 'PLN123',
    connectString: '192.168.18.190:1521/orcl',
    poolAlias: 'prplntt1',
    poolMin: 2,
    poolMax: 10,
    poolIncrement: 1
};

/**
 * Oracle 客户端封装类
 */
class OracleClient {
    constructor(config = dbConfig) {
        this.config = config;
        this.isPoolInitialized = false;
    }

    /**
     * 初始化连接池（只执行一次）
     */
    async initPool() {
        if (this.isPoolInitialized) return;

        try {
            await oracledb.createPool({
                user: this.config.user,
                password: this.config.password,
                connectString: this.config.connectString,
                poolAlias: this.config.poolAlias,
                poolMin: this.config.poolMin,
                poolMax: this.config.poolMax,
                poolIncrement: this.config.poolIncrement,
                _enableStats: true
            });
            this.isPoolInitialized = true;
            console.log('✅ Oracle 连接池已初始化');
        } catch (err) {
            console.error('❌ 初始化连接池失败:', err.message);
            throw err;
        }
    }

    /**
     * 获取连接
     */
    async getConnection() {
        if (!this.isPoolInitialized) {
            await this.initPool();
        }

        let connection;
        try {
            connection = await oracledb.getPool(this.config.poolAlias).getConnection();
            return connection;
        } catch (err) {
            console.error('❌ 获取数据库连接失败:', err.message);
            throw err;
        }
    }

    /**
     * 执行 SQL 查询
     * @param {string} sql SQL语句
     * @param {object} binds 参数绑定对象
     * @returns {Promise<object>} 查询结果
     */
    async query(sql, binds = {}) {
        const connection = await this.getConnection();

        try {
            const result = await connection.execute(sql, binds, {
                outFormat: oracledb.OUT_FORMAT_OBJECT
            });
            return result.rows;
        } catch (err) {
            console.error('❌ 查询失败:', err.message);
            throw err;
        } finally {
            await connection.close();
        }
    }

    /**
     * 插入数据
     * @param {string} sql SQL语句（如 INSERT INTO ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async insert(sql, binds = {}) {
        return this._executeDML(sql, binds);
    }

    /**
     * 更新数据
     * @param {string} sql SQL语句（如 UPDATE ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async update(sql, binds = {}) {
        return this._executeDML(sql, binds);
    }

    /**
     * 删除数据
     * @param {string} sql SQL语句（如 DELETE FROM ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async delete(sql, binds = {}) {
        return this._executeDML(sql, binds);
    }

    /**
     * 执行 DML 操作（insert/update/delete）
     */
    async _executeDML(sql, binds = {}) {
        const connection = await this.getConnection();

        try {
            const result = await connection.execute(sql, binds, {
                autoCommit: true
            });
            return result.rowsAffected;
        } catch (err) {
            console.error('❌ DML 操作失败:', err.message);
            throw err;
        } finally {
            await connection.close();
        }
    }

    /**
     * 关闭连接池（用于服务关闭时）
     */
    async closePool() {
        if (this.isPoolInitialized) {
            await oracledb.getPool(this.config.poolAlias).close();
            this.isPoolInitialized = false;
            console.log('🔌 Oracle 连接池已关闭');
        }
    }
}

// 单例模式导出（确保整个应用共享一个连接池）
const oracleClient = new OracleClient();
// module.exports = oracleClient;
export default oracleClient

使用方式

js
const oracleClient = require('../db/oracleClient');

(async () => {
    try {
        // 查询示例
        const rows = await oracleClient.query('SELECT * FROM ew_pollution');
        console.log('查询结果:', rows);

        // 插入示例
        // const count = await oracleClient.insert(
        //     `INSERT INTO my_table (id, name) VALUES (:id, :name)`,
        //     { id: 1, name: '测试' }
        // );
        // console.log(`${count} 条记录插入成功`);

    } catch (err) {
        console.error('数据库操作异常:', err.message);
    }
})();

多连接池示例 ​

多连接池完整示例
js
import oracledb from 'oracledb'

const dbConfig = {
    main: {
        user: '',
        password: '',
        connectString: '172.16.2.86:1521/orcl',
        poolAlias: 'main',
        poolMin: 2,
        poolMax: 10,
        poolIncrement: 1
    },
    c1: {
        user: '',
        password: '',
        connectString: '172.16.2.81:1521/orcl',
        poolAlias: 'c1',
        poolMin: 2,
        poolMax: 10,
        poolIncrement: 1
    }
};

/**
 * Oracle 客户端封装类
 */
class OracleClient {
    constructor(config = dbConfig) {
        // 连接池配置信息
        this.poolConfigs = config;
        // 初始化连接池对象
        this.connectionPools = {};
        for (const poolName in this.poolConfigs) {
            if (Object.hasOwnProperty.call(this.poolConfigs, poolName)) {
                this.connectionPools[poolName] = null; // 初始化为空
            }
        }
        console.log('✅ Oracle 连接池已初始化');
    }

    /**
     * 获取连接
     */
    async getConnection() {
        if (!this.isPoolInitialized) {
            await this.initPool();
        }

        let connection;
        try {
            connection = await oracledb.getPool(this.config.poolAlias).getConnection();
            return connection;
        } catch (err) {
            console.error('❌ 获取数据库连接失败:', err.message);
            throw err;
        }
    }

    /**
     * 执行 SQL 查询
     * @param {string} poolName 连接池名称
     * @param {string} sql SQL语句
     * @param {object} binds 参数绑定对象
     * @returns {Promise<object>} 查询结果
     */
    async query(poolName, sql, binds = {}) {
        let connection;
        try {
            connection = await this.connect(poolName);
            const result = await connection.execute(sql, binds, {
                outFormat: oracledb.OUT_FORMAT_OBJECT
            });
            return result.rows;
        } catch (err) {
            console.error(`❌ 查询失败: "${poolName}": `, err, `\n sql：${sql} \n`);
            throw err;
        } finally {
            if (connection) {
                await this.disconnect(poolName, connection);
            }
        }
    }

    /**
     * 插入数据
     * 使用示例 const insertResult1 = await db.executeInsert('pool1', 'INSERT INTO table1 (column1, column2) VALUES (:value1, :value2)', { value1: 'new_value1', value2: 'new_value2' });
     * @param {string} poolName 
     * @param {string} sql SQL语句（如 INSERT INTO ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async insert(poolName, sql, binds = {}) {
        let connection;
        try {
            connection = await this.connect(poolName);
            const result = await connection._executeDML(sql, binds, { autoCommit: true });
            return result.rowsAffected;
        } catch (error) {
            console.error(`Error executing insert in Oracle DB pool "${poolName}": `, error);
            throw error;
        } finally {
            if (connection) {
                await this.disconnect(poolName, connection);
            }
        }
    }

    /**
     * 更新数据
     * 使用示例 const updateResult1 = await db.executeUpdate('pool1', 'UPDATE table1 SET column1 = :value1 WHERE id = :id', { value1: 'new_value', id: 123 });
     * @param {string} poolName 
     * @param {string} sql SQL语句（如 UPDATE ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async update(poolName, sql, binds = {}) {
        let connection;
        try {
            connection = await this.connect(poolName);
            const result = await connection._executeDML(sql, binds, { autoCommit: true });
            return result.rowsAffected;
        } catch (error) {
            console.error(`Error executing update in Oracle DB pool "${poolName}": `, error);
            throw error;
        } finally {
            if (connection) {
                await this.disconnect(poolName, connection);
            }
        }
    }

    /**
     * 删除数据
     * 使用示例 const deleteResult2 = await db.executeDelete('pool2', 'DELETE FROM table2 WHERE id = :id', { id: 456 });
     * @param {string} poolName 
     * @param {string} sql SQL语句（如 DELETE FROM ...）
     * @param {object} binds 参数
     * @returns {Promise<number>} 返回影响行数
     */
    async delete(poolName, sql, binds = {}) {
        let connection;
        try {
            connection = await this.connect(poolName);
            const result = await connection.execute(sql, binds, { autoCommit: true });
            return result.rowsAffected;
        } catch (error) {
            console.error(`Error executing delete in Oracle DB pool "${poolName}": `, error);
            throw error;
        } finally {
            if (connection) {
                await this.disconnect(poolName, connection);
            }
        }
    }

    /**
     * 执行 DML 操作（insert/update/delete）
     */
    async _executeDML(sql, binds = {}) {
        const connection = await this.getConnection();

        try {
            const result = await connection.execute(sql, binds, {
                autoCommit: true
            });
            return result.rowsAffected;
        } catch (err) {
            console.error('❌ DML 操作失败:', err.message);
            throw err;
        } finally {
            await connection.close();
        }
    }

    /**
     * 连接数据库
     * @param {*} poolName 
     * @returns 
     */
    async connect(poolName) {
        try {
            if (!this.connectionPools[poolName]) {
                this.connectionPools[poolName] = await oracledb.createPool(this.poolConfigs[poolName]);
            }
            const connection = await this.connectionPools[poolName].getConnection();
            return connection;
        } catch (error) {
            console.error(`Error connecting to Oracle DB pool "${poolName}": `, error);
            throw error;
        }
    }

    /**
     * 关闭连接池（用于服务关闭时）
     */
    async closePool() {
        if (this.isPoolInitialized) {
            await oracledb.getPool(this.config.poolAlias).close();
            this.isPoolInitialized = false;
            console.log('🔌 Oracle 连接池已关闭');
        }
    }

    /**
     * 关闭连接
     * @param {*} poolName 
     * @param {*} connection 
     */
    async disconnect(poolName, connection) {
        try {
            await connection.close();
            console.log('🔌 Oracle 连接池已关闭');
        } catch (error) {
            console.error(`Error disconnecting from Oracle DB pool "${poolName}": `, error);
            throw error;
        }
    }

}

// 单例模式导出
const oracleClient = new OracleClient(dbConfig);
export default oracleClient

使用方式

js
const oracleClient = require('../db/oracleClient');

(async () => {
    try {
        // 查询示例
        const rows = await oracleClient.query(`main`, 'SELECT * FROM ew_pollution', {});
        console.log('查询结果:', rows);

        // 插入示例
        // const count = await oracleClient.insert(
        //     `INSERT INTO my_table (id, name) VALUES (:id, :name)`,
        //     { id: 1, name: '测试' }
        // );
        // console.log(`${count} 条记录插入成功`);

    } catch (err) {
        console.error('数据库操作异常:', err.message);
    }
})();

Copyright © 2025 芙芙笔记 | Powered by 涩涩