Nodejs 操作 Sql Server

WeihanLi發表於2018-11-24
NodeJSSQLServer

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

相關文章