登录
当前位置： 首页 > 工具软件 > connect-session-sequelize > 使用案例 >

sequelize 连接sql server服务的简单封装

司空鸿熙
2023-12-01

最近的项目，用nodejs做的webapi，连接sql server数据库用的sequelize框架，现在把我自己简单封装的sequelize服务记录下来，以备参考：

sql.js

const Sequelize = require('sequelize')
const fs = require('fs')
const path = require('path')

const basePath = path.resolve('./')

const db = {
  database: 'TestMall',
  username: 'sa',
  password: 'whb',

  host: '192.168.1.101',
  port: 1433,
  
  dialect: 'mssql',
  // close log
  logging: false,
  // "timestamps: false" fixed Unknown column 'createdAt' in 'field list'
  timestamps: false,
  dialectOptions: {
    multipleStatements: true
  }
}
//const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');
const sequelize = new Sequelize(db)

sequelize.sync()

sequelize.authenticate().then(() => {
    console.log('Connection has been established successfully.')
}).catch(err => {
    console.error('Unable to connect to the database:', err)
})

//sequelize.close()

// 统一事件调用方法，避免业务逻辑代码里大量重复的then()cathc()代码
Sequelize.Model.excute = function (res, action, param, cb) {
  this[action](param[0], (param.length > 1 ? param[1] : null)).then((re) => {
    if (cb) {
      cb(re)
    } else {
      res.json({
        code: 200,
        data: re
      })
    }
  }).catch(err => {
    console.log(err)
    // 错误日志
    fs.writeFile(`${basePath}/logs/sqlerr.log`, err, () => {});
    res.json({
      code: 500,
      data: err
    })
  })
}

// 统一存储过程、批处理调用方法，避免业务逻辑代码里大量重复的then()cathc()代码
Sequelize.Model.exec_proc = function (res, sql, param, cb) {
  sequelize.query(sql, {
    replacements: param
  }).then(re => {
    if (re[0][0].msg) {
      // 错误日志
      fs.writeFile(`${basePath}/logs/sqlerr.log`, re[0][0].msg, () => {});
      res.json({
        code: 500,
        data: re[0][0].msg
      })
    } else {
      if (cb) {
        cb(re[0])
      } else {
        res.json({
          code: 200,
          data: re[0]
        })
      }
    }
  }).catch(err => {
    // 错误日志
    fs.writeFile(`${basePath}/logs/sqlerr.log`, err, () => { });
    res.json({
      code: 500,
      data: err
    })
  })
}
module.exports = sequelize

考虑到新手不容易理解和接受，现在添加几张数据表映射模型定义和几个简单调用例子：

model.js

const Sequelize = require('sequelize')
const sequelize = require('./sql')
const Model = Sequelize.Model

//#region 数据模型申明
class user extends Model {}
class goods extends Model {}
class files extends Model {}
class orders extends Model {}
//#endregion

//#region 数据模型定义
/**
 * 用户账号信息表
 */
user.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  account: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Account'
  },
  name: {
    type: Sequelize.STRING,
    field: 'Name'
  },
  password: {
    type: Sequelize.BOOLEAN,
    allowNull: false,
    field: 'Password'
  }
}, {
  sequelize,
  timestamps: false,
  tableName: 'Person'
})

/**
 * 商品表
 */
goods.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  /**
   * book's name
   */
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Name'
  },
  /**
   * price
   * 定价
   */
  price: {
    type: Sequelize.REAL,
    allowNull: false,
    field: 'DJ'
  },
}, {
  sequelize,
  timestamps: false,
  tableName: 'Goods'
})

/**
 * 订单表
 */
orders.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  quantity: {
    type: Sequelize.INTEGER,
    allowNull: false,
    field: 'GoodsID'
  },
  amount: {
    type: Sequelize.REAL,
    allowNull: false,
    field: 'Amount'
  },
  /**
   * 外键, 关联商品表主键
   */
  goodsID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'GoodsID'
  },
  /**
   * 外键, 关联用户表主键
   */
  personID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Person_ID'
  }
}, {
  sequelize,
  timestamps: false,
  tableName: 'Orders'
})

/**
 * 资源文件表
 */
files.init({
  id: {
    primaryKey: true,
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    allowNull: false,
    field: 'xGUID'
  },
  /**
   * 外键, 关联商品表主键
   */
  goodsID: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'LYBMID'
  },
  path: {
    type: Sequelize.STRING,
    allowNull: false,
    field: 'Path'
  }
}, {  
  sequelize,
  timestamps: false,
  tableName: 'File'
})
//#endregion

//#region 表关联
goods.hasMany(files, {
  as: 'images',
  foreignKey: 'goodsID',
  through: null
});
orders.belongsTo(user, {
  as: 'buyer',
  foreignKey: 'userID',
  through: null
});
orders.belongsTo(goods, {
  as: 'goods',
  foreignKey: 'goodsID',
  through: null
});
files.belongsTo(orders, {
  as: 'order',
  sourceKey: 'goodsID',
  foreignKey: 'userID',
  through: null
});
//#endregion

module.exports = {
  user,
  goods,
  orders,
  files
}

接下来是业务逻辑层的调用例子：

order.js

const express = require('express')
const Sequelize = require('sequelize')
const Op = Sequelize.Op

const {
  user,
  goods,
  files,
  orders
} = require('../models')


Router.post('/add', (req, res) => {
  payInfo.exec_proc(res,"exec proc_addOrder :personID,:addressID,:amount,:goodsIDs,:nums", {
    personID: req.body.personID,
    goodsID: req.body.goodsID,
    quantity: req.body.quantity,
    amount: req.body.amount
  },(re)=>{
    if(re...){
      //处理库存锁定等业务。。。
      //存储过程其实已经做了, 这里就是表示下这个位置的用法。。。
    }
  })
})

