Go调用MySql数据库的简单示例之三业务代码

本示例分为三部分组成：项目创建、基础框架、业务代码。
业务代码目录和文件包括model、sevice、handler等；并在最后启动服务、验证服务。
数据库表结构


model / uers.go
user表的ORM模型

package model

import (
    "encoding/json"
    "errors"
    "gotest/common/errno"
    "log"
)

// model
type User struct {
    ID string `json:"id"`
    UserName string `json:"name"`
    Age string `json:"age"`
    Password string `json:"password"`
}

// 通过用户名查询用户数据
func (user *User)SelectUserByName(name string)error {
    stmt,err := ConstDb.Prepare("SELECT id,name,age,password FROM user WHERE name=?")
    if err != nil {
       return err
    }
    defer stmt.Close()
    rows, err := stmt.Query(name)
    // 关闭rows释放持有的数据库链接
    defer rows.Close()
    if err != nil {
        return err
    }
    // 数据处理
    for rows.Next() {
        rows.Scan( &user.ID, &user.UserName, &user.Age, &user.Password)
    }
    if err := rows.Err(); err != nil {
        return err
    }
    return nil
}
 
// Validate the fields.
func (user *User) Validate() error {
    if user.UserName =="" || user.Password ==""{
        return errors.New(errno.ErrValidation.Message)
    }
    return nil
}

func (user *User) Create() (int64,error)  {
    id,err := Insert("INSERT INTO user(name,age,password) values (?,?,?)", user.UserName, user.Age, user.Password)
    if err != nil {
        return 0,err
    }
 
    return id,nil
}

model.go
通用数据库访问模型

package model
 
import (
    "fmt"
    "log"
)

// Insert 插入操作
func Insert(sql string,args... interface{})(int64,error) {
    // SQL语句预处理
    stmt, err := ConstDb.Prepare(sql)
    // defer语句在方法返回“时”触发
    defer stmt.Close()
    if err != nil{
        return 0,err
    }
    result, err := stmt.Exec(args...)
    if err != nil{
        return 0,err
    }
    id, err := result.LastInsertId()
    if err != nil{
        return 0,err
    }
    fmt.Printf("插入成功，ID为%v\n",id)
    return id,nil
}
 
//  Update or Delete 更新或删除操作
func UpdateOrDelete(sql string,args... interface{})  {
    stmt, err := ConstDb.Prepare(sql)
    defer stmt.Close()
    CheckErr(err, "SQL语句预处理出现错误")
    result, err := stmt.Exec(args...)
    CheckErr(err, "参数出现错误")
    num, err := result.RowsAffected()
    CheckErr(err,"执行失败")
    fmt.Printf("执行成功，影响行数为%d\n",num)
}
 
// CheckErr 用来校验error对象是否为空
func CheckErr(err error,msg string)  {
    if nil != err {
        log.Panicln(msg,err)
    }
}

router.go
完整内容参见上篇

// 新增用户数据
router.POST("/addUser", service.AddUser)
// 通过用户名查询用户数据
router.POST("/selectUser", service.SelectUserByName)

middleware.go
router中间件

package middleware
 
import (
    "github.com/gin-gonic/gin"
    "net/http"
    "time"
)
 
// NoCache is a middleware function that appends headers
// to prevent the client from caching the HTTP response.
func NoCache(c *gin.Context) {
    c.Header("Cache-Control", "no-cache, no-store, max-age=0, must-revalidate, value")
    c.Header("Expires", "Thu, 01 Jan 1970 00:00:00 GMT")
    c.Header("Last-Modified", time.Now().UTC().Format(http.TimeFormat))
    c.Next()
}
 
// Options is a middleware function that appends headers
// for options requests and aborts then exits the middleware
// chain and ends the request.
func Options(c *gin.Context) {
    if c.Request.Method != "OPTIONS" {
        c.Next()
    } else {
        c.Header("Access-Control-Allow-Origin", "*")
        c.Header("Access-Control-Allow-Methods", "GET,POST,PUT,PATCH,DELETE,OPTIONS")
        c.Header("Access-Control-Allow-Headers", "authorization, origin, content-type, accept")
        c.Header("Allow", "HEAD,GET,POST,PUT,PATCH,DELETE,OPTIONS")
        c.Header("Content-Type", "application/json")
        c.AbortWithStatus(200)
    }
}
 
