Node 连接Mysql 通过接口返回数据

1、创建项目

// 创建 package.json
npm init 
// 安装依赖
npm i body-parser

npm i express

npm i mysql

2、目录结构

3、创建 config.js 文件 并导出

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 09:47:10
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 10:51:34
 */
const bodyParser = require('body-parser') // 中间价
//const importControls = require('./controller/index')
var appConfig = (app) => {
  app.use(bodyParser.json())
  app.use(bodyParser.urlencoded({ extended: true }))
  app.all('*', function (req, res, next) {
    res.header('Access-Control-Allow-Origin', '*')
    res.header('Access-Control-Allow-Headers', 'X-Requested-With,Content-Type')
    res.header('Access-Control-Allow-Methods', 'PUT,POST,GET,DELETE,OPTIONS')
    next()
  })
  //importControls(app)

  // 设置端口号
  const port = process.env.PORT || 7878
  app.listen(port)

  // 输出
  console.log('http://localhost:' + port)
}

module.exports = appConfig

4、创建api.js 文件 引入config.js文件

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 09:46:52
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 09:47:20
 */
const express = require('express')
const app = express()
const config = require('./config')
config(app)

5、连接mysql 创建db文件夹，添加index.js文件 并导出

// 此链接方法 来源于百度
/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 10:04:23
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-02 09:44:56
 */
const mysql = require('mysql')
const dbConfig = {
  host: 'localhost', // 主机地址
  port: 3306, //端口号
  user: 'user', // 用户名
  password: 'qwe123123', // 密码
  database: 'psmart', // 数据库名
}
// 连接数据库
let executeSql = (sql, params) => {
  return new Promise((resolve, reject) => {
    // 连接池（保持连接）
    const pool = mysql.createPool(dbConfig)
    pool.getConnection((err, conn) => {
      err
        ? reject(err)
        : conn.query(sql, params, (err, data, fields) => {
            err ? reject(err) : resolve(data)
          })
      conn.release() // 仅释放未关闭
      // pool.end() // 关闭连接
    })
  })
}
module.exports = {
  executeSql,
}

6、创建controller 文件夹 创建test.js 文件 做一个未连接数据库demo的接口,并导出

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 10:17:31
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 10:20:39
 */
const test = async (app) => {
  const data = [{ a: 1 }]
  app.get('/test', async (req, res, next) => {
    await res.send(data)
  })
}

module.exports = test

7、在controller文件夹下创建index.js 引入test.js文件、

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 10:16:23
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 10:35:41
 */
const test = require('./test')
var appConfig = (app) => {
  test(app)
}

module.exports = appConfig

8、在config.js中引入 controller 下的index.js文件

const importControls = require('./controller/index')
importControls(app)
// 序号3 注释部分

9、运行 node app.js ,在Postman中调用

10、创建dbTest.js 文件 连接数据库 增 删 改 查 demo 操作数据库 并导出

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 10:22:20
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 17:00:38
 */
const { executeSql } = require('../db')

const dbTest = async (app) => {
  app.post('/getAllList', async (req, res, next) => {
    const data = await getUser(req.body)
    res.send(data)
  })
  app.post('/saveForm', async (req, res, next) => {
    const data = await insertData(req.body)
    res.send(data)
  })
  app.post('/editData', async (req, res, next) => {
    const data = await editData(req.body)
    res.send(data)
  })
  app.post('/deleteData', async (req, res, next) => {
    const data = await deleteData(req.body)
    res.send(data)
  })
}

// 查询
const getUser = async (obj) => {
  let sql = 'SELECT * FROM sysuser WHERE 1=1'
  if (obj.personnelName) {
    sql += ` and personnelName like '%${obj.personnelName}%'`
  }
  if (obj.personnelId) {
    sql += ` and personnelId like '%${obj.personnelId}%'`
  }
  let res = await executeSql(sql)
  return res
}
// 新增
const insertData = async (obj) => {
  let sql = 'INSERT INTO sysuser set ?'
  let res = await executeSql(sql, obj)
  return res
}
// 修改
const editData = async (obj) => {
  let sql = `UPDATE sysuser SET ? where  uuid= '${obj.uuid}'`
  let res = await executeSql(sql, obj)
  return res
}
// 删除
const deleteData = async (obj) => {
  let sql = `delete from sysuser where uuid = '${obj.uuid}'`
  let res = await executeSql(sql, obj)
  return res
}

module.exports = dbTest

11、在index.js文件里引入 dbTest.js 文件

/*
 * @Descripttion: 菜单名称
 * @version: 0.0.1
 * @Author: PengShuai
 * @Date: 2023-03-01 10:16:23
 * @LastEditors: PengShuai
 * @LastEditTime: 2023-03-01 10:35:41
 */
const test = require('./test')
const dbTest = require('./dbTest')
var appConfig = (app) => {
  test(app)
  dbTest(app)
}

module.exports = appConfig

12、node app.js 运行 ,在Postman中调用查询