Node + Express + MySQL 接口开发完整案例

先上目录结构：

简单描述一下，主要的目录文件
config /
dbConfig: 文件配置了，数据库信息
DBHelp: 数据库连接池的连接与释放
controller：放置开发的接口
routes: 接口抛出（接口路由层）
app 启动入口，配置接口跨域，返回字符编码···

dbConfig.js

const config = {
    // 启动端口
    port: 3000,
    // 数据库配置
    database: {
        DATABASE: 'test',
        USERNAME: 'root',
        PASSWORD: '密码',
        PORT: '3306',
        HOST: 'localhost',
        insecureAuth : true,
        useConnectionPooling: true
    }
}

module.exports = config

DBHelp.js

let DB_MYSQL = require('mysql');
let DB_CONFIG = require('../config/dbConfig');
/**
 * 数据库连接池
 * @type {Pool}
 */
let pool = DB_MYSQL.createPool({
    host: DB_CONFIG.database.HOST,
    user: DB_CONFIG.database.USERNAME,
    password: DB_CONFIG.database.PASSWORD,
    database: DB_CONFIG.database.DATABASE,
    port: DB_CONFIG.database.PORT
});
/**
 * 通用方法
 * @param sql
 * @param options
 * @param callback
 */
let query = (sql, options, callback) =>{
    pool.getConnection((error, connection) =>{
        if (error) {
            callback(error, null, null);
        } else {
            connection.query(sql, options, (error, results, fields) =>{
                //释放连接
                connection.release();
                //事件驱动回调
                callback(error, results, fields);
            });
        }
    });
};
module.exports=query;

controller/ ····

/**
 * 获取列表
 * @type {Connection}
 */
let _ = require('lodash')
let DBHelp = require('../../config/DBHelp.js');
let getArticle = (data, success) =>{
    // SQL 语句
    let SQL = 'SELECT * FROM websites';
    let counter = 0
    let SQL_ = SQL+' WHERE ';
    // 多个条件时循环data去除key, val
    _.forEach(data, (key, val) =>{
        if (counter > 0){
            SQL = SQL+ ' AND ' + val + ' = ' + key;
        } else {
            SQL = SQL_+ val + '= ' + key + ' OR name LIKE %' + key +'%';
        }
        counter++
    });
    /**
     * resultData
     * @code: 状态码
     * @data：data
     * @codeMessage: 状态消息
     */
    const resultData = {
        code: null,
        data: null,
        codeMessage: null
    }
    DBHelp(SQL, (error, result, fields) =>{
        if(error){
            resultData.error = error.message
        }
        resultData.code = 200;
        resultData.data = result;
        resultData.codeMessage = 'success'
        success(resultData);
    })


}
module.exports = getArticle

router/index.js

let express = require('express');
let router = express.Router();
let getArticle = require('../controller/article/getArticle_controller.js');
let addArticle = require('../controller/article/addArticle_controller.js');
/**
 * 数据查询
 */
router.get('/article/list/get', (request, response, next)=>{
  getArticle(request.query, data =>{
    response.end(JSON.stringify(data));
  })
});
/**
 * 数据新增
 * @type {Router|router}
 * request.body.XX
 */
router.post('/article/list/add', (request, response, next)=>{
  request.body = {
    name: '测试',
    url: 'http://www.test.aa',
    alexa: '32',
    country: 'CN'
  }

  addArticle(request.body, data =>{
    response.end(JSON.stringify(data));
  })
});
module.exports = router;

app.js

let createError = require('http-errors');
let express = require('express');
let path = require('path');
let cookieParser = require('cookie-parser');
let logger = require('morgan');

let indexRouter = require('./routes/index');
let usersRouter = require('./routes/users');

let app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
// 可跨域 ,编码格式
app.all('*',function(request, response, next){
  response.header('Access-Control-Allow-Origin','*');
  response.header('Access-Control-Allow-Headers','X-Requested-With');
  response.header('Access-Control-Allow-Methods','PUT,POST,GET,DELETE,OPTIONS');
  response.header('X-Powered-By','3.2.1');
  response.header('Content-Type','application/json;charset=utf-8');
  next();
});
// 抛出接口
app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

// 下面奉上运行结果：（post方法用postman测试就好啦！）

Node + Express + MySQL 接口开发完整案例

Node + Express + MySQL 接口开发完整案例

相关推荐