查看更多资源

1. 简介:

使用 koa 框架、vue.js 与 mysql 数据库,搭建一个简单的任务管理器demo,实现任务列表的展示、分页查询、增删改查。 

2. 安装:

npm install koa koa-router koa-static-cache koa-bodyparser mysql2 --save

3. 图示:

4. app.js 代码: 

(async function () {

  const Koa = require('koa');
  const Static = require('koa-static-cache');
  const Router = require('koa-router');
  const Bodyparser = require('koa-bodyparser');
  const fs = require('fs');
  const mysql = require('mysql2/promise'); // 1. 加载模块

  const app = new Koa();
  const router = new Router();

  app.use(Bodyparser());

  app.use(Static('./static', { // 配置静态资源
    prefix: '/static',
    gzip: true
  }));

  const connection = await mysql.createConnection({ // 2. 连接 mysql 数据库
    host: '127.0.0.1',
    user: 'root',
    password: '123456',
    database: 'test'  // 数据库名
  });

  router.get('/', ctx => {
    const content = fs.readFileSync('./static/index.html');
    ctx.body = content.toString();
  });

  router.get('/todos', async ctx => {

    /*
    * (1) 查询及排序 
    */
    // 语法:SELECT 字段名 FROM 表名 ORDER BY 字段名 DESC        
    // ASC:升序,默认;  DESC:降序 

    const [data] = await connection.query("SELECT id,title,done FROM todos ORDER BY done DESC, id DESC");

    // 接受前端 分页请求参数 
    let page = ctx.query.page || 1; 
    page = Number(page);
    let prepage = ctx.query.prepage || 4;
    prepage = Number(prepage);
    let type = ctx.query.type;
    let where = '';

    if (type) {
      where = 'WHERE done=' + type;
    }

    // 查询总的记录条数
    const sql = `SELECT id,title,done FROM todos ${where}`;
    const [todosAll] = await connection.query(sql);
    // 总的数据量 / 每页显示条数,注意:小数
    const pages = Math.ceil(todosAll.length / prepage);

    /*
    * (2) 分页查询 
    */
    // 语法:LIMIT 5 OFFSET 3  查询5条,偏移3条
    // 语法 where ??=?  其中 ?? 表示字段或表名,? 表示值

    const sql2 = `SELECT id,title,done FROM todos ${where} LIMIT ? OFFSET ?`;

    const [todos] = await connection.query(sql2, [prepage, (page - 1) * prepage]);

    ctx.body = {
      code: 0,
      data: {
        page,
        prepage,
        pages,
        todos
      }
    }
  });

  router.post('/add', async ctx => {
    const title = ctx.request.body.title || '';

    if (title == '') {
      ctx.body = {
        code: 1,
        data: 'title不能为空'
      }
      return;
    }

    /*
    * (3) 添加数据 
    */

    const [rs] = await connection.query("INSERT INTO todos (title, done) VALUES ('" + title + "', 0)");

    if (rs.affectedRows > 0) {
      ctx.body = {
        code: 0,
        data: '添加成功'
      }
    } else {
      ctx.body = {
        code: 2,
        data: '添加失败'
      }
    }
  });

  router.post('/toggle', async ctx => {

    let id = Number(ctx.request.body.id) || 0;
    let todo = Number(ctx.request.body.todo) || 0;

    /*
    * (4) 更新数据 
    */

    let sql = "UPDATE todos SET ??=? WHERE ??=?";

    let [rs] = await connection.query(sql, ['done', todo, 'id', id]);

    if (rs.affectedRows > 0) {
      ctx.body = {
        code: 0,
        data: '修改成功'
      }
    } else {
      ctx.body = {
        code: 2,
        data: '修改失败'
      }
    }
  });

  router.post('/remove', async ctx => {
    let id = Number(ctx.request.body.id) || 0;

    /*
    * (5) 删除数据 
    */

    let sql = "DELETE FROM todos WHERE ??=?";

    let [rs] = await connection.query(sql, ['id', id]);

    if (rs.affectedRows > 0) {
      ctx.body = {
        code: 0,
        data: '删除成功'
      }
    } else {
      ctx.body = {
        code: 2,
        data: '删除失败'
      }
    }
  });

  app.use(router.routes());
  app.listen(8081);
})();

5. index.html 代码:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <script src="https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.js"></script>
    <style>
        li {
            line-height: 40px;
        }
        .done span {
            text-decoration: line-through;
            color: #cccccc;
        }
        .pages {
            margin: 10px;
        }
        .pages a {
            margin: 15px;
        }
        .pages a.active {
            color: red;
        }
        .footer {
            margin: 10px;
        }
        .footer button.active {
            background: yellow;
        }
    </style>
</head>

<body>
    <div id="app">
        <h1>App</h1>
        <div>
            <input type="text" v-model="newValue" />
            <button @click="add">添加新任务</button>
        </div>
        <hr />
        <ul>
            <li v-for="todo of todos" :key="todo.id" :class="{done: todo.done}">
                <input type="checkbox" :checked="todo.done" @click.prevent="toggle(todo.id, !todo.done)" />
                <span>#{{todo.id}} - {{todo.title}}</span>
                <button @click="remove(todo.id)">删除</button>
            </li>
        </ul>
        <div class="pages">
            <a href="" :class="{active: n == page}" @click.prevent="changePage(n)" v-for="n of pages">{{n}}</a>
        </div>
        <div class="footer">
            <button :class="{active: type===''}" @click="changeType('')">所有</button>
            <button :class="{active: type===1}" @click="changeType(1)">已完成</button>
            <button :class="{active: type===0}" @click="changeType(0)">未完成</button>
        </div>
    </div>

    <script>
    
        new Vue({
            el: '#app',
            data: {
                newValue: '',
                page: 1,
                prepage: 4,
                pages: 0,
                type: '',
                todos: []
            },
            created() {
                this.getData();
            },
            methods: {
                getData() {
                    fetch('/todos?type='+ this.type +'&page=' + this.page + '&prepage=' + this.prepage).then(res => {
                        return res.json();
                    }).then( data => {
                        this.todos = data.data.todos;
                        this.pages = data.data.pages;
                    } );
                },
                changePage(n) {
                    this.page = n;
                    this.getData();
                },
                changeType(m) {
                    this.type = m;
                    this.getData();
                },
                add() {
                    fetch('/add', {
                        method: 'post',
                        headers: {
                            'Content-Type': 'application/json'
                        },
                        body: JSON.stringify( {title: this.newValue} )
                    }).then(res => {
                        return res.json();
                    }).then( data => {
                        if (!data.code) {
                            this.getData();
                        }
                    } )

                    this.newValue = '';
                },
                toggle(id, todo) {
                    fetch('/toggle', {
                        method: 'post',
                        headers: {
                            'Content-Type': 'application/json'
                        },
                        body: JSON.stringify( {id, todo: Number(todo)} )
                    }).then(res => {
                        return res.json();
                    }).then(data => {
                        if (!data.code) {
                            this.getData();
                        }
                    });
                },
                remove(id) {
                    fetch('/remove', {
                            method: 'post',
                            headers: {
                                'Content-Type': 'application/json'
                            },
                            body: JSON.stringify( {id} )
                        }).then(res => {
                            return res.json();
                        }).then(data => {
                            if (!data.code) {
                                this.getData();
                            }
                        });
                }
            }
        })
    
    </script>
</body>
</html>

 

 

Logo

前往低代码交流专区

更多推荐