因为项目原因,需要前端写sql,所以弄了一套sql条件拼接的js工具
-
- /*常量
- LT : " < ",
- LE : " <= ",
- GT : " > ",
- GE : " >= ",
- NE : " != ",
- EQ : " = ",
- LIKE : " like ",
- OR : " or ",
- IN : " in ",
- */
-
- let sqlUtil = {
- // 拼接sql
- buildSql : '',
-
- /*函数*/
- lt: lessThan,
- lte: lessThanOrEqual,
- gt: greaterThan,
- gte: greaterThanOrEqual,
- ne: notEqual,
- eq: equal,
- toLike: sqlLike,
- leftLike:leftLike,
- rightLike:rightLike,
- in: sqlIn,
- notIn: notIn,
- isNull: isNull,
- isNotNull: isNotNull,
- isEmpty: isEmpty,
- isEmptyOrNull: isEmptyOrNull,
- isNotEmpty:isNotEmpty,
- isNotEmptyAndNotNull:isNotEmptyAndNotNull,
- sor: simpleOr,
- toBetween: between,
- notBetween : notBetween,
- sqlOr: sqlOr,
- orderByDesc : orderByDesc,
- orderByAsc: orderByAsc,
- orderBy: orderBy,
- notDelete : notDelete,
- userDefined:userDefined,
- toSql: toSql
- };
-
- /**
- * 小于 <
- * sqlUtil.lt("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function lessThan(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" < '"+fieldValue+"' ";
- }
- return this;
- }
-
- /**
- * 小于等于 <=
- * sqlUtil.le("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function lessThanOrEqual(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" <= '"+fieldValue+"' ";
- }
- return this;
- }
-
- /**
- * 大于 >
- * sqlUtil.gt("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function greaterThan(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" > '"+fieldValue+"' ";
- }
- return this;
- }
-
-
- /**
- * 大于等于 >=
- * sqlUtil.ge("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function greaterThanOrEqual(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" >= '"+fieldValue+"' ";
- }
- return this;
- }
-
-
- /**
- * 不等于 !=
- * sqlUtil.ne("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function notEqual(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" != '"+fieldValue+"' ";
- }
- return this;
- }
-
- /**
- * 等于 =
- * sqlUtil.eq("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function equal(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
-
- if(!fieldValue){
- return this;
- }
-
- if(typeof fieldValue === 'string' && fieldValue.trim() === ''){
- return this;
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" = '"+fieldValue+"' ";
- return this;
- }
-
- /**
- * 模糊搜索 like
- * sqlUtil.like("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function sqlLike(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- fieldValue = fieldValue.replaceAll('%','\\%');
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" like '%"+fieldValue+"%' ";
- }
- return this;
- }
-
- /**
- * 模糊搜索 左like
- * sqlUtil.leftLike("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function leftLike(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- fieldValue = fieldValue.replaceAll('%','\\%');
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" like '%"+fieldValue+"' ";
- }
- return this;
- }
-
-
-
- /**
- * 模糊搜索 右like
- * sqlUtil.rightLike("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function rightLike(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- fieldValue = fieldValue.replaceAll('%','\\%');
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" like '"+fieldValue+"%' ";
- }
- return this;
- }
-
- /**
- * in查询
- * sqlUtil.in("field_name",fieldValueArray)
- * @param fieldName 字段名
- * @param fieldValueArray 字段值数组
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function sqlIn(fieldName,fieldValueArray = [],condition = true){
- if(!condition){
- return this;
- }
- if (fieldValueArray.length === 0){
- return this;
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" in ( ";
- for (const fieldValue of fieldValueArray) {
- if(typeof fieldValue === "string"){
- sqlUtil.buildSql = sqlUtil.buildSql + "'" + fieldValue + "',";
- }else{
- sqlUtil.buildSql = sqlUtil.buildSql + fieldValue + ",";
- }
-
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length-1) + ") ";
- return this;
- }
-
- /**
- * not in查询
- * sqlUtil.notIn("field_name",fieldValueArray)
- * @param fieldName 字段名
- * @param fieldValueArray 字段值数组
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function notIn(fieldName,fieldValueArray = [],condition = true){
- if(!condition){
- return this;
- }
- if (fieldValueArray.length === 0){
- return this;
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" not in ( ";
- for (const fieldValue of fieldValueArray) {
- sqlUtil.buildSql = sqlUtil.buildSql + fieldValue + " ,";
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length-1) + " ) ";
- return this;
- }
-
-
- /**
- * is null
- * sqlUtil.isNull("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isNull(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" is null '"+fieldValue+"%' ";
- }
- return this;
- }
-
-
- /**
- * is not null
- * sqlUtil.isNotNull("field_name",fieldValue)
- * @param fieldName 字段名
- * @param fieldValue 字段值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isNotNull(fieldName,fieldValue,condition = true){
- if(!condition){
- return this;
- }
- if(fieldValue && fieldValue.trim() !== ''){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" is not null '"+fieldValue+"%' ";
- }
- return this;
- }
-
-
- /**
- * 是否为空字符
- * sqlUtil.isEmpty("field_name",fieldValue)
- * @param fieldName 字段名
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isEmpty(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(fieldName){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" = '' ";
- }
- return this;
- }
-
- /**
- * 是否为 空字符或null
- * sqlUtil.isEmpty("field_name",fieldValue)
- * @param fieldName 字段名
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isEmptyOrNull(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(fieldName){
- sqlUtil.buildSql = sqlUtil.buildSql + "and ("+fieldName+" = '' or "+fieldName+" is null)";
- }
- return this;
- }
-
- /**
- * 是否为空字符
- * sqlUtil.isNotEmpty("field_name",fieldValue)
- * @param fieldName 字段名
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isNotEmpty(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(fieldName){
- sqlUtil.buildSql = sqlUtil.buildSql + "and "+fieldName+" != '' ";
- }
- return this;
- }
-
- /**
- * 是否为空字符
- * sqlUtil.isNotEmpty("field_name",fieldValue)
- * @param fieldName 字段名
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function isNotEmptyAndNotNull(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(fieldName){
- sqlUtil.buildSql = sqlUtil.buildSql + "and ("+fieldName+" != '' or "+fieldName+" is not null)";
- }
- return this;
- }
-
- /**
- * between("age", 20, 22);
- * @param fieldName 字段名
- * @param fieldValue1 第一个值
- * @param fieldValue2 第二个值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
-
- function between(fieldName,fieldValue1,fieldValue2,condition = true){
- if(!condition){
- return this;
- }
- if(!fieldValue1 || !fieldValue2){
- return this;
- }
-
- if(typeof fieldValue1 === 'string' && typeof fieldValue2 === 'string'){
- // 有可能是日期
- sqlUtil.buildSql = sqlUtil.buildSql + "and '"+fieldName+"' between '"+fieldValue1+"' and '"+fieldValue2+"' ";
- }else{
- sqlUtil.buildSql = sqlUtil.buildSql + "and '"+fieldName+"' between "+fieldValue1+" and "+fieldValue2+" ";
- }
-
- return this;
- }
-
- /**
- * sqlUtil.notBetween("field_name",20,22)
- * notBetween("age", 20, 22);
- * @param fieldName 字段名
- * @param fieldValue1 第一个值
- * @param fieldValue2 第二个值
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
-
- function notBetween(fieldName,fieldValue1,fieldValue2,condition = true){
- if(!condition){
- return this;
- }
- if(fieldName){
- sqlUtil.buildSql = sqlUtil.buildSql + "and '"+fieldName+"' not between "+fieldValue1+" and "+fieldValue2+" ";
- }
- return this;
- }
-
- /**
- * sqlUtil.orderByAsc("field_name") || sqlUtil.orderByAsc(["field_name1","field_name2"])
- * "order by id asc"
- * @param fieldName 字段名 || 数组
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function orderByAsc(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(typeof fieldName === 'string'){
- sqlUtil.buildSql = sqlUtil.buildSql +" order by "+fieldName+" asc"
- }else{
- sqlUtil.buildSql = sqlUtil.buildSql +" order by ";
- for (const itemName of fieldName) {
- sqlUtil.buildSql = sqlUtil.buildSql + itemName + " asc,";
- }
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length-1) + " ";
- }
-
- return this;
- }
-
- /**
- * sqlUtil.orderByDesc("field_name") || sqlUtil.orderByDesc(["field_name1","field_name2"])
- * "order by id desc"
- * @param fieldName 字段名 || 数组
- * @param condition 判断条件是否成立
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function orderByDesc(fieldName,condition = true){
- if(!condition){
- return this;
- }
- if(typeof fieldName === 'string'){
- sqlUtil.buildSql = sqlUtil.buildSql +" order by "+fieldName+" desc"
- }else{
- sqlUtil.buildSql = sqlUtil.buildSql +" order by ";
- for (const itemName of fieldName) {
- sqlUtil.buildSql = sqlUtil.buildSql + itemName + " desc,";
- }
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length-1) + " ";
- }
-
- return this;
- }
-
- /**
- * 自定义排序
- * @param fieldList 字段名或数组[{fileName:'',order:''}],order的值为 desc|asc
- * @param condition
- * @returns {orderBy}
- */
- function orderBy(fieldList,condition = true){
- if(!condition){
- return this;
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql +" order by ";
- for (const item of fieldList) {
- sqlUtil.buildSql = sqlUtil.buildSql + item.fileName +" "+item.order +",";
- }
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length-1) + " ";
-
- return this;
- }
-
-
-
- /**
- * 简单的或条件 or
- * const paramMap = new Map([
- * ["field_name1",fieldValue1],
- * ["field_name2",fieldValue2],
- * ["field_name3",fieldValue3],
- * ])
- *
- * sqlUtil.sor(paramMap)
- * @param paramMap 参数集
- *
- * @param condition
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function simpleOr(paramMap,condition = true){
- if(!condition){
- return this;
- }
- if(! paramMap){
- return this;
- }
-
- sqlUtil.buildSql += "and ( ";
- for(let [index,field] of paramMap.entries()){
- sqlUtil.buildSql = sqlUtil.buildSql + field[0]+" = '"+field[1]+"' or ";
- }
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0, sqlUtil.buildSql.length - 4) + ") ";
-
- return this;
- }
-
-
- /**
- * 或条件 or
- *
- * sqlUtil.OR(paramMap)
- * @param paramSqlList sql集
- * @param condition
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function sqlOr(paramSqlList= [],condition = true){
- if(!condition){
- return this;
- }
-
- if(paramSqlList.length === 0){
- return this;
- }
-
- sqlUtil.buildSql = sqlUtil.buildSql +" and (";
- for (const paramSql of paramSqlList) {
- sqlUtil.buildSql = sqlUtil.buildSql + " or "
- }
- sqlUtil.buildSql = sqlUtil.buildSql.substring(0,sqlUtil.buildSql.length - 3) + ") "
-
- return this;
- }
-
- /**
- * 查询没有被逻辑删除的
- * @returns {{sqlUtil}} 拼接后的sqlUtil
- */
- function notDelete(){
- sqlUtil.buildSql += "and is_delete = '0' "
- return this;
- }
-
- /**
- * 自定义sql,
- * @param paramSql 'and {condition}'
- * @param condition 条件
- */
- function userDefined(paramSql,condition = true){
- if(!condition){
- return this;
- }
- sqlUtil.buildSql += paramSql;
- return this;
-
- }
-
- /**
- * 获取拼装后的sql
- * @returns {string}
- */
- function toSql(){
- let sql = '';
- //判断有没有orderBy
- if(sqlUtil.buildSql.indexOf("order by") != -1){
- let arr = sqlUtil.buildSql.split("order by");
- sql = arr[0] + " and is_delete = '0' "+"order by"+arr[1];
- }else{
- sql = sqlUtil.notDelete()["buildSql"];
- }
- // sqlUtil.notDelete().buildSql
-
- sqlUtil.buildSql = '';
- console.log("sql:"+sql);
- return sql;
- }
-
- export default sqlUtil;
-
-
或许某些场景,例如用户自定义条件的场景才用得到吧?这篇文章如能提供到帮助,方便的话请在评论下留言,我也想知道还能用到什么地方。
使用方式参考如下(使用userDefined方法可以利用sql函数,并进行条件拼接):
- return sqlUtil
- .toLike("discussion_title",searchParam.discussionTitle)
- .eq("division_code",searchParam.divisionCode)
- .eq("party_grid_deliberation_id",searchParam.partyGridDeliberationId)
- .userDefined(" and (DATE_FORMAT(discussion_time_start, '%Y-%m') = '"+this.searchParam.discussionYearMonth+"' or DATE_FORMAT(discussion_time_end, '%Y-%m') = '"+this.searchParam.discussionYearMonth+"') ",this.searchParam.discussionYearMonth)
- .orderByDesc("discussion_time_start")
- .toSql();
另外进阶版,可以结合jq的extend,进一步封装,像后端开发那样,把业务代码写到前端来。如图:
fwptPost(表名,提交的json对象)。
这样做当然是有缺陷,需要解决事务一致性的问题,要考虑如何联表,要考虑性能。