xqsql
This is a method warehouse for automatically generating various sql statements.
Install
Browser:
import cdn
<!-- Browser -->
<script src="https://xqgj.cc/xqcdn/libs/xqsql/xqsql.min.js"></script>
<!-- es module -->
<script type="module">
import xqsql from '../lib/xqsql-esm.min.js';
</script>
Node:
npm install xqsql
const xqsql = require('xqsql');
Usage
CURD operation
- Create record
This method is suitable for creating single or multiple records
Parameters of req.body
const addParams = [
{
name:'Apple',
number: 10,
price: 8.8
},
{
name:'Blueberry',
number: 20,
price: 9.9
}
]
Field array
const addFields = [
{
name:'Product name',
value:'name',
isMust: true
},
{
name:'Product quantity',
value:'number',
isMust: true
},
{
name:'Commodity price',
value:'price',
isMust: true
}
]
View Results
const addSql = xqsql.add('goods', addParams, addFields);
console.log('The added statement is:', addSql);
// The added statement is: INSERT INTO `goods` (id, name,number,price) VALUES (0,"apple",10,8),(0,"blueberry",20,9)
- Get information
- Default query
Parameters of req.query
let getDefaultOneParams = [1];
let getDefaultMoreParams = [1,2,3];
View results (single in default mode)
const getDefaultOneSql = xqsql.get('goods', {
type:'one',
key:'id',
ids: getDefaultOneParams,
},'default','id,name,number,price');
console.log('Query default mode (single):', getDefaultOneSql);
// Query the default mode (single): SELECT id,name,number,price FROM `goods` WHERE id = '1'
View results (single in default mode)
const getDefaultMoreSql = xqsql.get('goods', {
type:'more',
key:'id',
ids: getDefaultMoreParams,
},'default','id,name,number,price');
console.log('Query default mode (multiple):', getDefaultMoreSql);
// Query the default mode (multiple): SELECT id,name,number,price FROM `goods` WHERE id in (1,2,3)
View results (all in default mode)
const getDefaultSql = xqsql.get('goods', {
type:'all',
},'default','id,name,number,price');
console.log('Query default mode (all):', getDefaultSql);
// Query the default mode (all): SELECT id,name,number,price FROM `goods`
2.More conditional modes
const getMoretSql = xqsql.get('goods', {
id: 1,
name: "Banana",
},'more','id,name,number,price');
console.log('More condition mode:', getMoretSql);
// More conditional patterns: SELECT id,name,number,price FROM `goods` WHERE id = '1' AND name LIKE'%banana%'
3.Paging query
let getListParams = {
name: "Banana",
page: 1,
size: 10,
};
let currentPageSize = (getListParams.page-1) * getListParams.size;
let getListFields = [
{
name:'Product name',
value:'name'
},
{
name:'Product quantity',
value:'number'
},
{
name:'Commodity price',
value:'price'
}
]
let getList = {};
for (const item of getListFields) {
let k = item.value;
if (getListParams[k]) {
getList[k] = getListParams[k];
}
if (getListParams[k] === 0) {
getList[k] = 0;
}
}
let getListSql = xqsql.get('goods', {
list: getList,
sorts: {
name: getListParams.sort ||'create_time',
val: getListParams.sortRule ||'DESC'
},
limits: {
page: currentPageSize,
size: getListParams.size || 10
}
},'page');
console.log('The sentence after the paging list query is:', getListSql);
/*
The sentence after the paging list query is: {
sql: "SELECT * FROM `goods` WHERE name LIKE'%banana%' ORDER by create_time DESC LIMIT 0,10",
count: "SELECT COUNT(id) FROM `goods` WHERE name LIKE'%banana%'"
}
*/
- Update
Public section
const upParams = {
key:'id',
ids: null,
list: {
number: 10,
price: 9.9
}
}
const upFields = [
{
name:'Product quantity',
value:'number',
isMust: true
},
{
name:'Commodity price',
value:'price',
isMust: true
}
]
- Single update
upParams.ids = [1];
let upOneSql = xqsql.up('goods', upParams, upFields);
console.log('Update (single) statement:', upOneSql);
// Update (single) statement: UPDATE `goods` SET number = 10,price = 9 WHERE id = '1'
2.Multiple updates
upParams.ids = [1,2,3];
let upMoreSql = xqsql.up('goods', upParams, upFields,'more');
console.log('Update (multiple) statements:', upMoreSql);
// Update (multiple) statements: UPDATE `goods` SET number = 10,price = 9 WHERE id in (1,2,3)
3.Update all
let upSql = xqsql.up('goods', upParams, upFields,'all');
console.log('Update (all) statement:', upSql);
// Update (all) statement: UPDATE `goods` SET number = 10, price = 9
- Delete
Parameter req.query
- Delete a single
let delOneParams = {
key:'id',
ids: [1]
};
let delOneSql = xqsql.del('goods', delOneParams);
console.log('Delete (single) statement:', delOneSql);
// Delete (single) statement: DELETE FROM `goods` WHERE id = '1'
2.Delete multiple
let delMoreParams = {
key:'id',
ids: [1,2,3]
};
let delMoreSql = xqsql.del('goods', delMoreParams,'more');
console.log('Delete (multiple) statements:', delMoreSql);
// Delete (multiple) statements: DELETE FROM `goods` WHERE id in (1,2,3)
3.Delete all
let delSql = xqsql.del('goods','','all');
console.log('Delete (all) statements:', delSql);
// Delete (all) statements: DELETE FROM `goods`
View xqsql
Run this script to view the demonstration case: npm run test:node
, npm run test:browser
.