MTP20_IF/utils/sqlUtils.go

/**
* @Author: zou.yingbin
* @Create  : 2021/1/29 9:30
* @Modify  : 2021/1/29 9:30
 */

package utils

import (
	"fmt"
)

// sql辅助构建工具

type SQLVal string

// String 返回自身字符串
func (r *SQLVal) String() string {
	return string(*r)
}

// FormatParam 格式化参数
func (r *SQLVal) FormatParam(a ...any) {
	*r = SQLVal(fmt.Sprintf(r.String(), a...))
}

// And 增加and 条件
func (r *SQLVal) And(fieldName string, val interface{}) {
	if _, ok := val.(string); ok {
		*r = *r + SQLVal(fmt.Sprintf(" and %v = '%v'", fieldName, val))
	} else {
		*r = *r + SQLVal(fmt.Sprintf(" and %v = %v", fieldName, val))
	}
}

// And 增加and 条件, bAdd参数省了在外层写if语句
// 例如:  sqlId.AndEx("t.areauserid", r.areauserid, r.areauserid > 0)
// 如果没有bAdd参数，外层就得写if r.areauserid > 0 {...}
func (r *SQLVal) AndEx(fieldName string, val interface{}, bAdd bool) {
	if bAdd {
		r.And(fieldName, val)
	}
}

func AndBetween[T int | float64](r *SQLVal, fieldName string, start T, end T) {
	if start != 0 && end != 0 {
		*r = *r + SQLVal(fmt.Sprintf(" AND %v BETWEEN %v AND %v", fieldName, start, end))
	} else if start != 0 && end == 0 {
		r.BiggerOrEq(fieldName, start)
	} else if start == 0 && end != 0 {
		r.LessOrEq(fieldName, end)
	}
}

// 类似 AndEx, 但是不会为string类型加单引号
func (r *SQLVal) AndInterEx(fieldName string, val interface{}, bAdd bool) {
	if bAdd {
		*r = *r + SQLVal(fmt.Sprintf(" and %v = %v", fieldName, val))
	}
}

// Bigger 大于
func (r *SQLVal) Bigger(fieldName string, val interface{}) {
	*r = *r + SQLVal(fmt.Sprintf(" and %v > %v", fieldName, val))
}

// BiggerOrEq 大于等于
func (r *SQLVal) BiggerOrEq(fieldName string, val interface{}) {
	*r = *r + SQLVal(fmt.Sprintf(" and %v >= %v", fieldName, val))
}

// Less 小于等于
func (r *SQLVal) Less(fieldName string, val interface{}) {
	*r = *r + SQLVal(fmt.Sprintf(" and %v < %v", fieldName, val))
}

// LessOrEq 小于等于
func (r *SQLVal) LessOrEq(fieldName string, val interface{}) {
	*r = *r + SQLVal(fmt.Sprintf(" and %v <= %v", fieldName, val))
}

// Join 自由增加条件
func (r *SQLVal) Join(condition string) {
	*r = *r + SQLVal(condition)
}

// JoinEx  bJoin是否增加
func (r *SQLVal) JoinEx(bJoin bool, condition string) {
	if bJoin {
		r.Join(condition)
	}
}

// JoinFormat
func (r *SQLVal) JoinFormat(strFmt string, param ...interface{}) {
	*r += SQLVal(fmt.Sprintf(strFmt, param...))
}

// AndLike 模糊匹配, 参数 val 为空时不进行匹配
func (r *SQLVal) AndLike(fieldName string, val string) {
	if len(val) > 0 {
		*r += SQLVal(fmt.Sprintf(" and %v like '%%%v%%'", fieldName, val))
	}
}

// AndLikes 模糊匹配, 参数 param 为空时不进行匹配
func (r *SQLVal) AndLikes(fieldName string, param ...string) {
	if len(param) > 0 {
		for _, val := range param {
			*r += SQLVal(fmt.Sprintf(" and %v like '%%%v%%'", fieldName, val))
		}
	}
}

func (r *SQLVal) AndLikes2(fieldName string, fieldName2 string, param ...string) {
	if len(param) > 0 {
		for _, val := range param {
			if len(val) > 0 {
				*r += SQLVal(fmt.Sprintf(" and (%v like '%%%v%%' or %v like '%%%v%%')", fieldName, val, fieldName2, val))
			}
		}
	}
}

// OrderBy 排序
func (r *SQLVal) OrderBy(fieldName string) {
	if len(fieldName) > 0 {
		*r += SQLVal(fmt.Sprintf(" ORDER BY %v", fieldName))
	}
}

// OrderByDesc 反向排序
func (r *SQLVal) OrderByDesc(fieldName string) {
	if len(fieldName) > 0 {
		*r += SQLVal(fmt.Sprintf(" ORDER BY %v DESC", fieldName))
	}
}

// Page 分页查询 pageNum-页码 第几页, pageSize-每页大小
func (r *SQLVal) Page(pageNum int, pageSize int) {
	if pageNum <= 0 || pageSize <= 0 {
		return
	}
	cntBegin := (pageNum-1)*pageSize + 1
	cntEnd := pageNum * pageSize
	strPage := `select * from ( 
		select rownum cnt, count(*) over () total, a.* from (%v) a 
		) where cnt >= %v and cnt <= %v`
	strPage = fmt.Sprintf(strPage, r.String(), cntBegin, cntEnd)
	*r = SQLVal(strPage)
}