开始
使用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 简单操作
- 连接
- 导入驱动
import _ "github.com/go-sql-driver/mysql"
- Mysql
db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")
- 自动迁移
自动迁移模式将保持更新到最新。
警告:自动迁移仅仅会创建表,缺少列和索引,并且不会改变现有列的类型或删除未使用的列以保护数据。
db.AutoMigrate(&student{})
- 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的。