node对数据库的操作无非就是增删改查,所以封装好方法供以后调用是非常有必要的,总结网上的方法,并亲自测试后,特意记录一下!
- 特别要注意
WHERE
条件的数据类型 - SQL语句的准确性很关键
第一步 首先封装好连接数据库的方法 直接看代码
配置文件: config.js
代码:
const mysql = require('mysql')
const connectdb=()=>{
let connection = mysql.createConnection({
host : '192.168.1.2',
port : '3306',
user : 'root',
password : '123',
database : 'myfirst'
})
return connection;
}
module.exports=connectdb;
第二步封装对数据库的操作方法,注意包含增删改查
文件名称: db.js
代码内容:代码里面注释很详细了,应该都能看懂,不懂可以加我QQ讨论2377200400
const conn = require('./config');
const connection = conn();
// 查询所有数据
let selectAll = (sql,callback)=>{
connection.query(sql,(err,result)=>{
if(err){
console.log('错误信息-',err.sqlMessage);
let errNews = err.sqlMessage;
callback(errNews,'');
return;
}
var string=JSON.stringify(result);
var data = JSON.parse(string);
callback('',data);
// console.log(string);
})
}
// 插入一条数据
let insertData = (table,datas,callback)=>{
var fields='';
var values='';
for( var k in datas){
fields+=k+',';
values=values+"'"+datas[k]+"',"
}
fields=fields.slice(0,-1);
values=values.slice(0,-1);
console.log(fields,values);
var sql="INSERT INTO "+table+'('+fields+') VALUES('+values+')';
connection.query(sql,callback);
}
// 更新一条数据
let updateData=function(table,sets,where,callback){
var _SETS='';
var _WHERE='';
var keys='';
var values='';
for(var k in sets){
_SETS+=k+"='"+sets[k]+"',";
}
_SETS=_SETS.slice(0,-1);
for(var k2 in where){
// _WHERE+=k2+"='"+where[k2]+"' AND ";
_WHERE+= k2+"="+where[k2];
}
// UPDATE user SET Password='321' WHERE UserId=12
//update table set username='admin2',age='55' where id="5";
var sql="UPDATE "+table+' SET '+_SETS+' WHERE '+_WHERE;
console.log(sql);
connection.query(sql,callback);
}
// 删除一条数据
let deleteData=function(table,where,callback){
var _WHERE='';
for(var k2 in where){
//多个筛选条件使用 _WHERE+=k2+"='"+where[k2]+"' AND ";
_WHERE+= k2+"="+where[k2];
}
// DELETE FROM user WHERE UserId=12 注意UserId的数据类型要和数据库一致
var sql="DELETE FROM "+table+' WHERE '+_WHERE;
connection.query(sql,callback);
}
exports.selectAll = selectAll;
exports.insertData = insertData;
exports.deleteData = deleteData;
exports.updateData = updateData;
第三步使用方法,调用
我是以写接口的方式来搞的,主要就是先从注册,登录,修改,删除账户,接口写在了单独的路由页面,
文件在route文件下的base文件下的index.js
代码:
const express = require('express')
const app = express()
const router = express.Router()
const db = require('./../../db/db.js')
// 该路由使用的中间件
/*router.use(function timeLog(req, res, next) {
console.log('Time: ', new Date());
next();
});*/
// 定义网站主页的路由
router.get('/', function(req, res) {
res.send('Birds home page');
});
// 登录接口,并且验证密码--查询方法的使用案例
router.post('/login', function(req, res) {
let name = JSON.stringify(req.body.name);
let pwd = req.body.pwd;
let resData = {"name":name,"pass":pwd};
let errText = '',resultData='';
db.selectAll('select * from user where Account = '+ name,(e,r)=>{
if(e){
res.status(200).json({"status":false,"msg":e,"data":[]});
}
let tt = r.length ;
if(tt == 0){
errText="账号不存在";
}else if(pwd != r[0].Password){
errText="密码错误";
}else{
resultData = r[0];
}
// console.log(tt,errText);
res.status(200).json({"status":true,"msg":errText,"data":resultData});
})
//res.status(200).json({"status":true,"msg":"","data":resData});
});
// 注册接口 增加的方法使用案例
router.post('/register',(req,res)=>{
let name = req.body.names;
let password = req.body.pwd;
let errText = '',resultData='sccusee';
let saveData = {"Account":name,"Password":password};
db.insertData('user',saveData,(e,r)=>{
if(e){
res.status(200).json({"status":false,"msg":e,"data":[]});
}
res.status(200).json({"status":true,"msg":errText,"data":resultData});
})
})
// 注销接口 删除的方法使用案例
router.post('/cancel',(req,res)=>{
let datas = req.body;
let deleteId = {UserId:11};
console.log(datas);
db.deleteData('user',deleteId,(e,r)=>{
if(e){
return res.status(200).json({"status":false,"msg":e,"data":''});
}
res.status(200).json({"status":true,"msg":"ok","data":'resultData'});
});
})
// 修改密码接口 修改的方法使用案例
router.post('/modify',(req,res)=>{
let _where = {UserId : req.body.UserId};
let _set = {Password :req.body.pwd};
db.updateData('user',_set,_where,(e,r)=>{
if(e){
return res.status(200).json({"status":false,"msg":e,"data":''});
}
res.status(200).json({"status":true,"msg":'ok',"data":'resultData'});
})
})
module.exports = router;
多说一句,在入口页面app.js
需要添加上这个路由
const express = require('express')
const app = express()
const bodyParser = require('body-parser')
// 我的基础路由文件
const base = require('./routes/base/index.js')
const db = require('./db/db.js')
app.get('/',function(req,res){
db.selectAll('select * from user',(e,r)=>{
if(e){
res.status(200).json({"status":false,"msg":e,"data":[]});
}
res.status(200).json({"status":true,"msg":"","data":r});
})
})
// json 解析
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false}));
//设置跨域访问
app.all('*', function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "Content-Type,Content-Length, Authorization, Accept,X-Requested-With");
res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
res.header("X-Powered-By",' 3.2.1')
next();
});
// 暴露公共资源
app.use(express.static('public'))
app.use('/base',base)
app.listen(8010)
console.log('app run port 8010')
好了这样一套完整的操作就收工了。
可以用下面的代码进行测试:
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>测试</title>
</head>
<body>
<form>
姓名: <input type="text" name="user" id="user"> <br>
密码: <input type="text" name="password" id="pwd">
</form>
<button onclick="submit()">submit</button>
<script type="text/javascript" src="http://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
<script type="text/javascript">
function submit(){
var qdata = {
"UserId": $('#user').val(),
"pwd": $('#pwd').val()
};
console.log(qdata)
$.ajax({
url:'http://192.168.1.202:8010/base/modify',
dataType:'json',
type:'POST',
data: qdata,
//contentType:"application/json",
success: function(result){
console.log(result)
},
error:(error)=>{
console.log(error)
}
})
}
</script>
</body>
</html>