obj-to-sql

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'