开始

使用Go来完成一个简单的学生管理 RESTful API

Go结构体

type student struct{
    gorm.Model
    Name string
    StuId uint
    Phone string
    Age uint
}

Mysql表

• gorm自动生成

CURD

• POST student/
• GET student/
• GET student/{id}
• PUT student/{id}
• DELETE student/{id}

API调试

• Postman

Mysql操作

github.com/jinzhu/gorm

• 安装 go get -u github.com/jinzhu/gorm

Mysql可视化操作

• phpmyadmin
• Navicat

Gorm 简单操作

1. 连接

• 导入驱动

import _ "github.com/go-sql-driver/mysql"

• Mysql

db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")

1. 自动迁移

自动迁移模式将保持更新到最新。

警告：自动迁移仅仅会创建表，缺少列和索引，并且不会改变现有列的类型或删除未使用的列以保护数据。

db.AutoMigrate(&student{})

1. CURD

• 创建

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
db.Create(&user)

添加`gorm:"default:'galeone'"`设置默认值

• 查询

// 获取第一条记录，按主键排序
db.First(&user)
//// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取最后一条记录，按主键排序
db.Last(&user)
//// SELECT * FROM users ORDER BY id DESC LIMIT 1;

// 获取所有记录
db.Find(&users)
//// SELECT * FROM users;

// 使用主键获取记录
db.First(&user, 10)
//// SELECT * FROM users WHERE id = 10;


// 获取第一个匹配记录
db.Where("name = ?", "jinzhu").First(&user)
//// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// 获取所有匹配记录
db.Where("name = ?", "jinzhu").Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu';

db.Where("name <> ?", "jinzhu").Find(&users)

// IN
db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)

db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

• 更新

//Save将包括执行更新SQL时的所有字段，即使它没有更改
db.First(&user)
user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)
//// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;


// 更新单个属性（如果更改）
db.Model(&user).Update("name", "hello")
//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;


// 使用组合条件更新单个属性
db.Model(&user).Where("active = ?", true).Update("name", "hello")
//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;


// 使用`map`更新多个属性，只会更新这些更改的字段
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
//// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;


// 使用`struct`更新多个属性，只会更新这些更改的和非空白字段
db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;


// 警告:当使用struct更新时，FORM将仅更新具有非空值的字段
// 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值
db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

• 删除
  警告 删除记录时，需要确保其主要字段具有值，GORM将使用主键删除记录，如果主要字段为空，GORM将删除模型的所有记录

// 删除存在的记录
db.Delete(&email)
//// DELETE from emails where id=10;

// 为Delete语句添加额外的SQL选项
db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email)
//// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);


删除所有匹配记录
db.Where("email LIKE ?", "%jinzhu%").Delete(Email{})
//// DELETE from emails where email LIKE "%jinhu%";

db.Delete(Email{}, "email LIKE ?", "%jinzhu%")
//// DELETE from emails where email LIKE "%jinhu%";


//软删除
//如果模型有DeletedAt字段，它将自动获得软删除功能！
//那么在调用Delete时不会从数据库中永久删除，而是只将字段DeletedAt的值设置为当前时间。

db.Delete(&user)
//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 批量删除
db.Where("age = ?", 20).Delete(&User{})
//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// 软删除的记录将在查询时被忽略
db.Where("age = 20").Find(&user)
//// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

// 使用Unscoped查找软删除的记录
db.Unscoped().Where("age = 20").Find(&users)
//// SELECT * FROM users WHERE age = 20;

// 使用Unscoped永久删除记录
db.Unscoped().Delete(&order)
//// DELETE FROM orders WHERE id=10;

详细用法参阅gorm

完整代码

package main

import (
    "errors"
    "github.com/gin-gonic/gin"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    "log"
    "net/http"
    "os"
    "time"
)

type(
    student struct{
        CreatedAt time.Time
        UpdatedAt time.Time
        DeletedAt *time.Time

        Name string        `json:"name" form:"name" binding:"-"`
        StuId uint        `gorm:"primary_key" json:"stuId" form:"stuId" binding:"required"`
        Phone string    `json:"phone" form:"phone" binding:"-"`
        Age uint        `json:"age" form:"age" binding:"-"`
    }
)

