专栏首页学院君的专栏基于 Go 语言开发在线论坛(二):通过模型类与MySQL数据库交互

基于 Go 语言开发在线论坛(二):通过模型类与MySQL数据库交互

在这篇教程中,我们将在 MySQL 中创建一个 chitchat 数据库作为论坛项目的数据库,然后在 Go 项目中编写模型类与之进行交互。你可以本地安装 MySQL 数据库,也可以基于 Docker 容器运行(后续会介绍容器化启动方法)。

1、项目初始化

开始之前,我们先来初始化项目目录,我们将项目名设置为 chitchat,所以在 ~/Development/go/src/github.com/xueyuanjun 目录下创建这个项目目录,然后初始化目录结构如下:

重点看下红框内,各个子目录/文件的作用介绍如下:

  • main.go:应用入口文件
  • config.json:全局配置文件
  • handlers:用于存放处理器代码(可类比为 MVC 模式中的控制器目录)
  • logs:用于存放日志文件
  • models:用于存放与数据库交互的模型类
  • public:用于存放前端资源文件,比如图片、CSS、JavaScript 等
  • routes:用于存放路由文件和路由器实现代码
  • views:用于存放视图模板文件

接下来,我们在 chitchat 目录下运行如下命令初始化 go.mod,因为我们后续通过 Go Module 来管理依赖:

go mod init github.com/xueyuanjun/chitchat

2、创建数据表

开始正式编码之前,现在 chitchat 数据库中创建数据表,对应的 SQL 语句如下:

create table users (
  id         serial primary key,
  uuid       varchar(64) not null unique,
  name       varchar(255),
  email      varchar(255) not null unique,
  password   varchar(255) not null,
  created_at timestamp not null
);

create table sessions (
  id         serial primary key,
  uuid       varchar(64) not null unique,
  email      varchar(255),
  user_id    integer references users(id),
  created_at timestamp not null
);

create table threads (
  id         serial primary key,
  uuid       varchar(64) not null unique,
  topic      text,
  user_id    integer references users(id),
  created_at timestamp not null
);

create table posts (
  id         serial primary key,
  uuid       varchar(64) not null unique,
  body       text,
  user_id    integer references users(id),
  thread_id  integer references threads(id),
  created_at timestamp not null
);

在 MySQL 客户端连接到 chitchat 数据库,运行上述 SQL 语句创建所有数据表:

3、与数据库交互

1)数据库驱动

数据表创建完成后,接下来,如何在 Go 应用代码中与数据库交互呢?Go 语言开发组并没有为此提供官方的数据库驱动实现,只是提供了数据库交互接口,我们可以通过实现这些接口的第三方扩展包完成与 MySQL 数据库的交互,本项目选择的扩展包是 go-mysql-driver 。

我们可以在 Go 应用中编写模型类基于这个扩展包提供的方法与 MySQL 交互完成增删改查操作,开始之前,可以运行如下命令安装这个依赖:

go get github.com/go-sql-driver/mysql

2)数据库连接

然后在 chitchat/models 目录下创建 db.go,并编写数据库连接初始化方法以及生成 UUID、哈希加密方法:

package models

import (
    "crypto/rand"
    "crypto/sha1"
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

var Db *sql.DB

func init() {
    var err error
    Db, err = sql.Open("mysql", "root:root@/chitchat?charset=utf8&parseTime=true")
    if err != nil {
        log.Fatal(err)
    }
    return
}

// create a random UUID with from RFC 4122
// adapted from http://github.com/nu7hatch/gouuid
func createUUID() (uuid string) {
    u := new([16]byte)
    _, err := rand.Read(u[:])
    if err != nil {
        log.Fatalln("Cannot generate UUID", err)
    }

    // 0x40 is reserved variant from RFC 4122
    u[8] = (u[8] | 0x40) & 0x7F
    // Set the four most significant bits (bits 12 through 15) of the
    // time_hi_and_version field to the 4-bit version number.
    u[6] = (u[6] & 0xF) | (0x4 << 4)
    uuid = fmt.Sprintf("%x-%x-%x-%x-%x", u[0:4], u[4:6], u[6:8], u[8:10], u[10:])
    return
}

// hash plaintext with SHA-1
func Encrypt(plaintext string) (cryptext string) {
    cryptext = fmt.Sprintf("%x", sha1.Sum([]byte(plaintext)))
    return
}

其中,Db 变量代表数据库连接池,通过 init 方法在 Web 应用启动时自动初始化数据库连接,这样,我们就可以在应用中通过 Db 变量对数据库进行增删改查操作了,这也是该变量首字母大写的原因,方便在 models 包之外被引用,具体的操作实现我们放到独立的模型文件中处理。

注:这里通过 sql.Open 初始化数据库连接,我们写死了数据库连接配置,在实际生产环境,这块配置值应该从配置文件或系统环境变量获取。

3)用户相关模型类

