创建sql文件
--sqlfileyesql.sql--name:newest_tags@topic--getnewesttaginformationSELECTt.idid,t.user_iduser_id,t.tagtag,t.quote_numquote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM@tagtJOIN@useruONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDERBYt.idDESCLIMIT?OFFSET?;--name:hot_tags@topic--getgethosttaginformationSELECTt.idid,t.user_iduser_id,t.tagtag,t.quote_numquote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM@tagtJOIN@useruONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDERBYt.quote_numDESCLIMIT?OFFSET?;--name:tags_by_keyword_a@topic--gettagsbykeywordSELECTid,user_id,tag,quote_numFROM@tagWHEREis_del=0ORDERBYquote_numDESCLIMIT6;--name:tags_by_keyword_b@topicSELECTid,user_id,tag,quote_numFROM@tagWHEREis_del=0ANDtagLIKE?ORDERBYquote_numDESCLIMIT6;--name:insert_tag@topicINSERTINTO@tag(user_id,tag,created_on,modified_on,quote_num)VALUES(?,?,?,?,1);--name:tags_by_id_a@topic--clause:inSELECTidFROM@tagWHEREidIN(?)ANDis_del=0ANDquote_num>0;--name:tags_by_id_b@topic--clause:inSELECTid,user_id,tag,quote_numFROM@tagWHEREidIN(?);--name:decr_tags_by_id@topic--clause:inUPDATE@tagSETquote_num=quote_num-1,modified_on=?WHEREidIN(?);--name:tags_for_incr@topic--clause:inSELECTid,user_id,tag,quote_numFROM@tagWHEREtagIN(?);--name:incr_tags_by_id@topic--clause:inUPDATE@tagSETquote_num=quote_num+1,is_del=0,modified_on=?WHEREidIN(?);使用Scan模式（方式一）
//file:topics.gopackagetopicsimport("context""github.com/alimy/yesql""github.com/jmoiron/sqlx")//go:embedyesql.sqlvaryesqlBytes[]bytetypeTopicstruct{yesql.Namespace`yesql:"topic"`DecrTagsByIdstring`yesql:"decr_tags_by_id"`IncrTagsByIdstring`yesql:"incr_tags_by_id"`TagsByIdAstring`yesql:"tags_by_id_a"`TagsByIdBstring`yesql:"tags_by_id_b"`TagsForIncrstring`yesql:"tags_for_incr"`HotTags*sqlx.Stmt`yesql:"hot_tags"`InsertTag*sqlx.Stmt`yesql:"insert_tag"`NewestTags*sqlx.Stmt`yesql:"newest_tags"`TagsByKeywordA*sqlx.Stmt`yesql:"tags_by_keyword_a"`TagsByKeywordB*sqlx.Stmt`yesql:"tags_by_keyword_b"`}funcNewTopic(db*sqlx.DB)(*Topic,error){//use*sqlx.DBaspreparecontextyesql.UseSqlx(db)//getsqlqueryquery:=yesql.MustParseBytes(yesqlBytes)//scanobjectfromsqlqueryobj:=&Topic{}iferr:=yesql.Scan(obj,query);err!=nil{returnnil,err}returnobj,nil}使用代码生成模式（方式二）
编写代码生成逻辑/file:gen.gopackagemainimport("log""github.com/alimy/yesql")//go:generategorun$GOFILEfuncmain(){log.Println("[Yesql]generatecodestart")iferr:=yesql.Generate("yesql.sql","auto","yesql");err!=nil{log.Fatalf("generatecodeoccurserror:%s",err)}log.Println("[Yesql]generatecodefinish")}自动生成Go代码%gogenerategen.go2023/03/3119:34:44[Yesql]generatecodestart2023/03/3119:34:44[Yesql]generatecodefinish生成代码如下（生成文件路径：auto/yesql.go）//CodegeneratedbyYesql.DONOTEDIT.//versions://-Yesqlv1.1.2packageyesqlimport("context""github.com/alimy/yesql""github.com/jmoiron/sqlx")const(_TagsByKeywordB_Topic=`SELECTid,user_id,tag,quote_numFROM@tagWHEREis_del=0ANDtagLIKE?ORDERBYquote_numDESCLIMIT6`_InsertTag_Topic=`INSERTINTO@tag(user_id,tag,created_on,modified_on,quote_num)VALUES(?,?,?,?,1)`_TagsByIdA_Topic=`SELECTidFROM@tagWHEREidIN(?)ANDis_del=0ANDquote_num>0`_TagsByIdB_Topic=`SELECTid,user_id,tag,quote_numFROM@tagWHEREidIN(?)`_TagsForIncr_Topic=`SELECTid,user_id,tag,quote_numFROM@tagWHEREtagIN(?)`_IncrTagsById_Topic=`UPDATE@tagSETquote_num=quote_num+1,is_del=0,modified_on=?WHEREidIN(?)`_NewestTags_Topic=`SELECTt.idid,t.user_iduser_id,t.tagtag,t.quote_numquote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM@tagtJOIN@useruONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDERBYt.idDESCLIMIT?OFFSET?`_TagsByKeywordA_Topic=`SELECTid,user_id,tag,quote_numFROM@tagWHEREis_del=0ORDERBYquote_numDESCLIMIT6`_DecrTagsById_Topic=`UPDATE@tagSETquote_num=quote_num-1,modified_on=?WHEREidIN(?)`_HotTags_Topic=`SELECTt.idid,t.user_iduser_id,t.tagtag,t.quote_numquote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM@tagtJOIN@useruONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDERBYt.quote_numDESCLIMIT?OFFSET?`)typeTopicstruct{yesql.Namespace`yesql:"topic"`DecrTagsByIdstring`yesql:"decr_tags_by_id"`IncrTagsByIdstring`yesql:"incr_tags_by_id"`TagsByIdAstring`yesql:"tags_by_id_a"`TagsByIdBstring`yesql:"tags_by_id_b"`TagsForIncrstring`yesql:"tags_for_incr"`HotTags*sqlx.Stmt`yesql:"hot_tags"`InsertTag*sqlx.Stmt`yesql:"insert_tag"`NewestTags*sqlx.Stmt`yesql:"newest_tags"`TagsByKeywordA*sqlx.Stmt`yesql:"tags_by_keyword_a"`TagsByKeywordB*sqlx.Stmt`yesql:"tags_by_keyword_b"`}funcBuildTopic(pyesql.PreparexBuilder,ctx...context.Context)(obj*Topic,errerror){varccontext.Contextiflen(ctx)>0&&ctx[0]!=nil{c=ctx[0]}else{c=context.Background()}obj=&Topic{DecrTagsById:p.QueryHook(_DecrTagsById_Topic),IncrTagsById:p.QueryHook(_IncrTagsById_Topic),TagsByIdA:p.QueryHook(_TagsByIdA_Topic),TagsByIdB:p.QueryHook(_TagsByIdB_Topic),TagsForIncr:p.QueryHook(_TagsForIncr_Topic),}ifobj.HotTags,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_HotTags_Topic)));err!=nil{return}ifobj.InsertTag,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_InsertTag_Topic)));err!=nil{return}ifobj.NewestTags,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_NewestTags_Topic)));err!=nil{return}ifobj.TagsByKeywordA,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_TagsByKeywordA_Topic)));err!=nil{return}ifobj.TagsByKeywordB,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_TagsByKeywordB_Topic)));err!=nil{return}return}使用 Yesql 的项目examples -项目自带examplespaopao-ce -一个清新文艺的微社区
评论