【案例实战】NodeJS+Vue3+MySQL实现列表查询功能_nodejs分页查询和获取文章列表接口

这篇文章，给大家带来一个列表查询的功能，从前端到后端的一个综合案例实战。

采用vue3作为前端开发，nodejs作为后端开发。

首先我们先来看一下完成的页面效果。点击分页，可以切换到上一页、下一页。搜索框可以进行模糊查询。

后端项目开发

好的，那么看完项目的演示，我们先来开发一下后端的逻辑。

后端需要开发的功能那个很简单，就是一个列表分页查询的接口。当然也可以用java语言去开发，我们这里就直接用nodejs作为一个后端语言去开发列表分页查询的接口。

1.创建一个后端的项目

（1）初始化项目

npm init -y
1

（2）下载express框架，对于express其实就是创建http服务的一款框架

cnpm i express@4.17.3 -S
1

（3）安装解析接收参数的中间件

cnpm i body-parser@1.19.2 -S
1

（4）下载跨域cors配置

cnpm i cors@2.8.5 -S
1

（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");
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

控制台输入node app.js,正常运行

mac@bogon product_server % node app.js 
server run in http://127.0.0.1:8090
1
2

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
1
2
3
4
5
6

部署完成之后，采用可视化工具连接。

创建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;
1
2
3
4
5
6
7
8
9
10
11

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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

（3）下载mysql的依赖

cnpm i mysql@2.18.1 -S
1

（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;
1
2
3
4
5
6
7
8
9
10
11

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
        },
      });
    });
  });
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

（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;
1
2
3
4
5
6
7

（3）app.js配置路由

//配置路由
const productRouter = require("./router/course.js");
app.use("/course", productRouter);
1
2
3

整体的目录结构：

（4）访问接口测试

http://127.0.0.1:8090/course/api/v1/page?page=1&size=10&title
1

前端项目开发

前端采用vue3+elementUI开发。

1.前端项目搭建

（1）安装脚手架vue/cli

npm install -g @vue/cli@5.0.4
1

（2）下载elementUI组件

cnpm install element-plus@2.1.11 -S
cnpm install -D unplugin-vue-components@0 unplugin-auto-import@0
1
2

（3）安装less预处理器

cnpm i less@4.1.2 less-loader@7 -S
1

（4）搭建前端项目

vue create product_web
1

（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()],
      }),
    ],
  },
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

（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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

（7）安装路由插件

cnpm i vue-router@4.0.14 -S
1

（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');
1
2
3
4
5
6
7
8
9
10
11

（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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

（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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

这里面有两个组件需要创建一下

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

（2）创建api中index.js

import request from '../utils/request';

/**
 * 课程列表数据接口
 */
export const getCourse = (data) => {
  return request({ method: 'get', url: '/course/api/v1/page', data });
};
1
2
3
4
5
6
7
8

4.启动项目

npm run serve
1

当问页面

Ok,完成。源代码博主放在主页的资源上了，需要的可以下载哦，记得给博主三连啊！