[ gorm[基础] ]

发表评论
206 views

gorm基本查询

安装

  1. 安装gorm包
go get -u gorm.io/gorm
  1. 安装mysql驱动
go get -u gorm.io/driver/mysql

连接mysql数据库

package appinit

import (
    appglobal "gin_gorm_mark_vscode/applications/app_global"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "gorm.io/gorm/schema"
)

func InitGorm() *gorm.DB {
    dsn := appglobal.GloablConfig.Mysql.ToDns()

    gormLogger := logger.Default.LogMode(logger.Info)

    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        SkipDefaultTransaction: appglobal.GloablConfig.Mysql.Config.SkipDefaultTransaction, // 默认事务
        NamingStrategy: schema.NamingStrategy{
            TablePrefix:   appglobal.GloablConfig.Mysql.Config.TablePrefix,   // 表名前缀
            SingularTable: appglobal.GloablConfig.Mysql.Config.SingularTable, // 单数表名
            NoLowerCase:   appglobal.GloablConfig.Mysql.Config.NoLowerCase,   // 大小写转换
        },
        Logger: gormLogger,
    })
    if err != nil {
        panic("连接数据库失败" + err.Error())
    }

    return db
}

查询1

    router.GET("/", func(ctx *gin.Context) {
        var user appmodel.User
        // 默认查询第一个数据
        appglobal.GloablDb.Take(&user)

        //
        err := appglobal.GloablDb.Take(&user, "3").Error
        fmt.Println(err)

        //
        appglobal.GloablDb.Take(&user, "name = ?", "陈海峰")

        // 根据主键
        user.ID = 2
        appglobal.GloablDb.Take(&user)
        //
        appglobal.GloablDb.First(&user)
        //
        appglobal.GloablDb.Last(&user)
        //
        count := appglobal.GloablDb.Find(&user).RowsAffected
        fmt.Println(count)

        var userList []appmodel.User

        // 根据id列表查询
        appglobal.GloablDb.Find(userList, []int{1, 2, 3, 4})

        // in查询
        appglobal.GloablDb.Find(userList, "name in (?)", []string{"陈海峰", "欧子君"})

        ctx.JSON(200, gin.H{})
        //
        data, _ := json.Marshal(user)
        fmt.Println(string(data))
    })

查询2

    router.GET("/where", func(ctx *gin.Context) {
    // var user appmodel.User
    var userList []appmodel.User
    type UserSanc struct {
        ID   int    `gorm:"column:id" json:"id"`
        Age  int    `gorm:"column:age" json:"age"`
        Name string `gorm:"column:name" json:"name"`
    }
    // where普通用法
    // appglobal.GloablDb.Where("name = ?", "陈海峰").Find(&userList)
    // appglobal.GloablDb.Where("id = ?", 1).Take(&user)

    // not用法
    // appglobal.GloablDb.Not("name = ?", "陈海峰").Find(&userList)
    // appglobal.GloablDb.Where("not name = ?", "陈海峰").Find(&userList)

    // select 选择字段
    // appglobal.GloablDb.Select("name", "age").Find(&userList)
    var userSanc []UserSanc

    // scan扫描结构体(把查询结果过滤 可以设置别名column配置, 相当于最后再过滤)
    // appglobal.GloablDb.Select("name", "age").Scan(&userSanc).Find(&userList)
    // appglobal.GloablDb.Table("tb_User").Select("name", "age").Scan(&userSanc)
    appglobal.GloablDb.Model(appmodel.User{}).Select("id", "name", "age").Scan(&userSanc)
    // fmt.Println(userSanc)

    // model传表对象
    // appglobal.GloablDb.Model(&appmodel.User{}).Select("name", "age").Find(&userList)

    // orderby排序
    // appglobal.GloablDb.Order("age desc").Find(&userList)

    // limit offset分页
    // appglobal.GloablDb.Limit(5).Offset(0).Find(&userList)

    // distinct 去重
    // appglobal.GloablDb.Select("name", "age").Distinct("age").Find(&userList)

    // group分组
    type GormGroup struct {
        Count int    `gorm:"column:count"`
        Name  string `gorm:"column:name"`
    }
    var groupList []GormGroup
    appglobal.GloablDb.Model(appmodel.User{}).
        Select("count(id) as count", "name").
        Group("name").
        Scan(&groupList)

    // raw原生sql
    appglobal.GloablDb.Raw("select * from tb_User limit 1")

    // 自定义封装查询语句, 可以链式调用
    appglobal.GloablDb.Scopes(Avg1).Find(&userList)
    // fmt.Println(user)
    // user_dict,_ := json.Marshal(user)
    ctx.JSON(200, userSanc)
    })
    func Avg1(db *gorm.DB) *gorm.DB {
        return db.Where("age > ?", 11)
    }

