动态⽣成SQL语句,通⽤查询接⼝
很多时候,由于业务变化,使得同⼀数据表在不同业务模块中查询条件不同;例如:⽤户表在其模块管理页⾯中,需要根据⽤户名,真实姓名查询;⽽在课程报名时,则需要根据⽤户所在专业进修过滤。这种情况在后端查询接⼝中全部兼容,另外实现动态SQL语句⽣成,满⾜条件由前端进⾏根据需要进⾏配置。
1、查询条件配置如下:
{"groupOp":"AND", "rules":[{"field":"name","op":"cn","data":"1"},{"field":"major","op":"eq","data":"110"}], "groups":null}
// 表⽰ name like '%1%' and major = 110
同样也可以表⽰更复杂的条件,如下
{
"groupOp":"AND",
"rules":[{"field":"major","op":"eq","data":"110"}],
"groups":[
{
"groupOp":"OR",
"rules":[{"field":"name","op":"cn","data":"ad"},{"field":"login_id","op":"cn","data":"ad"}],
"groups":[]
}
]
}
// 表⽰ ( `major` = ? ) AND (( `name` LIKE ? OR `login_id` LIKE ? ))
2、后端解析
注意:后端⽤go语⾔,使⽤gin+xorm
2.1、实体对象:
type QueryResult struct {
Page int `json:"page"` //页码,从1开始
TotalRows int `json:"totalRows"` // 页⾏数
TotalPages int `json:"totalPages"` // 总页数
Result interface{} `json:"result"` // 结果
}
type QueryParam struct {
Rows int `form:"rows"json:"rows"` // 页⾏数
Page int `form:"page"json:"page"` // 页码,从1开始
Sort string `form:"sort"json:"sort"` // 排序,类似与name,age desc
Filter sqlgen.Filter `form:"filter"json:"filter"` // 过滤条件
Table string `form:"-"json:"-"` // 表名
Selects []string `form:"-"json:"-"` // select 字段列表
JoinParams []JoinParam `form:"-"json:"-"` // 连接表列表
}
2.2 解析
/**
* @brief: ⽣成where条件语句
* @ @param1 tableName: 表名
* @return1: where条件语句
* @return2: 条件值列表
* @return3: u错误信息
*/
func (f *Filter) GenWhereStmt(tableName string) (string, []interface{}, error) {
whereStatement := ""
groupWhereStatement := ""
datas := make([]interface{}, 0)
isAnd := true
if strings.ToLower(strings.TrimSpace(f.GroupOp)) == "or" {
isAnd = false
isAnd = false
}
if len(f.Rules) > 0 {
whereStr, ruleDatas := f.GenRuleWhereStmt(tableName, f.Rules[0])
if whereStr != "" {
whereStatement = whereStr
for _, ruleData := range ruleDatas {
datas = append(datas, ruleData)
}
} else {
return whereStatement, datas, errors.New(fmt.Sprintf("the 0 rule is invalid"))
}
for i := 1; i < len(f.Rules); i++ {
whereStr, ruleDatas = f.GenRuleWhereStmt(tableName, f.Rules[i])
if whereStr != "" {
if isAnd {
whereStatement += " AND " + whereStr
} else {
whereStatement += " OR " + whereStr
}
for _, ruleData := range ruleDatas {
datas = append(datas, ruleData)
}
} else {
return whereStatement, datas, errors.New(fmt.Sprintf("the %d rule is invalid", i)) }
}
}
if len(f.Groups) > 0 {
whereStr, groupDatas, err := f.Groups[0].GenWhereStmt(tableName)
if err != nil {
return whereStatement, datas, err
}
if whereStr != "" {
groupWhereStatement = whereStr
for _, groupData := range groupDatas {
datas = append(datas, groupData)
}
} else {
return whereStatement, datas, errors.New("the 0 group is invalid")
}
for i := 1; i < len(f.Groups); i++ {
whereStr, groupDatas, err := f.Groups[i].GenWhereStmt(tableName)
if err != nil {
return whereStatement, datas, err
}
if whereStr != "" {
if isAnd {
groupWhereStatement += " AND " + whereStr
} else {
groupWhereStatement += " OR " + whereStr
}
for _, groupData := range groupDatas {
datas = append(datas, groupData)
}
} else {
return whereStatement, datas, errors.New(fmt.Sprintf("the %d group is invalid", i)) }
}
if groupWhereStatement != "" {
groupWhereStatement = "(" + groupWhereStatement + ")"
}
}
}
if whereStatement == "" {
whereStatement = groupWhereStatement
} else {
sql语句实现的四种功能whereStatement = "(" + whereStatement + ")"
if groupWhereStatement != "" {
if isAnd {
whereStatement += " AND " + groupWhereStatement } else {
whereStatement += " OR " + groupWhereStatement }
}
}
return whereStatement, datas, nil
}
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论