vue+node.js+mysql+Express(零基础搭建一)_《手把手教你vue3+node.js+expres+mysql环境搭建》-优快云博客

本文链接：https://blog.youkuaiyun.com/xm_w_xm/article/details/91411168

实现简单的增删改查

首先到这一步可以看我之前的写的vue搭建 https://blog.youkuaiyun.com/xm_w_xm/article/details/81982128

一、安装依赖

npm install element-ui vuex axios mysql express --save

在package.json 中看到依赖已经下好了，在main.js中加入

import Vue from 'vue'
import App from './App'
import router from './router'
import ElementUI from 'element-ui'
import axios from 'axios'
import * as util from './util'
Vue.prototype.$http = axios;
import 'element-ui/lib/theme-chalk/index.css'
import services from './servers/server_web/index.js'
Vue.use(ElementUI);
Vue.prototype.services = services;
Vue.prototype.util = util;
Vue.config.productionTip = false


new Vue({
  el: '#app',
  router,
  components: {
    App
  },
  template: '<App/>'
})

二、前后台分离

我在src 文件下创建了一个servers文件，下面有两个，一个是写后台的方法，一个是写前端的方法，这样可以实现前后台的分离

三、数据库

在数据库中添加一个用户表我们写一个获取用户信息的服务

四、后台

后台用的是http://expressjs.com/en/starter/installing.html 可以去官网了解

src/servers/server_ht/db.js

// 数据库连接配置
module.exports = {
  mysql: {
    host: 'localhost', // 新建数据库连接时的 主机名或ID地址 内容
    user: 'root',
    password: '123456', // root 密码
    database: 'localtest', // 数据库名
    port: '3306'
  }
}

src/servers/server_ht/index.js

const routerApi = require('./router');
const bodyParser = require('body-parser'); // post 数据是需要
const express = require('express');
const app = express();

app.use(bodyParser.json());

// 后端api路由
app.use('/api', routerApi);

// 监听端口
app.listen(3000);
console.log('success listen at port:3000......');

验证这个是否监听

src/servers/server_ht 在这个目录下 cmd 打开后 node index.js 然后他会报错

原因：这是因为我们工程运行的端口是8080，而后端程序运行的端口是3000，所以是跨域请求，要想请求成功，就要先在配置里设置一下代理

解决办法：在config/index.js

proxyTable: {
      '/api': {
        target: 'http://172.16.106.3:3000/api',
        changeOrigin: true,
        pathRewrite: {
          '^/api': ''
        }
      }
    },

'/api': 表示所有以 /api 为开头的请求，如我们的请求 this.$axios.get('/api/getAll')
target: 将所有以 /api 为开头请求转发到 http://localhost:3000/api
changeOrigin：默认是false：请求头中host仍然是浏览器发送过来的host，如果设置成true：发送请求头中host会设置成target
pathRewrite: 重写地址。 '^/api': '' 表示将以 /api 开头的请求的地址中的 '/api' 替换为 ''，
即 path = path.replace(/^/api/, '')
eg: this.&dollar;http.get('/api/getAll')
path = '/api/getArticle'
path = path.replace(/^/api/, '') = '/getAll'
这样目标请求就变成了 http://localhost:3000/api/getAll,
如果不写 pathRewrite，请求则为 http://localhost:3000/api/api/getAll所以也可以这样

验证这个是否监听

src/servers/server_ht 在这个目录下 cmd 打开后 node index.js ，成功后返回这个

src/servers/server_ht/router.js

const express = require('express');
const router = express.Router();
const api = require('./api');
// 获取用户列表
router.get('/getAll', (req, res, next) => {
  api.getAll(req, res, next);
});
// 获取单独一个用户
router.get('/getValue', (req, res, next) => {
  api.getValue(req, res, next);
});
//添加一个用户
router.post('/setValue', (req, res, next) => {
  api.setValue(req, res, next);
});
//编辑一个用户
router.post('/updataValue', (req, res, next) => {
  api.updataValue(req, res, next);
});
//删除一个用户
router.post('/deleteValue', (req, res, next) => {
  api.deleteValue(req, res, next);
});
module.exports = router;

src/servers/server_ht/sqlMap.js

var sqlMap = {
  // 查询类表
  getAll: 'SELECT * FROM user',
  // 获取详情
  getValue: 'SELECT * FROM user WHERE USERID = ?',
  // 新建数据
  setValue: 'INSERT INTO user(USERNAME,PASSWORD) values (?,?)',
  // 更新数据
  updataValue: 'update user set USERNAME=?,PASSWORD=? where USERID=?',
  // 删除数据
  deleteValue: 'DELETE FROM user WHERE USERID = ?'
}

module.exports = sqlMap;

src/servers/server_ht/api.js