// Secure is a middleware function that appends security
// and resource access headers.
func Secure(c *gin.Context) {
    c.Header("Access-Control-Allow-Origin", "*")
    c.Header("X-Frame-Options", "DENY")
    c.Header("X-Content-Type-Options", "nosniff")
    c.Header("X-XSS-Protection", "1; mode=block")
    if c.Request.TLS != nil {
        c.Header("Strict-Transport-Security", "max-age=31536000")
    }
}

service.go
服务接口

package service
 
import (
    "fmt"
    "github.com/gin-gonic/gin"
    "gotest/common/errno"
    "gotest/handler"
    "gotest/model"
)

// 新增用户数据
func AddUser(c *gin.Context)  {
    var r model.User
    if err := c.Bind(&r); err != nil {
        handler.SendResponse(c, errno.ErrBind, nil)
        return
    }
    u := model.User{
        UserName: r.UserName,
        Age: r.Age,
        Password: r.Password,
    }
    // Validate the data
    if err := u.Validate(); err != nil {
        handler.SendResponse(c, errno.ErrValidation, nil)
        return
    }
    // Insert the user to the database
    if id,err := u.Create(); err != nil {
        handler.SendResponse(c, errno.ErrDatabase, nil)
        return
    }else{
        // 赋值
        u.ID = id
    }
    // Show the user information
    handler.SendResponse(c, nil, u)
}
 
// SelectUserByName 通过用户名查询用户数据
func SelectUserByName(c *gin.Context)  {
    name := c.Query("user_name")
    if name == ""{
        handler.SendResponse(c, errno.ErrValidation, nil)
        return
    }
    var  user model.User
    if err := user.SelectUserByName(name);nil != err {
        fmt.Println(err)
        handler.SendResponse(c, errno.ErrUserNotFound, nil)
        return
    }
    // Validate the data.
    if err := user.Validate(); err != nil {
        handler.SendResponse(c, errno.ErrUserNotFound, nil)
        return
    }

    handler.SendResponse(c, nil, user)
}
 

handlers.go

package handler
 
import (
    "github.com/gin-gonic/gin"
    "gotest/common/errno"
    "net/http"
)
 
type Response struct {
    Code    int         `json:"code"`
    Message string      `json:"message"`
    Data    interface{} `json:"data"`
}
 
func SendResponse(c *gin.Context, err error, data interface{}) {
    code, message := errno.DecodeErr(err)
 
    // 网络链路层正常返回 always return http.StatusOK
    c.JSON(http.StatusOK, Response{
        Code:    code,
        Message: message,
        Data:    data,
    })
}

common / error / code.go

package errno
 
var (
    // Common errors
    OK                  = &Errno{Code: 0, Message: "OK"}
    InternalServerError = &Errno{Code: 5001, Message: "Internal server error"}
    ErrBind             = &Errno{Code: 5002, Message: "Error occurred while binding the request body to the struct."}
 
    ErrValidation = &Errno{Code: 1001, Message: "Validation failed."}
    ErrDatabase   = &Errno{Code: 1002, Message: "Database error."}
 
    // user errors
    ErrUserNotFound      = &Errno{Code: 9101, Message: "The user was not found."}
    ErrPasswordIncorrect = &Errno{Code: 9102, Message: "The password was incorrect."}
)

common / error / error.go

package errno
 
import "fmt"
 
type Errno struct {
    Code    int
    Message string
}
 
func (err Errno) Error() string {
    return err.Message
}
 
// Err represents an error
type Err struct {
    Code    int
    Message string
    Err     error
}
 
func New(errno *Errno, err error) *Err {
    return &Err{Code: errno.Code, Message: errno.Message, Err: err}
}
 
func (err *Err) Add(message string) error {
    err.Message += " " + message
    return err
}
 
func (err *Err) Addf(format string, args ...interface{}) error {
    err.Message += " " + fmt.Sprintf(format, args...)
    return err
}
 
func (err *Err) Error() string {
    return fmt.Sprintf("Err - code: %d, message: %s, error: %s", err.Code, err.Message, err.Err)
}
 
func IsErrUserNotFound(err error) bool {
    code, _ := DecodeErr(err)
    return code == ErrUserNotFound.Code
}
 
func DecodeErr(err error) (int, string) {
    if err == nil {
        return OK.Code, OK.Message
    }
 
    switch typed := err.(type) {
    case *Err:
        return typed.Code, typed.Message
    case *Errno:
        return typed.Code, typed.Message
    default:
    }
 
    return InternalServerError.Code, err.Error()
}

启动服务器

验证测试
使用postman发起请求，查询用户