Skip to content

Instantly share code, notes, and snippets.

@rsj217

rsj217/golang-gin-mysql-curd.go

Last active August 26, 2024 11:08
Show Gist options
  • Save rsj217/26492af115a083876570f003c64df118 to your computer and use it in GitHub Desktop.
Save rsj217/26492af115a083876570f003c64df118 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
golang-gin-mysql-curd.go
package main
import (
"database/sql"
"strconv"
"log"
"net/http"
"fmt"
"bytes"
"gopkg.in/gin-gonic/gin.v1"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
type Person struct {
Id int `json:"id"`
FirstName string `json:"first_name" form:"first_name"`
LastName string `json:"last_name" form:"last_name"`
}
func (p Person) get() (person Person, err error) {
row := db.QueryRow("SELECT id, first_name, last_name FROM person WHERE id=?", p.Id)
err = row.Scan(&person.Id, &person.FirstName, &person.LastName)
if err != nil {
return
}
return
}
func (p Person) getAll() (persons []Person, err error) {
rows, err := db.Query("SELECT id, first_name, last_name FROM person")
if err != nil {
return
}
for rows.Next() {
var person Person
rows.Scan(&person.Id, &person.FirstName, &person.LastName)
persons = append(persons, person)
}
defer rows.Close()
return
}
func (p Person) add() (Id int, err error) {
stmt, err := db.Prepare("INSERT INTO person(first_name, last_name) VALUES (?, ?)")
if err != nil {
return
}
rs, err := stmt.Exec(p.FirstName, p.LastName)
if err != nil {
return
}
id, err := rs.LastInsertId()
if err != nil {
log.Fatalln(err)
}
Id = int(id)
defer stmt.Close()
return
}
func (p Person) update() (rows int, err error) {
stmt, err := db.Prepare("UPDATE person SET first_name=?, last_name=? WHERE id=?")
if err != nil {
log.Fatalln(err)
}
rs, err := stmt.Exec(p.FirstName, p.LastName, p.Id)
if err != nil {
log.Fatalln(err)
}
row, err := rs.RowsAffected()
if err != nil {
log.Fatalln(err)
}
rows = int(row)
defer stmt.Close()
return
}
func (p Person) del() (rows int, err error) {
stmt, err := db.Prepare("DELETE FROM person WHERE id=?")
if err != nil {
log.Fatalln(err)
}
rs, err := stmt.Exec(p.Id)
if err != nil {
log.Fatalln(err)
}
row, err := rs.RowsAffected()
if err != nil {
log.Fatalln(err)
}
defer stmt.Close()
rows = int(row)
return
}
func main() {
var err error
db, err = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true")
if err != nil {
log.Fatal(err.Error())
}
defer db.Close()
err = db.Ping()
if err != nil {
log.Fatal(err.Error())
}
router := gin.Default()
router.GET("/persons", func(c *gin.Context) {
p := Person{}
persons, err := p.getAll()
if err != nil {
log.Fatalln(err)
}
c.JSON(http.StatusOK, gin.H{
"result": persons,
"count": len(persons),
})
})
router.GET("/person/:id", func(c *gin.Context) {
var result gin.H
id := c.Param("id")
Id, err := strconv.Atoi(id)
if err != nil {
log.Fatalln(err)
}
p := Person{
Id: Id,
}
person, err := p.get()
if err != nil {
result = gin.H{
"result": nil,
"count": 0,
}
} else {
result = gin.H{
"result": person,
"count": 1,
}
}
c.JSON(http.StatusOK, result)
})
// curl http://127.0.0.1:8000/person -X POST -d '{"first_name": "rsj", "last_name": "你好"}' -H "Content-Type: application/json"
router.POST("/person", func(c *gin.Context) {
var p Person
err := c.Bind(&p)
if err != nil {
log.Fatalln(err)
}
Id, err := p.add()
if err != nil {
log.Fatalln(err)
}
fmt.Println(Id)
name := p.FirstName + " " + p.LastName
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf(" %s successfully created", name),
})
})
// curl http://127.0.0.1:8000/person/1 -X PUT -d "first_name=admin&last_name=reg"
router.PUT("/person/:id", func(c *gin.Context) {
var (
p Person
buffer bytes.Buffer
)
id := c.Param("id")
Id, err := strconv.Atoi(id)
if err != nil {
log.Fatalln(err)
}
err = c.Bind(&p)
if err != nil {
log.Fatalln(err)
}
p.Id = Id
rows, err := p.update()
if err != nil {
log.Fatalln(err)
}
fmt.Println(rows)
buffer.WriteString(p.FirstName)
buffer.WriteString(" ")
buffer.WriteString(p.LastName)
name := buffer.String()
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf("Successfully update to %s", name),
})
})
router.DELETE("/person/:id", func(c *gin.Context) {
id := c.Param("id")
Id, err := strconv.ParseInt(id, 10, 10)
if err != nil {
log.Fatalln(err)
}
p := Person{Id:int(Id)}
rows, err := p.del()
if err != nil {
log.Fatalln(err)
}
fmt.Println("delete rows ", rows)
c.JSON(http.StatusOK, gin.H{
"message": fmt.Sprintf("Successfully deleted user: %s", id),
})
})
router.Run(":8000")
}
Raw
golang-gin-mysql-restful-1.go
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"gopkg.in/gin-gonic/gin.v1"
"log"
"net/http"
"strconv"
)
type Person struct {
Id int `json:"id" form:"id"`
FirstName string `json:"first_name" form:"first_name"`
LastName string `json:"last_name" form:"last_name"`
}
func (p *Person) AddPerson() (id int64, err error) {
rs, err := db.Exec("INSERT INTO person(first_name, last_name) VALUES (?, ?)", p.FirstName, p.LastName)
if err != nil {
return
}
id, err = rs.LastInsertId()
return
}
func (p *Person) GetPersons() (persons []Person, err error) {
persons = make([]Person, 0)
rows, err := db.Query("SELECT id, first_name, last_name FROM person")
defer rows.Close()
if err != nil {
return
}
for rows.Next() {
var person Person
rows.Scan(&person.Id, &person.FirstName, &person.LastName)
persons = append(persons, person)
}
if err = rows.Err(); err != nil {
return
}
return
}
func (p *Person) GetPerson() (person Person, err error) {
err = db.QueryRow("SELECT id, first_name, last_name FROM person WHERE id=?", p.Id).Scan(
&person.Id, &person.FirstName, &person.LastName,
)
return
}
func (p *Person) ModPerson() (ra int64, err error) {
stmt, err := db.Prepare("UPDATE person SET first_name=?, last_name=? WHERE id=?")
defer stmt.Close()
if err != nil {
return
}
rs, err := stmt.Exec(p.FirstName, p.LastName, p.Id)
if err != nil {
return
}
ra, err = rs.RowsAffected()
return
}
func (p *Person) DelPerson() (ra int64, err error) {
rs, err := db.Exec("DELETE FROM person WHERE id=?", p.Id)
if err != nil {
log.Fatalln(err)
}
ra, err = rs.RowsAffected()
return
}
func IndexApi(c *gin.Context) {
c.String(http.StatusOK, "It works")
}
func AddPersonApi(c *gin.Context) {
firstName := c.Request.FormValue("first_name")
lastName := c.Request.FormValue("last_name")
p := Person{FirstName: firstName, LastName: lastName}
ra, err := p.AddPerson()
if err != nil {
log.Fatalln(err)
}
msg := fmt.Sprintf("insert successful %d", ra)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
}
func GetPersonsApi(c *gin.Context) {
var p Person
persons, err := p.GetPersons()
if err != nil {
log.Fatalln(err)
}
c.JSON(http.StatusOK, gin.H{
"persons": persons,
})
}
func GetPersonApi(c *gin.Context) {
cid := c.Param("id")
id, err := strconv.Atoi(cid)
if err != nil {
log.Fatalln(err)
}
p := Person{Id: id}
person, err := p.GetPerson()
if err != nil {
log.Fatalln(err)
}
c.JSON(http.StatusOK, gin.H{
"person": person,
})
}
func ModPersonApi(c *gin.Context) {
cid := c.Param("id")
id, err := strconv.Atoi(cid)
if err != nil {
log.Fatalln(err)
}
p := Person{Id: id}
err = c.Bind(&p)
if err != nil {
log.Fatalln(err)
}
ra, err := p.ModPerson()
if err != nil {
log.Fatalln(err)
}
msg := fmt.Sprintf("Update person %d successful %d", p.Id, ra)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
}
func DelPersonApi(c *gin.Context) {
cid := c.Param("id")
id, err := strconv.Atoi(cid)
if err != nil {
log.Fatalln(err)
}
p := Person{Id: id}
ra, err := p.DelPerson()
if err != nil {
log.Fatalln(err)
}
msg := fmt.Sprintf("Delete person %d successful %d", id, ra)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
}
var db *sql.DB
func main() {
var err error
db, err = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true")
if err != nil {
log.Fatalln(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
router := gin.Default()
router.GET("/", IndexApi)
router.POST("/person", AddPersonApi)
router.GET("/persons", GetPersonsApi)
router.GET("/person/:id", GetPersonApi)
router.PUT("/person/:id", ModPersonApi)
router.DELETE("/person/:id", DelPersonApi)
router.Run(":8000")
}
Raw
golang-gin-mysql-restful.gp
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"gopkg.in/gin-gonic/gin.v1"
"log"
"net/http"
"strconv"
)
type Person struct {
Id int `json:"id" form:"id"`
FirstName string `json:"first_name" form:"first_name"`
LastName string `json:"last_name" form:"last_name"`
}
func main() {
db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true")
if err != nil {
log.Fatalln(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatalln(err)
}
router := gin.Default()
router.GET("/", func(c *gin.Context) {
c.String(http.StatusOK, "It works")
})
router.POST("/person", func(c *gin.Context) {
firstName := c.Request.FormValue("first_name")
lastName := c.Request.FormValue("last_name")
rs, err := db.Exec("INSERT INTO person(first_name, last_name) VALUES (?, ?)", firstName, lastName)
if err != nil {
log.Fatalln(err)
}
id, err := rs.LastInsertId()
if err != nil {
log.Fatalln(err)
}
fmt.Println("insert person Id {}", id)
msg := fmt.Sprintf("insert successful %d", id)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
})
router.GET("/persons", func(c *gin.Context) {
rows, err := db.Query("SELECT id, first_name, last_name FROM person")
defer rows.Close()
if err != nil {
log.Fatalln(err)
}
persons := make([]Person, 0)
//var persons []Person
for rows.Next() {
var person Person
rows.Scan(&person.Id, &person.FirstName, &person.LastName)
persons = append(persons, person)
}
if err = rows.Err(); err != nil {
log.Fatalln(err)
}
c.JSON(http.StatusOK, gin.H{
"persons": persons,
})
})
router.GET("/person/:id", func(c *gin.Context) {
id := c.Param("id")
var person Person
err := db.QueryRow("SELECT sid, first_name, last_name FROM person WHERE id=?", id).Scan(
&person.Id, &person.FirstName, &person.LastName,
)
if err != nil {
log.Println(err)
c.JSON(http.StatusOK, gin.H{
"person": nil,
})
return
}
c.JSON(http.StatusOK, gin.H{
"person": person,
})
})
router.PUT("/person/:id", func(c *gin.Context) {
cid := c.Param("id")
id, err := strconv.Atoi(cid)
if err != nil {
log.Fatalln(err)
}
person := Person{Id: id}
err = c.Bind(&person)
if err != nil {
log.Fatalln(err)
}
stmt, err := db.Prepare("UPDATE person SET first_name=?, last_name=? WHERE id=?")
defer stmt.Close()
if err != nil {
log.Fatalln(err)
}
rs, err := stmt.Exec(person.FirstName, person.LastName, person.Id)
if err != nil {
log.Fatalln(err)
}
ra, err := rs.RowsAffected()
if err != nil {
log.Fatalln(err)
}
msg := fmt.Sprintf("Update person %d successful %d", person.Id, ra)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
})
router.DELETE("/person/:id", func(c *gin.Context) {
cid := c.Param("id")
id, err := strconv.Atoi(cid)
if err != nil {
log.Fatalln(err)
}
rs, err := db.Exec("DELETE FROM person WHERE id=?", id)
if err != nil {
log.Fatalln(err)
}
ra, err := rs.RowsAffected()
if err != nil {
log.Fatalln(err)
}
msg := fmt.Sprintf("Delete person %d successful %d", id, ra)
c.JSON(http.StatusOK, gin.H{
"msg": msg,
})
})
router.Run(":8000")
}
Raw
golang-gin.go
package main
import (
"gopkg.in/gin-gonic/gin.v1"
"github.com/gin-gonic/gin/binding"
"net/http"
"fmt"
"os"
"log"
"io"
"time"
)
type User struct {
Username string `form:"username" json:"username" binding:"required"`
Passwd string `form:"passwd" json:"passwd" binding:"required"`
Age int `form:"age" json:"age"`
}
func MiddleWare() gin.HandlerFunc {
return func(c *gin.Context) {
fmt.Println("before middleware")
c.Set("request", "clinet_request")
c.Next()
fmt.Println("after middleware")
}
}
func AuthMiddleWare() gin.HandlerFunc {
return func(c *gin.Context) {
if cookie, err := c.Request.Cookie("session_id"); err == nil {
value := cookie.Value
fmt.Println(value)
if value == "123" {
c.Next()
return
}
}
c.JSON(http.StatusUnauthorized, gin.H{
"error": "Unauthorized",
})
c.Abort()
return
}
}
func main() {
router := gin.Default()
router.GET("/user/:name", func(c *gin.Context) {
name := c.Param("name")
c.String(http.StatusOK, "Hello %s", name)
})
router.GET("/user/:name/*action", func(c *gin.Context) {
name := c.Param("name")
action := c.Param("action")
message := name + " is " + action
c.String(http.StatusOK, message)
})
router.GET("/welcome", func(c *gin.Context) {
firstname := c.DefaultQuery("firstname", "Guest")
lastname := c.Query("lastname")
c.String(http.StatusOK, "Hello %s %s", firstname, lastname)
})
router.POST("/form_post", func(c *gin.Context) {
message := c.PostForm("message")
nick := c.DefaultPostForm("nick", "anonymous")
c.JSON(http.StatusOK, gin.H{
"status": gin.H{
"status_code": http.StatusOK,
"status": "ok",
},
"message": message,
"nick": nick,
})
})
router.PUT("/post", func(c *gin.Context) {
id := c.Query("id")
page := c.DefaultQuery("page", "0")
name := c.PostForm("name")
message := c.PostForm("message")
fmt.Printf("id: %s; page: %s; name: %s; message: %s \n", id, page, name, message)
c.JSON(http.StatusOK, gin.H{
"status_code": http.StatusOK,
})
})
router.LoadHTMLGlob("templates/*")
router.GET("/upload", func(c *gin.Context) {
c.HTML(http.StatusOK, "upload.html", gin.H{"title": "index"})
})
// curl -X POST http://127.0.0.1:8000/upload -F "upload=@/Users/ghost/Desktop/pic.jpg" -H "Content-Type: multipart/form-data"
router.POST("/upload", func(c *gin.Context) {
name := c.PostForm("name")
fmt.Println(name)
file, header, err := c.Request.FormFile("upload")
if err != nil {
c.String(http.StatusBadRequest, "Bad request")
return
//log.Fatal(err)
}
filename := header.Filename
fmt.Println(file, err, filename)
out, err := os.Create(filename)
if err != nil {
log.Fatal(err)
}
defer out.Close()
_, err = io.Copy(out, file)
if err != nil {
log.Fatal(err)
}
c.String(http.StatusCreated, "upload successful")
})
// curl -X POST http://127.0.0.1:8000/multi/upload -F "upload=@/Users/ghost/Desktop/pic.jpg" -F "upload=@/Users/ghost/Desktop/journey.png" -H "Content-Type: multipart/form-data"
router.POST("/multi/upload", func(c *gin.Context) {
err := c.Request.ParseMultipartForm(200000)
if err != nil {
log.Fatal(err)
}
formdata := c.Request.MultipartForm // ok, no problem so far, read the Form data
//get the *fileheaders
files := formdata.File["upload"] // grab the filenames
for i, _ := range files { // loop through the files one by one
file, err := files[i].Open()
defer file.Close()
if err != nil {
log.Fatal(err)
}
out, err := os.Create(files[i].Filename)
defer out.Close()
if err != nil {
log.Fatal(err)
}
_, err = io.Copy(out, file) // file not files[i] !
if err != nil {
log.Fatal(err)
}
c.String(http.StatusCreated, "upload successful")
}
})
v1 := router.Group("/v1")
v1.Use(MiddleWare())
v1.GET("/login", func(c *gin.Context) {
fmt.Println(c.MustGet("request").(string))
c.String(http.StatusOK, "v1 login")
})
v2 := router.Group("/v2")
v2.GET("/login", func(c *gin.Context) {
c.String(http.StatusOK, "v2 login")
})
router.POST("/login", func(c *gin.Context) {
var user User
var err error
contentType := c.Request.Header.Get("Content-Type")
switch contentType {
case "application/json":
err = c.BindJSON(&user)
case "application/x-www-form-urlencoded":
err = c.BindWith(&user, binding.Form)
}
//err = c.Bind(&user)
if err != nil {
fmt.Println(err)
log.Fatal(err)
}
c.JSON(http.StatusOK, gin.H{
"username": user.Username,
"passwd": user.Passwd,
"age": user.Age,
})
})
router.GET("/render", func(c *gin.Context) {
contentType := c.DefaultQuery("content_type", "json")
if contentType == "json" {
c.JSON(http.StatusOK, gin.H{
"user": "rsj217",
"passwd": "123",
})
} else if contentType == "xml" {
c.XML(http.StatusOK, gin.H{
"user": "rsj217",
"passwd": "123",
})
} else {
c.YAML(http.StatusOK, gin.H{
"user": "rsj217",
"passwd": "123",
})
}
})
router.GET("/redict/google", func(c *gin.Context) {
c.Redirect(http.StatusMovedPermanently, "https://google.com")
})
router.GET("/before", MiddleWare(), func(c *gin.Context) {
request := c.MustGet("request").(string)
fmt.Println("before handler")
c.JSON(http.StatusOK, gin.H{
"middile_request": request,
})
})
router.Use(MiddleWare())
{
router.GET("/middleware", func(c *gin.Context) {
request := c.MustGet("request").(string)
req, _ := c.Get("request")
fmt.Println(req)
c.JSON(http.StatusOK, gin.H{
"middile_request": request,
"request": req,
})
})
}
router.GET("/after", func(c *gin.Context) {
request := c.MustGet("request").(string)
c.JSON(http.StatusOK, gin.H{
"middile_request": request,
})
})
router.GET("/auth/signin", func(c *gin.Context) {
cookie := &http.Cookie{
Name: "session_id",
Value: "123",
Path: "/",
HttpOnly: true,
}
http.SetCookie(c.Writer, cookie)
c.String(http.StatusOK, "Login successful")
})
router.GET("/home", AuthMiddleWare(), func(c *gin.Context) {
c.JSON(http.StatusOK, gin.H{"data": "home"})
})
router.GET("/sync", func(c *gin.Context) {
time.Sleep(5 * time.Second)
log.Println("Done! in path" + c.Request.URL.Path)
})
router.GET("/async", func(c *gin.Context) {
cCp := c.Copy()
go func() {
time.Sleep(5 * time.Second)
log.Println("Done! in path" + cCp.Request.URL.Path)
}()
})
router.Run(":8000")
}
@kilvn
Copy link

kilvn commented Sep 3, 2018

really kind of you

@bfish401
Copy link

bfish401 commented Nov 6, 2018

really kind of you.

@Maxiaoyu0
Copy link

有问题啊。为什么我总是取不到postman body里面的json值啊。总是空的（而且能存入数据库，明明数据库我定义的都是not null）。上面的几种方法都试了，不知道什么原因。

@john100ster
Copy link

really kind of you.

@ravidborse
Copy link

Thanks for Opensource

@super-rain
Copy link

really kind of you.

@Longwater1234
Copy link

really kind of you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment