Koa封装MySQL数据库

var mysql = require('mysql');

// 建立链接
function __connection() {
    var connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '12345678',
        database: 'myData'
    });
    connection.connect();
    return connection;
};

exports.query = function (sql, parmas = null) {
    // 获取数据库链接对象
    var connection = __connection();
    return new Promise(function (reject, resolve) {
        // 执行SQL语句
        connection.query(sql, parmas, function (error, results, fields) {
            if (error) throw error;
            reject(results);
        });
        // 关闭链接
        connection.end();
    })
}

const Koa = require('koa');
const router = require('koa-router')();
const views = require('koa-views');
const bodyParser = require('koa-bodyparser');
const static = require('koa-static');
const DB = require('./module/config.js');


var app = new Koa();
// 配置模板引擎
app.use(views('views', {
    extension: 'ejs'
}));
// 配置静态资源访问路径
app.use(static(__dirname + '/public'));
// 配置获取POST提交数据的请求
app.use(bodyParser());

// 执行查询操作
router.get('/', async (ctx) => {
    var sql = 'select * from user';
    var result = await DB.query(sql);
    await ctx.render('index', {
        list: result
    })
});
// 增加操作
router.get('/add', async (ctx) => {
    // 模拟获取前台的数据
    var username = '张三';
    var password = '123456';

    var sql = 'insert into user (username,password) value (?,?)';
    var params = [username, password];
    var result = await DB.query(sql, params);
    // 实际开发中需要判断后给出响应
    ctx.body = '增加成功';

});
// 编辑操作
router.get('/edit', async (ctx) => {
    // 模拟获取前台的数据
    var username = '李四';
    var password = '123456';

    var sql = 'update user set username=?,password=? where id=3';
    var result = await DB.query(sql, [username, password]);

    // 实际开发中需要判断后给出响应
    ctx.body = '修改成功';
});

// 删除操作
router.get('/delete', async (ctx) => {
    var sql = 'delete from user where id=8';
    var result = await DB.query(sql);
    // 实际开发中需要判断后给出响应
    ctx.body = '删除成功';
});


app.use(router.routes());
app.use(router.allowedMethods());
app.listen(3000);