修改

// 条件更新 UPDATE `t_user` SET `name`='aa',`updated_at`='2022-05-13 10:19:57.743' WHERE name = 'a'
db.Model(&model.TUser{}).Where("name = ?", "a").Update("name", "aa")

//根据ID更新 UPDATE `t_user` SET `name`='test',`updated_at`='2022-05-13 10:27:49.548' WHERE `id` = 1
var user = model.TUser{ID: 1}
db.Model(&user).Update("name", "test")

//--------------------------------------更新多列---------------------------------------------
//  根据 `struct` 更新属性，只会更新非零值的字段
//  UPDATE `t_user` SET `id`=1,`name`='hello',`password`='1',`phone`='123',`updated_at`='2022-05-13 10:32:17.812'
db.Model(&model.TUser{ID: 1}).Updates(model.TUser{Name: "hello", Password: "1", Phone: "123"})

// 根据 `map` 更新属性
// UPDATE `t_user` SET `password`='111',`phone`='456',`name`='hello',`updated_at`='2022-05-13 10:35:09.953' WHERE `id` = 1
db.Model(&model.TUser{ID: 1}).Updates(map[string]interface{}{"name": "hello", "Password": "111", "Phone": "456"})

//--------------------------------------更新选定字段--------------------------------------
//忽略某些字段：UPDATE `t_user` SET `name`='hello',`updated_at`='2022-05-13 10:38:56.004' WHERE `id` = 1
db.Model(&model.TUser{ID: 1}).Select("name").Updates(map[string]interface{}{"name": "hello", "Password": "111", "Phone": "456"})

//onmit 不更新这个字段 UPDATE `t_user` SET `password`='111',`phone`='456',`updated_at`='2022-05-13 10:39:57.516' WHERE `id` = 1
db.Model(&model.TUser{ID: 1}).Omit("name").Updates(map[string]interface{}{"name": "hello", "Password": "111", "Phone": "456"})

//--------------------------------------批量更新--------------------------------------
// 根据 struct 更新
//UPDATE `t_user` SET `name`='hello',`created_at`='2022-05-13 10:45:11.233',`updated_at`='2022-05-13 10:45:11.255' WHERE phone = '122222'
db.Model(&model.TUser{}).Where("phone = ?", "122222").Updates(&model.TUser{Name: "hello", CreatedAt: time.Now()})
// 根据 map 更新 UPDATE `t_user` SET `name`='hello',`password`='1213' WHERE id IN (1,2)
db.Table("t_user").Where("id IN ?", []int{1, 2}).Updates(map[string]interface{}{"name": "hello", "password": "1213"})
//更新的记录数
result := db.Model(model.TUser{}).Where("phone = ?", "122222").Updates(model.TUser{Name: "hello", Password: "1213"})
fmt.Print(result.RowsAffected)

删除

stu := Student{
        Id: 5,
    }
    conn.Delete(&stu) //删除id=5的数据
    fmt.Println(stu)

stu := Student{}
conn.Where("name = ?", "jun").Delete(&stu)

一对多

foreingKey外键表结构创建

  • 两张表中都需要指定关联关系字段

用户表

package appmodel

