go分页及多条件查询

package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"net/http"
	"time"
)

// UserInfo -->和数据表关联
type UserInfo struct {
	ID       uint      `json:"id" form:"id"`
	Name     string    `json:"name" form:"name"`
	Sex      string    `json:"sex" form:"sex"`
	Hobby    string    `json:"hobby" form:"hobby"`
	Birthday time.Time `json:"birthday" form:"birthday"`
}

type UserInfoDto struct {
	ID       uint      `json:"id" form:"id"`
	Name     string    `json:"name" form:"name"`
	Sex      string    `json:"sex" form:"sex"`
	Hobby    string    `json:"hobby" form:"hobby"`
	Birthday time.Time `json:"birthday" form:"birthday"`
	PageNum  int       `json:"pageNum"`
	PageSize int       `json:"pageSize"`
}

var (
	db *gorm.DB
)

func InitMySQL() (err error) {
	dsn := "root:root@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		//查看日志sql
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		return err
	}
	return
}

func getUsers(c *gin.Context) {
	var users []UserInfo
	//users := make([]UserInfo, 0)
	var userDto UserInfoDto
	Db := db

	c.ShouldBindJSON(&userDto)

	pageNum := userDto.PageNum
	pageSize := userDto.PageSize

	Db=Db.Model(&UserInfo )

	if id := userDto.ID; id > 0 {
		Db = Db.Debug().Where("id = ?", id)
	}

	if len(userDto.Name) > 0 {
		Db = Db.Debug().Where("name = ?", userDto.Name)
	}

	if pageNum > 0 && pageSize > 0 {
		Db = Db.Debug().Limit(pageSize).Offset((pageNum - 1) * pageSize)
	}

	if err := Db.Debug().Find(&users).Error; err != nil {
		fmt.Println(err.Error())
	}

	c.JSON(http.StatusOK, gin.H{
		"data": users,
	})

}

func main() {
	err := InitMySQL()
	if err != nil {
		panic(err)
	}
	//创建表 自动迁移 把结构体和数据表进行对应
	db.AutoMigrate(&UserInfo{})
	ginServer := gin.Default()
	ginServer.POST("/page", getUsers)
	ginServer.Run(":8088")

	//var us []UserInfo

	//-----------------------------start------------------------------------------
	//创建表 及创建一条数据

	//var LOC, _ = time.LoadLocation("Asia/Shanghai") //要指定时区 否则会加8小时
	//var timeLayoutStr = "2006-01-02 15:04:05"
	//parse, err := time.ParseInLocation(timeLayoutStr, time.Now().Format(timeLayoutStr), LOC)
	//
	//fmt.Printf("shiji", parse)
	//userInfo := UserInfo{Name: "123", Sex: "男", Hobby: "跑步222", Birthday: parse}
	//log.Printf("12dddd", parse)
	//
	通过数据的指针来创建
	//result := db.Create(&userInfo)
	//
	//id := userInfo.ID // 返回插入数据的主键
	//fmt.Printf("id为：%v", id)
	//err = result.Error
	//row := result.RowsAffected // 返回插入记录的条数
	//fmt.Printf("插入的条数为：%v", row)

	//-----------------------------end------------------------------------------

	//------------------------------start-----------------------------------------

	//var u UserInfo
	//var us []UserInfo

	// IN     SELECT * FROM users WHERE name in ('陈江河','陈江河1');
	//db.Where("name IN (?)", []string{"陈江河", "陈江河1"}).Find(&us)
	//fmt.Printf("us:%v\n", us)
	//
	主键     SELECT * FROM users WHERE id IN (1,2,3);
	//var id1 = 1
	//var id2 = 2
	//db.Find(&us, []int{id1, id2, 3})
	//fmt.Printf("us:%v\n", us)

	 LIKE   SELECT * FROM users WHERE name LIKE '%jin%';
	//db.Where("name LIKE ?", "%陈%").Find(&us)
	//fmt.Printf("us:%v\n", us)
	//
	//var name = "一"
	//db.Where("name like ?", "%"+name+"%").Find(&us)
	//fmt.Printf("us:%v\n", us)

	// Time
	//startTime := "2023-03-06"
	//endTime := time.Now().Format("2006-01-02 15:04:05")
	//
	//db.Where("birthday > ?", startTime).Find(&us)
	//fmt.Printf("us----------:%v\n", us)
	 SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
	//
	 BETWEEN
	//db.Where("birthday BETWEEN ? AND ?", startTime, endTime).Find(&us)
	//fmt.Printf("us111111111:%v\n", us)
	// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

	//-----------------------------end------------------------------------------

	//更新  将UserInfo中的hobby改为123
	//db.Model(&u).Update("Hobby", "123")
	//更新 多个字段
	//db.Model(&u).Updates(UserInfo{Name: "chenyiyi", Sex: "123"})
	//db.Model(&u).Updates(map[string]interface{}{"ID": 2, "Sex": "566"})

	//删除
	//db.Delete(&u)

	//time转string
	//var timeLayoutStr = "2006-01-02 15:04:05"
	//stringTime := time.Now().Format(timeLayoutStr)
	//fmt.Printf("时间为：%v,类型为：%t", stringTime, stringTime)
	//
	string类型时间转time
	//stringToTime, err := time.Parse(timeLayoutStr, "2023-03-08 14:03:02")
	//fmt.Printf("时间为：%v,类型为：%t", stringToTime, stringToTime)
}