The orm framework for the golang common library operations database gorm basic use details
- 2020-11-20 06:09:06
- OfStack
Common golang libraries: gorilla/ ES3en-ES4en routing libraries used
Common golang libraries: Configuration file parsing library - used by viper
golang Common libraries: orm framework for operating databases -gorm basic use
1: Field mapping - Model definition
struct is commonly used in gorm to map fields
模型定义
For example, we define a model Model:
type User struct {
gorm.Model
UserId int64 `gorm:"index"` // Set up the 1 A normal index with no index name set, gorm It will be named automatically
Birtheday time.Time
Age int `gorm:"column:age"`//column:1 a tag , you can set the column name
Name string `gorm:"size:255;index:idx_name_add_id"`//size: Set the length size, index: Set the index, and that's it 1 An index name
Num int `gorm:"AUTO_INCREMENT"`
Email string `gorm:"type:varchar(100);unique_index"`//type: Define the field type and size
AddressID sql.NullInt64 `gorm:"index:idx_name_add_id"`
IgnoreMe int `gorm:"_"`
Description string `gorm:"size:2019;comment:' User description field '"`//comment : Field comment
Status string `gorm:"type:enum('published', 'pending', 'deleted');default:'pending'"`
}
gorm. Model above is defined as follows:
type Model struct {
ID uint `gorm:"primary_key"`//primary_key: Set the primary key
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt *time.Time
}
Of course, we can also define a similar type without ES31en.Model
If you use ID, the system will automatically set the table as the primary key. Of course, we can define our own primary key:
Such as:
// use `AnimalID` A primary key
type Animal struct {
AnimalID int64 `gorm:"primary_key"`
Name string
Age int64
}
Reference: https: / / gorm io zh_CN/docs/conventions html
2: Create the table
Look directly at the following example: ES52en.go
package main
import (
"database/sql"
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
"time"
)
type User struct {
gorm.Model
UserId int64 `gorm:"index"`
Birtheday time.Time
Age int `gorm:"column:age"`
Name string `gorm:"size:255;index:idx_name_add_id"`
Num int `gorm:"AUTO_INCREMENT"`
Email string `gorm:"type:varchar(100);unique_index"`
AddressID sql.NullInt64 `gorm:"index:idx_name_add_id"`
IgnoreMe int `gorm:"_"`
Description string `gorm:"size:2019;comment:' User description field '"`
Status string `gorm:"type:enum('published', 'pending', 'deleted');default:'pending'"`
}
// Sets the table name, which defaults to the plural form of the structure's name
func (User) TableName() string {
return "VIP_USER"
}
func main() {
db, err := gorm.Open("mysql", "root:root@/gormdemo?charset=utf8&parseTime=True&loc=Local")
if err != nil {
fmt.Println("connect db err: ", err)
}
defer db.Close()
if db.HasTable(&User{}) { // Determine if the table exists
db.AutoMigrate(&User{}) // There is an automatic adaptation table, which means that if there are no fields previously, the fields will be added
} else {
db.CreateTable(&User{}) // Create a new table if it does not exist
}
}
The gorm.Open () operation above needs parentheses if you want to specify the host
()
Such as:
user:password@(localhost)/dbname?charset=utf8&parseTime=True&loc=Local
In the above program, we first created a new database named
gormdemo
And then run:
go run createtable.go
, the database will appear with the name of one
vip_user
In the table.
3: Add, delete, change and check
Create a new database for gormdemo and then execute the sql statement below to create a table for animals with 1 more test data
CREATE TABLE `animals` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'galeone',
`age` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of animals
-- ----------------------------
INSERT INTO `animals` VALUES ('1', 'demo-test', '20');
INSERT INTO `animals` VALUES ('2', 'galeone', '30');
INSERT INTO `animals` VALUES ('3', 'demotest', '30');
INSERT INTO `animals` VALUES ('4', 'jim', '90');
INSERT INTO `animals` VALUES ('5', 'jimmy', '10');
INSERT INTO `animals` VALUES ('6', 'jim', '23');
INSERT INTO `animals` VALUES ('7', 'test3', '27');
increase
Example: create go
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type Animal struct {
ID int64
Name string
Age int64
}
func main() {
db, err := gorm.Open("mysql", "root:root@/gormdemo?charset=utf8&parseTime=true&loc=Local")
if err != nil {
fmt.Println("connect db error: ", err)
}
defer db.Close()
animal := Animal{Name: "demo-test", Age: 20}
db.Create(&animal)
}
Note: In the above example, I create a data table of animals in mysql with fields id, name, age
To find the
select.go
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type Animal struct {
ID int64
Name string
Age int64
}
//https://gorm.io/zh_CN/docs/query.html
func main() {
db, err := gorm.Open("mysql", "root:root@/gormdemo?charset=utf8&parseTime=true&loc=Local")
if err != nil {
fmt.Println("connect db error: ", err)
}
defer db.Close()
// According to the progressive query rule 1 records
var animal Animal
db.First(&animal)
fmt.Println(animal)
// According to gradually query the last 1 records
var animal2 Animal
db.Last(&animal2)
fmt.Println(animal2)
// Specifies a record (available only if the primary key is an integer)
var animal3 Animal
db.First(&animal3, 2)
fmt.Println(animal3)
//where conditions
// The eligible number 1 records
var animal4 Animal
db.Where("name = ?", "demotest2").First(&animal4)
fmt.Println("where : ", animal4, animal4.ID, animal4.Name, animal4.Age)
// All records that meet the criteria
var animals5 []Animal
db.Where("name = ?", "galeone").Find(&animals5)
fmt.Println(animals5)
for k, v := range animals5 {
fmt.Println("k:", k, "ID:", v.ID, "Name:", v.Name, "Age:", v.Age)
}
//IN
var animals6 []Animal
db.Where("name IN (?)", []string{"demo-test", "demotest2"}).Find(&animals6)
fmt.Println(animals6)
//LIKE
var animals7 []Animal
db.Where("name like ?", "%jim%").Find(&animals7)
fmt.Println(animals7)
//AND
var animals8 []Animal
db.Where("name = ? AND age >= ?", "jim", "24").Find(&animals8)
fmt.Println(animals8)
// The total number of
var count int
var animals9 []Animal
db.Where("name = ?", "galeone").Or("name = ?", "jim").Find(&animals9).Count(&count)
fmt.Println(animals9)
fmt.Println(count)
//Scan, Native queries
var animals10 []Animal
db.Raw("SELECT id, name, age From Animals WHERE name = ? AND age = ? ", "galeone", "30").Scan(&animals10)
fmt.Println("Scan: ", animals10)
// Native queries, select all
var animals11 []Animal
rows, _ := db.Raw("SELECT id,name FROM Animals").Rows()
// Note: Above select id,name I can't write * Replace, otherwise the results are the default 0 value
// And it goes like this: ALL: [{0 0} {0 0} {0 0} {0 0} {0 0} {0 0} {0 0}]
//Scan What field is next, select What field will follow
for rows.Next() {
var result Animal
rows.Scan(&result.ID, &result.Name)
animals11 = append(animals11, result)
}
fmt.Println("ALL: ", animals11)
//output:ALL: [{1 demo-test 0} {2 galeone 0} {3 demotest2 0} {4 galeone 0} {5 galeone 0} {6 jim 0} {7 jimmy 0}]
//select The query
var animal12 Animal
db.Select("name,age").Find(&animal12) // Only the query name . age Field, equivalent to select name,age from user
fmt.Println("select: ", animal12)
// db.Select([]string{"name", "age"}).Find(&animal12)
// fmt.Println("select2: ", animal12)
}
update
update.go
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type Animal struct {
ID int64
Name string
Age int64
}
func main() {
db, err := gorm.Open("mysql", "root:root@/gormdemo?charset=utf8&parseTime=true&loc=Local")
if err != nil {
fmt.Println("connect db error: ", err)
}
defer db.Close()
/// According to the 1 Conditional update
// Update field values based on conditions ,
// After it Debug() , run time, can print out sql
db.Debug().Model(&Animal{}).Where("id = ? ", 4).Update("name", "jimupdate")
//UPDATE `animals` SET `name` = 'jimupdate' WHERE (id = 4)
// In addition 1 Kind of writing: Update by condition
var animal Animal
animal = Animal{ID: 3}
db.Debug().Model(animal).Update("name", "demotest2update")
// db.Debug().Model(&animal).Update("name", "demotest2update") // I could write it this way
//UPDATE `animals` SET `name` = 'demotest2update' WHERE `animals`.`id` = 3
/// Multiple conditional updates
db.Model(&Animal{}).Where("id = ? AND age = ?", 4, 45).Update("name", "jimupdate3")
//UPDATE `animals` SET `name` = 'jimupdate2' WHERE (id = 4 AND age = 45)
/// Updating multiple values
db.Debug().Model(&Animal{}).Where("id = ?", 4).Update(Animal{Name: "jim", Age: 90})
// UPDATE `animals` SET `age` = 90, `name` = 'jim' WHERE (id = 4)
animal2 := Animal{ID: 5}
db.Debug().Model(&animal2).Update(map[string]interface{}{"name": "jimm", "age": 100})
//UPDATE `animals` SET `age` = 100, `name` = 'jimm' WHERE `animals`.`id` = 5
}
delete
delete.go
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
type Animal struct {
ID int64
Name string
Age int64
}
func main() {
db, err := gorm.Open("mysql", "root:root@/gormdemo?charset=utf8&parseTime=true&loc=Local")
if err != nil {
fmt.Println("connect db error: ", err)
}
defer db.Close()
db.Debug().Where("id = ?", 13).Delete(&Animal{})
// DELETE FROM `animals` WHERE (id = 13)
db.Debug().Delete(&Animal{}, "id = ? AND age = ?", 14, 10)
//DELETE FROM `animals` WHERE (id = 14 AND age = 10)
}
4: Debug
Add Debug() directly after db, as in delete.go
5: reference
https://gorm.io/zh_CN/