【node.js】node连接oracle——oracledb

oracledb为Oracle官方提供的node连接Oracle的第三方包，github：https://github.com/oracle/node-oracledb，oracle-api：https://oracle.github.io/node-oracledb/doc/api.html

一、安装oracledb

yarn add oracledb
cnpm/npm i oracledb -D

二、使用oracledb

首先需要引入oracledb

const oracledb = require('oracledb')

定义数据库用户名、密码、地址等信息

const DB_CONFIG = {
  user: '123', 
  password: '123',
  connectString: '127.0.0.1:1521/orcl' // 数据库地址：{IP:PORT/数据库名称}
}

开始连接数据库，其中可通过连接池或直接进行连接，下面是两种方式的具体实现：

// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
      result = await connection.execute(`SELECT * FROM SYS_USER`);
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    throw err;
  } finally {
    await pool.close();
  }
}
// 直接连接
async function run() {
  let connection;
    try {
      connection = await pool.getConnection(DB_CONFIG);
      result = await connection.execute(`SELECT * FROM SYS_USER`);
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
}
run()

  其中connection.execute(sql,params,options)是用来执行sql的方法，它可以接受三个参数sql,params,options：sql是需要执行的sql语句，params用来设置sql中一些绑定属性或是返回值，options控制执行的sql的可选参数，如autoCommit是否自动提交事务。

2.1params

  params中的常用属性有dir,maxArraySize,maxSize,type,val：

	描述
dir	绑定的方向，可选值：oracledb.BIND_IN，oracledb.BIND_INOUT，oracledb.BIND_OUT 。oracledb.BIND_IN：作为变量输入；oracledb.BIND_OUT：作为变量输出。默认oracledb.BIND_IN
type	数据在oracle中的数据类型：oracledb.STRING,oracledb.NUMBER,oracledb.CLOB,oracledb.BLOB,oracledb.DATE等等
val	绑定变量的值

例：

const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_AGE = :0 and USER_NAME = :1`,
	[12, '李四']
)
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_AGE = :age and USER_NAME = :name`,
	{
		name: {dir: oracledb.BIND_IN, val: '李四', type: oracledb.NUMBER}
		age: 12
	}
)
// 模糊查询无法使用:
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_NAME like '%:name%'`, //报错
	{name: '李四'}
)
// 可使用以下方式代替或使用instr代替like，注意使用instr第二个参数如果为’‘是查不到数据的
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_NAME like '%${name}%'`
)
// instr
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE INSTR(USER_ANME, :name)>0`,
	{name: '李'}
)

2.2 options

options常用属性：

属性	描述
autoCommit: Boolean	自动提交事务
bindDefs: Object	对象定义绑定变量的类型，大小和方向
dmlRowCounts: Boolean	允许输出受每个输入数据记录影响的行数。只能为INSERT，UPDATE，DELETE或MERGE语句设置为true。
fetchInfo: Object	定义如何在JavaScript中表示查询列数据的对象

fetchInfo使用示例:

const data = connection.execute(`select * from INTERFACE_IMMUTABLE`, null,
	{fetchInfo: {'INT_RESPONSE': { type : oracledb.STRING }}} // CLOB转成字符串
)

  关于connection.execute()返回的数据结构如下(metaData返回的为字段名，sql中可使用as替换，但返回的都是大写，rows是查询的数据)：

{
    metaData: [
        {
            name: "ROLE_ID"
        },
        {
            name: "ROLE_NAME"
        }
    ],
    rows: [
        [
            10001,
            "管理员"
        ],
        [
            10002,
            "普通用户"
        ],
        [
            10003,
            "更新角色名"
        ]
    ]
}

三、封装oracledb工具类

const oracledb = require('oracledb')
const dateFormat = require('../util/date_format')

const DB_CONFIG = {
  user: '123', // 数据库用户名
  password: '123', // 数据库密码
  connectString: '127.0.0.1:1521/orcl' // 数据库地址：{IP:PORT/数据库名称}
}

var globalPool = null

class Oracle{
  /**
   * 创建连接池
   * @return {Promise<*>}
   */
  async createPool() {
    let pool;
    try {
      pool = await oracledb.createPool(DB_CONFIG);
      globalPool = pool;
    } catch(e) {
      globalPool = null
      throw e;
    }
    return pool;
  }
  
  /**
   * 创建连接
   * @return {Promise<void>}
   */
  async createConnection(pool) {
    let connection;
    try {
      connection = await pool.getConnection();
    } catch (e) {
      throw e;
    }
    return connection;
  }
  
  /**
   * 查询
   * @param sql
   * @param mapping
   * @param options
   * @return {Promise<void>}
   */
  query (sql, mapping, params = {}, options = {}) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try {
        pool = await this.createPool();
        let connection;
        try {
          connection = await this.createConnection(pool);
          const result = await connection.execute(sql, params, options)
          // 转换mapping
          const keys = Object.keys(mapping)
          const values = Object.values(mapping)
          // map: {数据库列名：返回字段名}
          const map = values.reduce((pre, curr, index) => {
            pre.set(curr.name, {name: keys[index], type: values[index].type})
            return pre
          }, new Map())
          resolve(result.rows.map(item => result.metaData.reduce((pre, key, index) => {
            let value = map.get(key.name)
            let curr = item[index]
            // 类型转换
            if (value) {
              if (value.type === oracledb.DATE) {
                curr && (curr = dateFormat(curr))
              } else if (value.type === oracledb.NUMBER){
                curr && (curr = Number(curr))
              }
              // 无字段名与数据库对应
              if (value.name === undefined) {
                pre[key.name] = curr
              } else {
                pre[value.name] = curr
              }
            } else {  // 无字段名与数据库对应
              let {name} = key
              name = name.toLowerCase()
              // 下划线转驼峰
              name = name.replace(/\_(\w)/g, function(all, letter){
                return letter.toUpperCase()
              })
              pre[name] = curr
            }
            return pre
          }, {})));
        } catch (e) {
          throw e;
        } finally {
          if (connection) {
            try {
              await connection.close();
            } catch (e) {
              throw e
            }
          }
        }
      } catch (e) {
        reject(e);
        throw e;
      } finally {
        await pool.close();
      }
    });
  }
  
  /**
   * 插入操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  insert (sql, params = {}, options = {autoCommit: true}) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try{
        pool = await this.createPool();
        let connection;
        try {
          connection = await this.createConnection(pool);
          const result = await connection.execute(sql, params, options);
          resolve(result);
        } catch (e) {
          throw e;
        } finally {
          if (connection) {
            try {
              await connection.close();
            } catch (e) {
              throw e;
            }
          }
        }
      } catch (e) {
        reject(e);
        throw e;
      } finally {
        await pool.close();
      }
    });
  }
  
  /**
   * 更新操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  update (sql, params = {}, options = {autoCommit: true}) {
   // ...同新增
  }
  
  /**
   * 删除操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  delete (sql, params = {}, options = {autoCommit: true}) {
    // ... 同新增
  }
  
  /**
   * 多sql事务控制
   * @param sqlParams [{sql, params, options, multi}] multi:是否执行多次
   * @return {Promise<[]>}
   */
  oracleTrans(sqlParams) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try {
        pool = await this.createPool()
        let connection
        try {
          connection = await this.createConnection(pool)
          let results = []
          for (let i = 0, length = sqlParams.length; i < length; i++) {
            const {sql, params, multi = false, options = {}} = sqlParams[i]
            if (multi) results.push(await connection.executeMany(sql, params, options))
            else results.push(await connection.execute(sql, params, options))
          }
          // 提交事务
          await connection.commit()
          resolve(results)
        }catch (e) {
          // 事务回滚
          if (connection) {
            try {
              await connection.rollback()
              throw e
            } catch (e) {
              throw e
            }
          }
        } finally {
          if (connection) {
            try {
              await connection.close()
            } catch (e) {
              throw e
            }
          }
        }
      }catch (e) {
        reject(e)
      } finally {
        await pool.close()
      }
    })
  }
  
  /**
   * sql执行多次
   * @param sql
   * @param binds []
   * @param options
   */
  multiSql (sql, binds = [], options) {
    return new Promise(async (resolve, reject) => {
      let pool
      try {
        pool = await this.createPool()
        let connection
        try {
          connection = await this.createConnection(pool)
          const data = await connection.executeMany(sql, binds, options).catch(async e => {
            if (connection) {
              await connection.rollback()
              throw e
            }
          })
          // 提交事务
          await connection.commit()
          resolve(data)
        } catch (e) {
          throw e
        } finally {
          if (connection) {
            try {
              await connection.close()
            } catch (e) {
              throw e
            }
          }
        }
      } catch (e) {
        throw e
      } finally {
        await pool.close()
      }
    })
  }
}

const oracle = new Oracle();
module.exports = {
  async query(sql, mapping, params, options) {
    return await oracle.query(sql, mapping, params, options);
  },
  async insert (sql, params, options) {
    return await oracle.insert(sql, params, options);
  },
  async update (sql, params, options) {
    return await oracle.update(sql, params, options);
  },
  async delete (sql, params, options) {
    return await oracle.delete(sql, params, options);
  },
  async oracleTrans (sqlParams) {
    return await oracle.oracleTrans(sqlParams)
  },
  async multiSql (sql, binds, options) {
    return await oracle.multiSql(sql, binds, options)
  },
  async pageSql(sql, page, pageNum, mapping, params, options) {
    const newParams = {...params,
      maxRow:page * pageNum,
      minRow:(page - 1) * pageNum + 1}
    return await oracle.query(
      `SELECT * FROM (SELECT a.*, ROWNUM ROW_ID
        FROM (${sql}) a
        WHERE ROWNUM <= :maxRow)
        WHERE ROW_ID >= :minRow`,
      mapping,
      newParams,
      options
    )
  }
}

  上方工具包含了常用的CRUD操作及事务、分页、一个sql执行多次等操作。上方中出现的mapping为js中的变量与数据库中字段的映射，其数据类型如下：

const oracledb = require('oracledb')
// 角色信息映射
const ROLE_MAPPING = {
  roleId: {
    type: oracledb.NUMBER,
    name: 'ROLE_ID'
  },
  roleName: {
    type: oracledb.STRING,
    name: 'ROLE_NAME'
  },
  roleDesc: {
    type: oracledb.STRING,
    name: 'ROLE_DESC'
  }
}

module.exports = ROLE_MAPPING

使用方法：

const db = require('../database/index')
...
const data = await db.pageSql(`select ROlE_ID,ROLE_NAME, ROLE_DESC from SYS_ROLE ${searchSql} order by ROLE_ID`,
      pageNo, pageSize, ROLE_MAPPING, sqlParams)
      .catch(e => {throw e})
console.log(data)
// 返回的data结构
[
      {
        "roleId": 10001,
        "roleName": "管理员",
        "roleDesc": null,
        "rowId": 1
      },
      {
        "roleId": 10002,
        "roleName": "普通用户",
        "roleDesc": null,
        "rowId": 2
      },
      {
        "roleId": 10003,
        "roleName": "更新角色名",
        "roleDesc": "更新角色描述信息",
        "rowId": 3
      }
    ]