普拉多VX

人生一路,不问来时,不知归期

0%

GORM安装使用

官方网站

Gorm安装

1
go get -u github.com/jinzhu/gorm

连接数据库

连接数据库首先要导入驱动。gorm默认包含了如下一些驱动可以使用

1
2
3
4
import _ "github.com/jinzhu/gorm/dialects/mysql"
// import _ "github.com/jinzhu/gorm/dialects/postgres"
// import _ "github.com/jinzhu/gorm/dialects/sqlite"
// import _ "github.com/jinzhu/gorm/dialects/mssql"

连接实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package main

import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)


func main() {
db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")
defer db.Close()

if err != nil {
fmt.Println("连接失败")
return
}

}

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package main

import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
"time"
)

//创建User表struct 默认表名是users
type User struct {
ID int //如果有ID字段,将自动设置为主键
Username string
Password string
CreatedAt *time.Time //系统将自动插入当前时间
UpdatedAt *time.Time
}

// 将 User 的表名设置为 `monitor_users`
func (User) TableName() string {
return "monitor_users"
}

func main() {
db, err := gorm.Open("mysql", "root:root@tcp(127.0.0.1:3307)/monitor?charset=utf8&parseTime=True&loc=Local")
defer db.Close()

if err != nil {
fmt.Println("连接失败")
return
}
// 应用模型到数据库
db.AutoMigrate(&User{})
}

插入数据

插入数据

1
db.Create(&User{Username: "roddy",Password: "123456",})  //插入一条数据

插入数据前判断

1
2
3
4
u := User{Username: "test",Password: "123456"}
fmt.Println(db.NewRecord(&u)) //判断主键是否为空,表中是否存在主键冲突,如果以Id为主键改判断可以忽略
db.Create(&u)
fmt.Println(db.NewRecord(&u))

查询数据

查询第一条数据

1
2
3
4
5
6
var firstValue User
db.First(&firstValue)
fmt.Println("username:",firstValue.Username)

//使用debug查询执行原始语句
db.Debug().First(&User{})

查询最后一条数据

1
2
3
4
//查询最后一条数据
var lastValue User
db.Last(&lastValue)
fmt.Println("last username:",lastValue.Username)

根据条件筛选

where 条件筛选

1
2
3
4
5
6
// 条件筛选
var users [] User
db.Where("username = ?", "roddy").Find(&users)
for _,v := range users{
fmt.Printf("username:%v password:%v\n",v.Username,v.Password)
}

参考

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

// 获取第一个匹配的记录
db.Where("name = ?", "jinzhu").First(&user)
//// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// 获取所有匹配的记录
db.Where("name = ?", "jinzhu").Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu';

// <>
db.Where("name <> ?", "jinzhu").Find(&users)
//// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
//// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
//// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
//// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

更新数据

更新表中username为roddy1的username 字段,修改为”haha”

1
db.Model(&User{}).Where("username=?","roddy1").Update("username","haha")

参考样例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 更新单个属性,如果它有变化
db.Model(&user).Update("name", "hello")
//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// 根据给定的条件更新单个属性
db.Model(&user).Where("active = ?", true).Update("name", "hello")
//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;

// 使用 map 更新多个属性,只会更新其中有变化的属性
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
//// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

// 使用 struct 更新多个属性,只会更新其中有变化且为非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 警告:当使用 struct 更新时,GORM只会更新那些非零值的字段
// 对于下面的操作,不会发生任何更新,"", 0, false 都是其类型的零值
db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

删除数据

1
2
3
4
5
6
7
8
9
//删除所有数据
db.Delete(&User{})

// 删除匹配数据
db.Where("email LIKE ?", "%jinzhu%").Delete(Email{})
//// DELETE from emails where email LIKE "%jinzhu%";

db.Delete(Email{}, "email LIKE ?", "%jinzhu%")
//// DELETE from emails where email LIKE "%jinzhu%";