node+sequelize实现单表多表操作

来源：互联网发布：淘宝客服中心代码编辑：程序博客网时间：2024/05/20 20:22

需要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 = frendidUser.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;

运行成功后查询结果
这里写图片描述

阅读全文

1 0