您的位置: 首页  >  文章  >  node+express+grunt+mysql操作数据库案例分析

node+express+grunt+mysql操作数据库案例分析

分类: 文章 • 2024-10-28 10:38:16

简单唠两句、

    在以前的node操作数据库，都是选择mongodb这类型的非关系型数据库，进行数据存储操作。而这次业务涉及到mysql，所以来简单了解下node环境下，是如何操作mesql数据库增删改查的？

一、安装

1. 开发依赖

yarn init
yarn add mysql
yarn add express
yarn global add add json-server # 本地服务工具
yarn global add add nodemon  / 或者 yarn add --dev grunt grunt-contrib-watch grunt-execute     # 自动化工具

2. 数据库工具：Navicat for MySQL

  **版下载地址：https://pan.baidu.com/s/1u8GGJ5AFFvAJKbXij4NyMA 提取码: vhzc

二、开发测试思路

采用json-server启动本地服务，默认启动public目录下index.html文件，在index.html文件中fetch请求express启动的服务，根据请求的对应路由操作mysql数据库

D:\me\npm\node-mysql>json-server db.json --port 3006

  \{^_^}/ hi!

  Loading db.json
  Done

  Resources
  http://localhost:3006/posts
  http://localhost:3006/comments
  http://localhost:3006/profile

  Home
  http://localhost:3006

  Type s + enter at any time to create a snapshot of the database


三、开发过程


1. 启动mysql服务，新建数据库，插入一些测试数据


（1）执行sql脚本：

CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  location varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO employees (id, name, location) VALUES
(1, 'Jasmine', 'Australia'),
(2, 'Jay', 'India'),
(3, 'Jim', 'Germany'),
(4, 'Lesley', 'Scotland');

（2）或者手动插入

node+express+grunt+mysql操作数据库案例分析


2. 新建/server.js文件、public/index.html文件


（1）server.js中开启服务，配置允许跨域请求，连接mysql数据库

// server.js
const mysql = require('mysql');
const app = require('express')();

//allow cross origin
app.all('*', function (req, res, next) {
  res.header('Access-Control-Allow-Origin', '*');
  //Access-Control-Allow-Headers ,可根据浏览器的F12查看,把对应的粘贴在这里就行
  res.header('Access-Control-Allow-Headers', 'Content-Type');
  res.header('Access-Control-Allow-Methods', '*');
  res.header('Content-Type', 'application/json;charset=utf-8');
  next();
});

// First you need to create a connection to the db
const con = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'node'
});

con.connect((err) => {
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});

