平日开发使用node+mysq大多数是这样写法来查询数据库，如果多个筛选条件又需要写许多and条件，于是写了个obj-to-sql发布在npm上，引用下就可以轻松的将对象转换为sql语句

下载

npm install obj-to-sql

结果对比

原来的方法：
ctx.request.body是koa框架的响应体，可自行更换

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)
}

封装后：
封装之后我们使用对象传输，通过转换方法获取最终的sql语句，业务模块不再关心sql语句是怎样形成的，只需要关心自己的查询条件与返回结果

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.查询数据
常用于列表查询，具备多个筛选条件，若没有传值则不生成相关的语句内容，筛选条件的type对应表见代码下方

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'], //响应体输出的数据字段，如果为空则输出全部字段
  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分别对应如下：
equal 相等
like 模糊匹配
like-start 开头模糊匹配
like-end 结尾模糊匹配
greater 大于
greater-equal 大于且等于
less 小于
less-equal 小于且等于
null 判断为空

2.查询数据总数

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.添加数据
添加数据的时候也支持对是否为空判断

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'], //数据表的列名
  filters: [
    { prop: 'idCard', type: 'equal', value: item.idCard } //判断是否有idCard相同的数据，如果为空则进行添加
  ],
  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.更新数据
可以自定义要修改的数据字段

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'], //代表数据只修改这三个字段
  filters: [
    { prop: 'id', type: 'equal', value: item.id } //通过id找到表里响应的数据
  ],
  value: item
}
const sql = objToSql.update(sqlParmas)

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

5.删除数据
id=1的数据会被删除

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'