Egg中使用Sequelize框架关联查询Mysql数据库

Sequelize视频教程_Eggjs仿小米商城企业级Nodejs项目实战视频教程: https://www.itying.com/goods-941.html

一、Sequelize简介

前面的章节中，我们介绍了如何在框架中通过 egg-mysql 插件来访问数据库。而在一些较为复杂的应用中，我们可能会需要一个 ORM 框架来帮助我们管理数据层的代码。而在 Node.js 社区中，sequelize 是一个广泛使用的 ORM 框架，它支持 MySQL、SQLite 和 MSSQL 、PostgreSQL等多个数据源。下面我们主要给大家讲讲sequelize 结合MySQL的使用。

相关文档：

https://eggjs.org/zh-cn/tutorials/sequelize.html

https://sequelize.org/

二、Sequelize 操作Mysql数据库

1、安装egg-sequelize以及mysql2

npm install --save egg-sequelize mysql2

2、在 config/plugin.js 中引入 egg-sequelize 插件

exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};

3、 在 config/config.default.js 中编写 sequelize 配置

config.sequelize = {
    dialect: 'mysql',
    host: '127.0.0.1',
    port: 3306,
    database: 'test',
    username:"root",
    password:"123456"    
};

三、Sequelize 操作Mysql实现增删改查

1、在app/model/ 目录下编写数据库Model,以用户表user为例

'use strict';

module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(30),
    age: INTEGER,
    created_at: DATE,
    updated_at: DATE,
  });

  return User;
};

或者

'use strict';

module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(30),
    age: INTEGER,
    created_at: DATE,
    updated_at: DATE,
  },{
        timestamps: false,  //自动增加创建时间
        tableName: 'user_info'   //设置表名称
});
  return User;
};

2、定义controller实现数据库的增删改查

async index() {
    const ctx = this.ctx;   	
    ctx.body = await ctx.model.User.findAll({limit: 10, offset: 0,order:[["id","desc"]]});

    //指定返回的字段
    //ctx.body = await ctx.model.User.findAll({attributes: ['id', 'name'],limit: 10,order:[["id","desc"]]});

}


 async findOne() {
    const ctx = this.ctx;    
    var result = await ctx.model.User.findByPk(106);
    ctx.body=result || '';
  }


async create() {
    const ctx = this.ctx;   
    const user = await ctx.model.User.create({ name:"张三", age:20 });
    ctx.status = 201;
    ctx.body = user;
}


async update() {
    const ctx = this.ctx;
    const id = 106;
    const user = await ctx.model.User.findByPk(id);
    if (!user) {
      ctx.status = 404;
      return;
    }
    await user.update({ name:"李四", age:43 });
    ctx.body = user;
  }


async destroy() {
    const ctx = this.ctx;
    const id = 213;
    const user = await ctx.model.User.findByPk(id);
    if (!user) {
      ctx.status = 404;
      return;
    }

    await user.destroy();
    ctx.status = 200;
    ctx.body="删除成功";
}

四、Sequelize 操作多表关联查询

4.1、1对1 hasOne 或者 belongsTo

 ArticleCate.associate = function (){
    // 1对1
    app.model.ArticleCate.hasOne(app.model.Article, {foreignKey: 'cateId'});   
  }
查询语句
    const { ctx } = this;
    let result = await ctx.model.ArticleCate.findAll({
        include: {
          model: ctx.model.Article
        }
    });

Article.associate = function (){
    // 1对1
    app.model.Article.belongsTo(app.model.ArticleCate, {foreignKey: 'cateId'});       
  }

查询语句
    const { ctx } = this;
    let result = await ctx.model.Article.findAll({
        include: {
          model: ctx.model.ArticleCate
        }
    });

4.2、1对多 hasMany

ArticleCate.associate = function (){  
    // 1对多
    app.model.ArticleCate.hasMany(app.model.Article, {foreignKey: 'cateId'});     
  }

查询语句

const { ctx } = this;
    let result = await ctx.model.ArticleCate.findAll({
        include: {
          model: ctx.model.Article
        }
    });

4.3、多对多 belongsToMany

model/student.js

module.exports = app => {
    const { STRING, INTEGER } = app.Sequelize;

    const Student = app.model.define('student', {
        id: {
            type: INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        name: {
            type: STRING,
        },
        number: {
            type: STRING,
            allowNull: false,
        },
        password: {
            type: STRING(32),
            allowNull: false
        }
    }, {
        timestamps: false,
        tableName: 'student'
    });

    Student.associate = function () {

        //一个学生可以选修多门课程 
        app.model.Student.belongsToMany(app.model.Lesson, {
            through: app.model.LessonStudent, 
            foreignKey: 'studentId',//注意写法 
            otherKey: 'lessonId' 
        });
    }

    return Student;
}

model/lesson.js

module.exports = app => {
    const { INTEGER, STRING } = app.Sequelize;
 
    const Lesson = app.model.define('lesson', {
        id: {
            type: INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: STRING,
            allowNull: false
        }
    },{
        timestamps: false,
        tableName: 'lesson'    
      });


      Lesson.associate = function () {

        //一个学生可以选修多门课程 
       //一门课程可以被多个学生选修
            app.model.Lesson.belongsToMany(app.model.Student, {
                through: app.model.LessonStudent, 
                foreignKey: 'lessonId',//注意写法 
                otherKey: 'studentId' 
            });
     }

 
  
 
    return Lesson;
}

model/lesson_student.js

module.exports = app => {
    const { INTEGER } = app.Sequelize;
 
    const LessonStudent = app.model.define('lesson_student', {
        lessonId: {
            type: INTEGER,
            primaryKey: true
        },
        studentId: {
            type: INTEGER,
            primaryKey: true
        }
    },{
        timestamps: false,
        tableName: 'lesson_student'    
      });
 
    //   LessonStudent.associate = function(){
 
    // }
 
    return LessonStudent;
}

多对多查询数据

'use strict';

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

class ArticleController extends Controller {

  //查询数据 1对1 1对多
  async index() {
    const { ctx } = this;

    // let result = await ctx.model.Article.findAll({
    //     include: {
    //       model: ctx.model.ArticleCate
    //     }
    // });


    let result = await ctx.model.ArticleCate.findAll({
        include: {
          model: ctx.model.Article
        }
    });

    ctx.body = result;
  }
  

   //查询数据  多对多
   async showAll() {
    const { ctx } = this;

    //课程有哪些学生选修
    // let result = await ctx.model.Lesson.findAll({
    //   include: {
    //     model:  ctx.model.Student
    //   }
    // });   
    

    //每个学生选修了哪些课程
    let result = await ctx.model.Student.findAll({
      include: {
        model:  ctx.model.Lesson
      }
    });   
    ctx.body = result;
  }
  
}

module.exports = ArticleController;