Golang Gin Mysql+Gorm 花活操作构建器（小笔记）

为了方便啥都想搞快些，操作方式呢都是链式操作，玩过ThinkPHP的应该都能玩的开，注释里有使用示例 ！……：）

/*
+--------------------------------------------------------------------------------
| If this code works, it was written by Xven. If not, I don't know who wrote it.
+--------------------------------------------------------------------------------
| Statement: An Ordinary Person
+--------------------------------------------------------------------------------
| Author: Xven <QQ:270988107>
+--------------------------------------------------------------------------------
| Copyright (c) 2024 Xven All rights reserved.
+--------------------------------------------------------------------------------
*/
package utils

import (
	"fmt"
	"xven-master/config"

	"gorm.io/gorm"
)

/**
 * 数据库构建器结构体
 * @Author Xven　<270988107@qq.com>
 */
type DBBuilder struct {
	db    *config.DB
	table string
}

/**
 * 创建新的数据库构建器实例
 * @Author Xven　<270988107@qq.com>
 * @return {*DBBuilder}
 */
func NewDBBuilder() *DBBuilder {
	return &DBBuilder{
		db: config.DB,
	}
}

/**
 * 设置表名
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * db.Table("users")
 */
func (b *DBBuilder) Table(name string) *DBBuilder {
	b.table = name
	return b
}

/**
 * Where条件查询
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * // 简单条件
 * db.Table("users").Where("id", 1)
 * // 比较运算
 * db.Table("users").Where("age", ">", 18)
 * // 多条件
 * db.Table("users").Where(map[string]interface{}{"name": "张三", "age": 18})
 */
func (b *DBBuilder) Where(args ...interface{}) *DBBuilder {
	switch len(args) {
	case 2:
		b.db = b.db.Where(fmt.Sprintf("%v = ?", args[0]), args[1])
	case 3:
		b.db = b.db.Where(fmt.Sprintf("%v %v ?", args[0], args[1]), args[2])
	case 1:
		if m, ok := args[0].(map[string]interface{}); ok {
			b.db = b.db.Where(m)
		}
	}
	return b
}

/**
 * 排序
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * // 升序
 * db.Table("users").Order("id", 1)
 * // 降序
 * db.Table("users").Order("id", 2)
 */
func (b *DBBuilder) Order(field string, sort int) *DBBuilder {
	if sort == 1 {
		b.db = b.db.Order(fmt.Sprintf("%s ASC", field))
	} else {
		b.db = b.db.Order(fmt.Sprintf("%s DESC", field))
	}
	return b
}

/**
 * 限制查询数量
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * db.Table("users").Limit(10)
 */
func (b *DBBuilder) Limit(limit int) *DBBuilder {
	b.db = b.db.Limit(limit)
	return b
}

/**
 * 查询单条数据
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * result := db.Table("users").Where("id", 1).Find()
 */
func (b *DBBuilder) Find() map[string]interface{} {
	var result map[string]interface{}
	b.db.Table(b.table).Take(&result)
	return result
}

/**
 * 查询多条数据
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * results := db.Table("users").Where("age", ">", 18).Select()
 */
func (b *DBBuilder) Select() []map[string]interface{} {
	var results []map[string]interface{}
	b.db.Table(b.table).Find(&results)
	return results
}

/**
 * 游标查询
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * db.Table("users").Cursor(func(row map[string]interface{}) error {
 *     // 处理每一行数据
 *     fmt.Println(row)
 *     return nil
 * })
 */
func (b *DBBuilder) Cursor(callback func(map[string]interface{}) error) error {
	rows, err := b.db.Table(b.table).Rows()
	if err != nil {
		return err
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return err
	}

	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range columns {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return err
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]
			if byteArray, ok := val.([]byte); ok {
				row[col] = string(byteArray)
			} else {
				row[col] = val
			}
		}

		if err := callback(row); err != nil {
			return err
		}
	}

	return rows.Err()
}

/**
 * 插入单条数据
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * data := map[string]interface{}{"name": "张三", "age": 18}
 * id := db.Table("users").InsertGetId(data)
 */
