golang基础-Postgresql-ORM框架github.com/go-pg/pg学习二(增删改)
葛威
创建表
在操作数据库的增删改查之前，我们先创建数据库表
这里就直接贴出代码来，如果有疑问可以参考我下面的博客：
golang基础-Postgresql-ORM框架github.com/go-pg/pg学习一(连接数据库、创建、删除表)
package main
import (
"fmt"
"github.com/go-pg/pg"
"github.com/go-pg/pg/orm"
_ "github.com/lib/pq"
)
const (
host = "localhost"
port = 5432
user = "wyfzhiliao"
password = "wyfzhiliao"
dbname = "go"
)
func connet() *pg.DB{
db:=pg.Connect(&pg.Options{
User:user,
Password:password,
Database:dbname,
})
var n int
_,err:=db.QueryOne(pg.Scan(&n),"SELECT 1")
if err != nil{
panic(err)
}
return db
}
type User struct {
Id int64
Name string
Emails []string
tableName struct{} `sql:"users"`
}
type Story struct {
Id int64
Title string
AuthorId int64
Author *User
tableName struct{} `sql:"storys"`
}
func (s Story) String() string {
return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}
func (u User) String() string {
return fmt.Sprintf("User(%d %s %v)", u.Id, u.Name, u.Emails)
}
func CreateTabel(db *pg.DB) error{
for _,model:= range []interface{}{&User{}, &Story{}}{
err:=db.CreateTable(model,&orm.CreateTableOptions{
IfNotExists:true,
FKConstraints:true,
})
if err!= nil{
return err
}
}
return nil
}
func main() {
db:=connet()
CreateTabel(db)
}
表如下：
go=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+------------
public | storys | table | wyfzhiliao
public | storys_id_seq | sequence | wyfzhiliao
public | users | table | wyfzhiliao
public | users_id_seq | sequence | wyfzhiliao
(4 rows)
插入
插入的方法有
func (db *DB) Insert(model …interface{}) error
func (q *Query) Insert(values …interface{}) (Result, error)
直接通过例子来了解吧，我下面的代码都做了注释
func main() {
db:=connet()
CreateTabel(db)
user1:=&User{
Name:"user1",
Emails:[]string{"user1@qq.com","user1@163.com"},
}
//插入方法1 db.Insert
db.Insert(user1)
fmt.Println("user1-->",user1)
//批量插入 db.Insert
user2:=&User{
Name:"user2",
Emails:[]string{"user2@qq.com","user2@163.com"},
}
user3:=&User{
Name:"user3",
Emails:[]string{"user3@qq.com","user3@163.com"},
}
db.Insert(user2,user3)
fmt.Println("user2->",user2,"-user2->",user3)
//批量插入db.Model.insert
user4:=&User{
Name:"user4",
Emails:[]string{"user4@qq.com","user4@163.com"},
}
user5:=&User{
Name:"user5",
Emails:[]string{"user5@qq.com","user5@163.com"},
}
db.Model(user4,user5).Insert()
fmt.Println("user4->",user4,"-user5->",user5)
//db.insert 插入切片
user6_7:=[]User{
{
Name:"user6",Emails:[]string{"user6@qq.com","user6@163.com"},
},{
Name:"user7",Emails:[]string{"user7@qq.com","user7@163.com"},
},
}
db.Insert(&user6_7)
fmt.Println("user6->",user6_7)
}
我们可以通过打印对应的model，可以查询插入成功后的信息，比如数据库生成的ID等等
user1--> User(20 user1 [user1@qq.com user1@163.com])
user2-> User(21 user2 [user2@qq.com user2@163.com]) -user2-> User(22 user3 [user3@qq.com user3@163.com])
user4-> User(23 user4 [user4@qq.com user4@163.com]) -user5-> User(24 user5 [user5@qq.com user5@163.com])
user6-> [User(25 user6 [user6@qq.com user6@163.com]) User(26 user7 [user7@qq.com user7@163.com])]
通过查询数据库，新增数据如下：
go=# select * from users;
id | name | emails
----+-------+-----------------------------------
20 | user1 | ["user1@qq.com", "user1@163.com"]
21 | user2 | ["user2@qq.com", "user2@163.com"]
22 | user3 | ["user3@qq.com", "user3@163.com"]
23 | user4 | ["user4@qq.com", "user4@163.com"]
24 | user5 | ["user5@qq.com", "user5@163.com"]
25 | user6 | ["user6@qq.com", "user6@163.com"]
26 | user7 | ["user7@qq.com", "user7@163.com"]
(7 rows)
删除
全匹配删除
func main() {
db:=connet()
user:=User{
Id:20,
Name:"user1",
Emails:[]string{"user1@qq.com","user1@163.com"},
}
db.Delete(&user)
}
查询数据库发现还剩下6条记录
go=# select * from users;
id | name | emails
----+-------+-----------------------------------
21 | user2 | ["user2@qq.com", "user2@163.com"]
22 | user3 | ["user3@qq.com", "user3@163.com"]
23 | user4 | ["user4@qq.com", "user4@163.com"]
24 | user5 | ["user5@qq.com", "user5@163.com"]
25 | user6 | ["user6@qq.com", "user6@163.com"]
26 | user7 | ["user7@qq.com", "user7@163.com"]
(6 rows)
或者使用如下的方法
func main() {
db:=connet()
user2:=User{
Id:21,
Name:"user2",
Emails:[]string{"user2@qq.com","user2@163.com"},
}
user3:=User{
Id:22,
Name:"user3",
Emails:[]string{"user3@qq.com","user3@163.com"},
}
db.Model(&user2,&user3).Delete()
}
然后记录查询数据库发现被删除了2条
go=# select * from users;
id | name | emails
----+-------+-----------------------------------
23 | user4 | ["user4@qq.com", "user4@163.com"]
24 | user5 | ["user5@qq.com", "user5@163.com"]
25 | user6 | ["user6@qq.com", "user6@163.com"]
26 | user7 | ["user7@qq.com", "user7@163.com"]
(4 rows)
go=#
带条件删除
我们还可以带条件删除
这里只是列举一个简单的，后续会详细的学习select的用法
ids := pg.In([]int{23,24})
db.Model(&User{}).Where("id IN (?)", ids).Delete()
然后记录查询数据库发现被删除了2条
go=# select * from users;
id | name | emails
----+-------+-----------------------------------
25 | user6 | ["user6@qq.com", "user6@163.com"]
26 | user7 | ["user7@qq.com", "user7@163.com"]
(2 rows)
go=#
或者如下：
通过某个字段查找然后删除即可
db.Model(&User{}).Where("name=?","user2").Delete()
修改
func (db *DB) Update(model interface{}) error
通过ID修改部分字段
通过ID，修改25 | user6 | ["user6@qq.com", "user6@163.com"]这一条记录的name为user6_
func main() {
db:=connet()
user:=User{
Id:25,
}
err:=db.Select(&user)
if err!=nil{
panic(err)
}
user.Name = "user6_"
err=db.Update(&user)
if err!=nil{
panic(err)
}
}
执行上述代码，查看数据库已经修改了
go=# select * from users;
id | name | emails
----+--------+-----------------------------------
26 | user7 | ["user7@qq.com", "user7@163.com"]
25 | user6_ | ["user6@qq.com", "user6@163.com"]
(2 rows)
go=#
通过ID全匹配修改
上述修改的方法，也可以直接用如下的方式，但是效果与上面不一样，这里会使emails字段为空
func main() {
db:=connet()
user:=&User{
Id:25,
Name:"user6+",
}
db.Update(user)
}
查询数据库返现已经修改了
go=# select * from users;
id | name | emails
----+--------+-----------------------------------
26 | user7 | ["user7@qq.com", "user7@163.com"]
25 | user6+ |
(2 rows)
更新几个字段1
我们也可以更新几个字段，如下的方法：
func main() {
db:=connet()
user:=&User{
Id:25,
Name:"user66",
Emails:[]string{"user6@qq.com", "user6@163.com"},
}
db.Model(user).Set("name=?name,emails=?emails").Where("id=?id").Update()
}
现在在查看下数据库，ID为25的记录已经发生了变化
go=# select * from users;
id | name | emails
----+--------+-----------------------------------
26 | user7 | ["user7@qq.com", "user7@163.com"]
25 | user66 | ["user6@qq.com", "user6@163.com"]
(2 rows)
更新几个字段2
我们还可以利用如下的方法，更新某几个字段
func main() {
db:=connet()
user:=&User{
Id:25,
Name:"user66",
Emails:[]string{"user6@qq.com", "user6@163.com"},
}
db.Model(user).Column("name","emails").WherePK().Update()
}
我们在来查看下数据库发现，已经发生了变化
go=# select * from users;
id | name | emails
----+-------+-------------------------------------
26 | user7 | ["user7@qq.com", "user7@163.com"]
25 | user6 | ["user66@qq.com", "user66@163.com"]
(2 rows)
以下也可以
user:=&Order{
//Id:25,
Body:"BodyBodyBodyBodyBody",
Platform:"sfs",
}
db.Model(user).Set("body=?body").Where("platform=?platform").Update()
今天就到这里吧，好累，下次在继续分析其他的方法，注入select等等
类似资料：