Nodejs 操作 Sql Server

Nodejs 操作 Sql Server

Intro

最近項(xiàng)目需要爬取一些數(shù)據(jù)，數(shù)據(jù)有加密，前端的js又被混淆了，ajax請求被 hook 了，有些復(fù)雜，最后打算使用 puppeteer 來爬取數(shù)據(jù)。

Puppeteer 是谷歌團(tuán)隊(duì)在維護(hù)的一個(gè)項(xiàng)目，初衷主要是用來做網(wǎng)頁的自動(dòng)化測試， Google Chrome 團(tuán)隊(duì)官方的無界面（Headless）Chrome 工具，它是一個(gè) Node 庫，提供了一個(gè)高級的 API 來控制 DevTools協(xié)議上的無頭版 Chrome ，也可以配置為使用完整（非無頭）的 Chrome。這里就不詳細(xì)介紹了，有興趣的可以自己先行 Google 一下，之后再寫一篇文章來介紹，今天主要介紹 node 操作 mssql。

node-mssql

node-mssql 是我們用來操作 Ms Sql Server 數(shù)據(jù)庫用到的 npm 包，支持 promise， async/await 語法。這個(gè)包也是微軟官方推薦使用的。

個(gè)人比較喜歡 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');

// 存儲(chǔ)過程
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

封裝

雖然提供比較完善的方法，但是如果用起來的話還是會(huì)覺得用起來有些不舒服，沒有那么流暢，沒有那么簡潔，于是想自己封裝一層

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(){//連接數(shù)據(jù)庫
    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) {//添加數(shù)據(jù)
    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) {//添加數(shù)據(jù)
    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) {//添加數(shù)據(jù)
    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) {//添加數(shù)據(jù)
    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) {//更新數(shù)據(jù)
    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

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容