node 实现对mysql数据库数据增删改查

最新推荐文章于 2024-07-02 17:52:16 发布

·暮雨·

最新推荐文章于 2024-07-02 17:52:16 发布

阅读量457

点赞数

分类专栏： node.js 文章标签： node.js

本文链接：https://blog.csdn.net/qq_43816654/article/details/121687794

版权

node.js 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

说明：本文通过界面点击再以ajax的形式进行数据交互,里面还有文件上传的代码。

代码示例

index.html

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Document</title>
</head>
<body>
<button id="btn_keyvalue"> post-传递普通键值对</button>
<hr/>
<button id="btn_json"> post-传递json</button>

<button id="btn_user"> 查询用户信息</button>
<button id="btn_insert_user"> 插入用户信息</button>
<button id="btn_up_user">更新用户信息</button>
<button id="btn_del_user">移除用户信息</button>
<hr/>
<form id="myform">
  <input type="text" name="title">
  <input type="file" name="cover">
</form>
<button id="btn_formdata">post-传递formdata</button>
<hr/>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
  // 最普通的键值对格式
  $('#btn_keyvalue').click(function () {
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/queryServer',
      data: {a: 1, b: 2},
      success(res) {
        console.log(res);
      }
    })
  })
  var obj = {
    "name": "abc",
    "address": {
      "a": 1,
      "b": 2,
      "info": "c"
    }
  }

  // 传递复杂的JSON对象数据
  $('#btn_json').click(function () {
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/queryDataJson',
      // contentType: false,
      contentType: "application/json; charset=UTF-8",
      data: JSON.stringify(obj),
      success(res) {
        console.log(res);
      }
    })
  })

  // 查询用户信息
  $('#btn_user').click(function () {
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/queryUser',
      data: {
        pageSize: 1,
        pageNum: 5
      },
      success(res) {
        console.log(res);
      }
    })
  })
  // 插入用户信息
  $('#btn_insert_user').click(function () {
    $.ajax({
      type: 'get',
      url: 'http://localhost:8088/kVal/insertUser',
      success(res) {
        console.log(res);
      }
    })
  })

  // 更新用户信息根据id更新
  $('#btn_up_user').click(function () {
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/updateUser',
      data: {
        id: 5
      },
      success(res) {
        console.log(res);
      }
    })
  })

  // 移除用户信息根据id移除
  $('#btn_del_user').click(function () {
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/removeUser',
      data: {
        id: 6
      },
      success(res) {
        console.log(res);
      }
    })
  })
  // 传formData表单数据
  $('#btn_formdata').click(function () {
    var fd = new FormData(document.getElementById('myform'));
    $.ajax({
      type: 'post',
      url: 'http://localhost:8088/kVal/admin/article_publish',
      contentType: false,
      processData: false,
      data: fd,
      success(res) {
        console.log(res);
      }
    })
  })
</script>
</body>
</html>

sql文件

-- 导出  表 test.user 结构
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL,
  `realname` varchar(50) NOT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 正在导出表  test.user 的数据：~5 rows (大约)
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT IGNORE INTO `user` (`id`, `username`, `password`, `realname`, `create_time`, `update_time`) VALUES
	(1, '张三', '123', '22', NULL, NULL),
	(2, 'nihao', '123', '33', NULL, NULL),
	(3, '哈哈哈', '123', '33', NULL, NULL),
	(4, '哈哈哈哈', '123', '66', NULL, NULL),
	(5, '王二', '123456789', '斯李', NULL, NULL);

mysqlUtils
主要连接数据库工具类

const mysql = require('mysql');
const connection = mysql.createConnection({
    host: '',
    user: 'root',
    password: '',
    port: '',
    database: 'test'
});
connection.connect();
let res;
exports.connectionMysql = function (sql,parms) {
    //查
    connection.query(sql, parms,function (err, result) {
        if (err) {
            res = "sql出错,请检查异常信息：" + err.message;
        }
        res = result;
    })
    return res;
    connection.end();  //为工具类的时候  这里不能退出
}

server.js

const express = require('express');
const router = express.Router();
router.use(express.static('public'))
const mysql = require('mysql');
const connection = require('../mysql/mysqlUtil');

// 使用包. 则在后续的post请求中
// 会自动加入req.body属性，这个属性中就包含了post请求所传入的参数
// 处理普通的键值对格式
// Content-Type: application/x-www-form-urlencoded
router.use(express.urlencoded())
// 处理JSON格式
// Content-Type: application/json;
router.use(express.json())
// 引入multer包
const multer = require('multer');
// 配置一下multer
// 如果本次post请求涉及文件上传，则上传到uploads这个文件夹下
// Content-Type: multipart/form-data;
var upload = multer({dest: 'upload/'})
// formDate
router.post('/admin/article_publish', upload.single('cover'), (req, res) => {
  //upload.single('cover')
  // 这里的cover就是在页面中表单元素中的name
  // <input type="file" name="cover" />
  // 把要上传文件放在指定的目录
  console.log(req.file);
  // 其它参数，还是在req.body中找
  console.log(req.body);
  res.send({code: 200, msg: '上传成功', info: req.file.path})
})
// 普通post 键值对
router.post('/queryServer', (req, res) => {
  // 希望在后端收到post传参
  console.log(req.body);
  let obj = req.body
  obj._t = Date.now();// 往里面增加数据
  console.log(obj);
  // 获取obj中的某个值
  console.log(obj.a);
  console.log(obj.b);
  console.log(obj._t);
  res.send({code: 200, data: req.body});
})
// JSON格式的数据
// Content-Type: application/json;
// router.post('/queryDataJson',(req,res)=>{
//    // 希望在后端收到post传参
//    console.log(req.body);
//    // res.send('/postJSON')
//    res.json( req.body )
// })

