golang sql动态查询where构造，入参构造和结构体构造两种方式的实现思路

一.先介绍struct方式:

//主方法
func GenWhereByStruct(in interface{})(string,[]interface{}){
    vValue :=reflect.ValueOf(in)
    vType :=reflect.TypeOf(in)
    var tagTmp =""
    var whereMap = make([][]string,0)
    var args = make([]interface{},0)

    for i:=0;i<vValue.NumField();i++{
        tagTmp = vType.Field(i).Tag.Get("column")
        if tagTmp =="-"||tagTmp==""{
            continue
        }
        cons :=strings.Split(tagTmp,",")
        if !IfZero(vValue.Field(i).Interface()) {
            if cons[2]=="*like"{
                cons[2] = "like"
                args = append(args, "%"+vValue.Field(i).Interface().(string))
            }else if cons[2]=="like*"{
                cons[2] = "like"
                args = append(args, vValue.Field(i).Interface().(string)+"%")
            }else if cons[2]=="*like*" || cons[2]=="like"{
                cons[2] = "like"
                args = append(args, "%"+vValue.Field(i).Interface().(string)+"%")
            }else{
                args = append(args, vValue.Field(i).Interface())
            }

            if len(whereMap)==0 {
                whereMap = append(whereMap,[]string{
                    "",cons[1],cons[2],
                })
            }else{
                whereMap = append(whereMap,[]string{
                    cons[0],cons[1],cons[2],
                })
            }

            if cons[2] == "between"{
                i++
                args = append(args,vValue.Field(i).Interface())
            }
        }
    }
    where :=GenWhere(whereMap)
    return where,args
}

//辅方法
//generate where through a where [][]string
func GenWhere(whereMap [][]string)string {
    rs:=""
    if len(whereMap) != 0 {
        rs = rs + " where "
        for _, v := range whereMap {
            //v[0]表示性质，and 还是or,v[1]表示field，比如name，age,v[2]表示条件符号,=,>,<,<>,like
            if v[2] == "between" {
                rs = rs + " " + v[0] + " " + v[1] + " " + "between" + " " + "?" + " " + "and" + " " + "?" + " "
                continue
            }
            if v[2] == "in" {
                rs = rs + " " + v[0] + " " + v[1] + " " + "in" + " " +v[3]
                continue
            }
            rs = rs + " " + v[0] + " " + v[1] + " " + v[2] + " " + "?"
        }
    }
    return rs
}

func IfZero(arg interface{}) bool {
    if arg == nil {
        return true
    }
    switch v := arg.(type) {
    case int, float64, int32, int16, int64, float32:
        if v == 0 {
            return true
        }
    case string:
        if v == "" || v == "%%" || v == "%" {
            return true
        }
    case *string, *int, *int64, *int32, *int16, *int8, *float32, *float64:
        if v == nil {
            return true
        }
    case time.Time:
        return v.IsZero()
    default:
        return false
    }
    return false
}

func RemoveZero(slice []interface{}) []interface{} {
    if len(slice) == 0 {
        return slice
    }
    for i, v := range slice {
        if IfZero(v) {
            slice = append(slice[:i], slice[i+1:]...)
            return RemoveZero(slice)
            break
        }
    }
    return slice
}

使用方法和测试:

func TestGenWhereByStruct(t *testing.T) {
    type Tmp struct{
        Addr string `column:"and,addr,like*"`
        Desc string `column:"and,desc,like"`
        Job string`column:"and,job,*like"`
        Name string `column:"and,name,="`
        Sal float32 `column:"and,sal,>"`
        AgeMin int`column:"or,age,between"`
        AgeMax int `column:"or,age,between"`
        Start time.Time `column:"and,created,between"`
        Stop time.Time `column:"and,created,between"`
        Jump string `column:"-"`
    }
    var tmp = Tmp{
        Addr:"earth",
        Name:"ft",
        Sal:333,
        AgeMin:9,
        AgeMax:18,
        Desc:"happ",
        Job:"engineer",
        Jump:"jump",
    }

    t.Log(GenWhereByStruct(tmp))
}

结果:

 where   addr like ? and desc like ? and job like ? and name = ? and sal > ? or age between ? and ?  [earth% %happ% %engineer ft 333 9 18]

注意事项:
1. between的参数必须两个，即AgeMin/Start和AgeMax/Stop必须同时存在或者同时没有,违反了此条件，where语句依然正确，args会不准确
2. between参数必须小的声明在大的上面，即不可定义成{AgeMax,AgeMin}
3. Like参数’*like’匹配%xxx,*like*和’like’匹配%xxx%,’like*’匹配xxx%

二.入参式:
直接使用辅方法里的GenWhere(whereMap [][]string) string
直接上测试例子:

func TestGenWhere(t *testing.T) {
    var name = "ft"
    var age =9
    var sal =1000
    var start =  "2018-01-01"
    var stop = "2018-02-02"

    var whereMap = make([][]string,0)
    whereMap = append(whereMap,[]string{
            "","1","=",
    })
    if name!=""{
        whereMap = append(whereMap,[]string{
            "and","name","=",
        })
    }
    if sal !=0 {
        whereMap = append(whereMap,[]string{
            "or","name","<",
        })
    }
    if age!=0{
        whereMap = append(whereMap,[]string{
            "and","name",">",
        })
    }
    if start !="" && stop !=""{
        whereMap =append(whereMap,[]string{
            "and","created","between",
        })
    }
    t.Log(GenWhere(whereMap))
}

结果:

where   1 = ? and name = ? or sal < ? and age like ? and created between ? and ? 

注意事项:如果不添加1=1,如果name为”“切age有值时，就会出现’where or age like ?’

最后，如果需要把问号转换成美元:

//将sql语句中的?转换成$i
func ReplaceQuestionToDollar(sql string) string {
    var temp = 1
    start := 0
    var i = 0
L:
    for i = start; i < len(sql); i++ {
        if string(sql[i]) == "?" {
            sql = string(sql[:i]) + "$" + strconv.Itoa(temp) + string(sql[i+1:])
            temp++
            start = i + 2
            goto L
        }

        if i == len(sql)-1 {
            return sql
        }
    }
    return sql
}

func TestReplaceQuestionToDollar(t *testing.T) {
    t.Log(ReplaceQuestionToDollar("where   1 = ? and name = ? or name < ? and name > ? and created between ? and ? "))
}

结果:

where   1 = $1 and name = $2 or name < $3 and name > $4 and created between $5 and $6 

~~~~~~~~~~~~~~~~~~~~~~~~~希望对各位有所帮助