Nodejs 操作 Sql Server

Nodejs 操作 Sql Server

Intro

最近项目需要爬取一些数据，数据有加密，前端的js又被混淆了，ajax请求被 hook 了，有些复杂，最后打算使用 puppeteer 来爬取数据。

Puppeteer 是谷歌团队在维护的一个项目，初衷主要是用来做网页的自动化测试， Google Chrome 团队官方的无界面（Headless）Chrome 工具，它是一个 Node 库，提供了一个高级的 API 来控制 DevTools协议上的无头版 Chrome ，也可以配置为使用完整（非无头）的 Chrome。这里就不详细介绍了，有兴趣的可以自己先行 Google 一下，之后再写一篇文章来介绍，今天主要介绍 node 操作 mssql。

node-mssql

node-mssql 是我们用来操作 Ms Sql Server 数据库用到的 npm 包，支持 promise， async/await 语法。这个包也是微软官方推荐使用的。

个人比较喜欢 async/await 语法。

基本用法：

let pool = await sql.connect(config);
// sql
let result1 = await pool.request()
            .input('input_parameter', sql.Int, value)
            .query('select * from mytable where id = @input_parameter');

// 存储过程
let result2 = await pool.request()
            .input('input_parameter', sql.Int, value)
            .output('output_parameter', sql.VarChar(50))
            .execute('procedure_name');

更多用法请参考官方文档介绍 https://www.npmjs.com/package/mssql

封装

虽然提供比较完善的方法，但是如果用起来的话还是会觉得用起来有些不舒服，没有那么流畅，没有那么简洁，于是想自己封装一层

const mssql = require("mssql");
const log4js = require("log4js");
const logger = log4js.getLogger("dbUtil");

const connConfig = {
    user: "",
    password: "",
    server: "",
    database: ".",
    connectionTimeout: 120000,
    requestTimeout: 3000000,
    retryTimes: 3,
    options: {
        encrypt: true
    },
    pool: {
        max: 1024,
        min: 1,
        idleTimeoutMillis: 30000
    }
};

mssql.on('error', err => {
    // ... error handler
    logger.error(err);
});
let connectionPool;

var getConnection = async function(){//连接数据库
    if(!(connectionPool && connectionPool.connected)) {
        connectionPool = await mssql.connect(connConfig);
    }
    return connectionPool;
}

var querySql = async function (sql, params) {//写sql语句自由查询
    await mssql.close();// close
    var pool = await getConnection();
    var request = pool.request();
    if (params) {
        for (var index in params) {
            if (typeof params[index] == "number") {
                request.input(index, mssql.Int, params[index]);
            } else if (typeof params[index] == "string") {
                request.input(index, mssql.NVarChar, params[index]);
            }
        }
    }
    var result = await request.query(sql);
    await mssql.close();// close
    return result;
};

var add = async function (addObj, tableName) {//添加数据
    if(!addObj){
        return;
    }    
    await mssql.close();// close
    var connection = await getConnection();
    var request = connection.request();

    var sql = "insert into " + tableName + "(";
    for (var index in addObj) {
        if (typeof addObj[index] == "number") {
            request.input(index, mssql.Int, addObj[index]);
        } else if (typeof addObj[index] == "string") {
            request.input(index, mssql.NVarChar, addObj[index]);
        }
        sql += index + ",";
    }
    sql = sql.substring(0, sql.length - 1) + ") values(";
    for (var index in addObj) {
        if (typeof addObj[index] == "number") {
            sql += "@" + index + ",";
        } else if (typeof addObj[index] == "string") {
            sql += "@" + index + ",";
        }
    }
    sql = sql.substring(0, sql.length - 1) + ")";

    var result = await request.query(sql);
    await mssql.close();// close
    return result;
};

var addIfNotExist = async function (addObj, whereObj, tableName) {//添加数据
    if(!addObj){
        return;
    }
    if(!whereObj){
        return await add(addObj, tableName);
    }
    await mssql.close();// close
    var connection = await getConnection();
    var request = connection.request();

    let sql = `BEGIN
    IF NOT EXISTS (SELECT 1 FROM ${tableName} WHERE 1 > 0`;

    for(var index in whereObj){
        if (typeof addObj[index] == "number") {
            request.input(index+'Where', mssql.Int, whereObj[index]);
        } else if (typeof addObj[index] == "string") {
            request.input(index+'Where', mssql.NVarChar, whereObj[index]);
        }
        sql += ` AND ${index} = @${index}Where`
    }
    sql+= ')';

    sql += 'BEGIN ';
    sql += "INSERT INTO " + tableName + "(";
    for (var index in addObj) {
        if (typeof addObj[index] == "number") {
            request.input(index, mssql.Int, addObj[index]);
        } else if (typeof addObj[index] == "string") {
            request.input(index, mssql.NVarChar, addObj[index]);
        }
        sql += index + ",";
    }
    sql = sql.substring(0, sql.length - 1) + ") values(";
    for (var index in addObj) {
        if (typeof addObj[index] == "number") {
            sql += "@" + index + ",";
        } else if (typeof addObj[index] == "string") {
            sql += "@" + index + ",";
        }
    }
    sql = sql.substring(0, sql.length - 1) + ")";

    sql += `   END
    END`;

    var result = await request.query(sql);
    await mssql.close();// close
    return result;
};


