nodejs之egg框架mysql應用

config/plugin.js

mysql: {
    enable: true,
    package: 'egg-mysql',
}

config/config.default.js

config.mysql = {
        client: {
            host: '127.0.0.1',
            port: '3306',
            user: 'devuser',
            password: 'devuser123',
            database: 'db_co_lottery',
        },
        // 是否加載到 app 上，默認開啟
        app: true,
        // 是否加載到 agent 上，默認關閉
        agent: false
 };

service類

'use strict';
const Service = require('egg').Service;
class NumberService extends Service {
    /**
     * 獲取所有Number
     * @returns {Promise<Array>}
     */
    async getAllNumbers() {
        let sql = 'SELECT zodiac,zodiac_img,punching FROM t_luckdraw_numbers GROUP BY zodiac,zodiac_img,punching ORDER BY sort';
        let res = await this.app.mysql.query(sql);
        let result = [];
        let sqlt = 'SELECT * FROM t_luckdraw_numbers WHERE zodiac = ? ORDER BY sort ASC';
        for (let i = 0; i < res.length; i++) {
            let object = {zodiac: res[i].zodiac, zodiac_img: res[i].zodiac_img, punching: res[i].punching};
            let numbers = await this.app.mysql.query(sqlt, res[i].zodiac);
            let numberArr = [];
            for (let j = 0; j < numbers.length; j++) {
                let numObj = {
                    number: numbers[j].number,
                    wave_color: numbers[j].wave_color,
                    five_elements: numbers[j].five_elements,
                    single_double: numbers[j].single_double,
                    big_small: numbers[j].big_small
                };
                numberArr.push(numObj);
            }
            object['numbers'] = numberArr;
            result.push(object);
        }
        return result;
    }

    /**
     * 獲取開獎結果
     * @returns {Promise<void>}
     */
    async getLastResult() {
        let sql = 'SELECT lr.*,ln.wave_color FROM t_luckdraw_result lr LEFT JOIN t_luckdraw_numbers ln ON ln.number = lr.number WHERE lr.number IS NOT NULL ORDER BY lr.stage DESC LIMIT 0,1';
        let res = await this.app.mysql.query(sql);
        var result = {};
        result['this_stage'] = res[0].stage;
        result['this_number'] = res[0].number;
        result['this_wave_color'] = res[0].wave_color;
        sql = 'SELECT * FROM t_luckdraw_result WHERE number IS NULL ORDER BY create_time DESC LIMIT 0,1';
        res = await this.app.mysql.query(sql);
        result['next_stage'] = res[0].stage;
        result['next_time'] = this.ctx.helper.formatTime(res[0].end_time);
        result['next_time_format'] = this.ctx.helper.formatTime(res[0].end_time, 'MM月DD日HH時mm分');
        result['next_week'] = this.ctx.helper.getWeekOfDate(res[0].end_time);
        return result;
    }

    async getNumberInfo(numbers) {
        const sql = 'SELECT * FROM t_luckdraw_numbers WHERE number IN(' + numbers + ')';
        const result = await this.app.mysql.query(sql);
        return result;
    }
}

module.exports = NumberService;

controller類

'use strict';

const Controller = require('egg').Controller;

class NumberController extends Controller {

    async getNumbers() {
        const {ctx, service} = this;
        let result = await service.number.getAllNumbers();
        ctx.body = result;
        ctx.status = 200;
    }

    async getLuckdrawResult() {
        const {ctx, service} = this;
        let result = await service.number.getLastResult();
        ctx.body = result;
        ctx.status = 200;
    }

    async getNumberInfo() {
        const {ctx, service} = this;
        const numbers = this.ctx.request.query.numbers;
        if (numbers == null || numbers.split(',').length <= 0) {
            ctx.body = {
                msg: '參數有誤'
            }
            return ctx.body;
        }
        let result = await service.number.getNumberInfo(numbers);
        ctx.body = result;
    }
}

module.exports = NumberController;