import "gorm.io/gorm"

type User struct {
    ID       uint      `gorm:"size:10"`
    Name     string    `gorm:"size:16;not null;comment:用户名"`
    Age      int       `gorm:"size:3;default:0;not null;comment:年龄"`
    Email    *string   `gorm:"size:128;comment:邮箱"`
    Articles []Article `gorm:"foreignKey:UID"` // 此行用于建立外键关系, 不会生成字段
}

文章表

package appmodel

type Article struct {
    ID    uint   `gorm:"size:10"`
    Title string `gorm:"size:16"`
    UID   uint   `gorm:"size:10"`
    User  User   `gorm:"foreignKey:UID"`  // 此行用于建立外键关系, 不会生成字段
}

同时创建用户, 文章

router.POST("/article", func(ctx *gin.Context) {
        // 1.创建用户, 再创建关联文章
        appglobal.GloablDb.Create(&appmodel.User{
            Name: "cbf",
            Age:  16,
            Articles: []appmodel.Article{
                {
                    Title: "文章1",
                },
                {
                    Title: "文章2",
                },
                {
                    Title: "文章3",
                },
            },
        })

        // 2. 创建文章, 再创建关联用户
        appglobal.GloablDb.Create(&appmodel.Article{
            Title: "创建文章1",
            User: appmodel.User{
                Name: "创建用户1",
                Age:  99,
            },
        })

        // 3. 创建文章, 关联已经存在的用户
        appglobal.GloablDb.Create(&appmodel.Article{
            Title: "创建文章2",
            UID:   1,
        })

        var user appmodel.User
        appglobal.GloablDb.Take(&user)
        appglobal.GloablDb.Create(&appmodel.Article{
            Title: "创建文章3",
            User:  user,
        })

        // 4. 给已经存在的用户绑定文章
        var article appmodel.Article
        appglobal.GloablDb.Take(&user, 11)
        appglobal.GloablDb.Take(&article, 1)
        user.Articles = []appmodel.Article{article}
        appglobal.GloablDb.Save(&user)

        appglobal.GloablDb.Model(&user).Association("Articles").Append(&article)

        // 5. 给已经存在的文章绑定用户
        appglobal.GloablDb.Model(&article).Association("User").Append(&user)

        ctx.JSON(200, gin.H{"msg": "创建用户, 与文章"})
    })

外键查询

router.GET("/article", func(ctx *gin.Context) {
        // 一对多查询

        // 1. 当查询文章时, 预加载User
        var article appmodel.Article
        appglobal.GloablDb.Preload("User").Take(&article)
        // 套娃关联
        appglobal.GloablDb.Preload("User.Articles.User.Articles").Take(&article)

        // 2. 预加载 + 过滤
        var user appmodel.User
        appglobal.GloablDb.Preload("Articles", "id > ?", 11).Take(&user)
        appglobal.GloablDb.Preload("Articles", MyPreload).Take(&user)

        ctx.JSON(200, gin.H{"msg": "查询一对多成功"})
    })

    func MyPreload(db *gorm.DB) *gorm.DB {
    return db.Where("id > ?", 11)
}

多对多

表结构创建

package appmodel

// 一对一 指针写哪都可以,
type Test struct {
    ID       uint
    Name     string
    TestInfo TestInfo
}

type TestInfo struct {
    ID     uint
    Info   string
    TestID uint
    Test   *Test
}

// 多对多
type TestTag struct {
    ID           uint
    Name         string
    TestArticles []TeatArticle `gorm:"many2many:article_tags"`
}

type TeatArticle struct {
    ID       uint
    Name     string
    TestTags []TestTag `gorm:"many2many:article_tags"`
}

查询

