golang - gorm
发布时间:2020-12-16 18:13:28 所属栏目:大数据 来源:网络整理
导读:gormgithub 、 gitbook 概述 package mainimport ("github.com/jinzhu/gorm"_ "github.com/jinzhu/gorm/dialects/mysql")type Product struct {ID uint `gorm:"primary_key"`Code stringPrice uint}func main() {db,err := gorm.Open("mysql","user:password
gormgithub、gitbook 概述package main import ( "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mysql" ) type Product struct { ID uint `gorm:"primary_key"` Code string Price uint } func main() { db,err := gorm.Open("mysql","user:password@/dbname?charset=utf8&parseTime=True&loc=Local") if err != nil { panic("failed to connect database") } defer db.Close() // 自动迁移表,生成的表名为 products db.AutoMigrate(&Product{}) // Create db.Create(&Product{Code: "L1212",Price: 1000}) // Read var product Product db.First(&product,1) // find product with id 1 db.First(&product,"code = ?","L1212") // find product with code l1212 // Update db.Model(&product).Update("Price",2000) //Delete db.Delete(&product) } 模型定义4 个特殊字段。其中 ID 字段默认为主键字段,可以无需加 Tag `gorm:"primary_key"`。 type Model struct { ID uint `gorm:"primary_key"` CreatedAt time.Time UpdatedAt time.Time DeletedAt *time.Time } 使用 gorm.Model 自动生成这4个字段。效果等同上。 type User struct { gorm.Model } 修改表名 type Product struct { ID uint Code string Price uint } //修改默认表名 func (Product) TableName() string { return "product2" } type Email struct { ID int Email string } func main() { db,"root:root@tcp(localhost:3306)/gorm") if err != nil { panic("failed to connect database") } defer db.Close() //设置默认表名前缀 gorm.DefaultTableNameHandler = func(db *gorm.DB,defaultTableName string) string { return "prefix_" + defaultTableName } //自动生成表 db.AutoMigrate(&Product{},&Email{}) } 设置字段 type Product struct { ID uint `gorm:"primary_key:id"` Num int `gorm:"AUTO_INCREMENT:number"` Code string Price uint `gorm:"default:'1000'"` Tag []Tag `gorm:"many2many:tag;"` Date time.Time `gorm:"-"` } type Email struct { ID int `gorm:"primary_key:id"` UserID int `gorm:"not null;index"` Email string `gorm:"type:varchar(100);unique_index"` Subscribed bool } type Tag struct { Name string } func main() { db,"root:root@tcp(localhost:3306)/gorm") if err != nil { panic("failed to connect database") } defer db.Close() gorm.DefaultTableNameHandler = func(db *gorm.DB,defaultTableName string) string { return "demo_" + defaultTableName } db.AutoMigrate(&Product{},&Email{}) } 设置外键字段 type Profile struct { gorm.Model Refer int Name string } type User struct { gorm.Model Profile Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"` ProfileID int } 增删改查增type Animal struct { ID int64 Name string `gorm:"default:'galeone'"` Age int64 } var animal = Animal{Age: 99,Name: ""} db.Create(&animal) 查// SELECT * FROM users ORDER BY id LIMIT 1; db.First(&user) // SELECT * FROM users ORDER BY id DESC LIMIT 1; db.Last(&user) // SELECT * FROM users; db.Find(&users) // SELECT * FROM users WHERE id = 10; db.First(&user,10) 添加 where子句 // Get first matched record db.Where("name = ?","jinzhu").First(&user) //// SELECT * FROM users WHERE name = 'jinzhu' limit 1; // Get all matched records db.Where("name = ?","jinzhu").Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu'; db.Where("name <> ?","jinzhu").Find(&users) // IN db.Where("name in (?)",[]string{"jinzhu","jinzhu 2"}).Find(&users) // LIKE db.Where("name LIKE ?","%jin%").Find(&users) // AND db.Where("name = ? AND age >= ?","jinzhu","22").Find(&users) // Time db.Where("updated_at > ?",lastWeek).Find(&users) db.Where("created_at BETWEEN ? AND ?",lastWeek,today).Find(&users) 结构或者 map // Struct db.Where(&User{Name: "jinzhu",Age: 20}).First(&user) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1; // Map db.Where(map[string]interface{}{"name": "jinzhu","age": 20}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20; // Slice of primary keys db.Where([]int64{20,21,22}).Find(&users) //// SELECT * FROM users WHERE id IN (20,22); inline condition // Get by primary key (only works for integer primary key) db.First(&user,23) //// SELECT * FROM users WHERE id = 23 LIMIT 1; // Get by primary key if it were a non-integer type db.First(&user,"id = ?","string_primary_key") //// SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1; // Plain SQL db.Find(&user,"name = ?","jinzhu") //// SELECT * FROM users WHERE name = "jinzhu"; db.Find(&users,"name <> ? AND age > ?",20) //// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20; // Struct db.Find(&users,User{Age: 20}) //// SELECT * FROM users WHERE age = 20; // Map db.Find(&users,map[string]interface{}{"age": 20}) //// SELECT * FROM users WHERE age = 20; Select 选择 db.Select("name,age").Find(&users) //// SELECT name,age FROM users; db.Select([]string{"name","age"}).Find(&users) //// SELECT name,age FROM users; db.Table("users").Select("COALESCE(age,?)",42).Rows() //// SELECT COALESCE(age,'42') FROM users; 排序 db.Order("age desc,name").Find(&users) //// SELECT * FROM users ORDER BY age desc,name; // Multiple orders db.Order("age desc").Order("name").Find(&users) //// SELECT * FROM users ORDER BY age desc,name; // ReOrder db.Order("age desc").Find(&users1).Order("age",true).Find(&users2) //// SELECT * FROM users ORDER BY age desc; (users1) //// SELECT * FROM users ORDER BY age; (users2) limit 子句 db.Limit(3).Find(&users) //// SELECT * FROM users LIMIT 3; // Cancel limit condition with -1 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) //// SELECT * FROM users LIMIT 10; (users1) //// SELECT * FROM users; (users2) count db.Model(&User{}).Where("name = ?","jinzhu").Count(&count) //// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count) db.Table("deleted_users").Count(&count) //// SELECT count(*) FROM deleted_users; group & having type Result struct { Date time.Time Total int64 } db.Table("orders").Select("date(created_at) as date,sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?",100).Scan(&results) Joins db.Table("users").Select("users.name,emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results) Scan type Result struct { Name string Age int } var result Result db.Table("users").Select("name,age").Where("name = ?",3).Scan(&result) // Raw SQL db.Raw("SELECT name,age FROM users WHERE name = ?",3).Scan(&result) 指定表名 // Create `deleted_users` table with struct User's definition db.Table("deleted_users").CreateTable(&User{}) 改save 全部更新 db.First(&user) user.Name = "jinzhu 2" user.Age = 100 db.Save(&user) //// UPDATE users SET name='jinzhu 2',age=100,birthday='2016-01-01',updated_at = '2013-11-17 21:34:10' WHERE id=111; 部分更新 db.Model(&user).Update("name","hello") db.Model(&user).Updates(User{Name: "hello",Age: 18}) 对更新语句进行 select 和 omit db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello","age": 18,"actived": false}) //// UPDATE users SET name='hello',updated_at='2013-11-17 21:34:10' WHERE id=111; db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello","actived": false}) //// UPDATE users SET age=18,actived=false,updated_at='2013-11-17 21:34:10' WHERE id=111; 删// Delete an existing record db.Delete(&email) //// DELETE from emails where id=10; 软删除当设置DeletedAt 字段时,默认不会真的删除该记录。只会把该记录的 DeletedAt 的值设置为当前时间。 // 执行软删除 db.Delete(&user) //// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111; // 虽然记录没有永久删除。但是查询时依然不会查询到该记录 db.Where("age = 20").Find(&user) //// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL; // 可以通过指定域查询到该软删除的记录 db.Unscoped().Where("age = 20").Find(&users) //// SELECT * FROM users WHERE age = 20; // 通过指定域进行永久删除 db.Unscoped().Delete(&order) //// DELETE FROM orders WHERE id=10; 子查询db.Preload("Orders").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,2,3,4); db.Preload("Orders","state NOT IN (?)","cancelled").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,4) AND state NOT IN ('cancelled'); db.Where("state = ?","active").Preload("Orders","cancelled").Find(&users) //// SELECT * FROM users WHERE state = 'active'; //// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled'); db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,4); // has many //// SELECT * FROM profiles WHERE user_id IN (1,4); // has one //// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to db.Preload("Orders.OrderItems").Find(&users) db.Preload("Orders","state = ?","paid").Preload("Orders.OrderItems").Find(&users) 关联存储增和改时默认级联处理 user := User{ Name: "jinzhu",BillingAddress: Address{Address1: "Billing Address - Address 1"},ShippingAddress: Address{Address1: "Shipping Address - Address 1"},Emails: []Email{ {Email: "jinzhu@example.com"},{Email: "jinzhu-2@example@example.com"},},Languages: []Language{ {Name: "ZH"},{Name: "EN"},} db.Create(&user) //// BEGIN TRANSACTION; //// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"); //// INSERT INTO "addresses" (address1) VALUES ("Shipping Address - Address 1"); //// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu",1,2); //// INSERT INTO "emails" (user_id,email) VALUES (111,"jinzhu@example.com"); //// INSERT INTO "emails" (user_id,"jinzhu-2@example.com"); //// INSERT INTO "languages" ("name") VALUES ('ZH'); //// INSERT INTO user_languages ("user_id","language_id") VALUES (111,1); //// INSERT INTO "languages" ("name") VALUES ('EN'); //// INSERT INTO user_languages ("user_id",2); //// COMMIT; db.Save(&user) 取消默认关联存储 type User struct { gorm.Model Name string CompanyID uint Company Company `gorm:"save_associations:false"` } type Company struct { gorm.Model Name string } 或着手动取消 db.Set("gorm:save_associations",false).Create(&user) db.Set("gorm:save_associations",false).Save(&user) 表间关系一对多关系type User struct { gorm.Model Emails []Email } type Email struct { gorm.Model Email string UserID uint } // 查询某userid为111的用户的所有Email 地址 db.Model(&user).Related(&emails) //// SELECT * FROM emails WHERE user_id = 111; // 111 is user's primary key 多对多关系(相互关联) type User struct { gorm.Model Languages []Language `gorm:"many2many:user_languages;"` } type Language struct { gorm.Model Name string Users []User `gorm:"many2many:user_languages;"` } // 查询某语言为111的所有用户 db.Model(&language).Related(&users) //// SELECT * FROM "users" INNER JOIN "user_languages" ON "user_languages"."user_id" = "users"."id" WHERE ("user_languages"."language_id" IN ('111')) (单一关联,比如个人与同学) type User struct { gorm.Model Languages []Language `gorm:"many2many:user_languages;"` } type Language struct { gorm.Model Name string } db.Model(&user).Related(&languages,"Languages") //// SELECT * FROM "languages" INNER JOIN "user_languages" ON "user_languages"."language_id" = "languages"."id" WHERE "user_languages"."user_id" = 111 关联模式(方便处理处理多对多) // 开始关联 db.Model(&user).Association("Languages") // 查询 db.Model(&user).Association("Languages").Find(&languages) // 添加 db.Model(&user).Association("Languages").Append([]Language{languageZH,languageEN}) db.Model(&user).Association("Languages").Append(Language{Name: "DE"}) // 删除 db.Model(&user).Association("Languages").Delete([]Language{languageZH,languageEN}) db.Model(&user).Association("Languages").Delete(languageZH,languageEN) // 更新 db.Model(&user).Association("Languages").Replace([]Language{languageZH,languageEN}) db.Model(&user).Association("Languages").Replace(Language{Name: "DE"},languageEN) db.Model(&user).Association("Languages").Count() // 移除关联 db.Model(&user).Association("Languages").Clear() 高级用法事务func CreateAnimals(db *gorm.DB) err { tx := db.Begin() // Note the use of tx as the database handle once you are within a transaction if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil { tx.Rollback() return err } if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil { tx.Rollback() return err } tx.Commit() return nil } 原生 sqldb.Exec("DROP TABLE users;") db.Exec("UPDATE orders SET shipped_at=? WHERE id IN (?)",time.Now(),[]int64{11,22,33}) // Scan type Result struct { Name string Age int } var result Result db.Raw("SELECT name,3).Scan(&result) sql.DB 接口// Get generic database object `*sql.DB` to use its functions db.DB() // Ping db.DB().Ping() db.DB().SetMaxIdleConns(10) db.DB().SetMaxOpenConns(100) 日志处理// Enable Logger,show detailed log db.LogMode(true) // Diable Logger,don't show any log db.LogMode(false) // Debug a single operation,show detailed log for this operation db.Debug().Where("name = ?","jinzhu").First(&User{}) //默认 error,设置日志级别 db.SetLogger(gorm.Logger{revel.TRACE}) 错误处理// 一般处理 if err := db.Where("name = ?","jinzhu").First(&user).Error; err != nil { // error handling... } // 获取所有的错误 db.First(&user).Limit(10).Find(&users).GetErrors() // 获取记录找不到错误(不排除有其他错误) db.Where("name = ?","hello world").First(&user).RecordNotFound() 函数钩子阅读原文 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |