sql - Node.JS Oracle 补丁请求不是动态的
问题描述
我正在尝试通过 Node.JS 动态地为 oracle 表发出补丁请求
这是我的设置:
在我的 router.js 文件中,我有这个:
const express = require('express');
const router = new express.Router();
const employees = require('../controllers/employees.js');
const smiCats = require('../controllers/smi/smiCats.js');
const auth = require('../controllers/auth.js');
router.route('/login/:id?')
.post(auth.getToken);
router.route('/ams/:id?')
.get(auth.verifyToken, employees.get)
.post(auth.verifyToken, employees.post)
.put(auth.verifyToken, employees.put)
.delete(auth.verifyToken, employees.delete)
.patch(auth.verifyToken, employees.patch);
router.route('/smi/cats/:id?')
.get(auth.verifyToken, smiCats.get)
.post(auth.verifyToken, smiCats.post)
.put(auth.verifyToken, smiCats.put)
.patch(auth.verifyToken, smiCats.patch);
module.exports = router;
然后调用具有我的补丁功能的控制器并进行清理。
//sanitizer
function sanitizeCats(req) {
const cats = {
cat_desc: req.body.cat_desc,
msg_for: req.body.msg_for,
msg_user_owner: req.body.msg_user_owner || 0,
msg_realtor_owner: req.body.msg_realtor_owner || 0
};
return cats;
}
async function patch(req, res, next) {
try {
let category = sanitizeCats(req);
category.cat_id = parseInt(req.params.id, 10);
const success = await smiCats.patch(category);
if (success) {
res.status(204).end();
} else {
res.status(404).end();
}
} catch (err) {
next(err);
}
}
module.exports.patch = patch;
当它被执行时,它会调用我的 db_api 模块,该模块组装 sql 语句
(下一个代码部分是我的问题来自哪里)
const database = require('../../services/database.js');
const oracledb = require('oracledb');
const patchSql =
`BEGIN
DECLARE
BEGIN
IF nvl(:cat_desc,'zzz') != 'zzz' THEN
UPDATE smi_contact_cats
SET cat_desc = :cat_desc
WHERE cat_id = :cat_id;
END IF;
IF nvl(:msg_for,'zzz') != 'zzz' THEN
UPDATE smi_contact_cats
SET msg_for = :msg_for
WHERE cat_id = :cat_id;
END IF;
IF nvl(:msg_user_owner,-1) > -1 THEN
UPDATE smi_contact_cats
SET msg_user_owner = :msg_user_owner
WHERE cat_id = :cat_id;
END IF;
IF nvl(:msg_realtor_owner,-1) > -1 THEN
UPDATE smi_contact_cats
SET msg_realtor_owner = :msg_realtor_owner
WHERE cat_id = :cat_id;
END IF;
:rowcount := sql%rowcount;
END;
END;`;
async function patch(cats) {
const category = Object.assign({}, cats);
//add binds
category.rowcount = {
dir: oracledb.BIND_OUT,
type: oracledb.NUMBER
};
const result = await database.simpleExecute(patchSql, category);
return result.outBinds.rowcount === 1;
}
module.exports.patch = patch;
然后调用数据库函数来实际执行和组装带有绑定变量的 sql:
const oracledb = require('oracledb');
const dbConfig = require('../config/database.js');
async function initialize() {
const pool = await oracledb.createPool(dbConfig.beta);
}
module.exports.initialize = initialize;
async function close() {
await oracledb.getPool().close();
}
module.exports.close = close;
function simpleExecute(statement, binds = [], opts = {}) {
return new Promise(async (resolve, reject) => {
let conn;
opts.outFormat = oracledb.OBJECT;
opts.autoCommit = true;
try {
conn = await oracledb.getConnection();
const result = await conn.execute(statement, binds, opts);
resolve(result);
} catch (err) {
reject(err);
} finally {
if (conn) { // conn assignment worked, need to close
try {
await conn.close();
} catch (err) {
console.log(err);
}
}
}
});
}
module.exports.simpleExecute = simpleExecute;
所以所有这些都有效......但它不够动态,我无法构建我们的公司 api。如何在 Node.JS 中发出更动态的补丁请求,而无需输入每一列并在其周围放置一个 nvl 以检查它是否存在。另一方面,如果有更好的方法来动态清理,我全神贯注,但主要问题是如何更好地动态构建补丁请求。
解决方案
当前代码不是最理想的,因为每个属性都进行一次更新。这是一个更动态的解决方案......
鉴于以下情况:
create table smi_contact_cats (
cat_id number,
cat_desc varchar2(50),
msg_for varchar2(50),
msg_user_owner varchar2(50),
msg_realtor_owner varchar2(50)
);
insert into smi_contact_cats (
cat_id,
cat_desc,
msg_for,
msg_user_owner,
msg_realtor_owner
) values (
1,
'cat_desc orginal value',
'msg_for orginal value',
'msg_user_owner orginal value',
'msg_realtor_owner orginal value'
);
commit;
你可以使用这样的逻辑。updatableColumns
是可以更新的列的白名单。请注意,您可以注释和取消注释底部的某些行以测试各种输入。
const oracledb = require('oracledb');
const config = require('./db-config.js');
async function patch(cat) {
let conn;
try {
const category = Object.assign({}, cat);
const categoryProps = Object.getOwnPropertyNames(category);
const updatableColumns = ['cat_desc', 'msg_for', 'msg_user_owner'];
// Validate that the pk was passed in
if (!categoryProps.includes('cat_id')) {
throw new Error('cat_id is required');
}
// Now remove the pk col from categoryProps
categoryProps.splice(categoryProps.indexOf('cat_id'), 1);
if (categoryProps.length === 0) {
throw new Error('At least one property must be specified');
}
let sql = 'update smi_contact_cats\nset ';
for (let propIdx = 0; propIdx < categoryProps.length; propIdx++) {
// Here's the whitelist check
if (!updatableColumns.includes(categoryProps[propIdx])) {
throw new Error('Invalid "update" column');
} else {
if (propIdx > 0 && propIdx < categoryProps.length) {
sql += ',\n ';
}
sql += categoryProps[propIdx] + ' = :' + categoryProps[propIdx];
}
}
sql += '\nwhere cat_id = :cat_id';
console.log('here is the sql', sql);
conn = await oracledb.getConnection(config);
const result = await conn.execute(
sql,
category,
{
autoCommit: true
}
);
if (result.rowsAffected && result.rowsAffected === 1) {
return category;
} else {
return null;
}
} catch (err) {
console.error(err);
} finally {
if (conn) {
try {
await conn.close();
} catch (err) {
console.error(err);
}
}
}
}
const patchObj = {
cat_id: 1
};
// Comment and uncomment the following to see various dynamic statements
patchObj.cat_desc = 'cat_desc value';
patchObj.msg_for = 'msg_for value';
patchObj.msg_user_owner = 'msg_user_owner value';
// Uncomment the following line to add a column that's not whitelisted
//patchObj.msg_realtor_owner = 'msg_realtor_owner value';
patch(patchObj)
.then(function(cat) {
console.log('Updated succeeded', cat);
})
.catch(function(err) {
console.log(err);
});
推荐阅读
- gradle - Gradle 实现项目抛出异常
- javascript - Python selenium 函数 driver.execute_script() 不会在脚本中执行我的代码?
- c# - 从 Web URL 反序列化 JSON 后列表为空
- java - 微软图形 - Java;AuthorizationCodeProvider :如何获取“授权码”
- reactjs - 模拟自定义事件
- linux - 在 Redmi 7 上从 adb shell 运行 AT 命令
- node.js - ReferenceError:未定义文本
- f# - 带有 Http.fs 的 F# - 无法执行 GraphQL API
- java - Java 中 DoubleAdder 的实际用途
- node.js - 如何防止基于 Angular+Express 的离线应用程序被盗?