nodejs数据库操作实践

首先创建一个app.js文件

创建一个服务器端文件

//服务器端的文件
const express=require(‘express’);
//psot
const bodyParser=require(‘body-parser’);
const userRouter=require(’./routers/user.js’);
const productRouter=require(’./routers/product.js’);
var server=express();
server.listen(8888);
//托管静态资源到public
server.use(express.static(’./public’));

//使用路由器管理路由
//把用户路由器挂载到/user,访问形式/user/detail,所以HTML中要改
server.use(bodyParser.urlencoded({
extended:false
}));

server.use(’/user’,userRouter);
server.use(’/product’,productRouter);

创建HTML文件

创建公共模块pool.js，定义数据库

//用于创建链接池，任意模块需要链接，只需要引入模块就行
const mysql=require(‘mysql’);
var pool=mysql.createPool({
host:‘127.0.0.1’,
port:3306,
user:‘root’,
password:‘123456’,
database:‘course’, //使用的数据库
connectionLimit:20 //设置连接池的数量
});
//导出连接池对象
module.exports=pool;

创建user.js路由器文件

//路由器文件

const express=require(‘express’);
//引入pool链接池模块
const pool=require(’…/pool.js’);
//创建空的路由器对象
var router=express.Router();
//添加路由
//1，检索用户
router.get(’/detail’,(req,res)=>{
//获取数据
var unameObj=req.query;
var uname=unameObj.username;
//验证是否为空
if(uname==’’){
console.log(uname);
res.send({code:401,msg:‘username require!’});
//如果验证失败，阻止继续往后执行
return;
}
//执行SQL语句，查询对应的数据
pool.query(select * from emp where username=?,[uname],(err,result)=>{
if(err) throw err;
//查询的结果发送到浏览器
res.send(result);
});
});

//2,用户注册
router.post(’/reg’,(req,res)=>{
var regObj=req.body;
var username=regObj.username;
var password=regObj.password;
var emile=regObj.emile;
var phone=regObj.phone;
/*
遍历对象

for(var key in regObj){
	//key是属性
	//属性值regObj[key]为空
	if(!regObj[key]){
		res.send({code:400,msg:key+' required'});
		return;
	}	
}

*/
if(!username){
	res.send({code:400,meg:'username required!'});
	return;
}else if(!password){
	res.send({code:400,meg:'password required!'});
	return;
}else if(!emile){
	res.send({code:400,meg:'emile required!'});
	return;
}else if(!phone){
	res.send({code:400,meg:'phone required!'});
	return;
}else{
pool.query(`insert into reg set ?`,[regObj],(err,result)=>{
	if(err) throw err;
	if(result.affectedRows){
		res.send({code:200,msg:'注册成功'});
	}
});}

});

//3,登录
router.post(’/login’,(req,res)=>{
var loginObj=req.body;
var username=loginObj.username;
var password=loginObj.password;
if(!username){
res.send({code:400,msg:‘username required’})
}else if(!password){
res.send({code:400,msg:‘username password’})
}else{
pool.query(select * from reg where username = ? and password =?,[username,password],(err,result)=>{
if(err) throw err;
var i=result.length;
if(i>0){
res.send({code:200,msg:‘登录成功！’});
}else{res.send({code:401,msg:‘用户名密码错误’});}
});
}
})

//4,更改用户
router.post(’/update’,(req,res)=>{
//获取数据
var updateObj=req.body;
var uid=updateObj.uid;
var username=updateObj.username;
var password=updateObj.password;
var emile=updateObj.emile;
var phone=updateObj.phone;
//在验证是否为空
if(!uid){
res.send({code:401,msg:‘uid require’});
return;
}
else if(!username){
res.send({code:401,msg:‘username require’});
return;
}else if(!password){
res.send({code:400,meg:‘password required!’});
return;
}else if(!emile){
res.send({code:400,meg:‘emile required!’});
return;
}else if(!phone){
res.send({code:400,meg:‘phone required!’});
return;
}else{
//执行SQL
pool.query(update reg set username=? , password=? , emile=?,phone=? where uid=?,[username,password,emile,phone,uid],(err,result)=>{
if(err) throw err;
if(result.affectedRows>0){
res.send({code:200,msg:‘update success’})
}else{res.send({code:400,msg:‘update failed！’})}

});
}

});

//5,删除用户
router.get(’/delete’,(req,res)=>{
var deleteObj=req.query;
var uid=deleteObj.uid;
if(!uid){
res.send({code:400,msg:‘uid required’});
}
pool.query(delete from reg where uid=?,[uid],(err,result)=>{
if(err) throw err;
if(result.affectedRows>0){
res.send({code:200,msg:‘success’});
}else{res.send({code:305,msg:‘failed！’})};
});
})

//分页查询
//路由
router.get(’/fSelect’,(req,res)=>{
var selectObj=req.query;
//转整型
var page=parseInt(selectObj.page);
var count=parseInt(selectObj.count);
//验证数据
if(!page){
page=1;
}else if(!count){
count=3;
}
//计算start=(页数-1)*数量
var start=(page-1)*count;
//执行SQL
pool.query(select * from reg limit ?,?,[start,count],(err,result)=>{
if(err) throw err;
res.send(result);
});
});

//导出路由
module.exports=router;