gozero结合sqlx实现增删改查

1.创建一个zerogorm项目1.准备好api文件shop.apisyntaxv1typeCommonResponse{Codeintjson:codeMessagestringjson:messageDatainterface{}json:data,omitempty// omitempty表示可以data返回也可以不返回Successbooljson:success}import./api/focus.apiapi/focus.apisyntaxv1type(FocusIdPathRequest{Idintpath:id//get传值}// 轮播图Focus{Idintjson:idTitlestringjson:titlePicstringjson:picLinkstringjson:linkPositionintjson:position}// 增加轮播图的requestAddFocusRequest{Titlestringform:title// post传值Picstringform:picLinkstringform:linkPositionintform:position}// 修改轮播图// omitempty 表示json序列号时可以为空// optional 表示接收数据时可以为空UpdateFocusRequest{Idintform:id// put传值Titlestringform:title,optionalPicstringform:pic,optionalLinkstringform:link,optionalPositionintform:position,optional}// 删除轮播图DeleteFocusRequest{Idintform:id})server(group:focus// 代表当前service代码块下的路由生成代码时都会被放到focus目录下prefix:/api/focus// 前缀)service shop-api{// http://localhost:8888/api/focushandler GetFocus get/returns(CommonResponse)// http://localhost:8888/api/focus/oneFocus/1handler GetFocusById get/oneFocus/:id(FocusIdPathRequest)returns(CommonResponse)// curl --location http://localhost:8888/api/focus/addFocus \// --header Content-Type: application/x-www-form-urlencoded \// --data-urlencode titletitlex \// --data-urlencode picimagex \// --data-urlencode linklinkxdoc增加轮播图handler AddFocus post/addFocus(AddFocusRequest)returns(CommonResponse)//curl --location --request PUT http://localhost:8888/api/focus/updateFocus \// --header Content-Type: application/x-www-form-urlencoded \// --data-urlencode titletitlex \// --data-urlencode picimagex \// --data-urlencode linklinkx \// --data-urlencode id222222doc修改轮播图handler UpdateFocus put/updateFocus(UpdateFocusRequest)returns(CommonResponse)// curl --location --request DELETE http://localhost:8888/api/focus/deleteFoucs?id123doc删除轮播图handler DeleteFocusdelete/deleteFoucs(DeleteFocusRequest)returns(CommonResponse)}2.生成代码goctl api go--apishop.api--dir.3.根据数据库表生成代码创建文件夹model\mysqlmodel\mysql\focus.sqlCREATETABLEfocus(idbigintNOTNULLAUTO_INCREMENTCOMMENT主键,titlevarchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNULLDEFAULTNULLCOMMENT名称,picvarchar(500)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNULLDEFAULTNULLCOMMENT图片地址,linkvarchar(500)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNULLDEFAULTNULLCOMMENT链接地址,positionbigintNULLDEFAULT0COMMENT排序位置,create_attimestamp(0)NULLDEFAULTCURRENT_TIMESTAMP(0)COMMENT创建时间,update_attimestamp(0)NULLDEFAULTCURRENT_TIMESTAMP(0)ONUPDATECURRENT_TIMESTAMP(0)COMMENT更新时间,PRIMARYKEY(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_0900_ai_ci;生成代码cd.\model\mysql goctl model mysql ddl--srcfocus.sql--dir.go mod tidy4.配置mysql连接修改etc\shop-api.yaml在etc\shop-api.yaml中增加mysql连接的配置Name:shop-apiHost:0.0.0.0Port:8888# mysql连接MySQL:DataSource:root:passwordtcp(127.0.0.1:3306)/gozero?charsetutf8mb4parseTimeTruelocLocal修改internal\config\config.goconfig.go接收自定义的Mysql.DataSourcepackageconfigimportgithub.com/zeromicro/go-zero/resttypeConfigstruct{rest.RestConf// 接收自定义的Mysql.DataSourceMysqlstruct{DataSourcestring}}修改internal\svc\servicecontext.gointernal\svc\servicecontext.go中开启数据库连接并且将数据库连接放入上下文packagesvcimport(zerosqlx/internal/configzerosqlx/model/mysqlgithub.com/zeromicro/go-zero/core/stores/sqlx)typeServiceContextstruct{Config config.Config FocusModel mysql.FocusModel// 轮播图的模型}funcNewServiceContext(c config.Config)*ServiceContext{// 初始化数据库和数据库建立连接conn:sqlx.NewMysql(c.Mysql.DataSource)returnServiceContext{Config:c,FocusModel:mysql.NewFocusModel(conn),}}5.实现增删改查修改internal\logic\focus\addfocuslogic.gofunc(l*AddFocusLogic)AddFocus(req*types.AddFocusRequest)(resp*types.CommonResponse,errerror){data:mysql.Focus{Title:sql.NullString{String:req.Title,Valid:true},Pic:sql.NullString{String:req.Pic,Valid:true},Link:sql.NullString{String:req.Link,Valid:true},Position:int64(req.Position),}_,errl.svcCtx.FocusModel.Insert(l.ctx,data)iferr!nil{returntypes.CommonResponse{Code:500,Message:添加失败,Data:err.Error(),Success:false,},nil}else{returntypes.CommonResponse{Code:200,Message:添加成功,Success:true,},nil}}修改internal\logic\focus\deletefocuslogic.gofunc(l*DeleteFocusLogic)DeleteFocus(req*types.DeleteFocusRequest)(resp*types.CommonResponse,errerror){id:req.Id errl.svcCtx.FocusModel.Delete(l.ctx,int64(id))iferr!nil{returntypes.CommonResponse{Code:500,Message:删除失败,Data:err.Error(),Success:false,},nil}else{returntypes.CommonResponse{Code:200,Message:删除成功,Success:true,},nil}}修改internal\logic\focus\getfocusbyidlogic.gofunc(l*GetFocusByIdLogic)GetFocusById(req*types.FocusIdPathRequest)(resp*types.CommonResponse,errerror){// 获取get传值id:req.Id// 查询数据库focus,err:l.svcCtx.FocusModel.FindOne(l.ctx,int64(id))iferr!nil{returntypes.CommonResponse{Code:500,Message:err.Error(),Success:false,},nil}else{returntypes.CommonResponse{Code:200,Message:查询成功,Success:true,Data:types.Focus{Id:int(focus.Id),Title:focus.Title.String,Pic:focus.Pic.String,Link:focus.Link.String,Position:int(focus.Position),},},nil}}修改internal\logic\focus\getfocuslogic.gofunc(l*GetFocusLogic)GetFocus()(resp*types.CommonResponse,errerror){// 获取轮播图数据focus,err:l.svcCtx.FocusModel.FindAll(l.ctx)iferr!nil{returntypes.CommonResponse{Code:500,Message:查询失败,Success:false,},err}else{varfocusResp[]*types.Focusfor_,f:rangefocus{focusRespappend(focusResp,types.Focus{Id:int(f.Id),Title:string(f.Title.String),Pic:string(f.Pic.String),Link:string(f.Link.String),Position:int(f.Position),})}returntypes.CommonResponse{Code:200,Message:查询成功,Data:focusResp,Success:true,},err}}修改internal\logic\focus\updatefocuslogic.gofunc(l*UpdateFocusLogic)UpdateFocus(req*types.UpdateFocusRequest)(resp*types.CommonResponse,errerror){// TODO title, pic, link, position可能为空因此需要判断data:mysql.Focus{Id:int64(req.Id),Title:sql.NullString{String:req.Title,Valid:true},Pic:sql.NullString{String:req.Pic,Valid:true},Link:sql.NullString{String:req.Link,Valid:true},Position:int64(req.Position),}errl.svcCtx.FocusModel.Update(l.ctx,data)iferr!nil{returntypes.CommonResponse{Code:500,Message:修改失败,Data:err.Error(),Success:false,},nil}else{returntypes.CommonResponse{Code:200,Message:修改成功,Success:true,},nil}}2.启动项目fresh运行启动freshgo run运行启动go run shop.go-fetc/shop-api.yaml编译后运行启动go build..\zerosqlx.exe调试启动.vscode\launch.json{version:0.2.0,configurations:[{name:Launch shop.go,type:go,request:launch,mode:auto,program:${workspaceFolder}/shop.go,args:[-f,etc/shop-api.yaml]}]}3.测试项目测试查询curl--locationhttp://localhost:8888/api/focuscurl--locationhttp://localhost:8888/api/focus/oneFocus/1测试新增curl--locationhttp://localhost:8888/api/focus/addFocus\--headerContent-Type: application/x-www-form-urlencoded\--data-urlencodetitletitlex\--data-urlencodepicimagex\--data-urlencodelinklinkx测试修改curl--location--requestPUThttp://localhost:8888/api/focus/updateFocus\--headerContent-Type: application/x-www-form-urlencoded\--data-urlencodetitletitlex\--data-urlencodepicimagex\--data-urlencodelinklinkx\--data-urlencodeid222222测试删除curl--location--requestDELETEhttp://localhost:8888/api/focus/deleteFoucs?id123