在本指南中，我将使用Express.js和MySQL在Node.js中构建CRUD应用程序。我将创建一个 books 表，并将为books表实现CRUD。
目录
- 创建项目并安装依赖项
- 创建数据库，表并连接到数据库
- 制作CRUD路由
- 创建视图文件
- 将创建的文件导入到app.js
- 运行并查看输出
第1步：创建项目并安装依赖项
如果您没有安装Express应用程序生成器，请使用以下命令在全局范围内安装它：
# with NPM command
npm install -g express-generator
现在，我们将使用express应用程序生成器创建一个项目。转到项目目录并创建一个名为“
nodejs-crud ”的项目。
# create project
express --view=ejs nodejs-crud
# go to the project folder:
cd nodejs-crud
接下来，我们需要安装一些依赖项。让我们安装这些：
# to send flash message:
npm install express-flash --save
# to make session like PHP:
npm install express-session --save
# to send PUT and DELETE requests:
npm install method-override --save
# driver to connect Node.js with MySQL:
npm install mysql --save第2步：创建数据库，表并连接到数据库
创建一个数据库，然后创建一个books表。这是books表的表结构：
CREATE TABLE `books` (
`id` int(11) NOT NULL,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`author` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `books` ADD PRIMARY KEY (`id`);
ALTER TABLE `books` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;我们已经创建了一个数据库和表。让我们将项目连接到该数据库。在根项目目录中，创建一个名为“ lib ”的文件夹。在lib文件夹下，创建一个名为“ db.js ”的文件。
然后打开db.js文件并粘贴以下代码：
lib / db.js
var mysql = require('mysql');
var connection = mysql.createConnection({
host:'localhost',
user:'USERNAME_HERE',
password:'PASSWORD_HERE',
database:'DATABASE_NAME_HERE'
});
connection.connect(function(error){
if(!!error) {
console.log(error);
} else {
console.log('Connected..!');
}
});
module.exports = connection;不要忘记输入数据库凭据。
第3步：建立CRUD路由
在此步骤中，我们将注册CRUD路由：从books表中创建，读取，更新和删除book数据。转到routes文件夹，并创建一个名为“ books.js ”的文件。我们将在此文件中定义路由。
路线/books.js
var express = require('express');
var router = express.Router();
var dbConn = require('../lib/db');
// display books page
router.get('/', function(req, res, next) {
dbConn.query('SELECT * FROM books ORDER BY id desc',function(err,rows) {
if(err) {
req.flash('error', err);
// render to views/books/index.ejs
res.render('books',{data:''});
} else {
// render to views/books/index.ejs
res.render('books',{data:rows});
}
});
});
// display add book page
router.get('/add', function(req, res, next) {
// render to add.ejs
res.render('books/add', {
name: '',
author: ''
})
})
// add a new book
router.post('/add', function(req, res, next) {
let name = req.body.name;
let author = req.body.author;
let errors = false;
if(name.length === 0 || author.length === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and author");
// render to add.ejs with flash message
res.render('books/add', {
name: name,
author: author
})
}
// if no error
if(!errors) {
var form_data = {
name: name,
author: author
}
// insert query
dbConn.query('INSERT INTO books SET ?', form_data, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to add.ejs
res.render('books/add', {
name: form_data.name,
author: form_data.author
})
} else {
req.flash('success', 'Book successfully added');
res.redirect('/books');
}
})
}
})
// display edit book page
router.get('/edit/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('SELECT * FROM books WHERE id = ' + id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'Book not found with id = ' + id)
res.redirect('/books')
}
// if book found
else {
// render to edit.ejs
res.render('books/edit', {
title: 'Edit Book',
id: rows[0].id,
name: rows[0].name,
author: rows[0].author
})
}
})
})
// update book data
router.post('/update/:id', function(req, res, next) {
let id = req.params.id;
let name = req.body.name;
let author = req.body.author;
let errors = false;
if(name.length === 0 || author.length === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and author");
// render to add.ejs with flash message
res.render('books/edit', {
id: req.params.id,
name: name,
author: author
})
}
// if no error
if( !errors ) {
var form_data = {
name: name,
author: author
}
// update query
dbConn.query('UPDATE books SET ? WHERE id = ' + id, form_data, function(err, result) {
//if(err) throw err
if (err) {
// set flash message
req.flash('error', err)
// render to edit.ejs
res.render('books/edit', {
id: req.params.id,
name: form_data.name,
author: form_data.author
})
} else {
req.flash('success', 'Book successfully updated');
res.redirect('/books');
}
})
}
})
// delete book
router.get('/delete/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('DELETE FROM books WHERE id = ' + id, function(err, result) {
//if(err) throw err
if (err) {
// set flash message
req.flash('error', err)
// redirect to books page
res.redirect('/books')
} else {
// set flash message
req.flash('success', 'Book successfully deleted! ID = ' + id)
// redirect to books page
res.redirect('/books')
}
})
})
module.exports = router;因此，我们的CRUD路由已准备就绪。
第4步：建立视图文件
要显示所有书籍，添加和编辑书籍形式，我们需要创建三个视图文件。我们已经将EJS设置为模板。转到views文件夹并创建一个名为books的文件夹。在books文件夹下，制作三个文件，分别名为index.ejs，add.ejs和edit.ejs。然后复制以下代码并粘贴：
books/index.ejs
books / index.ejs
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Books</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<% if (messages.success) { %>
<div class="alert alert-success" role="alert"><%- messages.success %></div>
<% } %>
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
<ul class="nav nav-pills w-100">
<li class="nav-pill active">
<a class="nav-link">Books</a>
</li>
<li class="nav-pill ml-auto">
<a class="nav-link active" href="/books/add">Add Book</a>
</li>
</ul>
</div>
<div class="card-body">
<% if(data.length) { %>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Author</th>
<th width="200px">Action</th>
</tr>
</thead>
<tbody>
<% for(var i = 0; i< data.length; i++) { %>
<tr>
<th scope="row"><%= (i+1) %></th>
<td><%= data[i].name%></td>
<td><%= data[i].author%></td>
<td>
<a class="btn btn-success edit" href="../books/edit/<%=data[i].id%>">Edit</a>
<a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../books/delete/<%=data[i].id%>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
<% } %>
<!-- if result is empty -->
<% if(!data.length) { %>
<p class="text-center">No book found!</p>
<% } %>
</div>
</div>
</body>
</html>
books/add.ejs
书籍/add.ejs
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Add Book</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Add Book
</div>
<div class="card-body">
<form action="/books/add" method="post">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off">
</div>
<div class="form-group">
<label>Author:</label>
<input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Add"/>
</div>
</form>
</div>
</div>
</body>
</html>
books/edit.ejs
书籍/edit.ejs
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Edit Book</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Edit Book
</div>
<div class="card-body">
<form action="/books/update/<%= id %>" method="post">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off">
</div>
<div class="form-group">
<label>Author:</label>
<input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Update"/>
</div>
</form>
</div>
</div>
</body>
</html>第5步：将创建的文件导入到app.js
我们的项目即将完成。我们必须将所有创建的文件导入到app.js。我们需要导入以下行：
var flash = require('express-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = require('./lib/db');
var booksRouter = require('./routes/books');
app.use(session({
cookie: { maxAge: 60000 },
store: new session.MemoryStore,
saveUninitialized: true,
resave: 'true',
secret: 'secret'
}))
app.use(flash());
app.use('/books', booksRouter);导入所有文件后，app.js如下所示：
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 flash = require('express-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = require('./lib/db');
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var booksRouter = require('./routes/books');
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(session({
cookie: { maxAge: 60000 },
store: new session.MemoryStore,
saveUninitialized: true,
resave: 'true',
secret: 'secret'
}))
app.use(flash());
app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/books', booksRouter);
// 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;第6步：运行并查看输出
我们已经完成了所有步骤。现在该运行项目了。让我们运行项目并查看输出：
# run project:
npm start
# project URL:
http://localhost:3000
# books crud URL:
http://localhost:3000/books
复制
该项目的最终输出：
本教程已结束。
116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
