xorm的基本使用(postgres)

1. 连接

package models

import (
    "fmt"

    "github.com/go-xorm/xorm"
    "xorm.io/core"

    _ "github.com/lib/pq"
)

var x *xorm.Engine

func init() {

    var err error
    connStr := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=disable",
        E.PgHost, E.PgPort, E.PgUser, E.PgPass, E.PgDB)

    x, err = xorm.NewEngine("postgres", connStr)

    if err != nil {
        fmt.Println("xorm failed to initialized db: ", err)
        return
    }
    err = x.Ping()
    if err != nil {
        fmt.Println("xorm failed to ping db: ", err)
        return
    }
    fmt.Println("Connect successfully!")

    x.SetMapper(core.GonicMapper{})
    //Set schema, schema is "public" if not set
    x.SetSchema(E.PgSchema)
}

SetMapper用于设置结构体与数据库表结构的映射模式。

SetSchema用于设置默认使用的schema。

Ping用于试验数据库是否连接成功，另：dbname为空时返回第一个error，dbname错误时返回第二个error。

2. 使用

2.1 支持sql语句

查询

res, err := engine.Query("select * from user")

增删改

res, err := engine.Exec("update user set .... where ...")

2.2 orm方法

插入一个或者多个数据：

res, err := engine.Insert(&struct)
// INSERT INTO struct () values ()
res, err := engine.Insert(&struct1, &struct2)
// INSERT INTO struct1 () values ()
// INSERT INTO struct2 () values ()
res, err := engine.Insert(&sliceOfStruct)
// INSERT INTO struct () values (),(),()
res, err := engine.Insert(&struct1, &sliceOfStruct2)
// INSERT INTO struct1 () values ()
// INSERT INTO struct2 () values (),(),()

从数据库里面查询一条记录：

has, err := engine.Get(&user)
// SELECT * FROM user LIMIT 1

从数据库中查询多条记录：

sliceOfStructs := new(Struct)
err := engine.Find(sliceOfStructs)
// SELECT * FROM user

查询多条记录，然后每条记录进行处理，有两个方法，一个是iterator，另一个是raw：

err := engine.Iterate(...)
// SELECT * FROM user

raws, err := engine.Raws(...)
// SELECT * FROM user
bean := new(Struct)
for raws.Next() {
    err = raws.Scan(bean)
}

更新一条或者多条记录：

res, err := engine.Update(&user)
// UPDATE user SET ...

删除一条或者多条记录，必须存在删除条件

res, err := engine.Where(...).Delete(&user)
// DELETE FROM user Where ...

查询记录条数:

counts, err := engine.Count(&user)
// SELECT count(*) AS total FROM user

2.3 条件

Id、In：

engine.Id(1).Get(&user) // for single primary key
// SELECT * FROM user WHERE id = 1
engine.Id(core.PK{1, 2}).Get(&user) // for composite primary keys
// SELECT * FROM user WHERE id1 = 1 AND id2 = 2
engine.In("id", 1, 2, 3).Find(&users)
// SELECT * FROM user WHERE id IN (1, 2, 3)
engine.In("id", []int{1, 2, 3})
// SELECT * FROM user WHERE id IN (1, 2, 3)

Where, And, Or:

engine.Where().And().Or().Find()
// SELECT * FROM user WHERE (.. AND ..) OR ...

OrderBy, Asc, Desc:

engine.Asc().Desc().Find()
// SELECT * FROM user ORDER BY .. ASC, .. DESC
engine.OrderBy().Find()
// SELECT * FROM user ORDER BY ..

Limit, Top:

engine.Limit().Find()
// SELECT * FROM user LIMIT .. OFFSET ..
engine.Top(5).Find()
// SELECT TOP 5 * FROM user // for mssql
// SELECT * FROM user LIMIT .. OFFSET 0 //for other databases

Sql, 查询原生SQL:

engine.Sql("select * from user").Find()

Cols, Omit, Distinct:

engine.Cols("col1, col2").Find()
// SELECT col1, col2 FROM user
engine.Cols("col1", "col2").Where().Update(user)
// UPDATE user set col1 = ?, col2 = ? Where ...
engine.Omit("col1").Find()
// SELECT col2, col3 FROM user
engine.Omit("col1").Insert()
// INSERT INTO table (non-col1) VALUES ()
engine.Distinct("col1").Find()
// SELECT DISTINCT col1 FROM user

Join, GroupBy, Having:

engine.GroupBy("name").Having("name='xlw'").Find()
//SELECT * FROM user GROUP BY name HAVING name='xlw'
engine.Join("LEFT", "userdetail", "user.id=userdetail.id").Find()
//SELECT * FROM user LEFT JOIN userdetail ON user.id=userdetail.id

And then

内联多表查询

type Result struct {
    Table1 `xorm:"extends"`
    Table2 `xorm:"extends"`
    Table3 `xorm:"extends"`
}
var result []Result 
x.Table("table1").
    Join("INNER", "table2", "table2.xx = table1.xx").
    Join("INNER", "table3", "table3.xx = table1.xx").
    Where("").Find(&result)

另：xorm-reverse的基本cmd

D:
cd D:\xx\Go\Gopath\pkg\mod\github.com\go-xorm\cmd\xorm@v0.0.0-20190426080617-f87981e709a1
xorm reverse -s postgres "host=xx port=xx user=xx password=xx dbname=xx sslmode=disable" templates/goxorm D:\temps
// 生成在D:\temps下

或在拥有templates\goxorm\config和templates\goxorm\struct.go.tpl两个文件（copy from cmd\xorm包）的项目目录下

xorm reverse -s postgres postgres://user:password@host:port/dbname?sslmode=disable templates/goxorm

具体参考xorm.io