最近項目中要用到node寫接口然后連接公司現(xiàn)有的sql.server數(shù)據(jù)庫,再把執(zhí)行結(jié)果返回給前端(還是我),因為之前一直做前端這塊,后端這方面不是很懂,花了很長的時間終于研究出來了(還是太菜了,走了很多彎路),所以寫個博客,一是復(fù)習(xí)鞏固,二是給其他有需要的小伙伴一個參考,盡量少走彎路,廢話不多說,直接上代碼

1.首先在自己的數(shù)據(jù)庫新建一張表,用于接下來的測試

?2.在自己的項目文件夾中建兩個文件,config.js & mssql.js

3.封裝數(shù)據(jù)庫信息,config.js

//config.js
let app = {
    user: 'xxxxxx', //這里寫你的數(shù)據(jù)庫的用戶名
    password: 'xxxxxx',//這里寫數(shù)據(jù)庫的密碼
    server: 'localhost', 
    database: 'testDB', // 數(shù)據(jù)庫名字
    port: 1433, //端口號,默認1433
    options: {
        encrypt: false,  //加密,設(shè)置為true時會連接失敗 Failed to connect to localhost:1433 - self signed certificate
        enableArithAbort: false
    },
    pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 3000
    }
}

module.exports = app

4.對sql語句的二次封裝 , mssql.js

//mssql.js
/**
 *sqlserver Model
 **/
const mssql = require("mssql");
const conf = require("./config.js");

const pool = new mssql.ConnectionPool(conf)
const poolConnect = pool.connect()

pool.on('error', err => {
    console.log('error: ', err)
})
/**
 * 自由查詢
 * @param sql sql語句，例如: 'select * from news where id = @id'
 * @param params 參數(shù)，用來解釋sql中的@*，例如： { id: id }
 * @param callBack 回調(diào)函數(shù)
 */