router.POST("/many2many", func(ctx *gin.Context) {
        // 多对多 增加
        // 方式1
        appglobal.GloablDb.Create(&appmodel.TeatArticle{
            Name: "文章1",
            TestTags: []appmodel.TestTag{
                {Name: "标签1"},
                {Name: "标签2"},
            },
        })

        // 方式2
        var tags []appmodel.TestTag
        appglobal.GloablDb.Where("name = ?", "标签1").Find(&tags)
        appglobal.GloablDb.Create(&appmodel.TeatArticle{
            Name:     "文章2",
            TestTags: tags,
        })

        ctx.JSON(200, gin.H{"msg": "多对多创建成功"})
    })

修改

    router.GET("/many2many", func(ctx *gin.Context) {
        var article appmodel.TeatArticle
        appglobal.GloablDb.Preload("testtags").Take(&article, 1)
        ctx.JSON(200, article)
    })

    router.PUT("/many2many", func(ctx *gin.Context) {
        var article appmodel.TeatArticle

        // 删除
        appglobal.GloablDb.Preload("testtags").Take(&article, 1)
        appglobal.GloablDb.Model(&article).Association("testtags").Delete(article.TestTags)

        // 增加
        var tag appmodel.TestTag
        appglobal.GloablDb.Take(&tag, 1)
        appglobal.GloablDb.Model(&article).Association("testtags").Append(&tag)

        // 替换
        appglobal.GloablDb.Model(&article).Association("testtags").Replace(&tag)
    })

多对多(自定义中级表)

表结构

// 自定义第三张表
type TestUser struct {
    ID   uint
    Name string
    // Age         int
    TestCoupons []TestCoupon `gorm:"many2many:user_coupon;"`
}

type TestCoupon struct {
    ID   uint
    Name string
    // TestUsers []TestUser `gorm:"many2many:UserCoupon;"`
}

type UserCoupon struct {
    TestUserID   uint      `gorm:"primaryKey"`
    TestCouponID uint      `gorm:"primaryKey"`
    CreatedAt    time.Time `json:"created_at"`
}

func (uc *UserCoupon) BeforeCreate(db *gorm.DB) (err error) {
    uc.CreatedAt = time.Now()
    return nil
}

初始化配置

  • SetupJoinTable来建立表关系
package appinit

import (
    appglobal "gin_gorm_mark_vscode/applications/app_global"
    appmodel "gin_gorm_mark_vscode/applications/app_model"
)

func InitModels() {
    appglobal.GloablDb.SetupJoinTable(&appmodel.TestUser{}, "TestCoupons", &appmodel.UserCoupon{})
    // appglobal.GloablDb.SetupJoinTable(&appmodel.UserCoupon{}, "TestCoupon", &appmodel.TestUser{})
    appglobal.GloablDb.
        AutoMigrate(
            &appmodel.User{},
            &appmodel.Article{},
            &appmodel.Test{},
            &appmodel.TestInfo{},
            &appmodel.TestTag{},
            &appmodel.TeatArticle{},
            &appmodel.TestUser{},
            &appmodel.TestCoupon{},
            &appmodel.UserCoupon{},
        )
}

视图

    router.POST("/many2many/custom", func(ctx *gin.Context) {
        // var user appmodel.TestUser
        var couponList []appmodel.TestCoupon
        // appglobal.GloablDb.Preload("TestCoupons").Take(&user)
        appglobal.GloablDb.Find(&couponList, []int{1, 2})
        appglobal.GloablDb.Create(&appmodel.TestUser{Name: "用户1", TestCoupons: couponList})

        ctx.JSON(200, gin.H{"msg": "创建成功"})
    })

    router.GET("/many2many/custom", func(ctx *gin.Context) {
        var user appmodel.TestUser
        // var coupon appmodel.TestCoupon
        appglobal.GloablDb.Preload("TestCoupons").Where("name = ?", "用户1").Take(&user)
        // appglobal.GloablDb.Take(&coupon)
        ctx.JSON(200, user)
    })
    router.PUT("/many2many/custom", func(ctx *gin.Context) {
        var user appmodel.TestUser
        var coupon appmodel.TestCoupon
        appglobal.GloablDb.Take(&user)
        appglobal.GloablDb.Take(&coupon)
        ctx.JSON(200, user)
    })