func (b *DBBuilder) InsertGetId(data map[string]interface{}) uint {
	result := b.db.Table(b.table).Create(data)
	if result.Error != nil {
		return 0
	}
	var id uint
	b.db.Raw("SELECT LAST_INSERT_ID()").Scan(&id)
	return id
}

/**
 * 插入或更新数据
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * data := map[string]interface{}{"id": 1, "name": "张三", "age": 18}
 * db.Table("users").Save(data)
 */
func (b *DBBuilder) Save(data map[string]interface{}) int64 {
	result := b.db.Table(b.table).Save(data)
	return result.RowsAffected
}

/**
 * 批量插入或更新数据
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * data := []map[string]interface{}{
 *     {"id": 1, "name": "张三", "age": 18},
 *     {"id": 2, "name": "李四", "age": 20},
 * }
 * db.Table("users").SaveAll(data)
 */
func (b *DBBuilder) SaveAll(data []map[string]interface{}) int64 {
	result := b.db.Table(b.table).Create(data)
	return result.RowsAffected
}

/**
 * 聚合函数 - Count
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * count := db.Table("users").Count()
 */
func (b *DBBuilder) Count() int64 {
	var count int64
	b.db.Table(b.table).Count(&count)
	return count
}

/**
 * 聚合函数 - Sum
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * sum := db.Table("users").Sum("age")
 */
func (b *DBBuilder) Sum(field string) float64 {
	var sum float64
	b.db.Table(b.table).Select(fmt.Sprintf("SUM(%s) as sum", field)).Scan(&sum)
	return sum
}

/**
 * 聚合函数 - Max
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * max := db.Table("users").Max("age")
 */
func (b *DBBuilder) Max(field string) float64 {
	var max float64
	b.db.Table(b.table).Select(fmt.Sprintf("MAX(%s) as max", field)).Scan(&max)
	return max
}

/**
 * 聚合函数 - Min
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * min := db.Table("users").Min("age")
 */
func (b *DBBuilder) Min(field string) float64 {
	var min float64
	b.db.Table(b.table).Select(fmt.Sprintf("MIN(%s) as min", field)).Scan(&min)
	return min
}

/**
 * 聚合函数 - Avg
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * avg := db.Table("users").Avg("age")
 */
func (b *DBBuilder) Avg(field string) float64 {
	var avg float64
	b.db.Table(b.table).Select(fmt.Sprintf("AVG(%s) as avg", field)).Scan(&avg)
	return avg
}

/**
 * IN查询
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * db.Table("users").WhereIn("id", []int{1,2,3}).Select()
 */
func (b *DBBuilder) WhereIn(field string, values interface{}) *DBBuilder {
	b.db = b.db.Where(fmt.Sprintf("%s IN ?", field), values)
	return b
}

/**
 * 模糊查询
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * // 指定字段模糊查询
 * db.Table("users").WhereLike("name", "张").Select()
 * // 全字段模糊查询
 * db.Table("users").WhereLike("*", "张").Select()
 */
func (b *DBBuilder) WhereLike(field string, value string) *DBBuilder {
	if field == "*" {
		// 获取表的所有字段
		var columns []string
		rows, _ := b.db.Table(b.table).Limit(1).Rows()
		columns, _ = rows.Columns()
		rows.Close()

		// 构建OR条件
		b.db = b.db.Where(func(db *gorm.DB) *gorm.DB {
			for i, col := range columns {
				if i == 0 {
					db = db.Where(fmt.Sprintf("%s LIKE ?", col), "%"+value+"%")
				} else {
					db = db.Or(fmt.Sprintf("%s LIKE ?", col), "%"+value+"%")
				}
			}
			return db
		})
	} else {
		b.db = b.db.Where(fmt.Sprintf("%s LIKE ?", field), "%"+value+"%")
	}
	return b
}

/**
 * 时间范围查询
 * @Author Xven　<270988107@qq.com>
 * 使用示例:
 * db := NewDBBuilder()
 * db.Table("users").WhereBetweenTime("created_at", "2024-01-01", "2024-12-31").Select()
 */
func (b *DBBuilder) WhereBetweenTime(field string, start string, end string) *DBBuilder {
	b.db = b.db.Where(fmt.Sprintf("%s BETWEEN ? AND ?", field), start, end)
	return b
}