NodeJs Express MySQL 搭建电影网站:第二期(MySQL 数据增删改查操作)

第一步：编辑相关页面路由信息，使用MySQL填充数据

编辑movie/routes/index.js 文件

var express = require('express'); 
var router = express.Router(); 
 
var db=require('./../db.js'); // 引入自定义DB 模块 
var bodyParser=require("body-parser");  //引入body-parser模块 
//创建application/x-www-form-urlencoded编码解析 
var urlencodedParser=bodyParser.urlencoded({extended:false}); 
 
//访问网站跟目录：localhost:3000/ 
router.get('/', function(req, res, next) { 
	db.DBConnection.query( 
	'select * from movie', 
	  function(err, result) { 
		return res.render('index.jade',{ 
			title:'网站首页', 
			movies: result 
		}); 
	  } 
	);  
}); 
 
//localhost:3000/movie/1 
router.get("/movie/:id",function(req,res){ 
	db.DBConnection.query( 
	'select * from movie where id = ?', 
	[req.params.id], // 路由参数获取:id 
	  function(err, result) { 
		console.log({ 
			title:'电影详情', 
			movies: result 
		}); 
		return res.render('detail.jade',{ 
			title:'电影详情', 
			movie: result[0] 
		}); 
	  } 
	);  
}); 
 
 
//localhost:3000/admin/add 
router.get("/admin/add",function(req,res){ 
    res.render('control.jade',{ 
        title:'后台电影添加页', 
        movie:{ 
            title:'', 
            director:'', 
            country:'',  
            language:'', 
            year:'', 
            poster:'', 
            summary:'', 
            flash:'' 
        } 
    }); 
}); 
 
 
//localhost:3000/admin/list 
router.get("/admin/list",function(req,res){ 
	db.DBConnection.query( 
	'select * from movie', 
	  function(err, result) { 
		return res.render('list.jade',{ 
			title:'后台电影列表', 
			movies: result 
		}); 
	  } 
	);  
}); 
 
//localhost:3000/admin/do 
router.post("/admin/do", urlencodedParser, function(req,res,next){ 
	var params =[ 
		req.body.title, 
        req.body.director, 
        req.body.country,  
        req.body.language, 
        '2019', 
        req.body.summary 
	] 
	console.log(params); 
	var query ='insert into movie (title,director,country,language,year,summary) values (?,?,?,?,?,?)'; 
	db.DBConnection.query(query,params,function(err,rows,fields){ 
		if(err){ 
			console.log(err);	 
		    return;		 
		}		 
		var success={			     
			message: '增加成功' 
		}; 
		 res.send(success);	 
	}); 
}); 
 
 
 
 
 
module.exports = router; 

第二步：编辑相关页面

在movie/views文件夹下修改如下文件：index.jade、detail.jade、list.jade、control.jade

control.jade:

extends ./layout.jade 
block content 
    .container 
        .row 
            form.form-horizontal(method="post",action="/admin/do") 
                //电影名字 
                .form-group 
                    label.col-sm-2.control-label(for="inputTitle") 电影名字： 
                    .col-sm-10 
                        input#inputTitle.form-control(type="text",name="title",value="#{movie.title}") 
                 //导演 
                .form-group 
                    label.col-sm-2.control-label(for="inputTitle") 导演： 
                    .col-sm-10 
                        input#inputDirector.form-control(type="text",name="director",value="#{movie.director}") 
                 //国家 
                .form-group 
                    label.col-sm-2.control-label(for="inputCountry") 国家： 
                    .col-sm-10 
                        input#inputCountry.col-sm-10.form-control(type="text",name="country",value="#{movie.country}") 
                 //语言 
                .form-group 
                    label.col-sm-2.control-label(for="inputLanguage") 语言： 
                    .col-sm-10 
                        input#inputLanguage.col-sm-10.form-control(type="text",name="language",value="#{movie.language}") 
                //上映年份 
                .form-group 
                    label.col-sm-2.control-label(for="inputYear") 上映年份： 
                    .col-sm-10 
                        input#inputYear.col-sm-10.form-control(type="text",name="year",value="#{movie.year}") 
                //简介 
                .form-group 
                    label.col-sm-2.control-label(for="inputSummary") 简介： 
                    .col-sm-10 
                        input#inputSummary.col-sm-10.form-control(type="text",name="summary",value="#{movie.summary}") 
                .form-group 
                    .col-sm-2 
                    .col-sm-10 
                        input.btn.btn-default(type="submit",name="submit")

