Node.js（七）MySql+ajax

Api.js

const express=require("express");
const router=express.Router();
const mysql = require('mysql')
const mysqlutil=require("./mysql");

let pool=mysql.createPool({
    host:"localhost",  //主机
    user:"root",  //用户名
    password:"123456",  //密码
    database:"gomall"  //数据库,port可以省去
});
function add(bookList){
    let sql="insert into book set ?";
    pool.getConnection((connErr,conn)=>{  //获取连接对象
        pool.query(sql,bookList,(err,results)=>{//执行添加，指定参数对象
            if(err) throw err;  //如果有错误，则抛出异常
            console.log(results);  //输出结果
            conn.release();  //释放连接
            //process.exit(1);  //关闭应用
        });
    });
}



//查询所有
router.get("/",(req,res,next)=>{
    pool.getConnection((connErr,conn)=>{  //获取连接对象
        pool.query("select * from book",(err,results)=>{//执行查询
            if(err) throw err;  //如果有错误，则抛出异常
            console.log(JSON.parse(JSON.stringify(results)));  //转换并输出查询结果
            conn.release();  //释放连接
            res.json({status:"ok","data":results})
        });
    });
});

//添加
router.post("/",(req,res,next)=>{
    let book=req.body;
    console.log(book);
    let msg="";
    let sql="insert into book set ?";
    pool.getConnection((connErr,conn)=>{  //获取连接对象
        pool.query(sql,book,(err,results)=>{//执行添加，指定参数对象
            if(results.affectedRows>0){
                msg="添加成功！";
            }else{
                msg="添加失败！";
            }
            res.json({status:"ok","data":msg})
            conn.release();  //释放连接
        });
    });
})

//Delete
router.delete("/:id",(req,res,next)=> {
    let id=parseInt(req.params.id);
    let sql="delete from book where id=?";
    pool.getConnection((connErr,conn)=>{  //获取连接对象
        pool.query(sql,id,(err,results)=>{//执行查询
            if(err) throw err;  //如果有错误，则抛出异常
            if(results.affectedRows===1){  //如果影响行数为1
                console.log("删除成功");
                res.json({status:"ok","data":"删除成功"})
            }
            else{
                console.log("删除失败");
            }
            conn.release();  //释放连接
        });
    });
})

//修改
router.post("/edit/:id",(req,res,next)=>{
    let book=req.body;
    let id=parseInt(req.params.id);
    console.log(book);
    console.log(id);
    let sql="update book set ? where id=?";
    pool.getConnection((connErr,conn)=>{  //获取连接对象
        pool.query(sql,[book,id],(err,results)=>{//执行更新，指定参数
            if(err) throw err;  //如果有错误，则抛出异常
            if(results.affectedRows===1){  //如果影响行数为1
                console.log("修改成功");
                res.json({status:"ok","data":"修改成功"})
            }
            else{
                console.log("修改失败");
            }
            conn.release();  //释放连接
        });
    });
})


module.exports = router;

 

book.js

var express=require('express');
var router=express.Router();

router.get('/',function (req,res,next){
    res.render("book",{});
})


module.exports = router;

 

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var apiRouter = require('./routes/bookApi');
var bookRouter = require('./routes/book');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/index', indexRouter);
app.use('/users', usersRouter);
app.use('/api',apiRouter);
app.use('/', bookRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

 

book.ejs

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>图书管理系统</title>
</head>
<body>
<h1>图书管理系统</h1>
<div id="app">
    <table width="800" border="1" style="margin: 0 auto">
        <tr>
            <th>编号</th>
            <th>书名</th>
            <th>封面</th>
            <th>价格</th>
            <th>作者</th>
            <th>操作</th>
        </tr>
        <tr style="text-align: center" v-for="(book,i) in bookInfo">
            <td>{{book.id}}</td>
            <td>{{book.name}}</td>
            <td>{{book.img}}</td>
            <td>{{book.price}}</td>
            <td>{{book.author}}</td>
            <td>
                <a href="" @click.stop.prevent="del(book)">删除</a>
                <a href="" @click.stop.prevent="update(book)">编辑</a>
            </td>
        </tr>
    </table>
    <fieldset>
        <legend>添加图书</legend>
        <p>
            <label>
                书名
                <input type="text" v-model="books.name">
            </label>
        </p>
        <p>
            <label>
                封面
                <input type="text" v-model="books.img">
            </label>
        </p>
        <p>
            <label>
                价格
                <input type="text" v-model="books.price">
            </label>
        </p>
        <p>
            <label>
                作者
                <input type="text" v-model="books.author">
            </label>
        </p>
        <input type="hidden" v-model="id">
        <p>
            <button @click="save" type="button"  id="btnadd">添加</button>
            <button @click="edit" type="button" disabled  id="btnsubmit">保存</button>
        </p>
    </fieldset>
</div>
</body>
<script src="/javascripts/vue/vue.min.js"></script>
<script src="/javascripts/axios/axios.js"></script>
<script>
    var app = new Vue({
        el: "#app",
        data: {
            state:0,
            bookInfo: [],
            id:"",
            books: {name: "", img: "", price: "", author: ""},
        },
        methods: {
            del(book) {
                let id = (book.id);
                if (confirm("你确定要删除吗？")) {
                    axios.delete("http://127.0.0.1:3000/api/"+id)
                        .then(res => {
                            console.log("删除成功！");
                            this.BookInfo();
                        })
                        .catch(err => {
                            console.log("删除失败！");
                        })
                }
            },
            BookInfo() {
                axios.get("http://127.0.0.1:3000/api", {})
                    .then(data => {
                        this.bookInfo = data.data.data;
                    }).catch(error => {
                    console.log(error);
                });
            },
            save() {
                axios.post("http://127.0.0.1:3000/api", this.books)
                    .then(res => {
                        console.log("添加成功！");
                        this.BookInfo();
                        app.books={name:"",img:"",price:"",author:""};
                    })
                    .catch(err => {
                        console.log("添加失败！");
                    })
            },
            update(book){
                var btn=document.getElementById("btnsubmit");
                var btnadd=document.getElementById("btnadd");
                btn.removeAttribute('disabled');
                btnadd.disabled=true;
                this.id=book.id;
                this.books.name=book.name;
                this.books.img=book.img;
                this.books.price=book.price;
                this.books.author=book.author;
            },
            edit(){
                axios.post("http://127.0.0.1:3000/api/edit/"+this.id, this.books)
                    .then(res => {
                        console.log("修改成功！");
                        this.BookInfo();
                        var btn=document.getElementById("btnsubmit");
                        var btnadd=document.getElementById("btnadd");
                        btn.disabled=true;
                        btnadd.removeAttribute('disabled');
                        app.books={name:"",img:"",price:"",author:""};
                    })
                    .catch(err => {
                        var btn=document.getElementById("btnsubmit");
                        var btnadd=document.getElementById("btnadd");
                        console.log("添加失败！");
                        btn.disabled=true;
                        btnadd.removeAttribute('disabled');
                    })
            }
        },
        created: function () {
            this.BookInfo();
        }
    })
</script>
</html>