社区首页 >专栏 >使用 Node.js 将 json 存入 MySQL 数据库

使用 Node.js 将 json 存入 MySQL 数据库

圆号本昊

发布于 2021-09-24 04:00:48

5.3K00

代码可运行

文章被收录于专栏：github@hornhuanggithub@hornhuang

运行总次数：0

代码可运行

将 Android 客户端使用 okHttp 框架 push 的 json 数据存入数据库中：

首先要进行数据库的连接

var fs = require('fs');
var mysql = require('./mysql');

function home(response){
    response.writeHead(200,{'Content-Type':'text/html'});
    var myReaderStream = fs.createReadStream(__dirname + '/index.html', 'utf8');
    myReaderStream.pipe(response);
}

function review(response){
    response.writeHead(200,{'Content-Type':'text/html'});
    var myReaderStream = fs.createReadStream(__dirname + '/review.html', 'utf8');
    myReaderStream.pipe(response);
}

function api_records(response, params){
    response.writeHead(200,{'Content-Type':'application/json'});
    //params:为要传递的内容
    response.end(JSON.stringify(params));
}

// 数据库读取操作
function api_mysql_getifo(response, params){
    response.writeHead(200,{'Content-Type':'application/json'});
    //------------------------------------------------------------
    var mysql = require('mysql');

    //创建云服务器连接
    var connection = mysql.createConnection({
      host     : 'localhost',
      user     : 'root',
      password : '',
      database : 'aliyun'
    });

    // // 创建本地连接
    // var connection = mysql.createConnection({
    // host     : 'localhost',
    // user     : 'root',
    // password : 'qaz123456',
    // database : 'aliyuntext'
    // });

    mysql = require('./mysql');
    // save(connection);
    mysql.select(connection,response);
    
    // response.end("succeed!");
}

// 数据库输入操作
function api_mysql_postifo(response, params){
    response.writeHead(200,{'Content-Type':'application/json'});
    //**************************************************************
    var mysql = require('mysql');

    //创建云服务器连接
    var connection = mysql.createConnection({
      host     : 'localhost',
      user     : 'root',
      password : '',
      database : 'aliyun'
    });
    
    // // 创建本地连接
    // var connection = mysql.createConnection({
    //     host     : 'localhost',
    //     user     : 'root',
    //     password : 'qaz123456',
    //     database : 'aliyuntext'
    //     });

    mysql = require('./mysql');
    mysql.save(connection, response, params);
    // mysql.
}


module.exports = {
    home : home,
    review : review,
    api_records : api_records,
    api_mysql_getifo : api_mysql_getifo,
    api_mysql_postifo : api_mysql_postifo
}

具体的数据库操作（插入 / 查询等）

var mysql = require('mysql');

// 保存
var save = function save(connection, response, params){
  var myObj = JSON.parse((JSON.stringify(params)));
  var string = 'INSERT INTO users VALUES(' + myObj.id + ',' + myObj.name + ',' + myObj.age + ')';
  //插入数据
  var insertText = connection.query(string, function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
    response.end(JSON.stringify(results));
  });
  connection.end();
}


// 数据库查找
var select = function(connection, response) {
  connection.connect(function (err) {
      if (err) {
          console.error('error connecting:' + err.stack)
      }
      console.log('connected as id ' + connection.threadId);
  })
  connection.query('SELECT * FROM `users` where id = 2', function (error, results, fields) {
      if (error) throw error;
      console.log('The solution is:', results);
      response.end(JSON.stringify(results));
  });
  connection.end();
}


// 输出表中数据
function outputall(connection, response){
  // 输出表内数据：
  var outputText = connection.query('SELECT * FROM `users`', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
  });
}


// 修改表中数据
function modify(connection, response){
  connection.connect(function (err) {
      if (err) {
          console.error('error connecting:' + err.stack);
      }
      console.log('connected as id ' + connection.threadId);
  });
  connection.query('UPDATE demo SET name=?where id?', ['update', 1], function (error, results, fields) {
      if (error) throw error;
      console.log('changed:' + results.changeRows + 'rows');
  });
  connection.end();
}


// 删除
function deletes(connection, response) {
  connection.connect(function (err) {
      if (err) {
          console.error('error connecting:' + err.stack);
          return;
      }
      connection.query('DELETE FROM demo SET where id=?', [ 1], function (error, results, fields) {
          if (error) throw error;
          console.log('deleted:' + results.affectedRows + 'rows');
      });
      console.log('connected as id ' + connection.threadId);
      connection.end();
  });
}

module.exports = {
  save : save,
  select : select,
  outputall : outputall,
  modify : modify,
  deletes : deletes,
}