亻中阝日

NodeJs MySql CURD
•发布于 •作者 liuzy •979 次浏览 •最后一次编辑是 •来自资讯

# 实现NodeJs下，对MySql数据库的操作封装：

- 一个表.一个方法(Js对象, 回调函数);

```js
ATable.aQuery({
    key1: value1,
    key2: value2
}, function(data) {
    // todo...
});
```

- 对传入Js对象的有效值判断
```js
var params = {
    id: 1,
    name: 'liuzy',
    age: 28,
    obj: { k: 'v' }, // No
    arr: [1, 2], // No
    stat1: true, // No
    stat2: 'true',
    stat3: false, // No
    stat4: 'false',
    empty: '',
    kong: 'null', // No
    null: null, // No
    un: undefined, // No
    ud: 'undefined' // No
};
```
- 把Js对象变成CURD操作SQL语句

```js
SQL.insertSql('users', params);
SQL.insertPms(params);
SQL.replaceSql('users', params);
SQL.replacePms(params);
SQL.updatebyidSql('users', params);
SQL.updatebyidPms(params);
SQL.updatebyidSql('users', params, 'name');
SQL.updatebyidPms(params, 'name');
SQL.deletebyidSql('users', params);
SQL.deletebyidPms(params);
SQL.deletebyidSql('users', params, 'name');
SQL.deletebyidPms(params, 'name');
SQL.findbyidSql('users', params);
SQL.findbyidPms(params);
SQL.findbyidSql('users', params, 'name');
SQL.findbyidPms(params, 'name');
```

### 代码详情 db.js table.js query.js sql.js pms.js

- DB 在此文件中写你的Table即可

```js
var Table = require('./table');
var Query = require('./query');
var SQL = require('./sql');
var PMS = require('./pms');

var DB = {};

DB.Users = new Table('users', {
    youquery: function(params, callback) {
        var sql = 'you sql...';
        var pms = [params.key];
        Query(sql, pms, callback);
    }
});

module.exports = DB;
```

- Query

```js
var mysql = require('mysql');
var pool = mysql.createPool(require('../config').db);

function datetime() {
    var d = new Date();
    return [d.getFullYear(), '-',
        ('0' + (d.getMonth() + 1)).slice(-2), '-',
        ('0' + d.getDate()).slice(-2), ' ',
        ('0' + d.getHours()).slice(-2), ':',
        ('0' + d.getMinutes()).slice(-2), ':',
        ('0' + d.getSeconds()).slice(-2)
    ].join('');
}

function log() {
    var args = [datetime()];
    for (k in arguments) {
        args.push(arguments[k]);
    }
    console.log(args.join(' '));
}

module.exports = Query;
```

- Table

```js
var Query = require('./query');
var SQL = require('./sql');

module.exports = Table;
```

- SQL

