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

下載

npm install obj-to-sql

結(jié)果對比

原來的方法：
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) }

封裝后：
封裝之后我們使用對象傳輸，通過轉(zhuǎn)換方法獲取最終的sql語句，業(yè)務模塊不再關(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ù)
常用于列表查詢，具備多個篩選條件，若沒有傳值則不生成相關(guān)的語句內(nèi)容，篩選條件的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'], //響應體輸出的數(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分別對應如下：
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ù)的時候也支持對是否為空判斷

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ù)，如果為空則進行添加],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ù)只修改這三個字段filters: [{ prop: 'id', type: 'equal', value: item.id } //通過id找到表里響應的數(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'

總結(jié)

以上是生活随笔為你收集整理的Node+Mysql增删改查obj-to-sql,多条件查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。