nodejs使用案例-mysql操作

標籤：val   post   l資料庫   操作   body   fail   jquery   console   password   

1、package.json:

{  "scripts": {    "start": "node app.js"  },  "devDependencies": {    "body-parser": "^1.17.1",    "ejs": "^2.5.6",    "express": "^4.15.2",    "express-session": "^1.15.1",    "mysql": "^2.13.0",    "session": "^0.1.0"  }}

2、db.js:

var mysql = require(‘mysql‘);//匯入mysql模組var db=mysql.createConnection({ //串連mysql資料庫host:‘localhost‘,user:‘root‘,password:‘123456‘,database:‘test‘,  //資料庫名字port:3306});db.connect((err)=>{if(err){console.log(‘connect fail‘)}else{console.log(‘connect suc‘)}});module.exports=db;

3、app.js:

var express = require(‘express‘);var app = express();var gets = require(‘./modle.js‘);var bodyParser = require(‘body-parser‘);var session = require(‘express-session‘)app.use(session({secret: ‘shixinke‘,resave: true,saveUninitialized: false,cookie: {secure: false}})); //這部分代碼可以讓你使用sessionapp.engine(‘.html‘, require(‘ejs‘).__express);app.set(‘view engine‘, ‘html‘);app.set(‘views‘, __dirname + ‘/views‘); //模板引擎啟用app.use(bodyParser.json());app.use(bodyParser.urlencoded());  //這部分可以讓你使用接收post資料app.get(‘/‘, function(req, res, next) { //路由，然後調用相應的modle.js裡的方法gets.showArticleHome(req, res, next)})app.get(‘/login‘, function(req, res, next) {res.render(‘login‘);})app.get(‘/outlogin‘, function(req, res, next) {req.session.sign = false;res.redirect(‘/‘);})app.post(‘/loginif‘, function(req, res, next) {gets.loginif(req, res, next);})app.set(‘trust proxy‘, 1) // trust first proxyapp.get(‘/list‘, function(req, res, next) {if (req.session.sign) {gets.showArticle(req, res, next)} else {res.send(‘you don,t have login‘)}})app.get(‘/article/:id‘, function(req, res, next) {gets.showArticleInfo(req, res, next)})app.get(‘/write‘, function(req, res, next) {gets.subForm(req, res, next)})app.post(‘/subform‘, function(req, res, next) {gets.saveArticle(req, res, next);})app.get(‘/delete/:id‘, function(req, res, next) {gets.deleteArticle(req, res, next);})app.get(‘/edit/:id‘, function(req, res, next) {if (req.session.sign) {gets.editArticle(req, res, next);}else{res.send(‘you don,t have login in‘)}})app.post(‘/update‘, function(req, res, next) {gets.updateArticle(req, res, next);})app.listen(8000)

4、modle.js:

var db = require(‘./db.js‘);var gets = {};gets.loginif=(req,res,next)=>{var userInfo=req.body.userinfo;console.log(userInfo,‘sdflksjfd‘)var qry="select count(*) as num from admin where user=‘"+userInfo.user+"‘ and password=‘"+userInfo.psd+"‘";db.query(qry,function(err,result){if(err){console.log(‘err‘);return;}if(result[0].num>=1){req.session.sign = true;}res.send(result)})}gets.findAll = (req, res, next) => {var qry = "select * from user";var qry2 = "select * from user_next";db.query(qry, function(err, result) {if (err) {console.log(‘err‘);return;}db.query(qry2, function(err, result2) {res.render(‘home‘, {data: result,data2: result2,title: ‘home‘});})});}gets.subForm = (req, res, next) => {res.render(‘write‘, {})}gets.showArticle = (req, res, next) => {db.query(‘select * from article‘, function(err, result) {if (err) {console.log(‘not get article‘)return;}res.render(‘list‘, {data: result})})}gets.showArticleHome = (req, res, next) => {db.query(‘select * from article‘, function(err, result) {if (err) {console.log(‘not get article‘)return;}res.render(‘home‘, {data: result})})}gets.showArticleInfo = (req, res, next) => {var id = req.params.id;db.query("select * from article where id=" + id + "", function(err, result) {if (err) {console.log(‘not get article‘)return;}res.render(‘info‘, {data: result})})}gets.saveArticle = (req, res, next) => {var formdata = req.body.formdata;console.log(typeof(formdata.tit));var str = formdata.con;str = str.replace(/\"/g, " ");str = str.replace(/"/g, "‘");str = str.replace(/&/g, "");str = str.replace(/lt;/g, "<");str = str.replace(/gt;/g, ">");str = str.replace(/\&/g, "");str = str.replace(/\n/g, "<br>");var qry = "insert into article (title,content) values (‘" + formdata.tit + "‘, ‘" + str + "‘)";db.query(qry, function(err, result) {if (err) {console.log(‘err‘)return;}res.send({state: true})})}gets.updateArticle = (req, res, next) => {var formdata = req.body.formdata;var str = formdata.con;str = str.replace(/\"/g, " ");str = str.replace(/"/g, "‘");str = str.replace(/&/g, "");str = str.replace(/lt;/g, "<");str = str.replace(/gt;/g, ">");str = str.replace(/\&/g, "");str = str.replace(/\n/g, "<br>");var qry = "update article set title=‘"+formdata.tit+"‘,content=‘"+str+"‘ where id=‘"+req.body.id+"‘";db.query(qry, function(err, result) {if (err) {console.log(‘err‘)return;}res.send({state: true})})}gets.deleteArticle = (req, res, next) => {var delId=req.params.id;var qry="delete from article where id="+delId+"";db.query(qry, function(err, result) {if (err) {console.log(‘err‘)return;}res.redirect(‘/list‘)})}gets.editArticle = (req, res, next) => {var editId=req.params.id;console.log(editId,‘dksfdksfj‘)var qry="select * from article where id="+editId+"";db.query(qry, function(err, result) {if (err) {console.log(‘err‘)return;}res.render(‘edit‘,{data:result})})}module.exports = gets;

5、home.html:

<!DOCTYPE html><html><head><title>article</title></head><style type="text/css">li{display: inline-block;}</style><body><% data.forEach(function(item){%><a href="article/<%=item.id%>"><li><%=item.title%></li></a><br><%})%><a href="/login" style="display:block;margin-top: 30px;"><button>login</button></a></body></html>

6、login.html:

<!DOCTYPE html><html><head><title>login</title></head><body><div class="user"><input class="users" type="text" name="user"></div><div class="password"><input class="psd" type="password" name="password"></div><div class="login"><button class="login">login</button></div><div class="tip"></div></body><script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-2.1.4.min.js"></script><script type="text/javascript">$(function(){$(‘.login‘).on(‘click‘,function(){var user=$(‘.users‘).val();var psd=$(‘.psd‘).val();var userInfo={user:user,psd:psd}console.log(userInfo)$.ajax({url:‘/loginif‘,dataType:‘json‘,data:{userinfo:userInfo},type:‘post‘,success:function(msg){var state=msg[0].num;console.log(state)if(state>=1){window.location.href=‘/list‘}else{$(‘.tip‘).html(‘name or password wrong‘)}},error:function(err){console.log(err)}})})})</script></html>

　　

nodejs使用案例-mysql操作