JavaScript Object Conversion SQL
Download
npm install obj-to-sql
Query Data
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
equal 相等
like 模糊匹配
like-start 开头模糊匹配
like-end 结尾模糊匹配
greater 大于
greater-equal 大于且等于
less 小于
less-equal 小于且等于
null 判断为空
Query Total
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%'
Insert
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 }
],
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')
Update
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 }
],
value: item
}
const sql = objToSql.update(sqlParmas)
//SQL Result
Update user Set birthday = '2008-01-01', sex = '1', phone = '15999999999' Where id = '1'
Delete
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'