Go关系型数据库持久化
Go开源项目 - gorp使用方法
2013-01-17 10:25 by Danny.tian, 618 阅读, 5 评论, 收藏 , 编辑
gorp 是一个Go开源ORM框架.
Go关系型数据库持久化
我很含糊的称gorp是一个ORM框架. Go还没有真正的对象, 至少没有一个经典的感觉和 Smalltalk/Java一样的,这是"O". gorp不知道任何你struct之间的关系(以后会实现,现在还在TODO list中). 所以"R"也是有质疑的(但是我使用这个名字,因为感觉上更贴切).
"M"是没有问题的. 给定一些Go结构和一个数据库, gorp应该可以减少你的重复代码.
我希望gorp能节省你的时间并简化从数据库获取数据的苦活来帮助你把精力投入到算法上而不是基础设施上面.
数据库驱动
gorp使用Go1 database/sql包. 一个完整可用的兼容驱动程序如下:
http://code.google.com/p/go-wiki/wiki/SQLDrivers
遗憾的是SQL数据库很多不一样的问题. gorp提供一个应该被每个数据库厂商实现的 Dialect 接口, Dialect支持如下数据库:
MySQL PostgreSQL sqlite3这三个数据库都通过了测试, 请查看 gorp_test.go, 例如针对这三个数据库的DSN.
特点
通过API或tag绑定struct字段到表的列 支持事务 从struct建立db架构正向工程(来做更好的单元测试) 在insert/update/delete的前后提供hook 自动为struct生成generate insert/update/delete语句 在insert后自动绑定自增主键到struct 通过主键删除 通过主键选择 可选的SQL跟踪日志 绑定任意SQL查询到struct 可通过一个version column来为update和delete实现乐观锁待办事项
支持内嵌struct
安装
# install the library:
go get github.com/coopernurse/ gorp
// use in your .go code:
import (
" github.com/coopernurse/gorp "
)
运行测试
现在测试测试包括了MySQL.我需要为测试工具添加额外的驱动, 但是现在你可以克隆repo并设置一个环境变量来为运行"go test"做准备
# Set env variable with dsn using mymysql format. From the mymysql docs, # the format can be of 3 types: # # DBNAME /USER/ PASSWD # unix:SOCKPATH *DBNAME/USER/ PASSWD # tcp:ADDR *DBNAME/USER/ PASSWD # # for example, on my box I use: export GORP_TEST_DSN =gomysql_test/gomysql_test/ abc123 # run the tests go test # run the tests and benchmarks go test -bench= " Bench " -benchtime 10
性能
gorp使用反射来构造SQL查询和绑定参数. 在gorp_test.go中查看 BenchmarkNativeCrud 对 BenchmarkGorpCrud的一个简单的性能测试. 在我的MacBook Pro上它比手写SQL慢大约2%-3%.
示例
首先定义一些类型:
type Invoice struct {
Id int64
Created int64
Updated int64
Memo string
PersonId int64
}
type Person struct {
Id int64
Created int64
Updated int64
FName string
LName string
}
// Example of using tags to alias fields to column names
// The 'db' value is the column name
//
// A hyphen will cause gorp to skip this field, similar to the
// Go json package.
//
// This is equivalent to using the ColMap methods:
//
// table := dbmap.AddTableWithName(Product{}, "product")
// table.ColMap("Id").Rename("product_id")
// table.ColMap("Price").Rename("unit_price")
// table.ColMap("IgnoreMe").SetTransient(true)
//
type Product struct {
Id int64 `db: " product_id " `
Price int64 `db: " unit_price " `
IgnoreMe string `db: " - " `
}
然后创建一个映射器, 一般你再app启动时做一次.
// connect to db using standard Go database/sql API
// use whatever database/sql driver you wish
db, err := sql.Open( " mymysql " , " tcp:localhost:3306*mydb/myuser/mypassword " )
// construct a gorp DbMap
dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{ " InnoDB " , " UTF8 " }}
// register the structs you wish to use with gorp
// you can also use the shorter dbmap.AddTable() if you
// don't want to override the table name
//
// SetKeys(true) means we have a auto increment primary key, which
// will get automatically bound to your struct post-insert
//
t1 := dbmap.AddTableWithName(Invoice{}, " invoice_test " ).SetKeys( true , " Id " )
t2 : = dbmap.AddTableWithName(Person{}, " person_test " ).SetKeys( true , " Id " )
t3 : = dbmap.AddTableWithName(Product{}, " product_test " ).SetKeys( true , " Id " )
自动创建删除已注册的表
// create all registered tables dbmap.CreateTables() // drop dbmap.DropTables()
可选项:你可以传入一个log.Logger来跟踪全部的SQL语句:
// Will log all SQL statements + args as they are run // The first arg is a string prefix to prepend to all log messages dbmap.TraceOn( " [gorp] " , log.New(os.Stdout, " myapp: " , log.Lmicroseconds)) // Turn off tracing dbmap.TraceOff()
然后保存一些数据:
// Must declare as pointers so optional callback hooks
// can operate on your data, not copies
inv1 := &Invoice{ 0 , 100 , 200 , " first order " , 0 }
inv2 : = &Invoice{ 0 , 100 , 200 , " second order " , 0 }
// Insert your rows
err := dbmap.Insert(inv1, inv2)
// Because we called SetKeys(true) on Invoice, the Id field
// will be populated after the Insert() automatically
fmt.Printf( " inv1.Id=%d inv2.Id=%d\n " , inv1.Id, inv2.Id)
你可以执行原始的SQL语句.尤其是批量操作时.
res, err := dbmap.Exec( " delete from invoice_test where PersonId=? " , 10 )
想要做join? 只写SQL和struct, gorp将绑定他们:
// Define a type for your join
// It *must* contain all the columns in your SELECT statement
//
// The names here should match the aliased column names you specify
// in your SQL - no additional binding work required. simple.
//
type InvoicePersonView struct {
InvoiceId int64
PersonId int64
Memo string
FName string
}
// Create some rows
p1 := &Person{ 0 , 0 , 0 , " bob " , " smith " }
dbmap.Insert(p1)
// notice how we can wire up p1.Id to the invoice easily
inv1 := &Invoice{ 0 , 0 , 0 , " xmas order " , p1.Id}
dbmap.Insert(inv1)
// Run your query
query := " select i.Id InvoiceId, p.Id PersonId, i.Memo, p.FName " +
" from invoice_test i, person_test p " +
" where i.PersonId = p.Id "
list, err : = dbmap.Select(InvoicePersonView{}, query)
// this should test true
expected := & InvoicePersonView{inv1.Id, p1.Id, inv1.Memo, p1.FName}
if reflect.DeepEqual(list[ 0 ], expected) {
fmt.Println( " Woot! My join worked! " )
}
你也可以在一个事务中批量操作:
func InsertInv(dbmap *DbMap, inv *Invoice, per * Person) error {
// Start a new transaction
trans := dbmap.Begin()
trans.Insert(per)
inv.PersonId = per.Id
trans.Insert(inv)
// if the commit is successful, a nil error is returned
return trans.Commit()
}
在更新数据到数据库(前/后)使用hook, 对时间戳很有效:
// implement the PreInsert and PreUpdate hooks
func (i * Invoice) PreInsert(s gorp.SqlExecutor) error {
i.Created = time.Now().UnixNano()
i.Updated = i.Created
return nil
}
func (i * Invoice) PreUpdate(s gorp.SqlExecutor) error {
i.Updated = time.Now().UnixNano()
return nil
}
// You can use the SqlExecutor to cascade additional SQL
// Take care to avoid cycles. gorp won't prevent them.
//
// Here's an example of a cascading delete
//
func (p * Person) PreDelete(s gorp.SqlExecutor) error {
query : = " delete from invoice_test where PersonId=? "
err : = s.Exec(query, p.Id); if err != nil {
return err
}
return nil
}
你可以实现以下的hook
PostGet PreInsert PostInsert PreUpdate PostUpdate PreDelete PostDelete All have the same signature. for example: func (p *MyStruct) PostUpdate(s gorp.SqlExecutor) error
乐观锁 (有点像JPA)
// Version is an auto-incremented number, managed by gorp
// If this property is present on your struct, update
// operations will be constrained
//
// For example, say we defined Person as:
type Person struct {
Id int64
Created int64
Updated int64
FName string
LName string
// automatically used as the Version col
// use table.SetVersionCol("columnName") to map a different
// struct field as the version field
Version int64
}
p1 : = &Person{ 0 , 0 , 0 , " Bob " , " Smith " , 0 }
dbmap.Insert(p1) // Version is now 1
obj, err : = dbmap.Get(Person{}, p1.Id)
p2 : = obj.(* Person)
p2.LName = " Edwards "
dbmap.Update(p2) // Version is now 2
p1.LName = " Howard "
// Raises error because p1.Version == 1, which is out of date
count, err := dbmap.Update(p1)
_, ok : = err.(gorp.OptimisticLockError)
if ok {
// should reach this statement
// in a real app you might reload the row and retry, or
// you might propegate this to the user, depending on the desired
// semantics
fmt.Printf( " Tried to update row with stale data: %v\n " , err)
} else {
// some other db error occurred - log or return up the stack
fmt.Printf( " Unknown db err: %v\n " , err)
}
至此结束.
分类: Golang
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did47177