Using gorm to operate mysql database in gin

1. create a database link object
2. declare structure and bind the databse table
3. configure route
4. implement operations such as adding data,modify data ,etc,

#preparatory work:

install gin:

go get -u github.com/gin-gonic/gin

install the database driver

go get -u gorm.io/gorm

go get -u gorm.io/driver/mysql

 

#1.create ./models/mysql

package models

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// gorm initializes the database
// Global use, defined as a commonvar DB *gorm.DB
var DB *gorm.DB
var err error
//databaseusername: Database user name
//passwrod:  Database login passwrod
//databasename: database username
//the databse server addressadn port are in parenthese.address:port
func init() {
	//dsn := "usrname:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local
	dsn := "databaseusername:password@tcp(gz-cynosdbmysql-grp-ikgx.sql.tencentcdb.com:24830)/databasename?charset=utf8mb4&parseTime=True&loc=Local"
	//Global use, defined as a common
	DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		//Connection failed
		fmt.Println(err)
	} else {
		//success
		fmt.Println(DB)
	}

}

#2.declare structure and bind the databse table

#create ./models/user.go

the database model should pay attention to the following points:

1. The name of the structure must be capitlized.
2. The first field name in the structure must be capitalized
3. Email corresponds to the email field in the receipt database,AddTime corresponds to the add_time field in the database.
4. By default, the table name is the plural form of the structure,the structure is user,and the table name is users.

 

package models

//the default table is user
// difine the structure corresponding to the table
type User struct {
	Id       int
	Username string
	Age      int
	Hobby    string
	AddTime  int
}

//we can use the custom method TableName in the structure to change the default table name of the structure

//Using the custome method TableName,cahnge the default table name of the structure
//Chage the table of default operation of User structure body to user.

// Represents the table name of the configuration database
func (user User) TableName() string {
	return "user"
}

3.configure route

#create ./routers/adminRouters.go

package routers

import (
	"ginorm/controllers/admin"
	//"gindemo2/middleware"
	"github.com/gin-gonic/gin"
)

func AdminRouterInit(r *gin.Engine) {
	adminRouters := r.Group("/admin")
	{
		adminRouters.GET("/ctable", admin.UserController{}.Index)
		adminRouters.GET("/edit", admin.UserController{}.Edit)
		adminRouters.GET("/del", admin.UserController{}.Delete)

	}
}

4.implement operations such as adding data,modify data ,etc,

#create ./controllers/admin/userController.go

package admin

import (
	"fmt"
	"ginorm/models"
	"github.com/gin-gonic/gin"
)

type UserController struct{}

func (con UserController) Index(c *gin.Context) {
	c.String(200, "routing pockets")
	//query database
	//instantiated structure
	//userList := []models.User{}
	//models.DB.Find(&userList)
	c.JSON(http.StatusOK,userList)
	//c.JSON(http.StatusOK, gin.H{
	//	"result": userList,
	//})

	//query data older than 20
	//userList := []models.User{}
	//models.DB.Where("age?20").Find(&userList)
	//c.JSON(200, gin.H{
	//	"result": userList,
	//})

	//insert data into a database

	//user := User{Username: "john", Age: 20, Hobby: "reading"}

	//user.ID        //returns the inserted primary key
	//result.Error    //return error
	//result.RowsAffected    //returns the number of entries inserted into the record

	//insert data
	user := models.User{
		Username: "john",
		Age:      22,
		AddTime:  1000000000,
		Hobby:    "reading",
	}
	models.DB.Create(&user)
	fmt.Println(user)
	c.String(200, "Data inserted successfully")

}

//Modify data

func (con UserController) Edit(c *gin.Context) {
	//save all fields
	//query data with id equal to 2
	//user := models.User{Id: 2}
	//models.DB.Find(&user)
	//user.Username = "hellollo"
	//user.AddTime = 2000000000
	//user.Hobby = "swiming"
	update data
	//models.DB.Save(&user)
	//-----------------------
	//user := models.User{}
	//models.DB.Model(&user).Where("id=?", 3).Update("username", "hellool")
	//-----------------------------------------------
	user := models.User{}
	models.DB.Where("id=?", 6).Find(&user)
	user.Username = "polly"
	models.DB.Save(&user)

	fmt.Println(user)
	c.String(200, "modify user information")

}

func (con UserController) Delete(c *gin.Context) {
	//user:=models.User{Id:6}
	//models.DB.Delete(&user)
	//c.String(200,"delete user")
	//-----------------------
	user := models.User{}
	models.DB.Where("username=?", "john").Delete(&user)

	c.String(200, "delete user")
}

5.main.go

package main

import (
	//"ginorm/middleware"
	"ginorm/routers"
	"github.com/gin-gonic/gin"
)

func main() {
	//1.create a route with default engine
	r := gin.Default()

	//r.Use(middleware.InitMiddleware)
	//register routing packets in main
	routers.AdminRouterInit(r)

	r.Run(":8089")
}

http://localhost:8089/admin/
#Listening and serving HTTP on :8089
#{0 john 22 reading 1000000000}
#[GIN] 2022/11/05 - 08:45:34 | 200 |     419.075µs |       127.0.0.1 | GET      #"/admin/"