const mysql = require('mysql');
const dbConfig = require('./db');
const sqlMap = require('./sqlMap');

const pool = mysql.createPool({
  host: dbConfig.mysql.host,
  user: dbConfig.mysql.user,
  password: dbConfig.mysql.password,
  database: dbConfig.mysql.database,
  port: dbConfig.mysql.port,
  multipleStatements: true // 多语句查询
});

module.exports = {
  // 获取列表
  getAll(req, res, next) {
    console.log(sqlMap.getAll, '获取列表')
    pool.getConnection((err, connection) => {
      connection.query(sqlMap.getAll, (err, result) => {
        // result = this.formatDate(result);
        res.json(result);
        connection.release();
        if (err) throw err;
      })
    })
  },
  // 获取详情
  getValue(req, res, next) {
    pool.getConnection((err, connection) => {
      connection.query(sqlMap.getValue, [req.query.id], (err, result) => {
        res.json(result);
        connection.release();
      })
    })
  },
  // 新建用户
  setValue(req, res, next) {
    console.log(req.query, '新建用户');
    pool.getConnection((err, connection) => {
      connection.query(sqlMap.setValue, [req.query.USERNAME, req.query.PASSWORD], (err, result) => {
        res.json(result);
        connection.release();
      })
    })
  },
  // 编辑用户
  updataValue(req, res, next) {
    console.log(req.query, '编辑用户');
    pool.getConnection((err, connection) => {
      connection.query(sqlMap.updataValue, [req.query.USERNAME, req.query.PASSWORD, req.query.USERID, ], (err, result) => {
        res.json(result);
        connection.release();
      })
    })
  },
  // 删除用户
  deleteValue(req, res, next) {
    console.log(req.query, '删除用户');
    pool.getConnection((err, connection) => {
      connection.query(sqlMap.deleteValue, [req.query.USERID], (err, result) => {
        res.json(result);
        connection.release();
      })
    })
  },
}

五、前端

src/servers/server_web/global.js

import axios from 'axios';
axios.defaults.timeout = 50000;
//请求时增加access-token
axios.interceptors.request.use(config => {
  return config;
}, function (err) {
  return Promise.reject(err);
});
axios.interceptors.response.use(response => {
  return response;
})
export default axios;

src/servers/server_web/index.js

import axios from './global'
export default {
  // 获取值test
  getValue(data) {
    return new Promise((resolve, reject) => {
      axios.get('/api/getValue' + "?" + data).then(res => {
        resolve(res)
        console.log("接口调用:", '---', 'getValue', '----', '/api/getValue', data, '----', res)
      }).catch(error => {
        reject(error);
      })
    })
  },
  // 获取所有用户信息
  getAll() {
    return new Promise((resolve, reject) => {
      axios.get('/api/getAll').then(res => {
        resolve(res)
        console.log("接口调用:", '---', 'getAll', '----', '/api/getAll', '----', res)
      }).catch(error => {
        reject(error);
      })
    })
  },
  // 新建数据
  setValue(data) {
    return new Promise((resolve, reject) => {
      axios.post('/api/setValue' + "?" + data).then(res => {
        resolve(res)
        console.log("接口调用:", '---', 'setValue', '----', '/api/setValue', +"?" + data, '----', res)
      }).catch(error => {
        reject(error);
      })
    })
  },
  //更新数据
  updataValue(data) {
    return new Promise((resolve, reject) => {
      axios.post('/api/updataValue' + "?" + data).then(res => {
        resolve(res)
        console.log("接口调用:", '---', 'updataValue', '----', '/api/updataValue', +"?" + data, '----', res)
      }).catch(error => {
        reject(error);
      })
    })
  },
  // 删除数据
  deleteValue(data) {
    return new Promise((resolve, reject) => {
      axios.post('/api/deleteValue' + "?" + data).then(res => {
        resolve(res)
        console.log("接口调用:", '---', 'deleteValue', '----', '/api/deleteValue', +"?" + data, '----', res)
      }).catch(error => {
        reject(error);
      })
    })
  },








}

六、页面

另外这个是拼接请求的字符串

// 拼接查询字符串 
export function createQuerystr(option) {
  var queryStr_arr = [];
  for (var key in option) {
    if (typeof option[key] == "object") {
      for (var val in option[key]) {
        if (option[key][val] !== "") {
          var k = JSON.stringify(option[key])
          var str = `${key}=${k}`
          queryStr_arr.push(str);
        }
      }
    } else {
      var str = `${key}=${option[key]}`;
      queryStr_arr.push(str);
    }
  }
  var queryStr = queryStr_arr.join("&");
  return queryStr
}

