go-sqlx增删改查操作

-

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "log"
    "time"
)

var tableName = "blog_article"

type Article struct {
    ID            int64  `json:"id"`
    Title         string `json:"title"`
    Desc          string `json:"desc"`
    CoverImageUrl string `json:"cover_image_url" db:"cover_image_url"`
    Content       string `json:"content"`
    CreatedOn     int64  `json:"created_on"  db:"created_on"`
    CreatedBy     string `json:"created_by" db:"created_by"`
    ModifiedOn    int64  `json:"modified_on" db:"modified_on"`
    ModifiedBy    string `json:"modified_by" db:"modified_by"`
    DeletedOn     int64  `json:"deleted_on" db:"deleted_on"`
    IsDel         int64  `json:"is_del" db:"is_del"`
    State         int64  `json:"state"`
}

func main() {
    db, err := sqlx.Open("mysql", "concise:mnb123@tcp(127.0.0.1:3306)/card_service?charset=utf8&parseTime=True&loc=Local")

    if err != nil {
        log.Fatalf("sql.Open fail %v\n", err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatalf("db.Pint fail %v\n", err)
    }

    // 增
    max := 1
    var lastId int64
    for i := 0; i < max; i++ {
        newArticle := Article{
            Title:      fmt.Sprintf("萌萌哒-%d", i),
            Content:    fmt.Sprintf("这是萌萌哒的内容啦-%d", i),
            CreatedBy:  "AVALON",
            CreatedOn:  time.Now().Unix(),
            ModifiedBy: "Proaholic",
            ModifiedOn: time.Now().Unix(),
        }
        insertSQL := "INSERT INTO " + tableName + " (title,content,created_on,created_by,modified_by,modified_on) VALUES (?,?,?,?,?,?)"
        // fmt.Println(insertSQL)
        insRes := db.MustExec(insertSQL, newArticle.Title, newArticle.Content, newArticle.CreatedOn, newArticle.CreatedBy, newArticle.ModifiedBy, newArticle.ModifiedOn)
        var err error
        lastId, err = insRes.LastInsertId()
        if err != nil {
            log.Fatalf("insRes.LastInsertId fail %v\n", err)
        }
    }
    fmt.Printf("Last Insert ID = %d\n", lastId)

    // 改
    updateSQL := "UPDATE " + tableName + " SET title=?, content =? WHERE id= ? "
    modRes := db.MustExec(updateSQL, "修改的标题", "修改的文章内容", lastId)
    modAffected, err := modRes.RowsAffected()
    if err != nil {
        log.Fatalf("modRes.RowsAffected() fail %v\n", err)
    }
    fmt.Printf("modAffected= %d\n", modAffected)

    // 查
    selectSQL := "select * FROM blog_article WHERE is_del = ? ORDER BY id DESC LIMIT 10"
    rows, err := db.Queryx(selectSQL, 0)
    defer rows.Close()
    for rows.Next() {
        var article Article
        err := rows.StructScan(&article)

        if err != nil {
            log.Fatalf("rows.StructScan fail %v\n", err)
        }

        fmt.Printf("row = %+v\n", article)
    }

    // 单独查
    selectOne := "select * FROM blog_article WHERE id = ? ORDER BY id DESC LIMIT 1"
    oneRow := db.QueryRowx(selectOne, lastId)
    var ar Article
    oneRow.StructScan(&ar)
    fmt.Printf("select one article = %+v\n", ar)

    // 便捷的GET查询
    var getArticle Article
    db.Get(&getArticle, selectOne, lastId)
    fmt.Printf("[GET] article = %+v\n", getArticle)

    // 便捷的Select查询
    articleList := []Article{}
    db.Select(&articleList, "select * from "+tableName+" where is_del = ? order by id desc limit 5 ", 0)
    fmt.Printf("articleList is %+v\n", articleList)

    // 删除
    deleteSQL := "DELETE FROM " + tableName + " WHERE id = ?"
    delRes := db.MustExec(deleteSQL, lastId)
    delAffected, err := delRes.RowsAffected()
    if err != nil {
        log.Fatalf("delAffected.RowsAffected fail %v\n", err)
    }
    fmt.Printf("delAffected = %d\n", delAffected)

    // 统计下记录数量
    countSQL := "select count(*) as total from " + tableName + " where is_del = 0"
    countRow := db.QueryRowx(countSQL)
    var total int64
    countRow.Scan(&total)
    fmt.Printf("total = %d\n", total)

}

相对于标准库来说，确实还是更方便，但如果写业务CRUD还是非常的繁琐，这点就不如gorm好用。