这篇文章，给大家带来一个列表查询的功能，从前端到后端的一个综合案例实战。
采用vue3作为前端开发，nodejs作为后端开发。
首先我们先来看一下完成的页面效果。点击分页，可以切换到上一页、下一页。搜索框可以进行模糊查询。
后端项目开发
好的，那么看完项目的演示，我们先来开发一下后端的逻辑。
后端需要开发的功能那个很简单，就是一个列表分页查询的接口。当然也可以用java语言去开发，我们这里就直接用nodejs作为一个后端语言去开发列表分页查询的接口。
1.创建一个后端的项目
（1）初始化项目
npm init -y
（2）下载express框架，对于express其实就是创建http服务的一款框架
cnpm i express@4.17.3 -S
（3）安装解析接收参数的中间件
cnpm i body-parser@1.19.2 -S
（4）下载跨域cors配置
cnpm i cors@2.8.5 -S
（5）创建app.js
/**
* 统一入口
*/
//引入express，配置app
const express = require("express");
const app = express();
//配置post请求的参数解析
const bodyParser = require("body-parser");
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
//配置跨域设置
const cors = require("cors");
app.use(cors());
//错误中间件配置
app.use((err, req, res, next) => {
console.log(err);
res.send({ code: 500, msg: err.message, data: null });
});
//启动8090端口监听的服务
app.listen(8090, () => {
console.log("server run in http://127.0.0.1:8090");
});
控制台输入node app.js,正常运行
mac@bogon product_server % node app.js
server run in http://127.0.0.1:8090
2.配置数据库
（1）linux部署mysql数据库
我们采用docker部署，大家可以在网上找一片博文去部署一下，这里就不再过多的去写部署步骤了。
docker run -p 3306:3306 --name mysql \
-v /usr/local/docker/mysql/conf:/etc/mysql \
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
部署完成之后，采用可视化工具连接。
创建video表，脚本给大家提供好。
CREATE TABLE `video` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '课程id',
`title` varchar(524) DEFAULT NULL COMMENT '视频标题',
`course_img` varchar(524) DEFAULT NULL COMMENT '封面图',
`price` varchar(11) DEFAULT NULL COMMENT '价格,分',
`point` double(11,2) DEFAULT '8.70' COMMENT '默认8.7，最高10分',
`level` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL COMMENT '课程分类',
`del` int DEFAULT '0' COMMENT '是否删除',
`learn_num` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb3;
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(1, 'MCA高级架构师', 'https://oss-cdn.mashibing.com/default/2ac035f1b09412a514833d72bd23629b.png', '109', 9.8, '高级', 0, '8372');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(4, 'Java后端工程师', 'https://oss-cdn.mashibing.com/default/ff0dbc7d6077b9656e3f68f8775d80de.png', '39', 9.2, '高级', 0, '2389');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(5, 'Python全系列大师课', 'https://oss-cdn.mashibing.com/default/e1b96583ce902f8475d50fccd00583f3.png', '49', 9.4, '高级', 0, '1231');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(6, 'AIoT智能物联网 ', 'https://oss-cdn.mashibing.com/default/fdf4171e34e95446c1faaab9780a6a3c.png', '29', 9.2, '高级', 0, '12331');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(7, 'AI人工智能算法班', 'https://oss-cdn.mashibing.com/default/b1519e27e526abce071077a46155debe.png', '29', 9.4, '高级', 0, '43123');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(8, 'Python全栈工程师', 'https://ksimage-cdn.mashibing.com/ee30a77db459480ab9e9dbca4110abb7.png', '3699', 9.9, '高级', 0, '3241');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(9, '网络安全大师课', 'https://oss-cdn.mashibing.com/default/4839c97b8638ef5d01d55ee945f73346.png', '1699', 9.9, '高级', 0, '2371');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(10, 'Web前端架构师', 'https://oss-cdn.mashibing.com/default/56d51db7d4e728b4bdd826871b31fcdd.png', '129', 9.9, '高级', 0, '43983');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(11, '大数据架构师', 'https://oss-cdn.mashibing.com/default/89c03f0a7d557932b2c916896f840ac0.png', '158', 9.9, '高级', 0, '8372');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(12, '嵌入式物联网工程师', 'https://oss-cdn.mashibing.com/default/8a0d860ae085d665cba6500037a42660.png', '189', 9.9, '高级', 0, '3874');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(13, '云原生架构师', 'https://oss-cdn.mashibing.com/default/0b611f56605230afa78e36d3fc28d7fe.png', '98', 9.9, '高级', 0, '2321');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(14, 'AI人工智能零基础入门班 ', 'https://oss-cdn.mashibing.com/default/93684399167651b31ed02224cdbc6f8a.jpg', '89', 9.9, '高级', 0, '32431');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(15, '游戏后端架构师', 'https://ksimage-cdn.mashibing.com/c6726d23750140fa9fb917172462e427.png', '99', 9.9, '高级', 0, '23543');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(16, 'C++ 软件开发工程师', 'https://oss-cdn.mashibing.com/default/f2fd73a74fa6465e3feaf41bb756457a.png', '1699', 9.9, '高级', 0, '12332');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(17, '数据分析全岗位实战班', 'https://oss-cdn.mashibing.com/default/440538433aa74ede1fbfe2945ec060c7.jpg', '68', 9.9, '高级', 0, '12343');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(18, '大厂算法特训班 ', 'https://oss-cdn.mashibing.com/default/412befe796fed3a83d695185001944fb.jpg', '79', 9.2, '高级', 0, '2341');
（3）下载mysql的依赖
cnpm i mysql@2.18.1 -S
（4）配置数据库连接
//引入mysql配置
const mysql = require('mysql');
//创建db实例
const db = mysql.createPool({
host: '192.168.140.134',
user: 'root',
password: '123456',
database: 'test',
});
//导出
module.exports = db;
3.编写课程查询接口
（1）创建course_controller.js
//引入db配置
const db = require("../config/db_config");
exports.page = (req, res) => {
//获取前端的参数
let { title, page, size } = req.query;
page = (page - 1) * size;
if (title.length == 0) {
title = "";
} else {
title = `and title like '%${title}%'`;
}
//查询课程列表sql
const pageSql = `select * from video where del=0 ${title} order by id limit ${page},${size}`;
//查询课程总数的sql
const totalSql = `select count(*) as total from video where del=0 ${title}`;
db.query(pageSql, (err, pageData) => {
if (err) {
throw new Error(err.message);
}
db.query(totalSql, (err, count) => {
if (err) {
throw new Error(err.message);
}
res.send({
code: 200,
msg: "",
data: {
data: pageData,
total: count[0].total,
pages:0
},
});
});
});
};
（2）创建router下的course.js
const express = require('express');
const router = express.Router();
const course_controller = require("../controller/course_controller");
//查询视频列表
router.get("/api/v1/page", course_controller.page);
//导出路由
module.exports = router;
（3）app.js配置路由
//配置路由
const productRouter = require("./router/course.js");
app.use("/course", productRouter);
整体的目录结构：
（4）访问接口测试
http://127.0.0.1:8090/course/api/v1/page?page=1&size=10&title
前端项目开发
前端采用vue3+elementUI开发。
1.前端项目搭建
（1）安装脚手架vue/cli
npm install -g @vue/cli@5.0.4
（2）下载elementUI组件
cnpm install element-plus@2.1.11 -S
cnpm install -D unplugin-vue-components@0 unplugin-auto-import@0
（3）安装less预处理器
cnpm i less@4.1.2 less-loader@7 -S
（4）搭建前端项目
vue create product_web
（5）配置vue.config.js
const { defineConfig } = require('@vue/cli-service');
const AutoImport = require('unplugin-auto-import/webpack');
const Components = require('unplugin-vue-components/webpack');
const { ElementPlusResolver } = require('unplugin-vue-components/resolvers');
module.exports = defineConfig({
transpileDependencies: true,
lintOnSave: false, //关闭ESlint校验
configureWebpack: {
plugins: [
AutoImport({
resolvers: [ElementPlusResolver()],
}),
Components({
resolvers: [ElementPlusResolver()],
}),
],
},
});
（6）编写公共样式
html,
body {
padding: 0;
margin: 0;
background-color: #f5f5f5;
}
html,
body,
#app {
height: 100%;
}
input {
border: none;
outline: none;
}
button {
border: none;
outline: none;
}
a {
text-decoration: none;
color: #333;
}
li {
list-style-type: none;
}
（7）安装路由插件
cnpm i vue-router@4.0.14 -S
（8）element-plus图标自动引入配置，公共样式引入，以及路由引入
import { createApp } from 'vue'
import App from './App.vue'
import './common/base.css';
import * as elementIcons from '@element-plus/icons-vue';
import router from './router/index';
const app = createApp(App);
for (let iconName in elementIcons) {
app.component(iconName, elementIcons[iconName]);
}
app.use(router).mount('#app');
（9）创建路由文件
import { createRouter, createWebHashHistory } from "vue-router";
//路由配置
const router = createRouter({
history: createWebHashHistory(), //选择hash路由
routes: [
{
path: "/",
redirect: "/home",
},
{
path: "/home",
component: () => import("../views/Home"),
}
],
});
export default router;
（10）创建views文件夹，创建Home.vue文件
先保证项目不报错。
2.编写列表页面
（1）首先编写Home.vue文件,这里涉及接口的调用下面我们在编写这个接口的请求
<template>
<div class="main">
<el-form>
<el-form-item>
<el-input v-model.trim="inputValue" placeholder="请输入内容"></el-input>
</el-form-item>
<el-button type="primary" @click="handleClick">查询</el-button>
</el-form>
<Table :list="data.list" />
<Pagination :currentChange="currentChange"></Pagination>
</div>
</template>
<script setup>
import Table from "./components/Table.vue";
import Pagination from "./components/Pagination.vue";
import { reactive, ref,onMounted } from "vue";
import { getCourse } from "../api/index";
/**
* 初始化的数据
*/
const data = reactive({
list: [],
page: 1, //默认展示第一页
total: 5, //课程总数
});
//onMounted首次加载调用一次接口
onMounted(() => {
getCourseData();
});
/**
* 课程列表数据获取和课程类目切换逻辑
*/
const getCourseData = async (query) => {
const title = query?.title || "";
const page = query?.page || 1;
const size = query?.size || 5;
const res = await getCourse({ title, page, size });
//筛选符合分类的课程
data.list = res?.data.data.data;
data.total = res?.data.data.total;
};
/**
* 分页的逻辑
*/
const currentChange = (val) => {
if (val === "pre") {
if (data.page > 1) {
data.page--;
} else {
ElMessage({
message: "已经是第一页了！！！",
type: "warning",
showClose: true,
});
}
}
if (val === "next") {
if (data.page < Math.ceil(data.total / 5)) {
data.page++;
} else {
ElMessage({
message: "已经是最后一页了！！！",
type: "warning",
showClose: true,
});
}
}
//请求课程的接口
getCourseData({ title: data.title, page: data.page });
};
/**
* 搜索框的逻辑
*/
const inputValue = ref("");
//搜索的按钮
const handleClick = () => {
getCourseData({ title: inputValue.value });
ElMessage({
message: "查询成功",
type: "success",
});
};
</script>
<style lang="less" scoped>
.el-form {
display: flex;
}
.main {
background-color: #fff;
padding: 20px;
flex: 1;
display: flex;
flex-direction: column;
.input-with-select {
width: 400px;
margin-bottom: 40px;
}
}
:deep(.el-table__header-wrapper) {
position: fixed;
z-index: 20;
}
:deep(.el-table__inner-wrapper) {
overflow: hidden;
}
:deep(.el-table__body-wrapper) {
margin-top: 40px;
}
:deep(.el-input__inner) {
width: 300px;
margin-right: 10px;
}
:deep(.warning-row) {
--el-table-tr-bg-color: var(--el-color-warning-light-9) !important;
height: 140px !important;
}
.table {
height: 80vh;
width: 98vw;
overflow: hidden;
overflow-y: scroll;
text-align: center;
}
.table::-webkit-scrollbar {
display: none;
}
</style>
这里面有两个组件需要创建一下
Pagination.vue
<template>
<div class="pagination">
<div class="pre" @click="currentChange('pre')">上一页</div>
<div class="next" @click="currentChange('next')">下一页</div>
</div>
</template>
<script setup>
import { defineProps } from 'vue';
const { currentChange } = defineProps(['currentChange'])
</script>
<style lang='less' scoped>
.pagination {
display: flex;
justify-content: center;
margin-top: 20px;
color: #fff;
.pre {
background-color: #409eff;
margin-right: 10px;
padding: 5px;
cursor:pointer;
}
.next {
padding: 5px;
background-color: #409eff;
cursor:pointer;
}
}
</style>
Table.vue
<template>
<div class="table">
<el-table :data="list">
<el-table-column prop="course_img" label="图片">
<template #default="scope">
<img :src="scope.row.course_img" class="courseImg-img">
</template>
</el-table-column>
<el-table-column prop="title" label="标题">
</el-table-column>
<el-table-column prop="price" label="价格">
</el-table-column>
<el-table-column prop="point" label="评分">
</el-table-column>
<el-table-column prop="level" label="级别">
</el-table-column>
<el-table-column prop="learn_num" label="学习人数">
</el-table-column>
<el-table-column label="操作">
<template #default="scope">
<el-button type="primary">
编辑
</el-button>
<el-popconfirm title="确定要删除该课程吗?">
<template #reference>
<el-button type="danger">删除</el-button>
</template>
</el-popconfirm>
</template>
</el-table-column>
</el-table>
</div>
</template>
<script setup>
import { defineProps } from 'vue';
const { list } = defineProps(['list'])
</script>
<style lang='less' scoped>
.courseImg-img {
width: 150px;
height: 100px;
}
</style>
3.创建接口请求
（1）创建request.js
import axios from 'axios';
/**
* 创建axios实例
*/
const ENV = process.env.NODE_ENV;
const host =
ENV === 'development' ? 'http://127.0.0.1:8090' : 'http://192.168.140.134:8090';
const service = axios.create({
baseURL: host,
timeout: '3000',
});
/**
* 封装请求函数
*/
const request = (options) => {
if (options.method === 'get') {
options.params = options.data;
}
return service(options);
};
export default request;
（2）创建api中index.js
import request from '../utils/request';
/**
* 课程列表数据接口
*/
export const getCourse = (data) => {
return request({ method: 'get', url: '/course/api/v1/page', data });
};
4.启动项目
npm run serve
当问页面
Ok,完成。源代码博主放在主页的资源上了，需要的可以下载哦，记得给博主三连啊！文章来源：https://www.toymoban.com/news/detail-736024.html
文章来源地址https://www.toymoban.com/news/detail-736024.html
到了这里，关于【案例实战】NodeJS+Vue3+MySQL实现列表查询功能的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