let querySql = async function (sql, params, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        if (params != "") {
            for (let index in params) {
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        ps.prepare(sql, function (err) {
            if (err)
                console.log(err);
            ps.execute(params, function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

/**
 * 按條件和需求查詢指定表
 * @param tableName 數(shù)據(jù)庫表名，例：'news'
 * @param topNumber 只查詢前幾個數(shù)據(jù)，可為空，為空表示查詢所有
 * @param whereSql 條件語句，例：'where id = @id'
 * @param params 參數(shù)，用來解釋sql中的@*，例如： { id: id }
 * @param orderSql 排序語句，例：'order by created_date'
 * @param callBack 回調(diào)函數(shù)
 */
let select = async function (tableName, topNumber, whereSql, params, orderSql, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        let sql = "select * from " + tableName + " ";
        if (topNumber != "") {
            sql = "select top(" + topNumber + ") * from " + tableName + " ";
        }
        sql += whereSql + " ";
        if (params != "") {
            for (let index in params) {
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        sql += orderSql;
        console.log(sql);
        ps.prepare(sql, function (err) {
            if (err)
                console.log(err);
            ps.execute(params, function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

/**
 * 查詢指定表的所有數(shù)據(jù)
 * @param tableName 數(shù)據(jù)庫表名
 * @param callBack 回調(diào)函數(shù)
 */
let selectAll = async function (tableName, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        let sql = "select * from " + tableName + " ";
        ps.prepare(sql, function (err) {
            if (err)
                console.log(err);
            ps.execute("", function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

/**
 * 添加字段到指定表
 * @param addObj 需要添加的對象字段，例：{ name: 'name', age: 20 }
 * @param tableName 數(shù)據(jù)庫表名
 * @param callBack 回調(diào)函數(shù)
 */
let add = async function (addObj, tableName, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        let sql = "insert into " + tableName + "(";
        if (addObj != "") {
            for (let index in addObj) {
                if (typeof addObj[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof addObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
                sql += index + ",";
            }
            sql = sql.substring(0, sql.length - 1) + ") values(";
            for (let index in addObj) {
                if (typeof addObj[index] == "number") {
                    sql += addObj[index] + ",";
                } else if (typeof addObj[index] == "string") {
                    sql += "'" + addObj[index] + "'" + ",";
                }
            }
        }
        sql = sql.substring(0, sql.length - 1) + ") SELECT @@IDENTITY id"; // 加上SELECT @@IDENTITY id才會返回id
        ps.prepare(sql, function (err) {
            if (err) console.log(err);
            ps.execute(addObj, function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

/**
 * 更新指定表的數(shù)據(jù)
 * @param updateObj 需要更新的對象字段，例：{ name: 'name', age: 20 }
 * @param whereObj 需要更新的條件，例: { id: id }
 * @param tableName 數(shù)據(jù)庫表名
 * @param callBack 回調(diào)函數(shù)
 */
let update = async function (updateObj, whereObj, tableName, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        let sql = "update " + tableName + " set ";
        if (updateObj != "") {
            for (let index in updateObj) {
                if (typeof updateObj[index] == "number") {
                    ps.input(index, mssql.Int);
                    sql += index + "=" + updateObj[index] + ",";
                } else if (typeof updateObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                    sql += index + "=" + "'" + updateObj[index] + "'" + ",";
                }
            }
        }
        sql = sql.substring(0, sql.length - 1) + " where ";
        if (whereObj != "") {
            for (let index in whereObj) {
                if (typeof whereObj[index] == "number") {
                    ps.input(index, mssql.Int);
                    sql += index + "=" + whereObj[index] + " and ";
                } else if (typeof whereObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                    sql += index + "=" + "'" + whereObj[index] + "'" + " and ";
                }
            }
        }
        sql = sql.substring(0, sql.length - 5);
        ps.prepare(sql, function (err) {
            if (err)
                console.log(err);
            ps.execute(updateObj, function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

/**
 * 刪除指定表字段
 * @param whereSql 要刪除字段的條件語句，例：'where id = @id'
 * @param params 參數(shù)，用來解釋sql中的@*，例如： { id: id }
 * @param tableName 數(shù)據(jù)庫表名
 * @param callBack 回調(diào)函數(shù)
 */
let del = async function (whereSql, params, tableName, callBack) {
    try {
        let ps = new mssql.PreparedStatement(await poolConnect);
        let sql = "delete from " + tableName + " ";
        if (params != "") {
            for (let index in params) {
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        sql += whereSql;
        ps.prepare(sql, function (err) {
            if (err)
                console.log(err);
            ps.execute(params, function (err, recordset) {
                callBack(err, recordset);
                ps.unprepare(function (err) {
                    if (err)
                        console.log(err);
                });
            });
        });
    } catch (e) {
        console.log(e)
    }
};

exports.config = conf;
exports.del = del;
exports.select = select;
exports.update = update;
exports.querySql = querySql;
exports.selectAll = selectAll;
exports.add = add;

5.接口代碼,api/user.js

//user.js
const express = require('express');
const db = require('../utils/mssql.js');
const moment = require('moment');
const router = express.Router();

/* GET home page. */
router.get('/info', function (req, res, next) {
    db.selectAll('userInfo', function (err, result) {//查詢所有userInfo表的數(shù)據(jù)
        res.send(result.recordset)
        // res.render('userInfo', { results: result.recordset, moment: moment });
    });
});
router.post('/delete', function (req, res, next) {//刪除一條id對應(yīng)的userInfo表的數(shù)據(jù)
    console.log(req.body, 77);
    const { UserId } = req.body
    const id = UserId
    db.del("where id = @id", { id: id }, "userInfo", function (err, result) {
        console.log(result, 66);
        res.send('ok')
    });
});
router.post('/update/:id', function (req, res, next) {//更新一條對應(yīng)id的userInfo表的數(shù)據(jù)
    var id = req.params.id;
    var content = req.body.content;
    db.update({ content: content }, { id: id }, "userInfo", function (err, result) {
        res.redirect('back');
    });
});

module.exports = router;

6.啟動文件,server.js

//1.導(dǎo)入模塊
const express = require('express')

//2.創(chuàng)建服務(wù)器
let server = express()
server.use(express.urlencoded()) //中間件要寫在啟動文件里面

const cors = require('cors')
server.use(cors())

const user = require('./api/user.js')

server.use('/', user)

//3.開啟服務(wù)器
server.listen(8002, () => {
    console.log('服務(wù)器已啟動,在端口號8002')
})

7.啟動服務(wù)器

8.用postman測試接口

?

?數(shù)據(jù)成功返回~~~文章來源地址http://www.zghlxwxcb.cn/news/detail-505858.html

到了這里，關(guān)于前端使用node.js連接sql.server數(shù)據(jù)庫教程的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！