This article introduces XORM. Xorm is a simple but powerful Go LANGUAGE ORM library that can greatly simplify our database operations.

Install the driver

First installation:

$ go get xorm.io/xorm

If the MySQL database driver is not installed, run the following command to install the driver:

$ go get github.com/go-sql-driver/mysql

Connecting to a Database

To use XORm to manipulate databases, you first need to create an engine using xorm.newengine (). This method takes the same parameters as the sql.open () argument.

package main

import (
  "log"
  "time"

  _ "github.com/go-sql-driver/mysql"
  "xorm.io/xorm"
)

func main(a) {
  engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")

  iferr ! =nil {
    log.Fatal(err)
  }
}
Copy the code

Synchronize the database table structure

In the following code, we demonstrate a very useful feature of XORM to synchronize tables in the database with structures in the corresponding Go code. In the initial state, dianping does not have a table named student. Calling Sync2() automatically creates a table named student based on the structure of student.

If table student already exists, the Sync() method compares the User structure with the table structure.

type Student struct {
	Id      int64
	Name    string
	Salt    string
	Age     int
	Passwd  string    `xorm:"varchar(200)"`
	Created time.Time `xorm:"created"`
	Updated time.Time `xorm:"updated"`
}

func main(a) {
	engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")

	iferr ! =nil {
		log.Fatal(err)
	}

	err = engine.Sync(new(Student))
	iferr ! =nil {
		log.Fatal(err)
	}
}
Copy the code

This change is limited to adding fields. Deleting existing fields in a table is risky. If we delete the Salt field of the User structure, then execute the program. The following error occurs:

[xorm] [warn]  2021/03/29 22:44:38.528784 Table student has column salt but struct has not related field
Copy the code

The query

Get()

The Get() method is used to query a single piece of data and assign a value to the passed object using the returned field:

func main(a) {
  engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")

	user1 := &Student{}
	has, _ := engine.ID(1).Get(user1)
	if has {
		fmt.Printf("user1:%v\n", user1)
	}

	user2 := &Student{}
	has, _ = engine.Where("name=?"."JJ").Get(user2)
	if has {
		fmt.Printf("user2:%v\n", user2)
	}

	user3 := &Student{Id: 5}
	has, _ = engine.Get(user3)
	if has {
		fmt.Printf("user3:%v\n", user3)
	}

	user4 := &Student{Name: "Alex"}
	has, _ = engine.Get(user4)
	if has {
		fmt.Printf("user4:%v\n", user4)
	}
}
Copy the code

Here are three ways to use Get() :

Use primary key: engine.id (1) to query the user whose primary key (ID) is 1; Engine.Where(“name=?”) , “DJ “) query user name =” DJ “; Engine. Get(user3) query user Id = 5; User4 sets field Name to “pipi”, engine.Get(user4) queries user whose Name = “pipi”. Run the program:

user1:&{1 CJ salt 18 123 2021-03-29 08:35:49 +0800 CST 2021-03-29 08:35:49 +0800 CST}
user3:&{5 cmy.K salt 15 123 2021-03-29 08:35:49 +0800 CST 2021-03-29 08:35:49 +0800 CST}
user4:&{6 Alex salt 19 123 2021-03-29 08:35:49 +0800 CST 2021-03-29 08:35:49 +0800 CST}

Process finished with exit code 0
Copy the code

Query criteria are used in an undifferentiated order, but must be called before the Get() method. In fact, the same is true for the query-statistics methods described below, where you can add some filtering criteria before calling the actual method. In addition, xORm supports returning only specified columns xorm.cols () or omits specified columns xorm.omit ().

In addition, xORm provides the ShowSQL() method to set the SQL to be executed simultaneously in the console for troubleshooting possible problems:

[xorm] [info] 2021/03/30 08:41:01.558169 [SQL] SELECT 'id', 'name', 'salt', 'age', 'passwd', 'created', `updated` FROM `student` WHERE `id`=? LIMIT 1 [1] -1.1464 ms user1:&{1 CJ salt 18 21 2021-03-29 08:35:49 +0800 CST 2021-03-29 08:35:49 +0800 CST}Copy the code

Xorm can set logging options to export logs to a file:

func main(a) {
	engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")

	f, err := os.Create("sql.log")
	iferr ! =nil {
		panic(err)
	}

	engine.SetLogger(log2.NewSimpleLogger(f))
	engine.Logger().SetLevel(log2.LOG_DEBUG)
	engine.ShowSQL(true)

	user1 := &Student{}
	has, _ := engine.ID(1).Get(user1)
	if has {
		fmt.Printf("user1:%v\n", user1)
	}
}
Copy the code

Iterate()

Iterate() finds all records that satisfy the condition, but passes in a callback for each record:

    engine.Where("age > ? and age < ?".12.30).Iterate(&User{}, func(i int, bean interface{}) error {
      fmt.Printf("user%d:%v\n", i, bean.(*User))
      return nil  // If the callback returns a non-nil error, subsequent records are not processed.
    })
Copy the code

Rows()

The Rows() method is similar to Iterate(), but returns a Rows object that we Iterate over ourselves, making it more flexible

    rows, _ := engine.Where("age > ? and age < ?".12.30).Rows(&User{})
    defer rows.Close()
    
    u := &User{}
    for rows.Next() {
      rows.Scan(u)
      
      fmt.Println(u)
    }
Copy the code

There are many more

  • The Get() method can only return a single record
  • The Exist() method checks whether the qualified record exists
  • The Find() method returns all eligible records

    mapUsers := make(map[int64]User)

    engine.Where("length(name) = ?" , 3).Find(&mapUsers)Copy the code
  • The Sum() method returns the Sum of a fieldengine.Sum(&Student{}, "total")
  • The Count() method counts the number of records that meet the conditionsengine.Where("age >= ?" , 50).Count(&Student{})

Insert data

With the engine.insert () method, you can either Insert a single piece of data or batch Insert multiple pieces of data:

    engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")
    user := &Student{Name: "lzy", Age: 50}
    
    affected, _ := engine.Insert(user)
Copy the code

Update the data

Updates are implemented by engine.update (), which can be passed in a structure pointer or map[string]interface{}. In the case of passing a structure pointer, xORM updates only non-empty fields. If you must update an empty field, you need to use the Cols() method to display the specified updated column. After specifying a column using the Cols() method, it is updated even if the field is empty:

    engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")
    engine.ID(1).Update(&User{Name: "ldj"})
    engine.ID(1).Cols("name"."age").Update(&User{Name: "dj"})
    
    engine.Table(&User{}).ID(1).Update(map[string]interface{} {"age": 18})
Copy the code

Delete the data

Call engine.delete () directly to Delete the eligible records and return the number of deleted entries:

    engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")
    
    affected, _ := engine.Where("name = ?"."lzy").Delete(&User{})
    fmt.Printf("%d records deleted", affected)
Copy the code

Execute raw SQL

    engine, err := xorm.NewEngine("mysql"."root:@/dianping? charset=utf8")
    
    querySql := "select * from user limit 1"
    reuslts, _ := engine.Query(querySql)
    for _, record := range reuslts {
    for key, val := range record {
      fmt.Println(key, string(val))
    }
    }
    
    updateSql := "update `user` set name=? where id=?"
    res, _ := engine.Exec(updateSql, "ldj".1)
    fmt.Println(res.RowsAffected())
Copy the code

This article has provided a brief introduction to XORM, which is much more than that. If you are interested, explore it in documentation and Github.

reference

  • Xorm GitHub:github.com/go-xorm/xor…
  • Xorm Manual: gobook.io/read/gitea….