一.先介绍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
~~~~~~~~~~~~~~~~~~~~~~~~~希望对各位有所帮助