node+sequelize实现单表多表操作

最新推荐文章于 2024-06-22 21:46:58 发布

FiveBigNiu

最新推荐文章于 2024-06-22 21:46:58 发布

阅读量9.5k

点赞数 3

分类专栏： node.js 文章标签： nodejs sequelize

本文链接：https://blog.csdn.net/qq_34764577/article/details/78211418

版权

node.js 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

需要mysql和sequelize两个模块

1.单表增删改查

创建User.js,用于连接和初始化

var Sequelize = require('sequelize');
var mysql  = require('mysql'); 

var config = {
    database:'sheila',
    username:'root',
    password:'',
    host:'localhost',
    port:3306
};

var sequelize = new Sequelize(config.database, config.username, config.password, {
  host:config.host,
  dialect:'mysql',
  pool: {
        max: 5,
        min: 0,
        idle: 30000
   }
});

var User = sequelize.define('userinfo', {
    id : {type : Sequelize.INTEGER, autoIncrement : true, primaryKey : true},
    user : {type : Sequelize.STRING, allowNull : false},
    password : {type : Sequelize.STRING, allowNull : false},
},{
    timestamps:false,
    freezeTableName:true
});

module.exports = User;

创建register.js,测试请求和返回

var express = require('express');
var router = express.Router();
var log4js = require('log4js');
var User = require("../models/User.js");
var bodyParser = require('body-parser');//使用Express接收form表单的submit(提交格式为www-form-urlencoded)需要 body-parse 插件支持

router.get('/', function(req, res, next) {
  res.render('register', {errMsg:"in register page11"});
  // res.redirect("http://www.example.com");//网址重定向
});

router.post('/', function(req, res, next) {
  var username = req.body.username;
  var password = req.body.password;
    User.findAll({
        where:{
            user : username
        }
    }).then(function(data){
        if(data.length > 0){
            res.json({status:'已经有相同的名字了'});
        }else{
          User.create({
            user : username,
            password : password
          }).then(function (p){
            if(p){
                res.json({status:1});
                res.end();
            }else{
                res.json({status:0});
                res.end();
            }
          }).catch(function (err){

            console.log('err'+err);

          });
        }
    }).catch(function(err){
        console.log('err'+ err);
    });

    // res.end();
});

module.exports = router;

创建register.html,发送请求页面

<!DOCTYPE html>
<html>
<head>
  <title>注册</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
</head>
<body>
  <form action="/register" method="post">
    <div class="form-group">
      <div class="form-name left">
        <label>用户名</label>
      </div>
      <input type="text" name="username" class="form-input left" value="test" />
    </div>
    <div class="form-group">
      <div class="form-name left">
        <label>密码</label>
      </div>
      <input type="password" name="password" class="form-input left"/ value="123456">
    </div>
    <div class="form-btn">
      <input type="submit" value="注册"  />
    </div>
    <div class="form-btn">
      <label class="warn"><%= errMsg %></label>
    </div>
<!--     <% if(locals.status=="success"){ %>
    <div class="form-btn">
      注册成功，请点击<a href='/' >登录</a>
    </div>
    <%} %> -->
    </form>
</body>
</html>

2.两张表关联和查询

初始化用户表

/*
 用户表 ct_user
 */
var Sequelize = require('sequelize');
var sequelize = require("../config/sql_config.js");
var User_relation = require('../models/User_relation');

var User = sequelize.define('ct_user', {
    // id : {type : Sequelize.INTEGER, autoIncrement : true, primaryKey : true},
    username : {type : Sequelize.STRING, allowNull : false},//用户名
    password : {type : Sequelize.STRING, allowNull : false},//密码
    details : {type : Sequelize.STRING, allowNull : true},//简介
    head_thumb : {type : Sequelize.STRING, allowNull : true},//头像
    gender : {type : Sequelize.STRING, allowNull : true},//性别
    nickname : {type : Sequelize.STRING, allowNull : true},//昵称
    userid : {type : Sequelize.INTEGER, autoIncrement : true, primaryKey : true}//用户userid
},{
    timestamps:false,
    freezeTableName:true
});

//指定User和User_relation的关系为1：1的关系，设定目标为frendid，即查询中 userid = frendid
User.belongsTo(User_relation,{foreignKey:'userid',targetKey: 'frendid'});

module.exports = User;

初始化用户朋友关系表

/*
    用户ct_user对应的关系表 ct_user_relation
 */
var Sequelize = require('sequelize');
var sequelize = require("../config/sql_config.js");

var User_relation = sequelize.define('ct_user_relation', {
    id : {type : Sequelize.INTEGER, autoIncrement : true, primaryKey : true},
    userid : {type : Sequelize.STRING, allowNull : false},//用户id
    frendid : {type : Sequelize.STRING, allowNull : false}//朋友id
},{
    timestamps:false,
    freezeTableName:true
});

module.exports = User_relation;

查询用户有哪些朋友

var express = require('express');
var router = express.Router();
var User = require("../models/User.js");
var User_relation = require("../models/User_relation.js");
var Sequelize = require('sequelize');

router.post('/contact', function(req, res, next) {

    var userid = req.body.userid;
    console.log(userid);
    User.findAll({
        include:[{
            model:User_relation,
            'where':{
                'userid':userid
            }
        }]
    }).then(function(data){
        console.log(data);
        if(data.length){
            res.json({list:data});
        }else{
            console.log("no frends");
        }
        res.end();
        // console.log(JSON.stringify(data));

    }).catch(function(err){
        console.log('err'+ err);
    });

    // res.end();
});

module.exports = router;