detail.jade

extends ./layout.jade 
block content 
    .container 
        .row 
            form.form-horizontal(method="post",action="/admin/do") 
                //电影名字 
                .form-group 
                    label.col-sm-2.control-label(for="inputTitle") 电影名字： 
                    .col-sm-10 
                        input#inputTitle.form-control(type="text",name="title",value="#{movie.title}") 
                 //导演 
                .form-group 
                    label.col-sm-2.control-label(for="inputTitle") 导演： 
                    .col-sm-10 
                        input#inputDirector.form-control(type="text",name="director",value="#{movie.director}") 
                 //国家 
                .form-group 
                    label.col-sm-2.control-label(for="inputCountry") 国家： 
                    .col-sm-10 
                        input#inputCountry.col-sm-10.form-control(type="text",name="country",value="#{movie.country}") 
                 //语言 
                .form-group 
                    label.col-sm-2.control-label(for="inputLanguage") 语言： 
                    .col-sm-10 
                        input#inputLanguage.col-sm-10.form-control(type="text",name="language",value="#{movie.language}") 
                //上映年份 
                .form-group 
                    label.col-sm-2.control-label(for="inputYear") 上映年份： 
                    .col-sm-10 
                        input#inputYear.col-sm-10.form-control(type="text",name="year",value="#{movie.year}") 
                //简介 
                .form-group 
                    label.col-sm-2.control-label(for="inputSummary") 简介： 
                    .col-sm-10 
                        input#inputSummary.col-sm-10.form-control(type="text",name="summary",value="#{movie.summary}") 
                .form-group 
                    .col-sm-2 
                    .col-sm-10 
                        input.btn.btn-default(type="submit",name="submit")

index.jade

extends ./layout.jade 
block content 
 .container 
    .row 
        each item in movies 
           .col-md-4 
             .thumbnall 
                a(href="/movie/#{item.id}") 
                    img(src="#{item.poster}",alt="#{item.title}") 
                .caption 
                    h3 #{item.title} 
                    p: a.btn.btn-primary(href="/movie/#{item.id}") 查看详情

list.jade

extends ./layout.jade 
block content 
    .container 
        .row 
            table.table.table-hover.table-bordered 
                thead 
                    tr 
                        th 电影名字 
                        th 导演 
                        th 查看 
                        th 更新 
                        th 删除 
                tbody 
                    each item in movies 
                        tr 
                            td #{item.title} 
                            td #{item.director} 
                            td: a(target="_blank",href="../movie/#{item.id}") 查看 
                            td: a(target="_blank",href="../movie/update/#{item.id}") 修改 
                            td  
                                a.btn.btn-danger.del(type="button",href="/admin/delete?id=#{item.id}") 删除

第三步：在movie文件夹下，创建MySQL数据库模板(db.js)

//student 文件夹下 
var mysql=require("mysql"); 
const DB={	     
	host : '127.0.0.1',	     
	user : 'root',	     
	password : '123456',    
	database : 'movie' 
} 
const DBConnection=mysql.createConnection({ 
	    host:DB.host,	     
	    user:DB.user, 
	    password:DB.password, 
	    database:DB.database, 
	    multipleStatements:true 
}); 
DBConnection.connect(); 
module.exports.DBConnection=DBConnection;

项目结构图：

项目源码地址:https://github.com/zhouzhiwengang/express-demon/tree/master/movie