```js
var PMS = require('./pms');
var SQL = {};

/* ******** 插入和替换插入 ******** */

SQL.insertSql = function(table, params) {
    var keys = PMS.keys(params);
    var qs = [];
    for (i in keys) {
        qs.push('?');
    }
    return 'INSERT INTO `' + table + '`(`' + keys.join('`,`') + '`) VALUES(' + qs.join(',') + ')';
}

SQL.insertPms = function(params) {
    return PMS.values(params);
}

SQL.replaceSql = function(table, params) {
    var keys = PMS.keys(params);
    var qs = [];
    for (i in keys) {
        qs.push('?');
    }
    return 'REPLACE INTO `' + table + '`(`' + keys.join('`,`') + '`) VALUES(' + qs.join(',') + ')';
}

SQL.replacePms = function(params) {
    return PMS.values(params);
}

/* ******** 按ID更新 ******** */

SQL.updatebyidSql = function(table, params, id) {
    id = id || 'id';
    if (PMS.isValue(params[id])) {
        var temp = [];
        for (key in params) {
            if (key != id) {
                if (PMS.isValue(params[key])) {
                    temp.push('`' + key + '`=?');
                }
            }
        }
        return 'UPDATE `' + table + '` SET ' + temp.join(', ') + ' WHERE `' + id + '`=?';
    } else {
        throw new Error('params not found ' + id + ' or value is invalid !');
    }
}

SQL.updatebyidPms = function(params, id) {
        id = id || 'id';
        var pms = [];
        for (key in params) {
            if (key != id) {
                if (PMS.isValue(params[key])) {
                    pms.push(params[key]);
                }
            }
        }
        pms.push(params.id);
        return pms;
    }
    /* ******** 按ID删除 ******** */

SQL.deletebyidSql = function(table, params, id) {
    id = id || 'id';
    if (PMS.isValue(params[id])) {
        return 'DELETE FROM `' + table + '` WHERE `' + id + '`=?';
    } else {
        throw new Error('params not found ' + id + ' or value is invalid !');
    }
}

SQL.deletebyidPms = function(params, id) {
    id = id || 'id';
    return [params[id]];
}

/* ******** 按ID查询 ******** */

SQL.findbyidSql = function(table, params, id) {
    id = id || 'id';
    if (PMS.isValue(params[id])) {
        return 'SELECT * FROM `' + table + '` WHERE `' + id + '`=?';
    } else {
        throw new Error('params not found ' + id + ' or value is invalid !');
    }
}

SQL.findbyidPms = function(params, id) {
    id = id || 'id';
    return [params[id]];
}

module.exports = SQL;

/* 测试 */
var params = {
    id: 1,
    name: 'liuzy',
    age: 28,
    obj: { k: 'v' }, // No
    arr: [1, 2], // No
    stat1: true, // No
    stat2: 'true',
    stat3: false, // No
    stat4: 'false',
    empty: '',
    kong: 'null', // No
    null: null, // No
    un: undefined, // No
    ud: 'undefined' // No
};
// 增
console.log(SQL.insertSql('users', params));
console.log(SQL.insertPms(params));
console.log(SQL.replaceSql('users', params));
console.log(SQL.replacePms(params));
// 改
console.log(SQL.updatebyidSql('users', params));
console.log(SQL.updatebyidPms(params));
console.log(SQL.updatebyidSql('users', params, 'name'));
console.log(SQL.updatebyidPms(params, 'name'));
// 删
console.log(SQL.deletebyidSql('users', params));
console.log(SQL.deletebyidPms(params));
console.log(SQL.deletebyidSql('users', params, 'name'));
console.log(SQL.deletebyidPms(params, 'name'));
// 查
console.log(SQL.findbyidSql('users', params));
console.log(SQL.findbyidPms(params));
console.log(SQL.findbyidSql('users', params, 'name'));
console.log(SQL.findbyidPms(params, 'name'));
```

- PMS

```js
var PMS = {};

/* ******** 是否有效值 ******** */

PMS.isValue = function(value) {
    return (typeof value === 'string' || typeof value === 'number') && value !== null && value !== undefined && value != 'null' && value != 'undefined';
}

/* ******** 参数中有效的列名 ******** */

PMS.keys = function(params) {
    var keys = [];
    for (key in params) {
        if (PMS.isValue(params[key])) {
            keys.push(key);
        }
    }
    return keys;
}

/* ******** 参数中有效的列值 ******** */

PMS.values = function(params) {
    var values = [];
    for (key in params) {
        if (PMS.isValue(params[key])) {
            values.push(params[key]);
        }
    }
    return values;
}

module.exports = PMS;
```

0 回复

作者

liuzy

积分: 823

“ 黑眼圈圈男 ”

无人回复话题

SonarQube 9.4 + PostgreSQL

shell倒计时

日常网络巧技

使用ssh创建socks5代理服务

NodeJS集群demo

作者其他话题

SonarQube 9.4 + PostgreSQL

shell倒计时

日常网络巧技

使用ssh创建socks5代理服务

NodeJS集群demo