package user
import (
"bytes"
"fmt"
"reflect"
"strconv"
"strings"
"text/template"
)
const (
COLUMNS = `
id ID,
code Code
`
SEARCH_COUNT = `
SELECT
count(0) count
FROM user
WHERE 1=1
`
SEARCH = `
SELECT
{{COLUMNS}}
FROM user
LEFT JOIN user
`
INSERT = `
{{$size := (len .) |Sub 1}}
INSERT INTO sm_user(id,code,name)
VALUES
{{range $index, $elem := .}}
(E_SEQ('sm_user'),'{{.Code}}'){{ if ne $index $size }} ,{{else}};{{end}}
{{end}}
`
UPDATE = `
UPDATE sm_user set
{{if ne .Code ""}} code='{{.Code}}', {{end}}
{{if ne .Name ""}} name='{{.Name}}',{{end}}
update_time=NOW()
WHERE id={{.ID}}
`
DELETE = ` UPDATE sm_user set deleted_flag='Y' WHERE id={{.ID}} `
WHERE = `
{{if ne .Code ""}} and smu.code = '{{.Code}}' , {{end}}
{{if ne .Name ""}} and smu.name = '{{.Name}}' {{end}}
`
)
var (
count *template.Template
search *template.Template
insert *template.Template
update *template.Template
delete *template.Template
condition *template.Template
funcs map[string]interface{}
)
//给template添加自定义的方法
func init(){
funcs = template.FuncMap{"Add": Add,"Sub":Sub}
}
//根据业务组装相应的SQL
func assembly(model interface{}, mode string) string {
buffer := bytes.NewBufferString("")
switch mode {
case "searchCount":
if count == nil {
count = template.New("count")
count.Parse(SEARCH_COUNT)
}
count.Execute(buffer, model)
buffer.WriteString(where(model, mode))
break
case "get":
if count == nil {
count = template.New("search")
count.Parse(SEARCH_COUNT)
}
count.Execute(buffer, model)
buffer.WriteString(" limit 1")
break
case "search":
if search == nil {
search = template.New("search")
//此处可以修改为子模板 {{template "COLUMNS" .}}
search.Parse(strings.Replace(SEARCH, "{{COLUMNS}}", COLUMNS, -1))
}
search.Execute(buffer, model)
buffer.WriteString(where(model, mode))
break
case "searchPage":
if search == nil {
search = template.New("search")
search.Parse(strings.Replace(SEARCH, "{{COLUMNS}}", COLUMNS, -1))
}
search.Execute(buffer, model)
buffer.WriteString(where(model, mode))
buffer.WriteString(limit(model))
break
case "insert":
if insert == nil {
insert = template.New("insert")
insert.Parse(INSERT)
}
insert.Execute(buffer, model)
break
case "batchInsert":
if insert == nil {
insert = template.New("insert").Funcs(funcs)
insert.Parse(INSERT)
}
insert.Execute(buffer, model)
break
case "update":
if update == nil {
update = template.New("update")
update.Parse(UPDATE)
}
update.Execute(buffer, model)
break
case "delete":
if delete == nil {
delete = template.New("delete")
update.Parse(DELETE)
}
update.Execute(buffer, model)
break
}
fmt.Println(buffer.String())
return buffer.String()
}
//条件where
func where(model interface{}, mode string) string {
buffer := bytes.NewBufferString("")
if condition == nil {
condition = template.New("where")
condition.Parse(WHERE)
}
condition.Execute(buffer, model)
return buffer.String()
}
//分页limit
func limit(model interface{}) string {
invoke := reflect.ValueOf(model).Elem()
//fmt.Println("%t",invoke.Kind())
//fmt.Println("%t",invoke.FieldByName("PageNo"))
pageNo := invoke.FieldByName("PageNo").Int()
pageSize := invoke.FieldByName("PageSize").Int()
offset := (pageNo - 1) * pageSize
return " limit " + strconv.FormatInt(offset, 10) + "," + strconv.FormatInt(pageSize, 10)
}
func Add(left int, right int) int{
return left+right
}
func Sub(left int, right int) int{
return right - left
}
原文
https://studygolang.com/articles/16005