|
简介
ORM
Object-Relationl Mapping， 它的作用是映射数据库和对象之间的关系，方便我们在实现数据库操作的时候不用去写复杂的sql语句，把对数据库的操作上升到对于对象的操作
Gorm
gorm就是基于Go语言实现的ORM库。
类似于Java生态里大家听到过的Mybatis、Hibernate、SpringData等。
下载使用Gorm库
go get -u github.com/jinzhu/gorm
// 这是比较原始的方式，现在有了go mod，我们可以更方便的配置，甚至不用配置。
// 写好代码，在文件下执行go build，go.mod会自动添加对于gorm的依赖包
使用Gorm
创建一个Gorm实例
Example1
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jinzhu/gorm"
"os"
"time"
)
const (
dbUser string = "test"
dbPassword string = "ZHOUjian.22"
dbHost string = "121.36.43.223"
dbPort int = 3306
dbName string = "cmdb"
)
var dsn string = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&loc=Local&parseTime=true",
dbUser, dbPassword, dbHost, dbPort, dbName)
type User3 struct {
// 会从gorm嵌入一些字段进来
gorm.Model
Name string
Password string
Birthday time.Time
Sex bool
Tel string
Addr string
Desc string
}
type User4 struct {
Id int `gorm: "primary_key; auto_increment"`
Name string
Password string
Birthday time.Time
Sex bool
Tel string
Addr string
Desc string
}
func (*User4)TableName() string {
return "user"
}
//type User4 struct {
// Id int `gorm:"primary_key"`
// Name string `gorm:"type:varchar(32);unique;not null; default:''"`
// Password string
// Birthday time.Time `gorm:"type:date"`
// Sex bool
// Tel string `gorm:"column:telephone"`
// Addr string
// Desciption string `gorm:"type:text"`
//}
func main() {
db, err := gorm.Open("mysql", dsn)
if err != nil {
fmt.Println(err)
os.Exit(-1)
}
// 创建表
db.AutoMigrate(&User3{})
db.AutoMigrate(&User4{})
// fmt.Println(db.CreateTable(&User3{},&User4{}))
//db.Model(&User{}).AddIndex("idx_name_addr", "name", "addr")
db.Close()
}
判断表是否存在
// 判断表是否存在
fmt.Println(db.HasTable(&User3{}))
fmt.Println(db.HasTable("user4"))
db.Close()
删除表
fmt.Println(db.DropTable(&User3{},&User4{}))
修改表指定列
db.Model(&User3{}).ModifyColumn("birthday","date")
db.Close()
删除列
db.Model(&User3{}).DropColumn("birthday")
添加删除索引
db.Model(&User3{}).AddIndex("idx_name","name")
// 联合索引 db.Model(&User3{}).AddIndex("idx_name_addr","name","addr")
db.Close()
// 删除索引
db.Model(&User3{}).RemoveIndex("idx_name_addr")
// 创建UniqueIndex索引
db.Model(&User3{}).AddUniqueIndex("idx_name","name")
增
package main
import (
"fmt"
"gorm_demo2/model"
"time"
)
func main() {
model.InitDb()
for i:=0;i<10;i++{
user := model.User2{
Name: fmt.Sprintf("youmen_%d",i),
Password: fmt.Sprintf("youmen_%d",i),
Birthday: time.Date(1988,11,11,0,0,0,0,time.UTC),
Sex: false,
Tel: "18621048481",
Addr: "北京市",
Description: "Devops",
Status: 1,
}
model.DB.Create(&user)
}
}
Example2
var user model.User
model.DB.First(&user)
user.Sex = true
model.DB.Save(&user)
model.DB.Model(&model.User{}).Where("id=2").UpdateColumn("sex",true)
model.DB.Model(&model.User{}).Where("id=3").UpdateColumns(map[string]interface{}{"sex":true})
model.DB.Model(&model.User{}).Where("id=4").Update("sex",true)
model.DB.Unscoped().Delete(&user)
查
package main
import (
"fmt"
"gorm_demo2/model"
)
func main() {
model.InitDb()
//for i:=0;i<10;i++{
// user := model.User2{
// Name: fmt.Sprintf("youmen_%d",i),
// Password: fmt.Sprintf("youmen_%d",i),
// Birthday: time.Date(1988,11,11,0,0,0,0,time.UTC),
// Sex: false,
// Tel: "18621048481",
// Addr: "北京市",
// Description: "Devops",
// Status: 1,
// }
// model.DB.Create(&user)
//}
var user model.User2
model.DB.First(&user,"name=?","youmen_0")
fmt.Println(user)
var user2 model.User2
model.DB.Last(&user2,"name=?","youmen_6")
fmt.Println(user2)
//var users []model.User2
// 查找6结尾的
//model.DB.Where("name like ?","%6").Find(&users)
//fmt.Println(users)
// in
//model.DB.Where("name in (?)",[]string{"youmen_6","youmen_8"}).Find(&users)
//fmt.Println(users)
// and
//model.DB.Where("name = ? and password =?","youmen_6","youmen_6").Find(&users)
//fmt.Println(users)
// not / or
//model.DB.Where("name = ?","youmen_6").Not("password = ?","youmen_1").Find(&users)
//fmt.Println(users)
// 排序
//model.DB.Order("id asc").Find(&users)
//model.DB.Order("id desc").Find(&users)
//model.DB.Order("id asc,name asc").Find(&users)
//fmt.Println(users)
// Limit,offset
//model.DB.Order("id").Limit(3).Offset(5).Find(&users)
//fmt.Println(users)
// count
//var count int
//model.DB.Model(&model.User2{}).Where("name=?","youmen_6").Count(&count)
//model.DB.Table("user").Where("name=?","youmen_6").Count(&count)
//model.DB.Model(&users).Where("name=?","youmen_6").Count(&count)
//fmt.Println(count)
// group by
//rows, _ := model.DB.Model(&model.User2{}).Select("name,password").Rows()
//for rows.Next() {
// var name,password string
// rows.Scan(&name,&password)
// fmt.Println(name,password)
//}
rows , _ := model.DB.Model(&model.User2{}).Select("name, count(*) as cnt").Group("name").Having("count(*) > ?",1).Rows()
for rows.Next() {
var name string
var count int
rows.Scan(&name,&count)
fmt.Println(name,count)
}
}
更新
// 更新
//var user model.User2
//if model.DB.First(&user,"name=?","youmen_0").Error == nil {
// user.Name = "zhoujian"
// model.DB.Save(user)
//}
//fmt.Println(user)
// 批量更新
//model.DB.Model(&model.User2{}).Where("id > ?",1).UpdateColumn("description","go")
//model.DB.Model(&model.User2{}).Where("id > ?",6).UpdateColumn(map[string]interface{}{"tel":"123","addr":"湖北"})
model.DB.Model(&model.User2{}).Where("id > ?",4).Updates(model.User2{Name: "1234",Addr: "北京",Description: "Devops"})
删除
// 删除
var user model.User2
if model.DB.First(&user,"password=?","youmen_1").Error == nil {
model.DB.Delete(&user)
}
model.DB.Where("id > ?",3).Delete(&model.User2{})
原始sql
var users []model.User
model.DB.Raw("select name from user where name = ?","youmen_4").Scan(&users)
fmt.Println(users)
var name string
model.DB.Raw("select name from user where id = ?",5).Row().Scan(&name)
fmt.Println(name)
fmt.Println(model.DB.Debug().Exec("insert into user(name) value(?)","wunai").RowsAffected)
日志
fmt.Println(model.DB.Debug().Exec("insert into user(name) value(?)","wunai").RowsAffected)
事务
/*
tx := db.Begin()
tx.Create()
tx.Rollback()
tx.Commit()
*/
BeegoORM
配置使用BeegoORM表结构
package main
import (
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User1 struct {
// 创建出id不会是i_d,beegoorm默认会将第一个数值类型当成主键
ID int `orm:"column(id);pk;auto;"`
Name string `orm:"unique;size(64);description(姓名)"`
Password string `orm:"size(1024)"`
Gender bool
Tel string `orm:"index"`
Text1 string `orm:"column(Text1);type(text)"`
// 设置默认
Height float32 `orm:"column(height);default(1.8)"`
// string默认为255,如果想更大可以自定义
Str string `orm:"column(description);size(1024)"`
//Salary float64 `orm:"column(description)"`
Birthday *time.Time `orm:"type(date)"`
// 创建时自动更新时间
CreateAt *time.Time `orm:"auto_now_add;"`
// 插入时自动更新时间
UpdateAt *time.Time `orm:"auto_now"`
DeleteAt *time.Time `orm:"null"`
}
// 将user1和user2关联起来,只是逻辑关联,并不是物理关联
func (m *User1) TableName() string {
// 此处返回什么值,就会创建对应的表
return "user3"
}
// 联合索引
func (m *User1) TableIndex() [][]string {
return [][]string{
{"Tel"},
{"Name"},
{"Tel", "Name"},
{"Tel", "Name", "Gender"},
}
}
// 修改默认Engine
func (m *User1) TableEngine() string {
return "mysiam"
}
func main() {
// 0. 导入包
// 1. 注册驱动
// 2. 注册数据库
// 3. 定义数据模型 model
// 4. 注册数据模型
// 5. 操作
// 同步表结构
// 数据: 增,删,改,查
dsn := "test:ZHOUjian.22@tcp(121.36.43.223:3306)/orm?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL)
// 可以同时注册连接多个数据库
orm.RegisterDataBase("default", "mysql", dsn)
orm.RegisterModel(new(User1))
// orm.RunCommand()
orm.RunCommand()
orm.RunSyncdb("default", true, true)
}
Beego_orm运行参数
// 将orm要创建的表呈现为sql
go run beego_rom.go orm sqlall
-- --------------------------------------------------
-- Table Structure for `main.User`
-- --------------------------------------------------
CREATE TABLE IF NOT EXISTS `user` (
`i_d` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(255) NOT NULL DEFAULT '' ,
`gender` bool NOT NULL DEFAULT FALSE ,
`tel` varchar(255) NOT NULL DEFAULT '' ,
`height` double precision NOT NULL DEFAULT 0
) ENGINE=InnoDB;
// 同步数据库
// -force 强制创建,先删在创建
// -v verbose info 创建过程将创建的表sql打印出来
go run beego_rom.go orm syncdb
table `user1` already exists, skip
add column `main.User1.Salary` for table `user1`
CURD
增
package main
import (
"fmt"
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User1 struct {
// 创建出id不会是i_d,beegoorm默认会将第一个数值类型当成主键
ID int `orm:"column(id);pk;auto;"`
Name string `orm:"unique;size(64);description(姓名)"`
Password string `orm:"size(1024)"`
Gender bool
Tel string `orm:"index"`
Text1 string `orm:"column(Text1);type(text)"`
// 设置默认
Height float32 `orm:"column(height);default(1.8)"`
// string默认为255,如果想更大可以自定义
Str string `orm:"column(description);size(1024)"`
//Salary float64 `orm:"column(description)"`
Birthday *time.Time `orm:"type(date)"`
// 创建时自动更新时间
CreateAt *time.Time `orm:"auto_now_add;"`
// 插入时自动更新时间
UpdateAt *time.Time `orm:"auto_now"`
DeleteAt *time.Time `orm:"null"`
}
// 将user1和user2关联起来,只是逻辑关联,并不是物理关联
func (m *User1) TableName() string {
// 此处返回什么值,就会创建对应的表
return "user3"
}
// 联合索引
func (m *User1) TableIndex() [][]string {
return [][]string{
{"Tel"},
{"Name"},
{"Tel", "Name"},
{"Tel", "Name", "Gender"},
}
}
// 修改默认Engine
//func (m *User1) TableEngine() string {
// return "mysiam"
//}
func main() {
// 将底层执行SQL打印出来
orm.Debug = true
// 0. 导入包
// 1. 注册驱动
// 2. 注册数据库
// 3. 定义数据模型 model
// 4. 注册数据模型
// 5. 操作
// 同步表结构
// 数据: 增,删,改,查
dsn := "test:ZHOUjian.22@tcp(121.36.43.223:3306)/orm?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL)
// 可以同时注册连接多个数据库
orm.RegisterDataBase("default", "mysql", dsn)
orm.RegisterModel(new(User1))
// orm.RunCommand()
orm.RunCommand()
orm.RunSyncdb("default", true, true)
//增
birthday, _ := time.Parse("2006-01-02", "1988-01-01")
user := &User1{
Name: "youmen",
Password: "youmen",
Gender: true,
Tel: "18620148888",
Birthday: &birthday,
}
// 数据库连接
ormer := orm.NewOrm()
fmt.Printf("%#v\n",user)
id,err := ormer.Insert(user)
fmt.Println(id,err)
fmt.Printf("%#v\n",user)
// 批量插入
users := make([]*User1,10,10)
for i:=0;i<10;i++{
user:= &User1{
Name: fmt.Sprintf("youmen-%d",i),
Password: "youmen",
Gender: true,
Tel: "18620148888",
Birthday: &birthday,
}
users [i] = user
}
ormer.InsertMulti(3,users)
}
查
package main
import (
"fmt"
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User struct {
ID int `orm:"column(id)"`
Name string `orm:"size(64)"`
Password string `orm:"size(1024)"`
Gender bool `orm:""`
Tel string `orm:""`
Height float32 `orm:""`
Birthday *time.Time `orm:"type(date);"`
CreatedAt *time.Time `orm:"auto_now_add;"`
UpdateAt *time.Time `orm:"auto_now;"`
DeletedAt *time.Time `orm:"null"`
}
func main() {
orm.Debug = true
dsn := "test:ZHOUjian.22@tcp(121.36.43.223:3306)/orm?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL) // 可省略
orm.RegisterDataBase("default", "mysql", dsn)
orm.RegisterModel(new(User))
// 增加
birthday, _ := time.Parse("2006-01-02", "1988-01-01")
user := &User{
Name: "youmen",
Password: "youmen",
Gender: true,
Tel: "186102348888",
Birthday: &birthday,
}
//数据库连接
ormer := orm.NewOrm()
/*
fmt.Printf("%#v\n", user)
id, err := ormer.Insert(user) // 插入数据
fmt.Println(id, err)
fmt.Printf("%#v\n", user)
users := make([]*User, 3, 10)
for i := 0; i < 3; i++ {
user := &User{
Name: fmt.Sprintf("youmen-%d", i),
Password: "youmen",
Gender: true,
Tel: "186102348888",
Birthday: &birthday,
}
users[i] = user
}
ormer.InsertMulti(2, users)
*/
// 读
//user = &User{ID: 3}
//err := ormer.Read(user)
//fmt.Println(user, err)
user = &User{Name: "youmen",Password: "aaa"}
err:=ormer.Read(user,"Name")
fmt.Println(user,err)
// 如果没有再创建
ormer.ReadOrCreate(user,"Name")
fmt.Println(user)
}
更新
// 更新
user = &User{ID:1}
err := ormer.Read(user)
fmt.Println(user,err)
user.Name = "常见"
ormer.Update(user)
// 只更新Name字段
ormer.Update(user,"Name")
删除
// 删除
fmt.Println(ormer.Delete(&User{ID: 1}))
查询
根据ID查询
// 1.有ORM对象
o := orm.NewOrm()
// 2.查询的对象
user := models.User{}
// 3.指定查询对象字段值
user.Id = 1 //根据Id查询
// 4.查询
err := o.Read(&user)
if err != nil{
beego.Info("查询失败：",err)
return
}
beego.Info("查询成功",user)
根据name查询
// 1.有ORM对象
o := orm.NewOrm()
// 2.查询的对象
user := models.User{}
// 3.指定查询对象字段值
user.Name = "root"
// 4.查询
err := o.Read(&user,"Name") //不同之处，传入两个参数
if err != nil{
beego.Info("查询失败：",err)
return
}
beego.Info("查询成功",user)
package main
import (
"fmt"
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User struct {
ID int `orm:"column(id)"`
Name string `orm:"size(64)"`
Password string `orm:"size(1024)"`
Gender bool `orm:""`
Tel string `orm:""`
Height float32 `orm:""`
Birthday *time.Time `orm:"type(date);"`
CreatedAt *time.Time `orm:"auto_now_add;"`
UpdateAt *time.Time `orm:"auto_now;"`
DeletedAt *time.Time `orm:"null"`
}
func main() {
orm.Debug = true
dsn := "test:ZHOUjian.22@tcp(121.36.43.223:3306)/orm?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL) // 可省略
orm.RegisterDataBase("default", "mysql", dsn)
orm.RegisterModel(new(User))
ormer := orm.NewOrm()
queryset := ormer.QueryTable(&User{})
var users []*User
queryset.All(&users)
fmt.Println(users)
// Where条件查询
// Filter
// 列名_条件,对象
// 条件,=((i)exact),<(lt),>(gt),>=(gte),<=(lte),in(in参数要是切片),like %content%([i]contains) %% start(startswitch) % %end(endswitch)
//queryset.Filter("name","youmen").Count()
//fmt.Println(queryset.Filter("name__iexact","Youmen").Count())
// 前后自动加上%
//fmt.Println(queryset.Filter("name__contains","you%men").Count())
//
//fmt.Println(queryset.Filter("name__startswitch","you%men").Count())
//fmt.Println(queryset.Filter("name__endswitch","you%men").Count())
// in
//fmt.Println(queryset.Filter("id__in",[]int{1,2}).Count())
//fmt.Println(queryset.Filter("id__gt",3).Count())
// id大于5小于10
//fmt.Println(queryset.Filter("id__gt",5).Filter("id__lt",10).Count())
// 不等于 Excude
//fmt.Println(queryset.Exclude("name__iexact","Youmen").Count())
// 分页
// 每页显示3条,偏移2条
//queryset.All(&users)
//queryset.Limit(3).Offset(2).All(&users)
// 排序
// 升序
queryset.OrderBy("Name").All(&users)
// 降序
fmt.Println(queryset.OrderBy("-Name","-Tel").All(&users))
// 只查一条数据
fmt.Println(queryset.OrderBy("-Name","-Tel").One(&users))
// name like '%youmen%' and (tel like '186%' or tel like '186%')
cond := orm.NewCondition()
condTel := orm.NewCondition()
condTel = condTel.Or("tel__istartswith", "186").Or("tel__istartswith", "186")
cond = cond.And("name__icontains", "youmen").AndCond(condTel)
queryset.SetCond(cond).All(&users)
//queryset.SetCond(cond).All(&users,"id")
//fmt.Println(users[0])
}
批量查询和修改
// 只查一条数据
//fmt.Println(queryset.OrderBy("-Name","-Tel").One(&users))
// name like '%youmen%' and (tel like '186%' or tel like '186%')
//cond := orm.NewCondition()
//condTel := orm.NewCondition()
//condTel = condTel.Or("tel__istartswith", "186").Or("tel__istartswith", "186")
//cond = cond.And("name__icontains", "youmen").AndCond(condTel)
//queryset.SetCond(cond).All(&users)
//queryset.SetCond(cond).All(&users,"id")
//fmt.Println(users[0])
//queryset.Update(orm.Params{"name":"wunai"})
//queryset.Filter("id__gt",5).Update(orm.Params{"name":"youmen"})
// 在原有值基础上加10
queryset.Filter("id__gt",10).Update(orm.Params{
"name": "youmen",
"height": orm.ColValue(orm.ColAdd,10),
})
queryset.Filter("id__gt",10).Delete()
原始SQL
package main
import (
"fmt"
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User struct {
ID int `orm:"column(id)"`
Name string `orm:"size(64)"`
Password string `orm:"size(1024)"`
Gender bool `orm:""`
Tel string `orm:""`
Height float32 `orm:""`
Birthday *time.Time `orm:"type(date);"`
CreatedAt *time.Time `orm:"auto_now_add;"`
UpdateAt *time.Time `orm:"auto_now;"`
DeletedAt *time.Time `orm:"null"`
}
func main() {
orm.Debug = true
dsn := "test:ZHOUjian.22@tcp(121.36.43.223:3306)/orm?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL) // 可省略
orm.RegisterDataBase("default", "mysql", dsn)
orm.RegisterModel(new(User))
// 原始SQL
//db,err := orm.GetDB("default")
//db.Ping()
//db.QueryRow().Scan()
//db.Exec()
ormer := orm.NewOrm()
// 插
//rawseter := ormer.Raw("insert into user(name,created_at,update_at,birthday)values(?,?,?,?)","1","2021-10-01","2021-10-01","2021-10-01")
//fmt.Println(rawseter.Exec())
// 修
//rawseter := ormer.Raw("update user set name=? where id=?","wunai",10)
//fmt.Println(rawseter.Exec())
// 删
//rawseter := ormer.Raw("delete from user where id=?",10)
//fmt.Println(rawseter.Exec())
// 查
//rawseter := ormer.Raw("select id,name from user")
//var users []*User
//rawseter.QueryRows(&users)
//fmt.Printf("%#v\n",users[1])
//var user *User
//rawster := ormer.Raw("select id,name from user where id = ?",11)
//rawster.QueryRow(&user)
//fmt.Printf("%#v\n",user)
rawster := ormer.Raw("select name,count(*) as cnt from user group by name")
var result []orm.Params
rawster.Values(&result)
fmt.Println(result)
var resultList []orm.ParamsList
rawster.ValuesList(&resultList)
fmt.Println(resultList)
}
ORM事务
// 启动事务
ormer.Begin()
// 提交
ormer.Commit()
// 撤回
ormer.Rollback()
连接多个数据库
package main
import (
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"time"
)
type User struct {
ID int `orm:"column(id)"`
StaffID string `orm: "column(staff_id);size(32)"`
Name string `orm:"size(64)"`
Nickname string `orm:"size(64)"`
Password string `orm:"size(64)"`
Gender int `orm:""`
Tel string `orm:"size(32)"`
Addr string `orm:"size(64)"`
Email string `orm:"size(32)"`
Department string `orm:"size(32)"`
Status int `orm:""`
CreateAt *time.Time `orm:"auto_now_add"`
UpdateAt *time.Time `orm:"auto_now"`
DeleteAt *time.Time `orm:"null"`
}
func (user *User) TableName() string {
return "youmen"
}
func main() {
orm.Debug = true
dsn1 := "test:ZH2@tcp(1.1.1.13:3306)/cmdb?charset=utf8mb4&parseTime=true&loc=PRC"
dsn2 := "youen:ZH.20@tcp(1.1.1.1:3306)/youmen?charset=utf8mb4&parseTime=true&loc=PRC"
orm.RegisterDriver("mysql", orm.DRMySQL) // 可省略
orm.RegisterDataBase("default", "mysql", dsn1)
orm.RegisterDataBase("wpy", "mysql", dsn2)
orm.RegisterModel(new(User))
birthday,_ := time.Parse("2006-01-02","1999-02-11")
user := &User{
Name: "youmen",
Password: "youmen",
Gender: true,
Tel: "186102348888",
Birthday: &birthday,
}
//
ormer1 := orm.NewOrm()
ormer1.Inster(user)
}
|
客服电话
APP下载
官方微信
请发表评论