koa2+mysql数据库增删改查

项目目录结构

1.初始化项目

项目初始化命令 npm init -y，自动生成一个package.json文件

2.链接数据库

创建db数据库文件夹，建立mysql.js

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'xxx',
    database: 'zengshangaicha',
});

connection.connect(() => {
    console.log('数据库连接成功');
});

function exec(sql) {
    const promise = new Promise((resolve, reject) => {
        connection.query(sql, (err, res) => {
            if (err) {
                reject(err);
                return;
            }
            resolve(res);
        });
    });
    return promise;
}

module.exports = exec;

3.操作mysql数据库

建立controller文件夹，创建roles.js

const exec = require('../db/mysql');
// 查询
const list = async (role, limit, page) => {
    let sql = `select * from roles where 1=1`;
    if (role) {
        sql += ` and role='${role}'`;
    }
    sql += ` order by id desc`;
    // 分页查询
    if (limit && page) {
        sql += ` limit ${(page - 1) * 10},${limit}`;
    }

    const result = exec(sql);
    return result;
};

// 增加
const add = async (role, permissions) => {
    console.log(role, permissions);
    const sql = `insert into roles (role,permissions) values ('${role}','${permissions}')`;
    const result = exec(sql);
    return result;
};

// 修改
const upt = async (id, role, permissions) => {
    const sql = `update roles set role='${role}',permissions='${permissions}' where id='${id}'`;
    const result = exec(sql);
    return result;
};

// 删除
const del = async id => {
    const sql = `delete from roles where id='${id}'`;
    const result = exec(sql);
    return result;
};

module.exports = {
    list,
    add,
    upt,
    del,
};

4.实现路由

创建routes路由目录，创建router.js

const Router = require('koa-router');
const router = new Router();
const { list, add, upt, del } = require('../controller/roles');

router.prefix('/api/roles');

router.get('/list', async (ctx, next) => {
    const role = ctx.query.role;
    const limit = ctx.query.limit;
    const page = ctx.query.page;

    // if (!ctx.session.username) return;

    let data = await list(role, limit, page);
    // 获取总数量 total
    const data2 = await list();
    if (data.length == 0) {
        ctx.body = {
            code: 400,
            msg: '查询失败',
        };
        return;
    }
    data.forEach(ele => {
        ele.permissions = JSON.parse(ele.permissions);
    });
    ctx.body = {
        data,
        total: data2.lenght,
    };
});

router.post('/add', async (ctx, next) => {
    const { role, permissions } = ctx.request.body;
    // if (!ctx.session.username) return;
    const data = await add(role, permissions);
    if (data.affectedRows > 0) {
        ctx.body = {
            code: 200,
            msg: '添加成功',
            res: data,
        };
        return;
    }
});

router.post('/upt', async (ctx, next) => {
    const { id, role, permissions } = ctx.request.body;
    console.log(id, role, permissions);
    // if (!ctx.session.username) return;
    const data = await upt(id, role, permissions);
    if (data.affectedRows > 0) {
        ctx.body = {
            code: 200,
            msg: '更新成功',
        };
        return;
    }
});

router.post('/del', async (ctx, next) => {
    const { id } = ctx.request.body;
    console.log(id);
    // if (!ctx.session.username) return;
    const data = await del(id);
    if (data.affectedRows > 0) {
        ctx.body = {
            code: 200,
            msg: '删除成功',
        };
        return;
    }
});

module.exports = router;

5.注册路由服务

创建 app.jsÏ

• 需要安装koa,koa-bodyparser,koa-router
• 命令npm install mysql koa koa-bodyparser koa-router --save

const Koa = require('koa');

const bodyParser = require('koa-bodyparser');

const app = new Koa();
const roles = require('./routes/router');

app.use(bodyParser());
app.use(roles.routes(), roles.allowedMethods());

app.listen(8080, () => {
    console.log('服务器启动了');
});

5.运行效果

小结

编辑不易，转载请注明出处。感谢赏阅，期待你的建议，点赞和关注。