go语言对gorm不固定条件查询封装

主要对gorm不定条件查询数据时的一个封装【灵感来于laravel项目中对db的封装laravel版本DB,EloquentORM不固定条件查询封装】

封装方法在 app/models/entity/Gorm.go文件里

条件说明

在写sql语句时，where的条件主要是 key=1 and key2=2或者key=1 or key2=2这种形式[还有and与or混合]。
认真分析会发现条件有4部分组成--字段名、操作符、查询值、与前一个条件的关系[and,or]，这样就很容易实现了。下面就是一个说明，为了简化，其中会默认省略一些特征。

["字段名","操作符","查询值","与前一个条件的关系[默认and]"]

1.如果是等于，可以省略"操作符" [默认and，如果是and，可以不写]:
[]interface{}{"username", "chen"}或[]interface{}{"username","=" , "chen"}

2.大于:
[]interface{}{"createtime", ">", "2019-1-1"}

3.如果为or，那就必须写全，4个部分都不能少：
[]interface{}{"username", "=", "chen", "or"}

4.其它的where兼容gorm的where方法

5.特殊说明
[]interface{}{"username = ? or nickname = ?", "chen", "yond"}这种拼接参数，在封装时做了特殊处理

测试

启动项目

go run main.go

访问测试地址：
http://127.0.0.1:8100/api/v1/user/test

带分页的地址：http://127.0.0.1:8100/api/v1/user/list

1、and条件测试

where := []interface{}{
    []interface{}{"id", "=", 1},
    []interface{}{"username", "chen"},
}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECT * FROM `users`  WHERE (id = 1)and(username = 'chen')

2、结构体条件测试

where := user.User{ID: 1, UserName: "chen"}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECT * FROM `users`  WHERE (id = 1) and (username = 'chen')

3、in,or条件测试

where := []interface{}{
    []interface{}{"id", "in", []int{1, 2}},
    []interface{}{"username", "=", "chen", "or"},
}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECT * FROM `users`  WHERE (id in ('1','2')) OR (username = 'chen')

3.1、not in,or条件测试

where := []interface{}{
    []interface{}{"id", "not in", []int{1}},
    []interface{}{"username", "=", "chen", "or"},
}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECT * FROM `users`  WHERE (id not in ('1')) OR (username = 'chen')

4、map条件测试

where := map[string]interface{}{"id": 1, "username": "chen"}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECT * FROM `users`  WHERE (`users`.`id` = '1') AND (`users`.`username` = 'chen')

5、and,or混合条件测试
注：[]interface{}{"username = ? or nickname = ?", "chen", "yond"}这种拼接参数，在封装时做了特殊处理

where := []interface{}{
    []interface{}{"id", "in", []int{1, 2}},
    []interface{}{"username = ? or nickname = ?", "chen", "yond"},
}
db, err = entity.BuildWhere(db, where)
db.Find(&users)
// SELECTSELECT * FROM `users`  WHERE (id in ('1','2')) AND (username = 'chen' or nickname = 'yond')

//注：不要使用下方方法
/*
where := []interface{}{
    []interface{}{"id", "in", []int{1, 2}},
    []interface{}{
        []interface{}{"username", "=", "chen"},
        []interface{}{"username", "=", "yond", "or"},
    },
}
// 返回sql: SELECT * FROM `users`  WHERE (id in ('1','2')) AND (username = 'chen') OR (username = 'yond')
// 与设想不一样
// 经过测试，gorm底层暂时不支持db.Where(func(db *gorm.DB) *gorm.DB {})闭包方法
*/

还支持分页查询，详见github

基于gin写的一个demo框架
https://github.com/qicmsg/go_vcard

最后编辑于：2020.03.03 22:14:44