var db *gorm.DB

func init(){
    log.Println("init...")
    var err error
    db, err = gorm.Open("mysql","root:admin@(10.0.0.1:3306)/demo?charset=utf8&parseTime=True&loc=Local")
    if err != nil{
        log.Panicln("failed to connect to DATABASE,",err.Error())
        os.Exit(1)
    }

    db.AutoMigrate(&student{})

    log.Println("init finished")
}

func main(){

    router := gin.Default()
    v1 := router.Group("/api/v1/student")
    {
        v1.GET("/",fetchAllHandler)
        v1.POST("/",createHandler)
        v1.GET("/:id",fetchSingleHandler)
        v1.PUT("/:id",updateHandler)
        v1.DELETE("/:id",deleteHandler)
    }

    router.Run(":8080")
}

func deleteHandler(context *gin.Context) {
    StuId := context.Param("id")
    var aStu student
    db.First(&aStu,StuId)
    if aStu.StuId == 0 {
        context.JSON(http.StatusNotFound,makeFailJson(errors.New("Not found")))
        return
    }
    db.Delete(&aStu)
    context.JSON(http.StatusOK,makeSuccessJson(&aStu))
}

func updateHandler(context *gin.Context) {
    stuId := context.Param("id")
    var aStu student
    err := context.ShouldBind(&aStu)
    if err != nil {
        context.JSON(http.StatusInternalServerError,makeFailJson(err))
        return
    }
    var tmpStu student
    db.First(&tmpStu,stuId)
    if tmpStu.StuId == 0 {
        context.JSON(http.StatusNotFound,makeFailJson(errors.New("Not found")))
        return
    }
    if aStu.Name != "" {
        tmpStu.Name = aStu.Name
    }
    if aStu.Age != 0 {
        tmpStu.Age = aStu.Age
    }
    if aStu.Phone != "" {
        tmpStu.Phone = aStu.Phone
    }
    if tmpStu.StuId != aStu.StuId {
        context.JSON(http.StatusBadRequest,makeFailJson(errors.New("Studetn ID is not changeable")))
        return
    }

    db.Save(&tmpStu)
    context.JSON(http.StatusOK,makeSuccessJson(&aStu))
}

func fetchSingleHandler(context *gin.Context) {
    stuId := context.Param("id")
    var aStu student
    db.Find(&aStu,stuId)
    if aStu.StuId == 0{
        context.JSON(http.StatusNotFound,makeFailJson(errors.New("Not found")))
        return
    }
    context.JSON(http.StatusOK,makeSuccessJson(&aStu))
}

func fetchAllHandler(context *gin.Context) {
    var stus []student
    db.Find(&stus)
    if len(stus) <= 0 {
        context.JSON(http.StatusNotFound,makeFailJson(errors.New("Not found")))
        return
    }
    context.JSON(http.StatusOK,makeSuccessJson(&stus))
}

func createHandler(c *gin.Context) {

    var aStu student
    err := c.ShouldBind(&aStu)
    log.Println("Log:",aStu)
    if err != nil {
        c.JSON(http.StatusBadRequest, makeFailJson(err))
        return
    }
    var tmpStu student
    db.Find(&tmpStu,aStu.StuId)
    if tmpStu.StuId != 0 {
        c.JSON(http.StatusBadRequest,makeFailJson(errors.New("student ID already exists")))
        return
    }
    db.Create(&aStu)
    c.JSON(http.StatusOK, makeSuccessJson(&aStu))
}

func makeSuccessJson(data interface{})gin.H{
    return gin.H{
        "status":0,
        "msg":"success",
        "data":data,
    }
}
func makeFailJson(err error)gin.H{
    return gin.H{
        "status":1,
        "msg":"failed",
        "error":err.Error(),
    }
}

结语

Gorm简化了mysql的复杂指令

对于初学者十分友好，不需要记住mysql语法也可以操作数据库，写出不错的程序

不过写出功能强大的程序还是需要学习mysql的。