安装

$ go get github.com/alimy/yesql

使用

创建sql文件

-- sql file yesql.sql-- name: newest_tags@topic-- get newest tag informationSELECTt.id id,t.user_id user_id,t.tag tag,t.quote_num quote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM @tag tJOIN @user uONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDER BYt.idDESCLIMIT ? OFFSET ?;-- name: hot_tags@topic-- get get host tag informationSELECTt.id id,t.user_id user_id,t.tag tag,t.quote_num quote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM @tag tJOIN @user uONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDER BYt.quote_numDESCLIMIT ? OFFSET ?;-- name: tags_by_keyword_a@topic-- get tags by keywordSELECT id, user_id, tag, quote_numFROM @tagWHERE is_del=0ORDER BY quote_numDESCLIMIT6;-- name: tags_by_keyword_b@topicSELECT id, user_id, tag, quote_numFROM @tagWHERE is_del=0AND tagLIKE ?ORDER BY quote_numDESCLIMIT6;-- name: insert_tag@topicINSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num)VALUES (?, ?, ?, ?,1);-- name: tags_by_id_a@topic-- prepare: raw-- clause: inSELECT idFROM @tagWHERE idIN (?)AND is_del=0AND quote_num>0;-- name: tags_by_id_b@topic-- prepare: raw-- clause: inSELECT id, user_id, tag, quote_numFROM @tagWHERE idIN (?);-- name: decr_tags_by_id@topic-- prepare: raw-- clause: inUPDATE @tagSET quote_num=quote_num-1, modified_on=?WHERE idIN (?);-- name: tags_for_incr@topic-- prepare: raw-- clause: inSELECT id, user_id, tag, quote_numFROM @tagWHERE tagIN (?);-- name: incr_tags_by_id@topic-- prepare: raw-- clause: inUPDATE @tagSET quote_num=quote_num+1, is_del=0, modified_on=?WHERE idIN (?);

使用Scan模式(方式一)

// file: topics.gopackage topicsimport ("context"_"embed""github.com/alimy/yesql""github.com/jmoiron/sqlx")//go:embed yesql.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.DB as prepare contextyesql.UseSqlx(db)// get sql queryquery:=yesql.MustParseBytes(yesqlBytes)// scan object from sql queryobj:=&Topic{}iferr:=yesql.Scan(obj,query);err!=nil {returnnil,err}returnobj,nil}

使用代码生成模式(方式二)

// file: gen.gopackage mainimport ("log""strings""github.com/alimy/yesql")//go:generate go run $GOFILEfuncmain() {log.Println("[Yesql] generate code start")yesql.SetDefaultQueryHook(func(query*yesql.Query) (*yesql.Query,error) {query.Query=strings.TrimRight(query.Query,";")returnquery,nil})iferr:=yesql.Generate("yesql.sql","auto","yesql");err!=nil {log.Fatalf("generate code occurs error: %s",err)}log.Println("[Yesql] generate code finish")}

% go generate gen.go2023/03/31 19:34:44 [Yesql] generate code start2023/03/31 19:34:44 [Yesql] generate code finish

// Code generated by Yesql. DO NOT EDIT.// versions:// - Yesql v1.1.2package yesqlimport ("context""github.com/alimy/yesql""github.com/jmoiron/sqlx")const (_TagsByKeywordB_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 AND tag LIKE ? ORDER BY quote_num DESC LIMIT 6`_InsertTag_Topic=`INSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num) VALUES (?, ?, ?, ?, 1)`_TagsByIdA_Topic=`SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0`_TagsByIdB_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?)`_TagsForIncr_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?)`_IncrTagsById_Topic=`UPDATE @tag SET quote_num=quote_num+1, is_del=0, modified_on=? WHERE id IN (?)`_NewestTags_Topic=`SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.id DESC LIMIT ? OFFSET ?`_TagsByKeywordA_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 ORDER BY quote_num DESC LIMIT 6`_DecrTagsById_Topic=`UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?)`_HotTags_Topic=`SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.quote_num DESC LIMIT ? 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(p yesql.PreparexBuilder,ctx...context.Context) (obj*Topic,errerror) {varc context.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 的项目