golang学习笔记16 beego orm 数据库操作

beego ORM 是一个强大的 Go 语言 ORM 框架。她的灵感主要来自 Django ORM 和 SQLAlchemy。

目前该框架仍处于开发阶段，可能发生任何导致不兼容的改动。

官方文档：https://beego.me/docs/mvc/model/overview.md

已支持数据库驱动：

以上数据库驱动均通过基本测试，但我们仍需要您的反馈。

ORM 特性：

支持 Go 的所有类型存储
轻松上手，采用简单的 CRUD 风格
自动 Join 关联表
跨数据库兼容查询
允许直接使用 SQL 查询／映射
严格完整的测试保证 ORM 的稳定与健壮

更多特性请在文档中自行品读。

安装 ORM：

go get github.com/astaxie/beego/orm

简单示例：

package main
 
import (
    "fmt"
    "github.com/astaxie/beego/orm"
    _ "github.com/go-sql-driver/mysql" // import your used driver
)
 
// Model Struct
type User struct {
    Id   int
    Name string `orm:"size(100)"`
}
 
func init() {
    // set default database
    orm.RegisterDataBase("default", "mysql", "username:password@tcp(127.0.0.1:3306)/db_name?charset=utf8", 30)
 
    // register model
    orm.RegisterModel(new(User))
 
    // create table
    orm.RunSyncdb("default", false, true)
}
 
func main() {
    o := orm.NewOrm()
 
    user := User{Name: "slene"}
 
    // insert
    id, err := o.Insert(&user)
    fmt.Printf("ID: %d, ERR: %v\n", id, err)
 
    // update
    user.Name = "astaxie"
    num, err := o.Update(&user)
    fmt.Printf("NUM: %d, ERR: %v\n", num, err)
 
    // read one
    u := User{Id: user.Id}
    err = o.Read(&u)
    fmt.Printf("ERR: %v\n", err)
 
    // delete
    num, err = o.Delete(&u)
    fmt.Printf("NUM: %d, ERR: %v\n", num, err)
}

　　关联查询

type Post struct {
    Id    int    `orm:"auto"`
    Title string `orm:"size(100)"`
    User  *User  `orm:"rel(fk)"`
}
 
var posts []*Post
qs := o.QueryTable("post")
num, err := qs.Filter("User__Name", "slene").All(&posts)

　　SQL 查询

当您无法使用 ORM 来达到您的需求时，也可以直接使用 SQL 来完成查询／映射操作。

var maps []orm.Params
num, err := o.Raw("SELECT * FROM user").Values(&maps)
for _,term := range maps{
    fmt.Println(term["id"],":",term["name"])
}

　　事务处理

o.Begin()
...
user := User{Name: "slene"}
id, err := o.Insert(&user)
if err == nil {
    o.Commit()
} else {
    o.Rollback()
}

　　在开发环境下，您可以使用以下指令来开启查询调试模式：

