nodJs基于express框架结合mysql实现后端简易增删改查数据接口
const express = require('express'); //调用模块
const app = express();
const server = app.listen(3000,function(){console.log('服务启动成功!');});
const io = require('socket.io')(server);
const mysql = require('mysql');
app.all('*', function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "X-Requested-With");
res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
res.header("X-Powered-By",' 3.2.1')
res.header("Content-Type", "text/plain");
next();
});
io.on('connection',function ( socket ) {
console.log('连接成功');
setInterval( ()=>{
socket.emit('sendMsg',sendMsg())
},1000);
function sendMsg(){
return (Math.floor( Math.random()*100 )) + '-from server.';
}
});
const connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'root',
port : '3306',
database : 'ngfw'
});
connection.connect();
// var WebSocketServer = require('ws').Server,
// wss = new WebSocketServer({ port: 666 });
// wss.on('connection', function (ws) {
// console.log('success')
// var cont ;
// ws.on('message', function (message) {
// console.log(message,'wes');
// cont = message;
// cont += 'from server';
// ws.send(cont);
// });
// setInterval( ()=>{
// var rand = Math.round( Math.random()*100 );
// ws.send(rand+'-from server');
// },1000)
// });
//用户管理
//验证查询数据是否存在
function allCheck( username ) {
let sql = 'select * from usermanager where username = ? ' , queryall = [username];
return new Promise((resove ,reject)=>{
connection.query(sql , queryall , function (err , result) {
if(err){
console.log('查询失败!');
return;
}else{
if( result.length == 0 ){
resove( false );
}else{
resove( true );
}
}
});
});
};
app.get('/usermanager', function (req, res) {
let sql = '' ,queryParams = [];
if( req.query.type != undefined && ( req.query.type == '管理员' || req.query.type == '普通用户') ){
sql = 'SELECT * FROM usermanager where usertype = ?',queryParams = [req.query.type];
}else{
sql = 'SELECT * FROM usermanager';
};
//查
connection.query(sql,queryParams,function (err, result) {
if(err){
console.log('[SELECT ERROR] - ',err.message);
return;
}else{
console.log('后台数据查寻成功!');
res.send(result);
}
});
});
let count = 0;
app.get('/usermanager/add', async function (req, res) {
count+=1;
let sf = count % 2 == 0 ? '管理员' : '普通用户';
let reqs = {username:'eth'+count , usertype:sf,userdesc:sf,vaddress:'192.168.1.'+count,otherinfo:'admin',status:false};
let addSql = `INSERT INTO usermanager(username,usertype,userdesc,vaddress,otherinfo,status) VALUES(?,?,?,?,?,?)`;
let hasNum = await allCheck( reqs.username );
if( hasNum ){
res.send('用户已经存在,添加失败');
return;
}else{
let addSqlParam = Object.values(reqs);
connection.query(addSql,addSqlParam,function(err,result){
if(err){
console.log(err);
res.send('error add');return;
}else{
res.send('添加成功');
return count;
}
})
}
});
app.get('/usermanager/update', async function (req,res) {
if(! req.query.hasOwnProperty('username') ){
res.send('传入参数为空!');
return;
};
let tmp = req.query;
let update = ` UPDATE usermanager SET usertype = ?, userdesc = ?, vaddress = ? ,otherinfo = ? ,status = ? WHERE username = ?`;
let updateParam = [ tmp.usertype ,tmp.userdesc,tmp.vaddress,tmp.otherinfo,tmp.status,tmp.username];
let hasContains = await allCheck(tmp.username);
if( hasContains ){
connection.query( update , updateParam , function (err,result) {
if(err){
res.send('更新失败'+err);
return;
}else{
res.send('更新成功!');
}
});
}else{
res.send('更新用户不存在');
}
});
function deleteData( param ){
app.get('/usermanager/'+param.types, async function (req,res) {
if( req.query.hasOwnProperty('username')){
}else{
return;
}
let delSql = ` DELETE FROM ${param.table} WHERE username = ? `, delParams = [ req.query.username ];
let hasContains = await allCheck(req.query.username);
if( hasContains ){
connection.query(delSql , delParams, function (err ,result) {
if(err){
res.send('删除失败',err);
return;
}else{
res.send('删除成功!');
}
});
}else{
console.log(' user no found')
res.send('删除用户不存在!')
}
});
}
deleteData({
types:'delete',
table:'usermanager'
});
app.post('/usermanager/allgroup',function (req,res) {
console.log('ssss');
let sql = `SELECT * FROM allgroup`;
connection.query(sql,function (err,result){
if(err){
return;
}
res.send( JSON.stringify(result) );
})
})