亚马逊店铺网站建设费用,个人作品网页设计,旺道seo软件,wordpress 文章页 tag1.在自己的项目JS文件夹中建文件#xff1a;config.js、mssql.js和server.js以及api文件夹下的user.js 2.在config.js中封装数据库信息
let app {user: sa, //这里写你的数据库的用户名password: ,//这里写数据库的密码server: localhost,database: medicineSystem, // 数据…1.在自己的项目JS文件夹中建文件config.js、mssql.js和server.js以及api文件夹下的user.js 2.在config.js中封装数据库信息
let app {user: sa, //这里写你的数据库的用户名password: ,//这里写数据库的密码server: localhost,database: medicineSystem, // 数据库名字port: 1433, //端口号,默认1433options: {encrypt: false, //加密,设置为true时会连接失败 Failed to connect to localhost:1433 - self signed certificateenableArithAbort: false},pool: {min: 0,max: 10,idleTimeoutMillis: 3000}
}module.exports app
3.在mssql.js中对sql语句的二次封装
//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 参数用来解释sql中的*例如 { id: id }* param callBack 回调函数*/
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 数据库表名例news* param topNumber 只查询前几个数据可为空为空表示查询所有* param whereSql 条件语句例where id id* param params 参数用来解释sql中的*例如 { id: id }* param orderSql 排序语句例order by created_date* param callBack 回调函数*/
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)}
};/*** 查询指定表的所有数据* param tableName 数据库表名* param callBack 回调函数*/
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 数据库表名* param callBack 回调函数*/
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才会返回idps.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)}
};/*** 更新指定表的数据* param updateObj 需要更新的对象字段例{ name: name, age: 20 }* param whereObj 需要更新的条件例: { id: id }* param tableName 数据库表名* param callBack 回调函数*/
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 参数用来解释sql中的*例如 { id: id }* param tableName 数据库表名* param callBack 回调函数*/
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;
4.在api/user.js下写接口代码
//user.js
const express require(express);
const db require(../mssql.js);
const moment require(moment);
const router express.Router();/* GET home page. */
router.get(/medicineList, function (req, res, next) {//查询某表下的全部数据db.selectAll(medicineList, function (err, result) {res.send(result.recordset)});
});
router.get(/medicineAssess, function (req, res, next) {db.selectAll(medicineAssess, function (err, result) {res.send(result.recordset)});
});
router.get(/medicineAsk, function (req, res, next) {db.selectAll(medicineAsk, function (err, result) {res.send(result.recordset)});
});
router.get(/diseaseList, function (req, res, next) {db.selectAll(diseaseList, function (err, result) {res.send(result.recordset)});
});
router.get(/diseaseMedicine, function (req, res, next) {db.selectAll(diseaseMedicine, function (err, result) {res.send(result.recordset)});
});
router.get(/user, function (req, res, next) {db.selectAll(user, function (err, result) {res.send(result.recordset)});
});
router.get(/admin, function (req, res, next) {db.selectAll(admin, function (err, result) {res.send(result.recordset)});
});
router.post(/delete, function (req, res, next) {//删除一条id对应的userInfo表的数据const { UserId } req.bodyconst id UserIddb.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) {//更新一条对应id的userInfo表的数据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;
5.在server.js中配置启动文件
//1.导入模块
const express require(express)//2.创建服务器
let server express()
server.use(express.urlencoded()) //中间件要写在启动文件里面const cors require(cors)
server.use(cors())const user require(./api/user.js)server.use(/, user)//3.开启服务器
server.listen(8002, () {console.log(服务器已启动,在端口号8002)
})
6.启动服务器
cmd到server.js所在的目录下输入
nodemon server.js 7.用postman测试接口
