beego 纯sql分页

package models

import (
   "bytes"
   "fmt"
   "log"
   "reflect"
   "strconv"
   "strings"
   "time"
)

type PageUtil struct {
   Current   int64 `json:"current"`
   PageSize  int64 `json:"pageSize"`
   TotalPage int64 `json:"totalPage"`
   Total     int64 `json:"total"`
   // 前端返回出去是查询出来的list
   Object interface{} `json:"object"`
   // 后端返回出去是查询出来的list
   List interface{} `json:"list"`
   // sql param
   SqlParam []string `json:"sqlParam"`
}

func (q *PageUtil) Offset() int64 {
   offset := (q.Current - 1) * q.PageSize
   if offset < 0 {
      offset = 0
   }
   return offset
}

func (q *PageUtil) Limit() int64 {
   return q.PageSize
}

func (q *PageUtil) GetPageLimitSql() (sql string) {
   log.Println("11111111111:" + reflect.TypeOf(q.Object).Name())
   QueryBuilder := mysqlBuilder().Select(" * ").From(
      q.ToLowerAndUnderline(reflect.TypeOf(q.Object).Name()).String()).
      Where(q.GetWhereSql(q.Object).String()).Limit(int(q.Limit())).Offset(int(q.Offset()))

   log.Println("print sql = " + QueryBuilder.String())
   return QueryBuilder.String()
}

func (q *PageUtil) GetCount() (sql string) {
   QueryBuilder := mysqlBuilder().Select(" count(*) ").From(
      q.ToLowerAndUnderline(reflect.TypeOf(q.Object).Name()).String()).
      Where(q.GetWhereSql(q.Object).String())

   log.Println("print sql = " + QueryBuilder.String())
   return QueryBuilder.String()
}

// 获取当前对象中不为null的字段
func (q *PageUtil) GetWhereSql(structName interface{}) (sql *bytes.Buffer) {
   // GetWhereSql
   t := reflect.TypeOf(structName)
   v := reflect.ValueOf(structName)
   if t.Kind() == reflect.Ptr {
      t = t
   }
   if t.Kind() != reflect.Struct {
      log.Println("Check type error not Struct")
      return
   }

   fieldNum := t.NumField()
   sql = bytes.NewBufferString("")
   sql.WriteString("  1=1 ")
   for i := 0; i < fieldNum; i++ {
      sql.WriteString(q.GetConcatSql(t.Field(i), v.Field(i)))
   }

   return sql
}

// 拼接sql
func (q *PageUtil) GetConcatSql(structField reflect.StructField, v reflect.Value) (sqlstr string) {
   sql := bytes.NewBufferString("")
   // 依次判断字段是否为空如果不为空那么拼接字符串
   value := q.getFieldValue(structField, v)
   log.Println("result :"+structField.Name+" Type:", structField.Type.Name()+"value", value)
   // 判断传递的各类值为空的情况
   if value != "" && value != "0" &&value !="01-01-01 00:00:00" {
      // 判断类型拼接字符串
      if structField.Type.Name() == "int64" || structField.Type.Name() == "int" {

         sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = " + value)
      } else if structField.Type.Name() == "bool" {
         // bool类型在数据库中表示的类型是tinyint 0表示false
         if value == "false" {
            sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = 0")
         } else {
            sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = 1")
         }
      } else if structField.Type.Name() == "Time" {
         sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = '"+value+"'")
      } else if structField.Type.Name() == "string" {

         sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = '" + value + "'")
      }
   }
   return sql.String()
}

// 获取当前字段的值
func (q *PageUtil) getFieldValue(structField reflect.StructField, v reflect.Value) (value string) {
   if structField.Type.Name() == "int64" || structField.Type.Name() == "int" {
      return strconv.FormatInt(v.Int(), 10)
   } else if structField.Type.Name() == "string" {
      return v.String()
   } else if structField.Type.Name() == "bool" {
      return strconv.FormatBool(v.Bool())
   }else if structField.Type.Name() == "Time" {
      t  := v.Interface().(time.Time)
      return FormatDateTime(t)
      //return t
      //return strconv.F(v.())
   }
   return v.String()
}

// 转化字符形式如：AppRepo 转app_repo
func (q *PageUtil) ToLowerAndUnderline(str string) (byte *bytes.Buffer) {

   byte = bytes.NewBufferString("")
   for i := 0; i < len(str); i++ {
      if i == 0 {
         byte.WriteString(strings.ToLower(string(str[i])))
      } else if str[i] >= 'A' && str[i] <= 'Z' {
         byte.WriteString("_" + strings.ToLower(string(str[i])))
      } else {
         byte.WriteString(string(str[i]))
      }
   }
   return
}


func FormatDateTime(t time.Time) string {
   var buffer bytes.Buffer
   buffer.WriteString(fmt.Sprintf("%.2d-%.2d-%.2d %.2d:%.2d:%.2d",  t.Year(),t.Month(),t.Day(), t.Hour(), t.Minute(),t.Second()))
   // fmt.Println(buffer.String())
   return buffer.String()
}

直接

func (m *appDiskModel) PageList(p PageUtil) (PageUtil, error) {
   // qb := mysqlBuilder().Select("T0.*,T3.name as namespace_name,(case when isnull(T1.id ) then 0 else 1 end ) as starred").From("app T0")
   // 接口转对象
   var AppDiskVar AppDisk
   jsonbyte, _ := json.Marshal(p.Object)
   json.Unmarshal(jsonbyte, &AppDiskVar)
   AppDiskList := make([]AppDisk, 0)

   p.Object = AppDiskVar
   // 获取总行数
   error := Ormer().Raw(p.GetCount()).QueryRow(&p.Total)
   // 获取获取当前页list
   _, error = Ormer().Raw(p.GetPageLimitSql()).QueryRows(&AppDiskList)

   p.List = AppDiskList

   return p, error
}