有了代表数据库连接池的 Db 变量之后,就可以为每个数据表编写对应的模型类实现增删改查操作了,首先在 models 目录下创建 user.go 用于定义用户模型类 Userusers 表进行交互,以及与 sessions 表进行关联:

package models

import "time"

type User struct {
    Id        int
    Uuid      string
    Name      string
    Email     string
    Password  string
    CreatedAt time.Time
}

// Create a new session for an existing user
func (user *User) CreateSession() (session Session, err error) {
    statement := "insert into sessions (uuid, email, user_id, created_at) values (?, ?, ?, ?)"
    stmtin, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmtin.Close()

    uuid := createUUID()
    stmtin.Exec(uuid, user.Email, user.Id, time.Now())

    stmtout, err := Db.Prepare("select id, uuid, email, user_id, created_at from sessions where uuid = ?")
    if err != nil {
        return
    }
    defer stmtout.Close()
    // use QueryRow to return a row and scan the returned id into the Session struct
    err = stmtout.QueryRow(uuid).Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt)
    return
}

// Get the session for an existing user
func (user *User) Session() (session Session, err error) {
    session = Session{}
    err = Db.QueryRow("SELECT id, uuid, email, user_id, created_at FROM sessions WHERE user_id = ?", user.Id).
        Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt)
    return
}

// Create a new user, save user info into the database
func (user *User) Create() (err error) {
    // Postgres does not automatically return the last insert id, because it would be wrong to assume
    // you're always using a sequence.You need to use the RETURNING keyword in your insert to get this
    // information from postgres.
    statement := "insert into users (uuid, name, email, password, created_at) values (?, ?, ?, ?, ?)"
    stmtin, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmtin.Close()

    uuid := createUUID()
    stmtin.Exec(uuid, user.Name, user.Email, Encrypt(user.Password), time.Now())

    stmtout, err := Db.Prepare("select id, uuid, created_at from users where uuid = ?")
    if err != nil {
        return
    }
    defer stmtout.Close()
    // use QueryRow to return a row and scan the returned id into the User struct
    err = stmtout.QueryRow(uuid).Scan(&user.Id, &user.Uuid, &user.CreatedAt)
    return
}

// Delete user from database
func (user *User) Delete() (err error) {
    statement := "delete from users where id = ?"
    stmt, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmt.Close()

    _, err = stmt.Exec(user.Id)
    return
}

// Update user information in the database
func (user *User) Update() (err error) {
    statement := "update users set name = ?, email = ? where id = ?"
    stmt, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmt.Close()

    _, err = stmt.Exec(user.Name, user.Email, user.Id)
    return
}

// Delete all users from database
func UserDeleteAll() (err error) {
    statement := "delete from users"
    _, err = Db.Exec(statement)
    return
}

// Get all users in the database and returns it
func Users() (users []User, err error) {
    rows, err := Db.Query("SELECT id, uuid, name, email, password, created_at FROM users")
    if err != nil {
        return
    }
    for rows.Next() {
        user := User{}
        if err = rows.Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt); err != nil {
            return
        }
        users = append(users, user)
    }
    rows.Close()
    return
}

// Get a single user given the email
func UserByEmail(email string) (user User, err error) {
    user = User{}
    err = Db.QueryRow("SELECT id, uuid, name, email, password, created_at FROM users WHERE email = ?", email).
        Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt)
    return
}

// Get a single user given the UUID
func UserByUUID(uuid string) (user User, err error) {
    user = User{}
    err = Db.QueryRow("SELECT id, uuid, name, email, password, created_at FROM users WHERE uuid = ?", uuid).
        Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt)
    return
}

创建 session.go 用于定义会话模型类 Session

package models

import "time"

type Session struct {
    Id        int
    Uuid      string
    Email     string
    UserId    int
    CreatedAt time.Time
}

