## **1. 连接**
```go
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语句
查询
```go
res, err := engine.Query("select * from user")
```
增删改
```go
res, err := engine.Exec("update user set .... where ...")
```
### 2.2 orm方法
插入一个或者多个数据：
```go
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 (),(),()
```
从数据库里面查询一条记录：
```go
has, err := engine.Get(&user)
// SELECT * FROM user LIMIT 1
```
从数据库中查询多条记录：
```go
sliceOfStructs := new(Struct)
err := engine.Find(sliceOfStructs)
// SELECT * FROM user
```
查询多条记录，然后每条记录进行处理，有两个方法，一个是iterator，另一个是raw：
```go
err := engine.Iterate(...)
// SELECT * FROM user
raws, err := engine.Raws(...)
// SELECT * FROM user
bean := new(Struct)
for raws.Next() {
err = raws.Scan(bean)
}
```
更新一条或者多条记录：
```go
res, err := engine.Update(&user)
// UPDATE user SET ...
```
删除一条或者多条记录，必须存在删除条件
```go
res, err := engine.Where(...).Delete(&user)
// DELETE FROM user Where ...
```
查询记录条数:
```go
counts, err := engine.Count(&user)
// SELECT count(*) AS total FROM user
```
### 2.3 条件
Id、In：
```go
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:
```go
engine.Where().And().Or().Find()
// SELECT * FROM user WHERE (.. AND ..) OR ...
```
OrderBy, Asc, Desc:
```go
engine.Asc().Desc().Find()
// SELECT * FROM user ORDER BY .. ASC, .. DESC
engine.OrderBy().Find()
// SELECT * FROM user ORDER BY ..
```
Limit, Top:
```go
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:
```go
engine.Sql("select * from user").Find()
```
Cols, Omit, Distinct:
```go
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:
```go
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
**内联多表查询**
```go
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](http://gobook.io/read/github.com/go-xorm/manual-en-US/)
有疑问加站长微信联系（非本文作者））