func main() {
    orm.Debug = true
...

开启后将会输出所有查询语句，包括执行、准备、事务等。

例如：

[ORM] - 2013-08-09 13:18:16 - [Queries/default] - [    db.Exec /     0.4ms] -   [INSERT INTO `user` (`name`) VALUES (?)] - `slene`
...

注意：我们不建议您在部署产品后这样做。

实战例子：

model部分

// UpdateUser updates User by Id and returns error if
// the record to be updated doesn't exist
func UpdateUserById(m *User) (err error) {
    o := orm.NewOrm()
    v := User{Id: m.Id}
    // ascertain id exists in the database
    if err = o.Read(&v); err == nil {
        var num int64
        if num, err = o.Update(m); err == nil {
            fmt.Println("Number of records updated in database:", num)
        }
    }
    return
}
 
// GetUserByEmailOrMobileAndPassword
func GetUserByEmailOrMobileAndPassword(email string, password string) (maps []orm.Params, err error) {
    //orm.Debug = true
    o := orm.NewOrm()
    //var maps []orm.Params
    num, err := o.Raw("select * FROM user WHERE step>0 and (email=? or mobile=?) and password=? ", email, email,password).Values(&maps)
    //beego.Debug("row nums: ", num)
    if err == nil && num > 0 {
        return maps, nil
    }
    return nil, err
}
 
// GetUserByEmail
func GetUserByEmail(email string) (maps []orm.Params, err error) {
    o := orm.NewOrm()
    //var maps []orm.Params
    var num int64
    num, err = o.Raw("select * FROM user WHERE email=? ", email).Values(&maps)
    if err == nil && num > 0 {
        //fmt.Println("maps:", maps[0])
        return maps, nil
    }
    return nil, err
}
 
// UpdateUser step
func UpdateUserStepById(m *User) (err error) {
    //orm.Debug = true
    o := orm.NewOrm()
    v := User{Id: m.Id}
    // ascertain id exists in the database
    if err = o.Read(&v); err == nil {
        var num int64
        if num, err = o.Update(m,"Step", "UpdateTime"); err == nil {
            fmt.Println("Number of records updated in database:", num)
        }
    }
    return
}

　　Control部分（maps []orm.Params 返回的map是个interface对象，需要对里面的值做强制转换才能使用）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

type RegisterReq struct {
    Email string
    Password string
}
 
// @Title Register
// @Description Register User
// @Param   body        body    controllers.RegisterReq true        "body for User register"
// @Success 201 {int} models.User
// @Failure 403 body is empty
// @router /register [post]
func (c *UserController) Register() {
    var v RegisterReq
    if err := json.Unmarshal(c.Ctx.Input.RequestBody, &v); err == nil {
        md5pwd := c.GetMd5String(v.Password + beego.AppConfig.String("MD5_SALT"))
        var u models.User
        u.Email = v.Email
        u.Password = md5pwd
        u.Step = 0
        u.Status = 0
        u.Level = 0
        u.Role = 0
        u.Nickname = strings.Split(v.Email, "@")[0]
        u.CreateTime = time.Now()
        u.UpdateTime = time.Now()
        if _, err := models.AddUser(&u); err == nil {
            //c.Ctx.Output.SetStatus(201)
            c.Data["json"] = u
            utils.SendmailForVerify(v.Email)
        } else {
            //"Error 1062: Duplicate entry 'xxx' for key 'email'"
            c.Data["json"] = err.Error()
        }
    } else {
        c.Data["json"] = err.Error()
    }
    c.ServeJSON()
}
 
type LoginReq struct {
    LoginId string  `description:"Email or Phone"`
    Password string
}
 
// @Title Login
// @Description Login
// @Param   body        body    controllers.LoginReq    true        "body for User login"
// @Success 201 {int} models.User
// @Failure 403 body is empty
// @router /login [post]
func (c *UserController) Login() {
    var v LoginReq
    if err := json.Unmarshal(c.Ctx.Input.RequestBody, &v); err == nil {
        md5pwd := c.GetMd5String(v.Password + beego.AppConfig.String("MD5_SALT"))
        user, _ := models.GetUserByEmailOrMobileAndPassword(v.LoginId,md5pwd)
        if user != nil {
            // get token uid nickname
            id, _  := strconv.Atoi(user[0]["id"].(string))
            nickname := user[0]["nickname"].(string)
            tokenString := utils.GetToken(id, v.LoginId, nickname)
            c.Data["json"] = map[string]interface{}{"success": 0, "msg": "登录成功","token":tokenString,"email":v.LoginId,"nickname":nickname,"id":id}
        } else {
            c.Data["json"] = map[string]interface{}{"success": -1, "msg": "账号密码不对或邮箱未验证激活"}
        }
    } else {
        c.Data["json"] = err.Error()
    }
    c.ServeJSON()
}
 
type ChangePasswordReq struct {
    OldPassword string
    NewPassword string
}
 
// @Title Change Password
// @Description Change Password
// @Security mySecurityApiKey
// @Param   body        body    controllers.ChangePasswordReq   true        "body for Change Password"
// @Success 201 {int} models.User
// @Failure 403 body is empty
// @router /change_password [put]
func (c *UserController) ChangePassword() {
    email := c.GetUserMailByToken()
    var v ChangePasswordReq
    if err := json.Unmarshal(c.Ctx.Input.RequestBody, &v); err == nil {
        md5pwd := c.GetMd5String(v.OldPassword + beego.AppConfig.String("MD5_SALT"))
        user, _ := models.GetUserByEmailOrMobileAndPassword(email, md5pwd)
        if user != nil {
            u, _ := models.GetUserByFilter("email", email)
            u.Password = c.GetMd5String(v.NewPassword + beego.AppConfig.String("MD5_SALT"))
            models.UpdateUserById(u)
            c.Data["json"] = Response{0, "success.", nil}
        } else {
            c.Data["json"] = map[string]interface{}{"success": -1, "msg": "账号密码不对"}
        }
    } else {
        c.Data["json"] = err.Error()
    }
    c.ServeJSON()
}
 
// Put ...
// @Title Put
// @Description update the User
// @Param   id      path    string  true        "The id you want to update"
// @Param   body        body    models.User true        "body for User content"
// @Success 200 {object} models.User
// @Failure 403 :id is not int
// @router /:id [put]
func (c *UserController) Put() {
    idStr := c.Ctx.Input.Param(":id")
    id, _ := strconv.Atoi(idStr)
    v := models.User{Id: id}
    if err := json.Unmarshal(c.Ctx.Input.RequestBody, &v); err == nil {
        if err := models.UpdateUserById(&v); err == nil {
            c.Data["json"] = "OK"
        } else {
            c.Data["json"] = err.Error()
        }
    } else {
        c.Data["json"] = err.Error()
    }
    c.ServeJSON()
}
 
// @Title Get user profile
// @Description get user profile
// @Security mySecurityApiKey
// @Success 200 {object} models.User
// @router /profile [get]
func (c *UserController) Profile() {
    uid := c.GetUserIdByToken()
    v, err := models.GetUserById(uid)
    if err != nil {
        c.Data["json"] = err.Error()
    } else {
        c.Data["json"] = v
    }
    c.ServeJSON()
}
 
// Get Funds ...
// @Get My Funds
// @Security mySecurityApiKey
// @Description get my Funds
// @Success 200 {object} []models.Fund
// @Failure 403
// @router /funds [get]
func (c *UserController) Funds() {
    uid := int(c.GetUserIdByToken())
    fund, _ := models.GetAllAccountByUserId(uid)
    c.Data["json"] = fund
    c.ServeJSON()
}