Router.post('/list', (req, res) => {
  orders.excute(res, 'findAndCountAll', [{
    attributes: ['id',  'goodsID', 'buyerID', quantity, amount],
    where: {
      buyerID: req.body.userID,
      status: {
        [Op.ne]: '已取消'
      }
    },
    include: [{
        as: 'goods',
        model: goods,
        attributes: ['id', 'name', 'price'],
        required: false
      }, {
        as: 'images',
        model: files,
        where: {
          status: 0
        },
        attributes: ['id', 'name', 'path'],
        required: false
    }],
    order: [
      ['createTime', 'DESC'],
    ],
    offset: req.body.pageIndex * req.body.pageSize,
    limit: req.body.pageSize
  }])
})

module.exports = Router

最后是路由设定了：

route.js

const express = require('express')
const bodyParser = require('body-parser')
const cookieParser = require('cookie-parser')
const session = require('express-session')
const fs = require('fs')
const path = require('path')

//导入接口文件
const order = require('./controller/order')

// 默认页，首页
const pagePath = path.resolve(__dirname, 'public/index.html')
const page = fs.readFileSync(pagePath, 'utf-8', (err, data) => {
  if (err) {
    return err
  }
  return data
})

//创建express应用实例
const app = express()

//启用session、cookie等
app.use(cookieParser())
app.use(
  session({
    secret: 'keyboard cat',
    // cookie: {
    //   domain: '192.168.2.7',
    //   maxAge: 60 * 60 * 1000,
    //   secure: false
    // },
    resave: false,
    saveUninitialized: true
  })
)

app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: false
  })
)

//设置跨域等头部参数
app.all('*', function (req, res, next) {
  res.header('Access-Control-Allow-Origin', '*')
  //res.header('Access-Control-Allow-Origin', 'http://www.uni-engine.cn:8060')
  res.header('Access-Control-Allow-Headers', 'Content-Type')
  res.header('Access-Control-Allow-Methods', 'PUT,POST,GET,DELETE,OPTIONS')
  res.header('Access-Control-Allow-Credentials', true);
  res.header('X-Powered-By', ' 3.2.1')
  res.header('Content-Type', 'application/json;charset=utf-8')
  res.cookie('test', 'sadasdsad')
  next()
})

//指定路由
app.use('/api/v1/order', order)

//允许访问静态页
app.use('/apidoc', (req, res) => {
  res.set('Content-Type', 'text/html')
  res.send(page)
})

//允许访问指定的静态资源
app.use('/public', express.static(path.join(__dirname, 'public')))

//端口监听
app.listen('9000', () => {
  console.log('open Browser on http://127.0.0.1:9000')
})

这样，一个相对完整的webapi程序的代码就全有了。。。

下面再贴几个常用写法：

关联查询加分页：

Router.post('/list', (req, res) => {
  orders.findAndCountAll({
    attributes: ['id', 'code', 'amount', 'status', 'createTime'],
    where: {
      personID: req.body.personID,
    },
    distinct: true, //避免因连接查询导致数据重复,统计条数错误
    include: [{
      as: 'address',
      model: receiveAddress,
      attributes: ['id', 'area', 'address'],
      required: false
    }, {
      as: 'details',
      model: orderDetail,
      attributes: ['id', 'status', 'quantity'],
      include: [{
        as: 'goods',
        model: goods,
        raw: true, //合并字段
        attributes: ['desc', 'name', 'price', 'salePrice', 'stock'],
        required: false //避免关联表数据为空是不返回主表数据
      }, {
        as: 'images',
        model: files,
        where: {
          scenes: '图书封面'
        },
        //through: false //如果多对多关系，中间有关联表，这个属性表示是否留存关联表结构
        raw: true, //合并字段
        attributes: ['path'],
        required: false //避免关联表数据为空是不返回主表数据
      }]
    }],
    order: [
      ['createTime', 'DESC'],
    ],
    offset: req.body.pageIndex * req.body.pageSize,
    limit: req.body.pageSize
  }).then((re)={
    res.Json({
      code: '200',
      data: re
    })
  }).catch((err)=>{
    res.Json({
      code: '500',
      data: err
    })
  })
})

通过调用存储过程添加订单：

Router.post('/add', (req, res) => {
  payInfo.sequelize.query("exec proc_addOrder :personID,:addressID,:amount,:goodsIDs,:nums", {
    personID: req.body.personID,
    addressID: req.body.addressID,
    amount: req.body.amount,
    goodsIDs: req.body.goodsID,
    nums: req.body.quantity
  }).then((re) => {
    res.json({
      code: 200,
      data: re[0]
    })
  }).catch(err => {
    res.json({
      code: 500,
      data: err
    })
  })
})

 

 类似资料：

相关阅读

WebService 的简单封装接口调用方法测试链接服务器的连接并返回连接的链接服务器超简单的Python HTTP服务面向连接的服务和无连接的服务之间的差异。简单谈谈mysql左连接内连接

相关文章

深信服二面单面经-技术服务Go语言封装简介及实现细节RPC服务与HTTP服务RPC服务与HTTP服务简单大方的简历自我介绍

相关问答

（psycopg2.OperationalError）无法连接到服务器：服务器拒绝连接sqlserver - 连接 SqlServer 的问题？为什么一定需要通过 DSN？Netty服务器不接受第一次连接后的连接如何创建简单的okhttp3 websocket连接？Appcelerator连接到nodejs服务器

相关文档

Sequelize ORM 实践Markdown 简单的世界Sequelize Docs 中文版Sequelize 文档中文版Sequelize 中文API文档
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.