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