var addList = async function (addObjs, tableName) {//添加数据
    if(!addObjs || addObjs.length == 0){
        return;
    }
    await mssql.close();// close
    var connection = await getConnection();
    var sql = "INSERT INTO " + tableName + "(";
    if (addObjs) {
        let addObj = addObjs[0];
        for (var index in addObj) {
            sql += index + ",";
        }
        sql = sql.substring(0, sql.length - 1) + ") VALUES";
        addObjs.forEach(addObj => {
            sql = sql + "(";
            for (var index in addObj) {
                if (typeof addObj[index] == "number") {
                    sql += addObj[index] + ",";
                } else if (typeof addObj[index] == "string") {
                    sql += "N'" + addObj[index] + "'" + ",";
                }
            }
            sql = sql.substring(0, sql.length - 1) + "),";
        });
    }
    sql = sql.substring(0, sql.length - 1);
    // logger.info(sql);
    var result = await connection.request().query(sql);
    await mssql.close();// close
    return result;
};

var addListIfNotExist = async function (addObjs, uniqueFieldName, tableName) {//添加数据
    if(!addObjs || addObjs.length == 0){
        return;
    }
    await mssql.close();// close
    var connection = await getConnection();
    let addObj = addObjs[0];
    let sql = `
      CREATE TABLE #${tableName}Temp(
    `;

    for (var index in addObj) {
        if (typeof addObj[index] == "number") {
            sql += index + " BIGINT,";
        } else if (typeof addObj[index] == "string") {
            sql += index + " NVARCHAR(MAX),";
        }
    }
    sql = sql.substring(0, sql.length - 1) + ");";


    sql += "INSERT INTO #" + tableName + "Temp(";
    if (addObjs) {
        for (var index in addObj) {
            sql += index + ",";
        }
        sql = sql.substring(0, sql.length - 1) + ") VALUES";
        addObjs.forEach(addObj => {
            sql = sql + "(";
            for (var index in addObj) {
                if (typeof addObj[index] == "number") {
                    sql += addObj[index] + ",";
                } else if (typeof addObj[index] == "string") {
                    sql += "N'" + addObj[index] + "'" + ",";
                }
            }
            sql = sql.substring(0, sql.length - 1) + "),";
        });
    }
    sql = sql.substring(0, sql.length - 1) + ";";
    //
    sql += "INSERT INTO " + tableName+ "(";
    for (var index in addObj) {
        sql += index + ",";
    }
    sql = sql.substring(0, sql.length - 1) + ") SELECT ";
    for (var index in addObj) {
        sql += index + ",";
    }
    sql = sql.substring(0, sql.length - 1);
    sql += ` FROM #${tableName}Temp
    WHERE ${uniqueFieldName} NOT IN (
        SELECT ${uniqueFieldName} FROM ${tableName}
    )
    `;

    // logger.info(sql);
    var result = await connection.request().query(sql);
    await mssql.close();// close
    return result;
};

var update = async function (updateObj, whereObj, tableName) {//更新数据
    await mssql.close();// close
    var connection = await getConnection();
    var request = connection.request();

    var sql = "UPDATE " + tableName + " SET ";
    if (updateObj) {
        for (var index in updateObj) {
            if (typeof updateObj[index] == "number") {
                request.input(index, mssql.Int, updateObj[index]);
                sql += index + "=@" + index + ",";
            } else if (typeof updateObj[index] == "string") {
                request.input(index, mssql.NVarChar, updateObj[index]);
                sql += index + "=@" + index + ",";
            }
        }
    }
    sql = sql.substring(0, sql.length - 1) + " WHERE ";
    if (whereObj) {
        for (var index in whereObj) {
            if (typeof whereObj[index] == "number") {
                request.input(index, mssql.Int, whereObj[index]);
                sql += index + "=@" + index + " AND ";
            } else if (typeof whereObj[index] == "string") {
                request.input(index, mssql.NVarChar, whereObj[index]);
                sql += index + "=@" + index + " AND ";
            }
        }
    }
    sql = sql.substring(0, sql.length - 5);
    var result = await request.query(sql);
    await mssql.close();// close
    return result;
};

exports.query = querySql;
exports.update = update;
exports.add = add;
exports.addIfNotExist = addIfNotExist;
exports.addList = addList;
exports.addListIfNotExist = addListIfNotExist;

Contact

Contact me: weihanli@outlook.com

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • puppeteer
    1.puppeteer简介 puppeteer是一个node库，是Google chrome团队官方的无界面（he...
    伊人风采_690d阅读 7,696评论 0赞 11
  • Koa2框架学习笔记
    框架提出的背景 ES6/7带来的变革 自ES6确定和ES7中async/await开始普及，Node的发展变得更加...
    宫若石阅读 8,536评论 1赞 14
  • web自动化测试-puppeteer入门与实践
    前言 对于web的自动测试，很多人熟悉的是selenium、webdriver的解决方案，比如说webdriver...
    _夏兮阅读 2,433评论 0赞 2
  • 2015-12-15 年底的加班福利
    Tags: 加班 买车 －－－－ 凡有的，还要加给他叫他多余；没有的，连他所有的也要夺过来。 是的，我就是没有时间...
    哈慈开阅读 139评论 0赞 0
  • 身边事/原来，你也在看这本
    排队的队伍，很长，想着不会很快轮到我，便掏出了随身的大部头书，看了起来。 正看得起劲，突然感觉后面有人推我，回过头...
    凤西阅读 275评论 0赞 0