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: "db user",

    password: "password",

    server: "server",

    database: "database name",

    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 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;

Contact

Contact me: weihanli@outlook.com

巴特西

Nodejs 操作 Sql Server

Nodejs 操作 Sql Server

Intro

node-mssql

封装

Contact

最新文章

热门文章