go分页及多条件查询

傷城~ 2023-10-10 20:13 42阅读 0赞

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)
    }