GoLang连接mysql数据库
跟着文档走
GORM 指南 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.
1.使用命令拉取
go get -u gorm.io/gorm go get -u gorm.io/driver/sqlite |
2.开始使用
package mainimport ("fmt""github.com/gin-gonic/gin""gorm.io/driver/mysql""gorm.io/gorm""gorm.io/gorm/schema""net/http""strconv""time"
)func main() {// 创建服务ginServer := gin.Default()// 连接mysql// 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情dsn := "root:zsp123456@tcp(127.0.0.1:3306)/zsp?charset=utf8mb4&parseTime=True&loc=Local"db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{// 表明复数问题NamingStrategy: schema.NamingStrategy{SingularTable: true},})// 设置sql的一些属性sqlDB, err := db.DB()sqlDB.SetMaxIdleConns(10)sqlDB.SetMaxOpenConns(100)sqlDB.SetConnMaxLifetime(10 * time.Second) // 10s//结构体，就是创建表用的，包含了name，state，phonetype List struct {// 主键id等信息gorm.ModelName string `gorm:"type:varchar(20);not null" json:"name" binding:"required"`State int `gorm:"type:int(10);not null" json:"state" binding:"required"`Phone string `gorm:"type:varchar(50);not null" json:"phone" binding:"required"`}// 数据库迁移db.AutoMigrate(&List{})// 以上就是准备工作，下面开始crud// CRUD// 增加ginServer.POST("/add", func(context *gin.Context) {var data Listerr := context.ShouldBindJSON(&data)// 判断绑定是否有错误if err != nil {context.JSON(http.StatusBadRequest, gin.H{"msg": "数据有误"})} else {// 数据库操作 增加一条数据db.Create(&data) // 创建一条数据context.JSON(http.StatusOK, gin.H{"msg": "添加成功","data": data,"code": http.StatusOK,})}})// 删除ginServer.DELETE("/delete/:id", func(context *gin.Context) {var data []List// 接收idid := context.Param("id")// 判断id是否存在db.Where("id = ?", id).Find(&data)// id存在则删除，不存在报错if len(data) == 0 {context.JSON(http.StatusOK, gin.H{"msg": "删除失败","code": http.StatusBadRequest,})} else {// 操作数据库删除db.Where("id = ?", id).Delete(&data)context.JSON(http.StatusOK, gin.H{"msg": "删除成功","code": http.StatusOK,})}})//修改ginServer.PUT("/edit/:id", func(context *gin.Context) {var data List// 接收idid := context.Param("id")// 判断id是否存在db.Select("id").Where("id = ?", id).Find(&data)// 判断id是否存在if data.ID == 0 {context.JSON(http.StatusBadRequest, gin.H{"msg": "用户id没有找到",})} else {err := context.ShouldBindJSON(&data)if err != nil {context.JSON(http.StatusBadRequest, gin.H{"msg": "修改失败",})} else {//db修改数据库db.Where("id = ?", id).Updates(&data)context.JSON(http.StatusOK, gin.H{"msg": "修改成功",})}}})// 查询(条件查询，全部查询/分页查询)//条件查询ginServer.GET("/list/:name", func(context *gin.Context) {//获取路径参数name := context.Param("name")var data []List// 查询数据库是否存在db.Where("name = ?", name).Find(&data)// 判断是否查询到数据if len(data) == 0 {context.JSON(http.StatusBadRequest, gin.H{"msg": "没有查询到数据",})} else {context.JSON(http.StatusOK, gin.H{"msg": "查询成功","data": data,})}})// 分页查询ginServer.GET("/list", func(context *gin.Context) {var pageList []List// 1.查询全部数据、查询分页数据pageSize, _ := strconv.Atoi(context.Query("pageSize"))pageNum, _ := strconv.Atoi(context.Query("pageNum"))// 判断是否需要分页if pageSize == 0 {pageSize = -1}if pageNum == 0 {pageNum = -1}offsetVal := (pageNum - 1) * pageSizeif pageNum == -1 && pageSize == -1 {offsetVal = -1}// 返回一个总数var total int64// 查询数据库// limit 分页 -1:查询全部数据db.Model(pageList).Count(&total).Limit(pageSize).Offset(offsetVal).Find(&pageList)if len(pageList) == 0 {context.JSON(http.StatusBadRequest, gin.H{"msg": "暂无数据",})} else {context.JSON(http.StatusOK, gin.H{"msg": "查询成功","data": gin.H{"list": pageList,"total": total,"pageNum": pageNum,"pageSize": pageSize,},"code": http.StatusOK,})}})//启动服务err = ginServer.Run(":8082")if err != nil {return}
}
构造体创建出来的表