// 不知道为啥 接收不了
router.post('/queryDataJson', function (req, res) {
  //用req.body就能获取到json格式的数据
  console.log(req.body)

  //验证是否能取出其中的数据
  //以免出现看上去很像json其实是字符串的情况
  // console.log(req.body.data.info)
})

// 实现接口，返回所传入的参数，并附上上时间戳
router.get('/getapi', (req, res) => {
  // 通过 req.query快速获取传入的参数
  console.log(req.query);
  let obj = req.query
  obj._t = Date.now();
  // 设置接口响应数据
  res.send({code: 200, data: obj});
})

/**
 * 分页查询用户信息  携带参数使用post请求,无参使用get
 */
router.post('/queryUser', (req, res) => {
  // let page = req.body;
  // let pageSize = page.pageSize;
  // let pageNum = page.pageNum;

  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  //et sql = "SELECT * FROM user where id=?";
   let sql = "SELECT * FROM user limit ?,?";
  let parms = [page,pageSize];

  let result = connection.connectionMysql(sql, parms);
  res.send({code: 200, msg: "ok", data: result});
})

//     "id": 1,
//     "username": "张三",
//     "password": "123",
//     "realname": "22",
//     "create_time": null,
//     "update_time": null

router.get('/insertUser', (req, res) => {
  // 业务当中插入数据的时候可能需要查询校验，这里测试就不做校验了，直接插入
  let sql = "insert into user (username,password,realname) values (?,?,?)";
  let parms = ["李斯", "666666", "里斯不是李斯"]
  let result = connection.connectionMysql(sql, parms);
  res.send({code: 200, msg: "插入成功"});
})

router.post('/updateUser', (req, res) => {
  console.log(req.body.id);
  let id = req.body.id;
  let sql = "update user set username=?,password=?,realname=? where id=?";
  let parms = ["王二", "123456789", "斯李", id];
  let result = connection.connectionMysql(sql, parms);
  res.send({code: 200, msg: '更新' + id + "成功"});
})

//removeUser
router.post('/removeUser', (req, res) => {
  let sql = "delete from user where id=?";
  let parms = [req.body.id];
  let result = connection.connectionMysql(sql, parms);
  res.send({code: 200, msg: req.body.id + "删除成功"});
})
module.exports = router;

启动类index.js
业务类都在上面

const { app, pool, Result } =require('../mysql/connect')
const login = require('../routes/login')
const uploads=require('../routes/upload')
const kVal=require('../server/server')
app.all('*', (req, res, next) => {
  //这里处理全局拦截，一定要写在最上面   相当于java中的config  当中的拦截校验
  //设置允许跨域
  res.header("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Headers", "X-Requested-With");
  res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
  res.header("X-Powered-By",' 3.2.1');
  res.header("Content-Type", "application/json;charset=utf-8");
  next()
})
app.all('/', (req, res) => {
  pool.getConnection((err, conn) => {
    res.json({ type: 'test'})
    pool.releaseConnection(conn) // 释放连接池，等待别的连接使用
  })
})
// 登录请求
app.use('/login', login)
app.use("/upload",uploads);
app.use("/kVal",kVal);

//上传文件请求


app.listen(8088, () => {
  console.log('服务启动')
})

//访问路径  http://localhost:8088/login

package.json配置
这里如果使用hotnode 启动需要安装hotnode
可以参考我的安装hotnode 博客进行安装

https://blog.csdn.net/qq_43816654/article/details/121643646

{
  "name": "node",
  "version": "0.0.0",
  "private": true,
  "main": "index.js",
  "scripts": {
    "start": "hotnode ./routes/index.js"
  },
  "dependencies": {
    "cookie-parser": "~1.4.4",
    "debug": "~2.6.9",
    "ejs": "~2.6.1",
    "express": "~4.16.1",
    "formidable": "^2.0.1",
    "hashmap": "^2.4.0",
    "http-errors": "~1.6.3",
    "morgan": "~1.9.1",
    "multer": "^1.4.3",
    "mysql": "^2.18.1"
  }
}

源码地址：

https://gitee.com/FetterXxZzz/node-mysql

·暮雨·

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
node 实现对mysql数据库数据增删改查

说明：本文通过界面点击再以ajax的形式进行数据交互,里面还有文件上传的代码。代码示例index.html<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Comp
复制链接

扫一扫

专栏目录