nodejs操作达梦数据库的封装
在达梦官方提供的dmdb.js这个驱动的基础上，封装了一个类似sequelize应用的达梦专用orm框架
·
最近一个项目，需要在nodejs后端接入达梦数据库，在网上找了几个类似sequelize的模块，都不好用，要么存在依赖项兼容问题，要么运行不起来。最后不得不在达梦官方提供的dmdb.js这个驱动的基础上，封装了一个类似sequelize应用的达梦专用orm框架。现将代码示下，已备参考，希望也能给大家带来一些参考和启发。
myDmdb.js：
/*
* 该例程封装了达梦数据库初始化连接、表结构同步、基础数据插入等功能，
* 并且封装了插入数据，批量插入，修改数据，删除数据，数据基本查询、分页查询等基本操作。
*/
var db = require("dmdb");
exports.dmdb = (function () {
return {
pool: null,
conn: null,
config: null,
tables: null,
// 统一日志记录方法，便于做日志控制
log: function (title, msg) {
if (this.config.showLog) {
console.log(title, msg || "");
}
},
// 判断是否是数字类型
isNumber(value) {
return Object.prototype.toString.call(value) === '[object Number]';
},
// 将达梦数据库返回的数据集，转换为json数据集
getDataList: async function (result, cols, table) {
var th = this;
var re = [];
var tcols = {};
if (cols) {
cols.for(k => {
if (table[k]) {
tcols[k] = table[k];
}
});
} else {
tcols = table;
}
if (result && result.rows && result.rows.length) {
var m = result.metaData;
for(var j = 0; j < result.rows.length; j++){
r = result.rows[j];
var rc = {};
for (var i = 0; i < r.length; i++) {
var v = r[i];
var col = tcols[m[i].name];
if (col) {
var typ = col.type.toLowerCase();
if (typ == 'clob') {
if(v){
var cv = v.iLob.data;
if(!cv){
await v.iLob.loadAllData();
cv = v.iLob.data;
}
rc[m[i].name] = cv;
}else{
rc[m[i].name] = '';
}
} else if (typ == 'image') {
rc[m[i].name] = '';
} else {
rc[m[i].name] = v;
}
} else {
rc[m[i].name] = v;
}
}
re.push(rc);
}
}
return re;
},
getColumnSql: function (columns) {
var sql = '';
if (columns && columns.length > 0) {
columns.for((column) => {
sql += '"' + column + '",';
});
sql = sql.vtrim(",");
} else {
sql += " * ";
}
return sql;
},
// 拼接where条件的节点
getWhereNode: function (key, value, type) {
var th = this;
var sql = "";
var tp = type || " AND ";
if (key && value) {
if (key == "or") {
sql += " ( ";
var i = 0;
for (var ky in value) {
sql += th.getWhereNode(ky, value[ky], " OR ");
}
sql = sql.vtrim("OR ") + " ) " + tp;
} else {
if (typeof value == "object") {
if ('like' in value) {
sql += ' "' + key + '" like';
sql += " '%" + value.like + "%'" + tp;
} else if ('in' in value) {
if (value.in.length > 0) {
var vs = '';
value.in.for((v) => {
vs += "'" + v + "',";
});
sql += '"' + key + '" in (' + vs.vtrim(',') + ")" + tp;
}
} else if ('gt' in value) {
sql += '"' + key + '" > ' + value.gt + tp;
} else if ('lt' in value) {
sql += '"' + key + '" < ' + value.lt + tp;
} else if ('gte' in value) {
sql += '"' + key + '" >= ' + value.gte + tp;
} else if ('lte' in value) {
sql += '"' + key + '" <= ' + value.lte + tp;
} else if ('is' in value) {
if (value.is == 'empty') {
sql += '"' + key + '" = \'\' ' + tp;
}
if (value.is == 'null') {
sql += '"' + key + '" is null ' + tp;
}
} else if ('isnot' in value) {
if (value.is == 'empty') {
sql += '"' + key + '" != \'\' ' + tp;
}
if (value.is == 'null') {
sql += '"' + key + '" is not null ' + tp;
}
} else if ('between' in value) {
sql += '"' + key + '" BETWEEN ' + value.between[0] + "AND " + value.between[1] + tp;
} else {
var v = th.isNumber(value.value) ? value.value : "'" + value.value + "'";
sql += '"' + key + '" ' + value.type + " " + v + tp;
}
} else {
var v = th.isNumber(value) ? value : "'" + value + "'";
sql += '"' + key + '" = ' + v + tp;
}
}
}
return sql;
},
// 拼接where条件
getWhereSql: function (where) {
var th = this;
var sql = "";
if (where) {
if (typeof where == "string") {
sql += " WHERE " + where;
} else {
for (var key in where) {
sql += th.getWhereNode(key, where[key]);
}
if (sql != "") {
sql = " WHERE " + sql.vtrim("AND ");
}
}
}
return sql;
},
getOrderSql: function (order) {
var sql = '';
if (order) {
sql += " ORDER BY ";
for (var i = 0; i < order.length; i++) {
var od = order[i];
sql += '"' + od[0] + '" ' + (od.length > 1 ? od[1].toUpperCase() : "ASC") + ",";
}
sql = sql.vtrim(",");
}
return sql;
},
// 初始化，包括建立数据库连接，同步表结构，插入基础数据等操作
init: async function (config, cb) {
var th = this;
var cfg = (th.config = config.dm8);
var tbs = th.tables = cfg.tables;
// 数据库连接池
// 数据库连接池
th.pool = await db.createPool({
connectString: "dm://" + cfg.account + ":" + cfg.password + "@" + cfg.ip + ":" + cfg.port + "?autoCommit=false&loginEncrypt=false",
poolMax: cfg.pool.max,
poolMin: 1,
});
th.conn = await th.pool.getConnection();
if (cfg.asyncTable) {
for (var key in tbs) {
if (key && tbs[key]) {
var result = await th.conn.execute(`select COUNT(1) from user_objects where object_type='TABLE' AND OBJECT_NAME='` + key + `';`);
if (result.rows[0][0] == 0) {
console.log('create table ', key);
await th.createTable(
{ tbName: key, table: tbs[key].columns },
async () => {
var dt = dts[key];
if (dt) {
console.log('bcp table basedata', key);
await th.bcp(
{
tbName: key,
list: dt.list,
},
null
);
}
}
);
}
}
}
}
cb && cb();
},
// 创建表
createTable: async function (cfg, cb) {
this.log("开始创建表, tbName=" + cfg.tbName + ", struct=" + JSON.stringify(cfg.table));
var sql = 'CREATE TABLE "' + cfg.tbName + '" (';
var unique = {};
var foreignKey = [];
var primaryKey = [];
for (var key in cfg.table) {
var col = cfg.table[key];
sql += '"' + col.field + '" ' + col.type;
// if(col.autoIncrement){
// sql += ' AUTO_INCREMENT'
// }
sql += col.autoIncrement
? " NOT NULL,"
: col.primaryKey
? " NOT NULL,"
: col.allowNull || true
? " NULL,"
: " NOT NULL,";
if (col.primaryKey) {
primaryKey.push(col.field);
}
if (col.unique) {
unique[col.unique] = unique[col.unique] || [];
unique[col.unique].push(col.field);
}
if (col.relation) {
foreignKey.push([
col.field,
col.relation.table,
col.relation.field,
]);
}
}
if (primaryKey.length > 0) {
sql += "PRIMARY KEY (";
primaryKey.forEach((a) => {
sql += '"' + a + '",';
});
sql = sql.vtrim(",") + "),";
}
for (var key in unique) {
sql += "CONSTRAINT " + key + " UNIQUE (";
unique[key].forEach((a) => {
sql += '"' + a + '",';
});
sql = sql.vtrim(",") + "),";
}
foreignKey.for((a) => {
sql += 'FOREIGN KEY ("' + a[0] + '") REFERENCES "' + a[1] + '" ("' + a[2] + '") ON DELETE CASCADE ON UPDATE CASCADE,';
});
sql = sql.vtrim(",") + ");";
try {
await this.conn.execute(sql);
await this.conn.execute("commit;");
this.log("表【" + cfg.tbName + "】, struct:【" + sql + "】, 创建完成！");
cb && cb();
} catch (err) {
this.log("Sql:" + sql + ", 创建表出错：", err.message);
}
},
// 插入数据
insert: async function (cfg, res, cb) {
var th = this;
th.log("开始插入数据, tbName=" + cfg.tbName + ", params=" + JSON.stringify(cfg.params));
var sql = 'INSERT INTO "' + cfg.tbName + '" (';
var cols = "";
var vals = "";
for (var key in cfg.params) {
cols += '"' + key + '",';
var v = cfg.params[key];
v = th.isNumber(v) ? v : "'" + v + "'";
vals += v + ",";
}
cols = cols.slice(0, -1); // 移除最后一个逗号
vals = vals.slice(0, -1); // 移除最后一个逗号
sql += cols + ") VALUES(" + vals + ");";
try {
await th.conn.execute(sql, null);
await th.conn.execute("commit;");
this.log("Sql:" + sql + ", 插入数据结束！");
if (cb) {
cb();
} else {
res && res.json({
code: 200,
data: 1,
});
}
} catch (err) {
this.log("Sql:" + sql + ", 插入数据出错：", err.message);
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
}
},
//批量插入
bcp: async function (cfg, res, cb) {
if (!cfg.tbName || !cfg.list || cfg.list.length < 1) {
if (cb) {
cb();
} else {
res && res.json({
code: 100,
message: '没有发现需要批量插入的数据',
});
}
return false;
}
var th = this;
var tb = th.tables[cfg.tbName].columns;
th.log("开始批量插入数据, tbName=" + cfg.tbName + ", list=", cfg.list);
try {
var dt = cfg.list[0];
var sql = 'INSERT INTO "' + cfg.tbName + '" (';
for (var key in dt) {
sql += '"' + key + '",';
}
sql = sql.vtrim(",") + ") VALUES ";
cfg.list.for((a) => {
sql += "(";
for (var key in a) {
var v = a[key];
var c = tb[key];
if (c && (c.type == "int" || c.type == "INT")) {
sql += ((!v && v != 0) ? 'null' : v) + ",";
} else {
sql += "'" + v + "',";
}
}
sql = sql.vtrim(",") + "),";
});
th.log("批量插入数据, tbName=" + cfg.tbName + ", sql: ", sql);
//th.log("批量插入数据, data：", pms)
await th.conn.execute(sql.vtrim(","));
await th.conn.execute("commit;");
th.log(cfg.tbName + "批量插入数据结束！");
if (cb) {
cb();
} else {
res && res.json({
code: 200,
data: 1,
});
}
} catch (err) {
th.log(cfg.tbName + "批量插入数据出错：", err.message);
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
}
},
//修改
update: async function (cfg, res, cb) {
var th = this;
th.log("开始修改数据, tbName=" + cfg.tbName + ", params=" + JSON.stringify(cfg.params) + ", where=" + JSON.stringify(cfg.where));
var sql = 'UPDATE "' + cfg.tbName + '" SET ';
var pms = "";
for (var key in cfg.params) {
var v = cfg.params[key];
v = th.isNumber(v) ? v : "'" + v + "'";
pms += '"' + key + '" = ' + v + ",";
}
pms = pms.vtrim(",");
sql += pms + th.getWhereSql(cfg.where);
sql += ";";
try {
await th.conn.execute(sql, pms);
await th.conn.execute("commit;");
th.log("Sql:" + sql + ", 更新数据结束！");
if (cb) {
cb(true);
} else {
res && res.json({
code: 200,
data: 1,
});
}
return true;
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
th.log("Sql:" + sql + ", 更新数据出错：", err.message);
}
},
//删除
delete: async function (cfg, res, cb) {
var th = this;
th.log("开始删除数据, tbName=" + cfg.tbName + ", params=" + JSON.stringify(cfg.params));
var sql = 'DELETE FROM "' + cfg.tbName + '" ';
sql += th.getWhereSql(cfg.where);
sql += ";";
try {
await th.conn.execute(sql);
await th.conn.execute("commit;");
th.log("Sql:" + sql + ", 删除数据结束！");
if (cb) {
cb(true);
} else {
res && res.json({
code: 200,
data: 1,
});
}
return true;
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
th.log("Sql:" + sql + ", 删除数据出错：", err.message);
}
},
// 获取全表数据
getTableData: async function (tableName) {
var th = this;
var tb = this.tables[tableName];
var sql = "select * from “" + tableName + "”;";
var result = await th.conn.execute(sql);
return await th.getDataList(result, null, tb);
},
//查询数据集
queryBySql: async function (sql, res, cb) {
var th = this;
var tb = this.tables[cfg.tbName];
try {
var result = await this.conn.execute(sql, {});
this.log('result:', result);
var list = await th.getDataList(result, cfg.cols, tb);
this.log("Sql:" + sql + ", 查询数据结束！");
if (cb) {
cb(list);
} else {
res && res.json({
code: 200,
data: list,
});
}
return list;
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
this.log("Sql:" + sql + ", 查询数据出错：", err.message);
return null;
}
},
//查询数据集
query: async function (cfg, res, cb) {
var th = this;
var tb = this.tables[cfg.tbName];
this.log("query开始查询数据, tbName=" + cfg.tbName + ", columns=" + JSON.stringify(cfg.columns) + ", where=" + JSON.stringify(cfg.where) + ", order=" + JSON.stringify(cfg.order));
var pms = [];
var sql = "SELECT ";
sql += th.getColumnSql(cfg.columns);
sql += ' FROM "' + cfg.tbName + '" ';
sql += th.getWhereSql(cfg.where);
sql += th.getOrderSql(cfg.order);
if (cfg.limit) {
sql += ' LIMIT ' + cfg.limit + ';';
}
try {
var result = await this.conn.execute(sql, pms);
var list = await th.getDataList(result, cfg.cols, tb);
//this.log('result:', re);
this.log("Sql:" + sql + ", 查询数据结束！");
if (cb) {
cb(list);
} else {
res && res.json({
code: 200,
data: list,
});
}
return list;
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
this.log("Sql:" + sql + ", 查询数据出错：", err.message);
return null;
}
},
//查询分页数据
queryPage: async function (cfg, res, cb) {
var th = this;
var tb = this.tables[cfg.tbName];
var pIndex = parseInt(cfg.pageIndex || 1, 10);
var pSize = parseInt(cfg.pageSize || 10, 10);
this.log("queryPage开始查询数据, tbName=" + cfg.tbName + ", columns=" + JSON.stringify(cfg.columns) + ", where=" + JSON.stringify(cfg.where) + ", order=" + JSON.stringify(cfg.order));
var pms = [];
var csql = "SELECT count(1) as total ";
var sql = "SELECT ";
sql += th.getColumnSql(cfg.columns);
csql += ' FROM "' + cfg.tbName + '"';
sql += ' FROM "' + cfg.tbName + '"';
var wsql = th.getWhereSql(cfg.where);
csql += wsql;
sql += wsql;
sql += th.getOrderSql(cfg.order);
sql += " OFFSET " + (pIndex - 1) * pSize + " LIMIT " + pSize;
try {
var total = await th.conn.execute(csql, pms);
var result = await th.conn.execute(sql, pms);
var list = await th.getDataList(result, cfg.cols, tb);
this.log("csql:" + csql + ", 查询数据结束！");
var re = {
count: total.rows[0][0],
rows: list,
};
if (cb) {
cb(re);
} else {
res && res.json({
code: 200,
data: re,
});
}
return re;
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
th.log("Sql:" + sql + ", 查询数据出错：", err.message);
}
},
//查询单条数据
queryOne: async function (cfg, res, cb) {
var th = this;
var tb = this.tables[cfg.tbName];
th.log("queryOne开始查询数据, tbName=" + cfg.tbName + ", columns=" + JSON.stringify(cfg.columns) + ", where=" + JSON.stringify(cfg.where) + ", order=" + JSON.stringify(cfg.order));
var pms = [];
var sql = "SELECT ";
sql += th.getColumnSql(cfg.columns);
sql += ' FROM "' + cfg.tbName + '"';
sql += th.getWhereSql(cfg.where);
sql += th.getOrderSql(cfg.order);
try {
var result = await th.conn.execute(sql, pms);
var list = await th.getDataList(result, cfg.cols, tb);
th.log("Sql:" + sql + ", 查询数据结束！");
if (cb) {
cb(list[0]);
} else {
res && res.json({
code: 200,
data: list[0],
});
}
return list[0];
} catch (err) {
res && res.json({
code: 100,
message: "系统异常，请联系管理员处理。",
});
th.log("Sql:" + sql + ", 查询数据出错：", err.message);
}
},
writeLoginLog: async function (req, user, type, ip, reason) {
this.insert({
tbName: 'loginLog',
params: {
id: req.tools.getUUID(),
userID: user ? user.id : '',
address: ip,
time: (new Date().format('yyyy-MM-dd HH:mm:ss')),
type: type || '',
reason: reason || '',
status: reason ? '失败' : '成功'
}
});
},
getConn: async function (obj) {
var cfg = obj.config.srcDatabase;
try {
// 数据库连接池
obj.pool = await db.createPool({
connectString: "dm://" + cfg.account + ":" + cfg.password + "@" + cfg.ip + ":" + cfg.port + "?autoCommit=false&loginEncrypt=false",
poolMax: cfg.pool.max,
poolMin: 1,
});
obj.conn = await obj.pool.getConnection();
} catch (ex) {
obj.conn = null;
console.log('连接源数据库出错。');
}
},
asyncTable: async function (obj, cfg) {
var th = this;
th.log((new Date()).format('yyyy-MM-dd HH:mm:ss.S') + ": " + cfg.destTbName + "表同步数据开始！");
obj.conn.execute('SELECT * FROM "' + cfg.srcDbName + '"."' + cfg.srcTbName + '"', null, async (err, result) => {
if (err) {
cfg.isErr = 1;
console.log(err);
} else {
var srcDatas = await th.getDataList(result);
th.log((new Date()).format('yyyy-MM-dd HH:mm:ss.S') + ": 查找源表" + cfg.srcTbName + "数据共计" + srcDatas.length + "条！");
var lst = [];
th.query({ tbName: cfg.destTbName }, null, (destDatas) => {
th.log((new Date()).format('yyyy-MM-dd HH:mm:ss.S') + ": 查找目标表" + cfg.destTbName + "数据共计" + destDatas.length + "条！");
srcDatas.for((u) => {
var filters = destDatas.filter(m => {
return m[cfg.relation.destKey] == u[cfg.relation.srcKey]
});
if (filters.length > 0) {
dt = filters[0];
var params = {};
var isUpdate = false;
var wh = {};
wh[cfg.relation.destKey] = u[cfg.relation.srcKey];
cfg.updateCols.for(col => {
if (u[col.srcCol] && u[col.srcCol] != 'undefined' && dt[col.destCol] != u[col.srcCol]) {
params[col.destCol] = col.defaultValue ? (col.defaultValue == 'UUID' ? obj.tools.getUUID() : col.defaultValue) : u[col.srcCol];
isUpdate = true;
}
});
if (isUpdate) {
th.update({
tbName: cfg.destTbName,
params: params,
where: wh
})
}
} else {
dt = {};
cfg.insertCols.for(col => {
if (u[col.srcCol] && u[col.srcCol] != 'undefined') {
dt[col.destCol] = col.defaultValue ? (col.defaultValue == 'UUID' ? obj.tools.getUUID() : col.defaultValue) : u[col.srcCol];
}
});
lst.push(dt);
}
});
if (lst.length > 0) {
th.log((new Date()).format('yyyy-MM-dd HH:mm:ss.S') + ": 目标表" + cfg.destTbName + "不存在，需要新插入的数据共计" + lst.length + "条！");
th.bcp({
tbName: cfg.destTbName,
list: lst
})
}
th.log((new Date()).format('yyyy-MM-dd HH:mm:ss.S') + ': ' + cfg.destTbName + '表同步数据结束！');
});
}
});
}
};
})();
然后是对应的配置文件config.js：
module.exports = {
database: {
dm8: {
type: "dm8",
ip: "localhost",
port: 5236,
account: "SYSDBA",
password: "SYSDBA",
pool: {
max: 100,
idle: 30000,
acquire: 60000
},
asyncTable: true,
showLog: true
}
},
tables: {
// 单位信息
organize: {
columns: {
id: {
primaryKey: true,
type: "NVARCHAR(36)",
allowNull: false,
field: "id"
},
code: {
// 单位编码
type: "NVARCHAR(50)",
field: "code"
},
name: {
// 单位名称
type: "NVARCHAR(50)",
field: "name"
},
fullname: {
// 单位全称
type: "NVARCHAR(50)",
field: "fullname"
},
address: {
// 地址
type: "NVARCHAR(50)",
field: "address"
},
description: {
// 描述
type: "NVARCHAR(50)",
field: "description"
}
}
},
// 部门信息
dept: {
columns: {
id: {
primaryKey: true,
type: "NVARCHAR(36)",
allowNull: false,
field: "id"
},
name: {
// 部门名称
type: "NVARCHAR(50)",
field: "name"
},
organizeID: {
// 所属企业、组织
type: "NVARCHAR(36)",
field: "organizeID"
}
}
},
//#region 用户信息
user: {
columns: {
id: {
primaryKey: true,
type: "NVARCHAR(36)",
allowNull: false,
field: "id"
},
type: {
// 管理员、主管、员工、采购
type: "NVARCHAR(50)",
field: "type"
},
code: {
// 员工编码
type: "NVARCHAR(50)",
field: "code"
},
name: {
type: "NVARCHAR(50)",
field: "name"
},
nickname: {
type: "NVARCHAR(50)",
field: "nickname"
},
gender: {
type: "NVARCHAR(50)",
field: "gender"
},
phone: {
type: "NVARCHAR(50)",
field: "phone"
},
IDCard: {
type: "NVARCHAR(50)",
field: "IDCard"
},
email: {
type: "NVARCHAR(50)",
field: "email"
},
wechart: {
type: "NVARCHAR(50)",
field: "wechart"
},
address: {
type: "NVARCHAR(50)",
field: "address"
},
avatar: {
//化身、头像，专指网络头像、个人形象
type: "NVARCHAR(50)",
field: "avatar"
},
organizeID: {
// 所属单位编号
type: "NVARCHAR(36)",
field: "organizeID"
},
dept: {
// 所属部门编号
type: "NVARCHAR(36)",
field: "dept"
},
account: {
type: "NVARCHAR(50)",
field: "account"
},
password: {
type: "NVARCHAR(50)",
field: "password"
},
roleID: {
type: "NVARCHAR(36)",
field: "roleID"
},
status: {
//状态： 0：新建，1：正常，2：禁用
type: "INT",
field: "status"
}
}
},
// 角色信息
role: {
columns: {
id: {
primaryKey: true,
type: "NVARCHAR(36)",
allowNull: false,
field: "id"
},
idx: {
type: "INT",
field: "idx"
},
name: {
type: "NVARCHAR(50)",
field: "name"
},
rightIDs: {
type: "NVARCHAR(2000)",
field: "rightIDs"
},
description: {
type: "NVARCHAR(50)",
field: "description"
}
}
},
// 权限信息
right: {
columns: {
id: {
primaryKey: true,
type: "NVARCHAR(36)",
allowNull: false,
field: "id"
},
idx: {
type: "INT",
field: "idx"
},
name: {
// 权限名称
type: "NVARCHAR(50)",
field: "name",
},
description: {
// 权限说明
type: "NVARCHAR(50)",
field: "description"
}
}
}
//#endregion
},
baseData: {
organize: {
update: ["id"],
list: [
{
id: "68562d93-56b0-4510-8aad-d749ccf19716",
code: "C002",
name: "鼎盛电子",
fullname: "",
address: "",
description: ""
}
]
},
dept: {
update: ["id"],
list: [
{
id: "7875ecd2-0c4c-4ef7-a24d-d3e7b0842f34",
code: "D003",
name: "生产部",
organizeID: "855bbe02-cf9d-4871-8327-5e52a560d347"
},
{
id: "1a0c5540-f3ce-42aa-a7cd-8172391b7b55",
code: "D004",
name: "财务部",
organizeID: "855bbe02-cf9d-4871-8327-5e52a560d347"
},
{
id: "83f775ed-be0d-4d16-86cb-076932c4a83f",
code: "D006",
name: "开发部",
organizeID: "855bbe02-cf9d-4871-8327-5e52a560d347"
},
{
id: "c6c562bb-812c-4847-83e1-838ffc5202a1",
code: "D007",
name: "技术部",
organizeID: "855bbe02-cf9d-4871-8327-5e52a560d347"
}
]
},
right: {
update: ["id"],
list: [
{
id: "72c0ce85-8820-40ab-8021-aca203ece926",
idx: 1,
name: "首页",
description: "",
},
{
id: "55d53e7d-ad90-49a8-9329-240e6d1c4036",
idx: 3,
name: "业务流程",
description: "",
},
{
id: "debdf877-2570-4fd9-b729-e56e35adc0ab",
idx: 4,
name: "岗位职责",
description: "",
},
{
id: "ccb6e493-0828-49d8-ac35-a8f962585210",
idx: 5,
name: "知识服务",
description: "",
},
{
id: "8ab4358e-5538-4818-abc7-da8ab2869a13",
idx: 7,
name: "基础数据",
description: ""
},
},
role: {
update: ["id"],
list: [
{
id: "b50c902c-ded7-4d58-b654-644da1b5caf2",
idx: 1,
name: "管理员",
rightIDs:
"55d53e7d-ad90-49a8-9329-240e6d1c4036,72c0ce85-8820-40ab-8021-aca203ece926,8ab4358e-5538-4818-abc7-da8ab2869a13,ccb6e493-0828-49d8-ac35-a8f962585210,debdf877-2570-4fd9-b729-e56e35adc0ab",
description: ""
},
{
id: "3d8c27fb-f10c-4c7e-ae9f-c32bc756a159",
idx: 5,
name: "专家",
rightIDs: "ccb6e493-0828-49d8-ac35-a8f962585210,8ab4358e-5538-4818-abc7-da8ab2869a13",
description: ""
},
{
id: "6a69b225-2cef-4045-8136-3f558da0454f",
idx: 6,
name: "教师",
rightIDs:
"8ab4358e-5538-4818-abc7-da8ab2869a13",
description: ""
}
]
},
user: {
update: ["id"],
list: [
{
id: "537ea8c2-8084-4a32-9974-99cc152cce06",
code: "P00001",
type: "管理员",
name: "管理员",
roleID: "b50c902c-ded7-4d58-b654-644da1b5caf2",
account: "admin",
password: "d2h0cDEyMw==",
status: 1,
organizeID: 1
},
{
id: "6e115105-a3d5-4a76-8e0c-ea9f1004abb5",
code: "P00005",
type: "专家组",
name: "吴浩",
roleID: "3d8c27fb-f10c-4c7e-ae9f-c32bc756a159",
account: "test",
password: "d2h0cDEyMw==",
status: 1,
organizeID: 1
},
{
id: "10cc704c-708d-4790-81d9-d5a0e1f55f8b",
code: "P00006",
type: "专家组",
name: "方博",
roleID: "3d8c27fb-f10c-4c7e-ae9f-c32bc756a159",
account: "test",
password: "d2h0cDEyMw==",
status: 1,
organizeID: 3
},
{
id: "11009177-de18-4f62-9a7c-acb8d73e2270",
code: "P00007",
type: "专家组",
name: "杨月",
roleID: "3d8c27fb-f10c-4c7e-ae9f-c32bc756a159",
account: "test",
password: "d2h0cDEyMw==",
status: 1,
organizeID: 1
},
{
id: "696a6e70-3a95-4628-a11a-1bcf360daa09",
code: "P00008",
type: "专家组",
name: "何凯",
roleID: "3d8c27fb-f10c-4c7e-ae9f-c32bc756a159",
account: "test",
password: "d2h0cDEyMw==",
status: 1,
organizeID: 3
}
]
}
}
};
最后来一个使用打样：
const express = require("express");
const Router = express.Router();
/**
* 获取列表信息
* 返回：{code:200/100, data/message}
*/
Router.post('/list', (req, res) => {
var pSize = req.body.pageSize || 10;
var pIndex = req.body.pageIndex || 1;
var param = {}
if (!!req.body.name) {
param = {
name: { like: req.body.name }
};
}
if (!!req.body.keywords) {
param = {
or: [
{ name: { like: req.body.keywords } },
{ dept: { like: req.body.keywords } }
],
};
}
req.db.queryPage({
tbName: 'right',
pageIndex: pIndex * 1,
pageSize: pSize * 1,
where: param,
order: [['id']]
}, res);
});
/**
* 保存
* 返回：{code:200/100, data/message}
*/
Router.post("/save", async (req, res) => {
var { id, name, description, } = req.body;
// 使用 !id 来检查 id 是否为假值
if (!id) {
// 新增操作
id = req.tools.getUUID();
try {
const result = await req.db.insert({
tbName: 'right',
params: { id, name, description }
});
res.send({ code: 200, message: '数据提交成功', data: result });
} catch (error) {
res.status(500).send({ code: -1, message: error.message });
}
} else {
// 编辑操作
try {
const result = await req.db.update({
tbName: 'right',
params: { name, description },
where: { id }
});
res.send({ code: 200, message: '数据提交成功', data: result });
} catch (error) {
res.status(500).send({ code: -1, message: error.message });
}
}
});
/**
* 删除
* 参数：id
* 返回：{code:200/100, data/message}
*/
Router.post('/delete', (req, res) => {
var wh = {};
if(req.body.ids){
wh.id = {in: req.body.ids}
}else if(req.body.id){
wh.id = req.body.id;
}
req.db.delete({tbName: 'right', where: wh}, res);
})
module.exports = Router;
这个达梦专用orm框架目前还比较粗糙，但已经可以满足绝大部分日常业务数据的交互。后续还可以继续添加关联关系的处理、关联查询、嵌套查询等复杂功能。如果各位同学有兴趣，可以帮忙完善一下，谢谢！！
欢迎加入 MCP 技术社区！与志同道合者携手前行，一同解锁 MCP 技术的无限可能！
更多推荐
8
0- 0
已为社区贡献2条内容
所有评论(0)