// Check if session is valid in the database
func (session *Session) Check() (valid bool, err error) {
    err = Db.QueryRow("SELECT id, uuid, email, user_id, created_at FROM sessions WHERE uuid = ?", session.Uuid).
        Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt)
    if err != nil {
        valid = false
        return
    }
    if session.Id != 0 {
        valid = true
    }
    return
}

// Delete session from database
func (session *Session) DeleteByUUID() (err error) {
    statement := "delete from sessions where uuid = ?"
    stmt, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmt.Close()

    _, err = stmt.Exec(session.Uuid)
    return
}

// Get the user from the session
func (session *Session) User() (user User, err error) {
    user = User{}
    err = Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", session.UserId).
        Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt)
    return
}

// Delete all sessions from database
func SessionDeleteAll() (err error) {
    statement := "delete from sessions"
    _, err = Db.Exec(statement)
    return
}

这里面定义了基于 Db 数据库连接实例实现用户模型和会话模型相关的增删改查操作,具体的语法可以参考 go-mysql-driver 的 官方文档,这里不详细展开,具体细节,我们留到后面专门介绍数据库模块时再详细说明。

4)主题相关模型类

编写好用户相关模型类后,接下来在同级目录下创建 thread.go,定义群组模型类 Threadthreads 表进行交互:

package models

import "time"

type Thread struct {
    Id        int
    Uuid      string
    Topic     string
    UserId    int
    CreatedAt time.Time
}

// format the CreatedAt date to display nicely on the screen
func (thread *Thread) CreatedAtDate() string {
    return thread.CreatedAt.Format("Jan 2, 2006 at 3:04pm")
}

// get the number of posts in a thread
func (thread *Thread) NumReplies() (count int) {
    rows, err := Db.Query("SELECT count(*) FROM posts where thread_id = ?", thread.Id)
    if err != nil {
        return
    }
    for rows.Next() {
        if err = rows.Scan(&count); err != nil {
            return
        }
    }
    rows.Close()
    return
}

// get posts to a thread
func (thread *Thread) Posts() (posts []Post, err error) {
    rows, err := Db.Query("SELECT id, uuid, body, user_id, thread_id, created_at FROM posts where thread_id = ?", thread.Id)
    if err != nil {
        return
    }
    for rows.Next() {
        post := Post{}
        if err = rows.Scan(&post.Id, &post.Uuid, &post.Body, &post.UserId, &post.ThreadId, &post.CreatedAt); err != nil {
            return
        }
        posts = append(posts, post)
    }
    rows.Close()
    return
}

// Get all threads in the database and returns it
func Threads() (threads []Thread, err error) {
    rows, err := Db.Query("SELECT id, uuid, topic, user_id, created_at FROM threads ORDER BY created_at DESC")
    if err != nil {
        return
    }
    for rows.Next() {
        conv := Thread{}
        if err = rows.Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt); err != nil {
            return
        }
        threads = append(threads, conv)
    }
    rows.Close()
    return
}

// Get a thread by the UUID
func ThreadByUUID(uuid string) (conv Thread, err error) {
    conv = Thread{}
    err = Db.QueryRow("SELECT id, uuid, topic, user_id, created_at FROM threads WHERE uuid = ?", uuid).
        Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt)
    return
}

// Get the user who started this thread
func (thread *Thread) User() (user User) {
    user = User{}
    Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", thread.UserId).
        Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt)
    return
}

以及 post.go 编写主题模型类与 posts 表进行交互:

package models

import "time"

type Post struct {
    Id        int
    Uuid      string
    Body      string
    UserId    int
    ThreadId  int
    CreatedAt time.Time
}

func (post *Post) CreatedAtDate() string {
    return post.CreatedAt.Format("Jan 2, 2006 at 3:04pm")
}

// Get the user who wrote the post
func (post *Post) User() (user User) {
    user = User{}
    Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", post.UserId).
        Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt)
    return
}

此外,我们到 user.go 中为 User 模型新增如下两个方法与 ThreadPost 模型进行关联,用于创建新的群组和主题:

// Create a new thread
func (user *User) CreateThread(topic string) (conv Thread, err error) {
    statement := "insert into threads (uuid, topic, user_id, created_at) values (?, ?, ?, ?)"
    stmtin, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmtin.Close()

    uuid := createUUID()
    stmtin.Exec(uuid, topic, user.Id, time.Now())

    stmtout, err := Db.Prepare("select id, uuid, topic, user_id, created_at from threads where uuid = ?")
    if err != nil {
        return
    }
    defer stmtout.Close()

    // use QueryRow to return a row and scan the returned id into the Session struct
    err = stmtout.QueryRow(uuid).Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt)
    return
}

