平日开发使用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'
