[Case Combat] NodeJS+Vue3+MySQL implements list query function

This article brings you a list query function and a comprehensive case study from front-end to back-end.

Use vue3 as front-end development and nodejs as back-end development.

First, let’s take a look at the completed page effect. Click paging to switch to the previous page and next page. The search box allows for fuzzy queries.

Back-end project development

Okay, so after reading the project demonstration, let’s first develop the back-end logic.

The function that needs to be developed on the backend is very simple, which is an interface for list paging query. Of course, it can also be developed in Java language. Here we will directly use nodejs as a back-end language to develop the interface for list paging query.

1. Create a back-end project

(1) Initialization project

npm init -y

(2) Download the express framework, which is actually a framework for creating http services

cnpm i [email protected] -S

(3) Install the middleware that parses the received parameters

cnpm i [email protected] -S

(4) Download cross-domain cors configuration

cnpm i [email protected] -S

(5) Create app.js

/**
 * Unified entrance
 */

//Introduce express and configure app
const express = require("express");
const app = express();

//Configure parameter parsing of post request
const bodyParser = require("body-parser");
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({<!-- --> extended: false }));

//Configure cross-domain settings
const cors = require("cors");
app.use(cors());

//Error middleware configuration
app.use((err, req, res, next) => {<!-- -->
  console.log(err);
  res.send({<!-- --> code: 500, msg: err.message, data: null });
});

//Start the service listening on port 8090
app.listen(8090, () => {<!-- -->
  console.log("server run in http://127.0.0.1:8090");
});
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

Enter node app.js in the console and run normally

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

2. Configure database

(1) Linux deployment mysql database

We use docker deployment. You can find a blog post on the Internet to deploy it. I won’t write too many deployment steps here.

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

After the deployment is completed, use visual tools to connect.

Create a video table and provide the script for everyone.