// Create a new post to a thread
func (user *User) CreatePost(conv Thread, body string) (post Post, err error) {
    statement := "insert into posts (uuid, body, user_id, thread_id, created_at) values (?, ?, ?, ?, ?)"
    stmtin, err := Db.Prepare(statement)
    if err != nil {
        return
    }
    defer stmtin.Close()

    uuid := createUUID()
    stmtin.Exec(uuid, body, user.Id, conv.Id, time.Now())

    stmtout, err := Db.Prepare("select id, uuid, body, user_id, thread_id, created_at from posts where uuid = ?")
    if err != nil {
        return
    }
    defer stmtout.Close()

    // use QueryRow to return a row and scan the returned id into the Session struct
    err = stmtout.QueryRow(uuid).Scan(&post.Id, &post.Uuid, &post.Body, &post.UserId, &post.ThreadId, &post.CreatedAt)
    return
}

4、小结

在上述编写的模型类中,模型类与数据表是如何映射的呢?这个由 go-mysql-driver 底层实现,每次从数据库查询到结果之后,可以通过 Scan 方法将数据表字段值映射到对应的结构体模型类,而将模型类保存到数据库时,又可以基于字段映射关系将结构体属性值转化为对应的数据表字段值。对应的底层交互逻辑如下所示:

再次重申,本项目旨在帮助大家快速熟悉 Go Web 项目的完整开发流程和代码组织架构,具体的技术实现细节留到后面单独的数据库模块详细介绍,不过,如果你之前有过其他语言的 Web 开发经验,比如 PHP、Python、Java,基本上看一眼这些代码就能知道个大概了,因为他们的实现思路都是相通的,无非是语言的语法不同而已,所以这也是学院君反复强调新人应该花个三五年时间先精通一门语言,再学习其他语言的原因。

本文分享自微信公众号 - 学院君的后花园(geekacademy),作者:学院君

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-03-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 在 Laravel 项目中编写第一个 Vue 组件

    和 CSS 框架一样,Laravel 不强制你使用什么 JavaScript 客户端框架,但是开箱对 Vue.js 提供了良好的支持,如果你更熟悉 React ...

    学院君
  • 引入 SB Admin 2 作为后台管理系统主题

    完全前端基本功能之后,接下来,我们来构建这个 PHP 博客项目后台管理系统,主要包含登录认证,仪表盘页面,专辑、文章的创建、修改和删除,以及消息后台查看等功能。...

    学院君
  • Go 语言网络编程系列(四)—— HTTP 编程篇:http.Client 底层实现剖析

    除了上篇教程介绍的基本 HTTP 操作,Go 语言标准库也提供了比较底层的 HTTP 相关库,让开发者可以基于这些库灵活定制 HTTP 服务器并使用 HTTP ...

    学院君
  • css 禁止复制,屏蔽鼠标选中

    Savalone
  • CSS禁止网页中文本被选中

    现在可以直接使用CSS3禁止网页中文本被选中了,但只兼容Firefox/Chrome/Safari,IE10了,不过也是未来主流了,下面来看看。

    周俊辉
  • memset函数使用方法

    将s所指向的某一块内存中的每个字节的内容全部设置为ch指定的ASCII值, 块的大小由第三个参数指定,这个函数通常为新申请的内存做初始化工作, 其返回值为指向S...

    猿人谷
  • html禁止相关css

    小小咸鱼YwY
  • 如何使用Feign构造多参数的请求

    最近经常有人在Spring Cloud中国社区(http://springcloud.cn)QQ群(157525002)里问到该问题。索性整理一下。 本节我们来...

    用户1516716
  • (new)Flutter-国际化适配终结者

    首先在这里提前祝大家新春大吉,最近发现有粉丝私聊我,Flutter i18n插件在idea中的插件市场已经找不到了,然后我就到Flutter i18n 官网 看...

    rhyme_lph
  • Android自定义控件实现带文本与数字的圆形进度条

    本文实例为大家分享了Android实现圆形进度条的具体代码,供大家参考,具体内容如下

    砸漏

扫码关注云+社区

领取腾讯云代金券