本文用go实现对mysql的增删改查以及事务的操作:
1、表结构:
type User struct {
Id int64
Name string
Age int
Passwd string `xorm:"varchar(200)"`
Created time.Time `xorm:"created"`
Updated time.Time `xorm:"updated"`
}
2、连接数据库,并新建数据库表结构:
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
"time"
)
func main(){
host := "localhost"
port := "3306"
username := "root"
password := "123456"
database := "test"
dataSourceName := fmt.Sprintf("%s:%s@(%s:%s)/%s?charset=utf8", username, password, host, port, database)
engine, err := xorm.NewEngine("mysql", dataSourceName)
if err != nil{
fmt.Println(err)
}
//建表
err = engine.Sync2(new(User))
if err != nil{
fmt.Println("error in create table user, ", err)
}
}
3、增---表中插入一行,在main函数中添加如下:
var user User
user.Name = "guigui"
user.Salt = "hell"
user.Age = 18
user.Passwd = "23"
affected, err := engine.Insert(&user)
if err != nil{
fmt.Println(err)
}
4、删---删除某行,在main函数中添加如下:
var user User
affected, err := engine.ID(2).Delete(&user)
if err != nil{
fmt.Println(err)
}
5、改---修改表结构,main函数中添加如下:
mm, err := engine.Exec("update user set name = ? where id = ?", "lgr", 1)
if err != nil{
fmt.Println(err)
}
fmt.Println(mm)
6、查---查询多行结果,并遍历输出,查询方法很多,Query(返回数据类型为[]map[string][]byte)、QueryString(返回数据类型为[]map[string]string)、QueryInterface(返回数据类型为[]map[string]interface{}):
1)QueryString方法实例:
results, err := engine.QueryString("select * from user")
for k, v := range results{
fmt.Println(k)
for m, n := range v{
fmt.Println(m)
fmt.Println(n)
}
fmt.Println("=====")
}
2)查询还可以用如下方法:
var users []User
err = engine.Table("user").Where("name = ?", "lgr").And("age > 10").Find(&users)
//以上相当于sql语句:SELECT * FROM user WHERE name = "lgr" AND age > 10
if err != nil{
fmt.Println(err)
}
fmt.Println(users)
7、实现事务的两种方法:
1)一种,新建session,并使用session方法实现,在main函数中调用如下方法:
func myTransaction(engine *xorm.Engine) error{
session := engine.NewSession()
defer session.Close()
if err := session.Begin(); err != nil {
// if returned then will rollback automatically
return err
}
user := User{Name: "xiaoxiao", Salt: "dev", Age: 18, Passwd: "111"}
if _, err := session.Insert(&user); err != nil {
return err
}
if _, err := session.Exec("update user set name = ? where id = ?", 70, "8"); err != nil {
return err
}
return session.Commit()
}
2)第二种,重写Transaction方法,在main函数中添加如下代码:
res, err := engine.Transaction(func(session *xorm.Session) (interface{}, error) {
user := User{Name: "xiaoxiao", Salt: "dev", Age: 18, Passwd: "111"}
if _, err := session.Insert(&user); err != nil {
return nil, err
}
if _, err := session.Exec("update user set name = ? where id = ?", 70, "id"); err != nil {
return nil, err
}
return nil, nil
})
if err != nil{
fmt.Println(err)
}
fmt.Println(res)
更多请查看官网