NodeJS+Express+Mysql+MongoDB之环境配置

node作为一款可以兼容前后端的js语言,在做持久层操作上和Java比较类似,下面就简单介绍一下项目中的数据库配置操作.

首选使用express框架自动创建一个测试项目,并在目录下建立一个专门存放数据库配置的配置文件,比如:db.js  代码如下 

/*
* 数据库配置文件
* @Author: zth
* @Date:   2019-02-18 10:50:45
* @Last Modified by:   zth
* @Last Modified time: 2019-03-05 11:35:42
*/

// Mysql 配置项
var mysql = require("mysql");
var pool = mysql.createPool({
  host:"localhost",
  user:"root",
  password:"123456",
  database:"elink_platform"
});

/**
 * mysql查询类
 * @Author   zth
 * @DateTime 2019-03-05T11:34:58+0800
 * @param    {string}                 sql      查询语句
 */
function query(sql,callback) {
  pool.getConnection(function(err,connection) {
    connection.query(sql, function (err,rows) {
      callback(err,rows);
      connection.release();
    });
  });
}

// MongoDB配置项
var MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017/farmDB';
const dbName = 'farmDB';  

/**
 * MongoDB查询类
 * @Author   zth
 * @DateTime 2019-03-05T10:23:09+0800
 * @param    {[string]}  collection [数据集合名称]
 * @param    {[json]}    query      [查询语句json对象]
 */
function find(collectionName, query) {
  MongoClient.connect(url, {useNewUrlParser:true}, function(err, client) { 
  const db = client.db(dbName);
  const collection = db.collection(collectionName);
  collection.find(query).toArray(function(err, docs) {
    console.log(docs);
  });
});
}

exports.query = query;
exports.find = find;

这里为了测试就简单创建两个查询的方法,以调试通过为目的.

其次在需要的路由文件中引入db.js,然后进行调用即可.

var express = require('express');
var router = express.Router();

var db = require("../config/db");

最后处理返回的结果集,这个和平时注册请求后端接口的数据处理一致.

router.get('/haveJobTypes', function(req, res, next) {
  console.log(JSON.stringify(req.query));
  var sql = "SELECT COALESCE (b.job_type_code,'sum') AS job_type_code,SUM(b.job_mu) AS job_mu FROM farm_job_info b LEFT JOIN farm_terminal_reg_info a ON a.car_id=b.car_id AND a.flag=1 AND b.flag=1 WHERE a.org_code IN (SELECT t1.CODE FROM sys_org_info t1,(SELECT @pcode :=CODE FROM sys_org_info WHERE id = " + req.query.orgId + ") t2 WHERE t1.CODE LIKE CONCAT(@pcode,'%')) AND b.job_date BETWEEN '" + req.query.startTime + "' AND '" + req.query.endTime + "' GROUP BY b.job_type_code WITH ROLLUP";

  db.query(sql, function(err, result){
    const obj = {
      status: err ? 1 : 0,
      data: err ? [] : result
    }
    res.json(obj);
  });

  var queryJson = { "carId": "HD146555" };
  db.find('terminal', queryJson, function(err, result) {
    console.log("result");
    console.log(result);
  })
});