con.end((err) => { //注：在end之后就不能操作数据库了！ });

(2) 启动node服务

C:\web\npm\node-mysql>node server.js
Connection established


(3) 根据需求或者个人喜好安装nodemon/grunt自动编译工具，如下是grunt的安装

2-3-1. 安装

C:\web\npm\node-mysql>cnpm install -g grunt grunt-contrib-watch grunt-execute

2-3-2. 查看版本

C:\web\npm\node-mysql>grunt --version
grunt-cli v1.2.0
grunt v1.0.3

2-3-3. 新建一个Gruntfile.js文件

module.exports = (grunt) => {
  grunt.initConfig({
    execute: {
      target: {
        src: ['./server.js']
      }
    },
    watch: {
      scripts: {
        files: ['./server.js'],
        tasks: ['execute'],
      },
    }
  });

  grunt.loadNpmTasks('grunt-contrib-watch');
  grunt.loadNpmTasks('grunt-execute');
};

2-3-4. 执行监听

C:\web\npm\node-mysql>grunt watch
Running "watch" task
Waiting...
>> File "server.js" changed.
Running "execute:target" (execute) task
-> executing C:\web\npm\node-mysql\server.js
Connected !
-> completed C:\web\npm\node-mysql\server.js (254ms)

>> 1 file and 0 calls executed (270ms)

Done.
Completed in 0.835s at Tue Oct 23 2018 21:44:08 GMT+0800 (中国标准时间) - Waiting...

（4）index.html文件中发出fetch请求服务，如下

fetch('http://localhost:3000/delete').then(res=>res.json()).then((res)=>{
    document.querySelector('#box').innerHTML += JSON.stringify(res);
});


2.查询所有数据

// 查询所有信息
app.get('/getAll', function(req, res){
    connection.query('SELECT * FROM employees', (err,rows) => {
        res.send(rows);
    });
});

3. 插入数据信息

app.get('/set', function(req, res){
    console.log('123');
    const employ = {name: '小虎的家乡', location: '九寨沟'};
    connection.query('INSERT INTO employees SET ?',employ, (err,doc) => {
        res.send(doc);
        //{"fieldCount":0,"affectedRows":1,"insertId":5,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
    });
});

4. 更新数据

app.get('/update', function(req, res){
    const employ = ['大明王朝的芹菜',1];
    connection.query('update employees SET location = ? where ID = ? ',employ, (err,doc) => {
        res.send(doc);
        //{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"(Rows matched: 1 Changed: 1 Warnings: 0","protocol41":true,"changedRows":1}
    });
});

5. 删除信息

app.get('/delete', function(req, res){
    connection.query('DELETE FROM employees WHERE id = ?',4, (err,doc) => {
        res.send(doc);
        //{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
    });
});

四、使用存储过程查询数据库

1. 首先需要在navicat中执行如下查询结构：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getall`()
BEGIN
  SELECT id, name, location FROM employees;
END

2. 然后在server.js中执行查询服务

connection.query('CALL sp_getall()',function(err,rows){
    if(err) throw err;

    console.log('Data received form Db: \n');
    console.log(rows);
});

3. 打印如下：

Data received form Db:

[ [ RowDataPacket { id: 1, name: 'Jasmine', location: 'Australia' },
    RowDataPacket { id: 2, name: 'Jay', location: 'India' },
    RowDataPacket { id: 3, name: 'Jim', location: 'Germany' },
    RowDataPacket { id: 4, name: 'Lesley', location: 'Scotland' },
    RowDataPacket { id: 5, name: null, location: null } ],
  OkPacket {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 34,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]


五、使用存储过程传递参数查询

1. 执行sql语句

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_employee_detail`(
  in employee_id int
)
BEGIN
  SELECT name, location FROM employees where id = employee_id;
END

2. server.js中执行查询

// 使用存储过程查询id为1的数据
connection.query('CALL sp_get_employee_detail(2)',function(err,rows){
    if(err) throw err;
    
    console.log('Data received form Db: \n');
    console.log(rows);
});

3. 打印如下：

Data received form Db:

[ [ RowDataPacket { name: 'Jasmine', location: 'Australia' } ],
  OkPacket {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 2,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]


五、使用存储过程插入数据操作

1. 执行sql语句

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_employee`(
  out employee_id int,
  in employee_name varchar(25),
  in employee_location varchar(25)
)
BEGIN
  insert into employees(name, location)
  values(employee_name, employee_location);
  set employee_id = LAST_INSERT_ID();
END

2. `query查询` （前置条件：需要把connect的连接参数对象中增加字段 multipleStatements: true ）

connection.query( "SET @employee_id = 0; CALL sp_insert_employee(@employee_id, 'tiger', 'forest'); SELECT @employee_id",function(err,rows){
    if(err) throw err;

    console.log('Data received form Db: \n');
    console.log(rows);
});

3. 打印如下：

Data received form Db:

[ OkPacket {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 10,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 },
  OkPacket {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 0,
    serverStatus: 10,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 },
  [ RowDataPacket { '@employee_id': 11 } ] ]


六、安全转义参数

const userLandVariable = 4;
connection.query(
  `SELECT * FROM employees WHERE id = ${mysql.escape(userLandVariable)}`,
  function(err, rows){ 
    console.log(rows);
  }
);


// [ RowDataPacket { id: 4, name: 'Lesley', location: 'Scotland' } ]


七、结论

  关于mysql的操作，还有很多相关的api，想参考更加详细的内容，请阅读：https://www.npmjs.com/package/mysql

 

无论什么时候，不管遇到什么情况，我绝不允许自己有一点点灰心丧气。—— 爱迪生

相关推荐