CREATE TABLE `video` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'course id',
  `title` varchar(524) DEFAULT NULL COMMENT 'Video title',
  `course_img` varchar(524) DEFAULT NULL COMMENT 'Cover image',
  `price` varchar(11) DEFAULT NULL COMMENT 'price, cents',
  `point` double(11,2) DEFAULT '8.70' COMMENT 'Default 8.7, maximum 10 points',
  `level` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Course classification',
  `del` int DEFAULT '0' COMMENT 'Whether to delete',
  `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 Senior Architect', 'https://oss-cdn.mashibing.com/default/2ac035f1b09412a514833d72bd23629b.png', '109', 9.8, 'Advanced', 0, '8372');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(4, 'Java Backend Engineer', 'https://oss-cdn.mashibing.com/default/ff0dbc7d6077b9656e3f68f8775d80de.png', '39', 9.2, 'Advanced', 0, '2389');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(5, 'Python full series master class', 'https://oss-cdn.mashibing.com/default/e1b96583ce902f8475d50fccd00583f3.png', '49', 9.4, 'Advanced', 0, '1231');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(6, 'AIoT Intelligent Internet of Things ', 'https://oss-cdn.mashibing.com/default/fdf4171e34e95446c1faaab9780a6a3c.png', '29', 9.2, 'Advanced', 0, '12331');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(7, 'AI Artificial Intelligence Algorithm Class', 'https://oss-cdn.mashibing.com/default/b1519e27e526abce071077a46155debe.png', '29', 9.4, 'Advanced', 0, '43123');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(8, 'Python full stack engineer', 'https://ksimage-cdn.mashibing.com/ee30a77db459480ab9e9dbca4110abb7.png', '3699', 9.9, 'Advanced', 0, '3241');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(9, 'Network Security Master Class', 'https://oss-cdn.mashibing.com/default/4839c97b8638ef5d01d55ee945f73346.png', '1699', 9.9, 'Advanced', 0, '2371');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(10, 'Web front-end architect', 'https://oss-cdn.mashibing.com/default/56d51db7d4e728b4bdd826871b31fcdd.png', '129', 9.9, 'Advanced', 0, '43983');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(11, 'Big Data Architect', 'https://oss-cdn.mashibing.com/default/89c03f0a7d557932b2c916896f840ac0.png', '158', 9.9, 'Advanced', 0, '8372');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(12, 'Embedded IoT Engineer', 'https://oss-cdn.mashibing.com/default/8a0d860ae085d665cba6500037a42660.png', '189', 9.9, 'Advanced', 0, '3874');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(13, 'Cloud Native Architect', 'https://oss-cdn.mashibing.com/default/0b611f56605230afa78e36d3fc28d7fe.png', '98', 9.9, 'Advanced', 0, '2321');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(14, 'AI artificial intelligence zero-based introductory class', 'https://oss-cdn.mashibing.com/default/93684399167651b31ed02224cdbc6f8a.jpg', '89', 9.9, 'Advanced', 0, '32431') ;
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(15, 'Game Backend Architect', 'https://ksimage-cdn.mashibing.com/c6726d23750140fa9fb917172462e427.png', '99', 9.9, 'Advanced', 0, '23543');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(16, 'C++ Software Development Engineer', 'https://oss-cdn.mashibing.com/default/f2fd73a74fa6465e3feaf41bb756457a.png', '1699', 9.9, 'Advanced', 0, '12332');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(17, 'Data analysis full-position practical class', 'https://oss-cdn.mashibing.com/default/440538433aa74ede1fbfe2945ec060c7.jpg', '68', 9.9, 'Advanced', 0, '12343');
INSERT INTO test.video
(id, title, course_img, price, `point`, `level`, del, learn_num)
VALUES(18, 'Dachang Algorithm Special Training Course', 'https://oss-cdn.mashibing.com/default/412befe796fed3a83d695185001944fb.jpg', '79', 9.2, 'Advanced', 0, '2341');
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(3) Download mysql dependencies

cnpm i [email protected] -S

(4) Configure database connection

//Introduce mysql configuration
const mysql = require('mysql');
//Create db instance
const db = mysql.createPool({<!-- -->
  host: '192.168.140.134',
  user: 'root',
  password: '123456',
  database: 'test',
});
//Export
module.exports = db;

3. Write a course query interface

(1) Create course_controller.js

//Introduce db configuration
const db = require("../config/db_config");

exports.page = (req, res) => {<!-- -->
  //Get the front-end parameters
  let {<!-- --> title, page, size } = req.query;
  page = (page - 1) * size;

  if (title.length == 0) {<!-- -->
    title = "";
  } else {<!-- -->
    title = `and title like '%${<!-- -->title}%'`;
  }
  //Query course list sql
  const pageSql = `select * from video where del=0 ${<!-- -->title} order by id limit ${<!-- -->page},${<!-- -->size} `;
  //Sql to query the total number of courses
  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
        },
      });
    });
  });
};
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(2) Create course.js under router

const express = require('express');
const router = express.Router();
const course_controller = require("../controller/course_controller");
//Query video list
router.get("/api/v1/page", course_controller.page);
//Export route
module.exports = router;

(3) app.js configuration routing

//Configure routing
const productRouter = require("./router/course.js");
app.use("/course", productRouter);

Overall directory structure:

(4) Access interface test

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

Front-end project development

The front-end is developed using vue3 + elementUI.

1. Front-end project construction

(1) Install scaffolding vue/cli

npm install -g @vue/[email protected]

(2) Download elementUI component

cnpm install [email protected] -S
cnpm install -D unplugin-vue-components@0 unplugin-auto-import@0

(3) Install less preprocessor

cnpm i [email protected] less-loader@7 -S

(4) Build front-end project

vue create product_web

(5) Configure 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, //Turn off ESlint verification
  configureWebpack: {<!-- -->
    plugins: [
      AutoImport({<!-- -->
        resolvers: [ElementPlusResolver()],
      }),
      Components({<!-- -->
        resolvers: [ElementPlusResolver()],
      }),
    ],
  },
});
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(6) Write public styles

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;
}
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(7) Install routing plug-in

cnpm i [email protected] -S

(8) element-plus icon automatically imports configuration, public style import, and routing import

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) Create routing file

import {<!-- --> createRouter, createWebHashHistory } from "vue-router";

//routing configuration
const router = createRouter({<!-- -->
  history: createWebHashHistory(), //Select hash route
  routes: [
    {<!-- -->
      path: "/",
      redirect: "/home",
    },
    {<!-- -->
      path: "/home",
      component: () => import("../views/Home"),
    }
  ],
});
export default router;
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(10) Create views folder and Home.vue file

First ensure that the project does not report errors.

2. Write a list page

(1) First write the Home.vue file, which involves the call of the interface. Next, we will write the request for this interface

<template>
  <div class="main">
    <el-form>
      <el-form-item>
        <el-input v-model.trim="inputValue" placeholder="Please enter content"></el-input>
      </el-form-item>
      <el-button type="primary" @click="handleClick">Query</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";
/**
 * Initialized data
 */
const data = reactive({<!-- -->
  list: [],
  page: 1, //The first page is displayed by default
  total: 5, //Total number of courses
});

//onMounted calls the interface once when loading for the first time
onMounted(() => {<!-- -->
  getCourseData();
});

/**
 * Course list data acquisition and course category switching logic
 */
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 });
  //Filter courses that match the classification
  data.list = res?.data.data.data;
  data.total = res?.data.data.total;
};

/**
 *Paging logic
 */
const currentChange = (val) => {<!-- -->
  if (val === "pre") {<!-- -->
    if (data.page > 1) {<!-- -->
      data.page--;
    } else {<!-- -->
      ElMessage({<!-- -->
        message: "It's already the first page!!!",
        type: "warning",
        showClose: true,
      });
    }
  }
  if (val === "next") {<!-- -->
    if (data.page < Math.ceil(data.total / 5)) {<!-- -->
      data.page + + ;
    } else {<!-- -->
      ElMessage({<!-- -->
        message: "This is the last page!!!",
        type: "warning",
        showClose: true,
      });
    }
  }

  //Interface for requesting courses
  getCourseData({<!-- --> title: data.title, page: data.page });
};

/**
 *Search box logic
 */
const inputValue = ref("");

//Search button
const handleClick = () => {<!-- -->
  getCourseData({<!-- --> title: inputValue.value });
  ElMessage({<!-- -->
    message: "Query successful",
    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>
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

There are two components that need to be created

Pagination.vue

<template>
  <div class="pagination">
    <div class="pre" @click="currentChange('pre')">Previous page</div>
    <div class="next" @click="currentChange('next')">Next page</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>
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

Table.vue

<template>
    <div class="table">
      <el-table :data="list">
        <el-table-column prop="course_img" label="image">
          <template #default="scope">
            <img :src="scope.row.course_img" class="courseImg-img">
          </template>
        </el-table-column>
        <el-table-column prop="title" label="title">
        </el-table-column>
        <el-table-column prop="price" label="price">
        </el-table-column>
        <el-table-column prop="point" label="rating">
        </el-table-column>
        <el-table-column prop="level" label="level">
        </el-table-column>
        <el-table-column prop="learn_num" label="Number of learners">
        </el-table-column>
        <el-table-column label="Operation">
          <template #default="scope">
            <el-button type="primary">
              edit
            </el-button>
            <el-popconfirm title="Are you sure you want to delete this course?">
              <template #reference>
                <el-button type="danger">Delete</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>
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

3. Create interface request

(1) Create request.js

import axios from 'axios';
/**
 * Create an axios instance
 */
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',
});

/**
 * Encapsulate request function
 */
const request = (options) => {<!-- -->
  if (options.method === 'get') {<!-- -->
    options.params = options.data;
  }
  return service(options);
};

export default request;
</code><img class="look-more-preCode contentImg-no-view" src="//i2.wp.com/csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreBlack.png" alt ="" title="">

(2) Create index.js in api

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

/**
 * Course list data interface
 */
export const getCourse = (data) => {<!-- -->
  return request({<!-- --> method: 'get', url: '/course/api/v1/page', data });
};

4. Start the project

npm run serve

Ask page

Ok, done. The source code blogger has placed it as a resource on the homepage. If you need it, you can download it. Remember to give it to the blogger three times!