平日開發(fā)使用node+mysq大多數(shù)是這樣寫法來查詢數(shù)據(jù)庫，如果多個(gè)篩選條件又需要寫許多and條件，于是寫了個(gè)obj-to-sql發(fā)布在npm上，引用下就可以輕松的將對象轉(zhuǎn)換為sql語句

下載

npm install obj-to-sql

結(jié)果對比

原來的方法：
ctx.request.body是koa框架的響應(yīng)體，可自行更換

const requestBody = ctx.request.body
const sql = `Select * From user Where name = '${requestBody.name}' And phone = '${requestBody.phone}'`
connection.query(sql , (err, result) => {
  console.log('query', result)
}

封裝后：
封裝之后我們使用對象傳輸，通過轉(zhuǎn)換方法獲取最終的sql語句，業(yè)務(wù)模塊不再關(guān)心sql語句是怎樣形成的，只需要關(guān)心自己的查詢條件與返回結(jié)果

const objToSql = require('obj-to-sql')
const requestBody = ctx.request.body
const sqlParmas = {
  table: 'user', //表名
  filters: [
    { prop: 'name', type: 'equal', value: requestBody.name },
    { prop: 'phone', type: 'equal', value: requestBody.phone }
  ]
}
const sql = objToSql.select(sqlParmas)
connection.query(sql, (err, result) => {
  console.log('query', result)
}

使用方法

1.查詢數(shù)據(jù)
常用于列表查詢，具備多個(gè)篩選條件，若沒有傳值則不生成相關(guān)的語句內(nèi)容，篩選條件的type對應(yīng)表見代碼下方

const objToSql = require('obj-to-sql')
const requestBody = {
  pageIndex: 1,
  pageSize: 20,
  name: 'tony',
  idCard: '350100',
  areaCode: '3501',
  birthdayBegin: '1990-01-01',
  birthdayEnd: '2000-01-01',
  notMark: true
}
const sqlParmas = {
  table: 'user', //表名
  sort: { 
    prop: 'id',
    type: 'desc', //desc倒序、asc升序
    value: true //開啟排序
  },
  props: ['name', 'idCard', 'phone'], //響應(yīng)體輸出的數(shù)據(jù)字段，如果為空則輸出全部字段
  page: {
    index: requestBody.pageIndex,
    size: requestBody.pageSize,
    value: true //開啟分頁
  },
  filters: [ 
    { prop: 'name', type: 'equal', value: requestBody.name },
    { prop: 'idCard', type: 'like', value: requestBody.idCard },
    { prop: 'idCard', type: 'like-start', value: requestBody.areaCode },
    { prop: 'birthday', type: 'greater-equal', value: requestBody.birthdayBegin },
    { prop: 'birthday', type: 'less-equal', value: requestBody.birthdayEnd },
    { prop: 'mark', type: 'null', value: requestBody.notMark }
  ]
}
const sql = objToSql.select(sqlParmas)

//SQL Result
Select name, idCard, phone From user Where name = 'tony' And idCard Like '%350100%' And idCard Like '3501%' And birthday >= '1990-01-01' And birthday <= '2000-01-01' And (mark Is Null Or mark = '') Order By id Desc Limit 0, 20

篩選條件的type分別對應(yīng)如下：
equal 相等
like 模糊匹配
like-start 開頭模糊匹配
like-end 結(jié)尾模糊匹配
greater 大于
greater-equal 大于且等于
less 小于
less-equal 小于且等于
null 判斷為空

2.查詢數(shù)據(jù)總數(shù)

const objToSql = require('obj-to-sql')
const requestBody = {
  name: 'tony',
  idCard: '350100'
}
const sqlParmas = {
  table: 'user',
  filters: [ 
    { prop: 'name', type: 'equal', value: requestBody.name },
    { prop: 'idCard', type: 'like', value: requestBody.idCard }
  ]
}
const sql = objToSql.total(sqlParmas)

//SQL Result
Select Count(*) As total From user Where name = 'tony' And idCard Like '%350100%'

3.添加數(shù)據(jù)
添加數(shù)據(jù)的時(shí)候也支持對是否為空判斷

const objToSql = require('obj-to-sql')
const item = {
  name: 'tony',
  idCard: '350100000000000000',
  birthday: '1999-01-01',
  sex: 0,
  phone: '13600000000'
}
const sqlParmas = {
  table: 'user',
  props: ['name', 'idCard', 'birthday', 'sex', 'phone'], //數(shù)據(jù)表的列名
  filters: [
    { prop: 'idCard', type: 'equal', value: item.idCard } //判斷是否有idCard相同的數(shù)據(jù)，如果為空則進(jìn)行添加
  ],
  value: item
}
const sql = objToSql.insert(sqlParmas)

//SQL Result
Insert Into user(name, idCard, birthday, sex, phone) Select 'tony', '350100000000000000', '1999-01-01', '0', '13600000000' From Dual Where Not Exists (Select * From user Where idCard = '350100000000000000')

4.更新數(shù)據(jù)
可以自定義要修改的數(shù)據(jù)字段

const objToSql = require('obj-to-sql')
const item = {
  id: 1,
  birthday: '2008-01-01',
  sex: 1,
  phone: '15999999999'
}
const sqlParmas = {
  table: 'user',
  props: ['birthday', 'sex', 'phone'], //代表數(shù)據(jù)只修改這三個(gè)字段
  filters: [
    { prop: 'id', type: 'equal', value: item.id } //通過id找到表里響應(yīng)的數(shù)據(jù)
  ],
  value: item
}
const sql = objToSql.update(sqlParmas)

//SQL Result
Update user Set birthday = '2008-01-01', sex = '1', phone = '15999999999' Where id = '1'

5.刪除數(shù)據(jù)
id=1的數(shù)據(jù)會被刪除

const objToSql = require('obj-to-sql')
const sqlParmas = {
  table: 'user',
  filters: [
    { prop: 'id', type: 'equal', value: 1 }
  ]
}
const sql = objToSql.delete(sqlParmas)

//SQL Result
Delete From user Where id = '1'