golang之xorm简单使用

go get github.com/go-xorm/xorm

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/go-xorm/xorm"
)

type PointInfo struct {
    Id         int64  `xorm:"pk autoincr"`
    ProductKey string `xorm:"product_key"`
    DeviceName string `xorm:"device_name"`
    PointId    string `xorm:"point_id"`
}

const (
    UserName = "root"
    PassWord = "root"
    Host     = "127.0.0.1"
    Port     = "33306"
    Database = "test"
    Charset  = "utf8"
)

var xe *xorm.Engine

func init() {
    sqlStr := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", UserName, PassWord, Host, Port, Database, Charset)

    // 1、创建数据库引擎
    var err error
    xe, err = xorm.NewEngine("mysql", sqlStr)
    if err != nil {
        fmt.Println("数据库连接失败:", err)
        return
    }
    if xe == nil {
        fmt.Println("引擎初始化异常")
        return
    }

    // 2、创建或者同步表（名称为Stu）
    err = xe.Sync(new(PointInfo))
    if err != nil {
        fmt.Println("数据表同步失败:", err)
    }
}

func main() {
    session := xe.NewSession()
    defer xe.Close()
    defer session.Close()
    err := session.Begin()
    if err != nil {
        return
    }

    id, err := insert()
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = query(id)
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = queryList("device0", "point0")
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = queryByPage("device0")
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = update(id)
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = deleteOne(id)
    if err != nil {
        _ = session.Rollback()
        return
    }
    err = session.Commit()
    if err != nil {
        return
    }
}

func insert() (id int64, err error) {
    pointInfo := new(PointInfo)
    pointInfo.ProductKey = "product1"
    pointInfo.DeviceName = "device1"
    pointInfo.PointId = "point1"

    affected, err := xe.InsertOne(pointInfo)
    if err != nil {
        return 0, err
    }
    fmt.Println(affected)
    return pointInfo.Id, nil
}

func query(id int64) (err error) {
    pointInfo := PointInfo{Id: id}
    get, err := xe.Get(&pointInfo)
    fmt.Println(get)
    return err
}

func queryList(deviceName string, pointId string) (err error) {
    list := make([]PointInfo, 0)
    err = xe.Where("device_name like ?", "%"+deviceName+"%").And("point_id like ?", "%"+pointId+"%").OrderBy("id").Find(&list)
    fmt.Println(list)
    return err
}

func queryByPage(deviceName string) (err error) {
    list := make([]PointInfo, 0)
    page := 0
    pageSize := 2
    limit := pageSize
    start := page * pageSize
    totalCount, err := xe.Where("device_name like ?", "%"+deviceName+"%").Limit(limit, start).FindAndCount(&list)
    fmt.Println(totalCount)
    fmt.Println(list)
    return err
}

func update(id int64) (err error) {
    pointInfo := new(PointInfo)
    pointInfo.ProductKey = "product2"
    pointInfo.DeviceName = "device2"
    pointInfo.PointId = "point2"
    affected, err := xe.Id(id).Update(pointInfo)
    fmt.Println(affected)
    return err
}

func deleteOne(id int64) (err error) {
    pointInfo := new(PointInfo)
    affected, err := xe.Id(id).Delete(pointInfo)
    fmt.Println(affected)
    return err
}