<template>
  <div class="hello">
    <div style="width:100%;float:left;text-align: left;">
      <el-button type="success" @click="dialogVisible= true">添加</el-button>
    </div>
    <!-- <div v-for="item in userdata" :key="item.id">姓名：{{item.USERNAME}}</div> -->
    <el-table :data="userdata" border style="width: 600px">
      <el-table-column type="index" width="50" label="序号"></el-table-column>
      <el-table-column prop="CJSJ" label="日期"></el-table-column>
      <el-table-column prop="USERNAME" label="姓名"></el-table-column>
      <el-table-column fixed="right" label="操作">
        <template slot-scope="scope">
          <el-button @click="getValue(scope.row)" type="text" size="small">编辑</el-button>
          <el-button @click="deleteValue(scope.row.USERID)" type="text" size="small">删除</el-button>
        </template>
      </el-table-column>
    </el-table>
    <el-dialog title="新建" :visible.sync="dialogVisible" width="600px">
      <span slot="footer" class="dialog-footer">
        <el-form
          :model="form"
          status-icon
          :rules="rules"
          ref="form"
          label-width="100px"
          class="demo-form"
        >
          <el-form-item label="用户名" prop="USERNAME">
            <el-input v-model.number="form.USERNAME"></el-input>
          </el-form-item>
          <el-form-item label="密码" prop="PASSWORD">
            <el-input type="password" v-model="form.PASSWORD" autocomplete="off"></el-input>
          </el-form-item>
          <el-form-item label="确认密码" prop="CHECKPASS">
            <el-input type="password" v-model="form.CHECKPASS" autocomplete="off"></el-input>
          </el-form-item>
        </el-form>
        <el-button @click="dialogVisible = false">取 消</el-button>
        <el-button type="primary" @click="saveValue">确 定</el-button>
      </span>
    </el-dialog>
  </div>
</template>

<script>
export default {
  name: "HelloWorld",
  data() {
    var validatePass = (rule, value, callback) => {
      if (value === "") {
        callback(new Error("请输入密码"));
      } else {
        if (this.form.CHECKPASS !== "") {
          this.$refs.form.validateField("CHECKPASS");
        }
        callback();
      }
    };
    var validatePass2 = (rule, value, callback) => {
      if (value === "") {
        callback(new Error("请再次输入密码"));
      } else if (value !== this.form.PASSWORD) {
        callback(new Error("两次输入密码不一致!"));
      } else {
        callback();
      }
    };
    return {
      userdata: [],
      userinfo: [],
      dialogVisible: false,
      form: {
        PASSWORD: "",
        CHECKPASS: "",
        USERNAME: ""
      },
      rules: {
        USERNAME: [
          { required: true, message: "请输入用户名", trigger: "blur" }
        ],
        PASSWORD: [{ validator: validatePass, trigger: "blur" }],
        CHECKPASS: [{ validator: validatePass2, trigger: "blur" }]
      }
    };
  },
  methods: {
    getValue(val) {
      let self = this;
      let params = {
        id: val.USERID
      };
      var str = this.util.createQuerystr(params);
      this.services.getValue(str).then(function(response) {
        console.log(response.data, "response.data");
        self.dialogVisible = true;
        self.form.USERID = response.data[0].USERID;
        self.form.USERNAME = response.data[0].USERNAME;
        self.form.PASSWORD = response.data[0].PASSWORD;
        // self.form.CHECKPASS = response.data[0].PASSWORD;
      });
    },
    getAll() {
      let self = this;
      this.services.getAll().then(function(response) {
        self.userdata = response.data;
      });
    },
    saveValue() {
      this.$refs["form"].validate(valid => {
        if (valid) {
          delete this.form.CHECKPASS;
          var str = this.util.createQuerystr(this.form);
          if (!this.form.USERID) {
            this.services.setValue(str).then(response => {
              this.getAll();
              this.dialogVisible = false;
              this.$message.success("成功");
            });
          } else {
            this.services.updataValue(str).then(response => {
              this.getAll();
              this.dialogVisible = false;
              this.$message.success("成功");
            });
          }
        } else {
          this.$message.error("错误");
          return false;
        }
      });
    },
    deleteValue(USERID) {
      this.$confirm("此操作将永久删除该文件, 是否继续?", "提示", {
        confirmButtonText: "确定",
        cancelButtonText: "取消",
        type: "warning"
      })
        .then(() => {
          let params = {
            USERID: USERID
          };
          var str = this.util.createQuerystr(params);
          this.services.deleteValue(str).then(response => {
            this.getAll();
            this.dialogVisible = false;
            this.$message.success("成功");
          });
        })
        .catch(() => {
          this.$message({
            type: "info",
            message: "已取消删除"
          });
        });
    }
  },
  mounted() {
    this.getAll();
  }
};
</script>
<style scoped>
</style>