database/sql和database/sql/driver中的命名参数

huangapple go评论101阅读模式
英文:

named parameters in database/sql and database/sql/driver

问题

我正在尝试弄清楚在Go语言内置的database/sql包中使用命名参数的模式是什么。我查看了Oracle驱动程序,但它似乎只是C库的一个包装器。有人以优雅的方式解决了这个问题吗?到目前为止,我只是通过在单元测试中将参数设置为{0}{1}来解决这个问题,但是如果能够像map[string]interface{}一样正常使用它们就太好了。有人有想法或者看起来符合惯用法的实现吗?

参考一下这个测试:

db := testConn()
stmt, err := db.Prepare("return {0} as int1, {1} as int2")
if err != nil {
   t.Fatal(err)
}
rows, err := stmt.Query(123, 456)
if err != nil {
   t.Fatal(err)
}
rows.Next()

var test int
var test2 int
err = rows.Scan(&test, &test2)
if err != nil {
   t.Fatal(err)
}
if test != 123 {
   t.Fatal("test != 123;", test)
}
if test2 != 456 {
   t.Fatal("test2 != 456;", test2)
}

我在Query中的操作是:

func (stmt *cypherStmt) Query(args []driver.Value) (driver.Rows, error) {
   cyphReq := cypherRequest{
      Query: stmt.query,
   }
   if len(args) > 0 {
      cyphReq.Params = make(map[string]interface{})
   }
   for idx, e := range args {
      cyphReq.Params[strconv.Itoa(idx)] = e
   }
   ...
}

以上是你要翻译的内容。

英文:

I'm trying to figure out what the pattern is for using named parameters in go's built-in database/sql package. I looked at the oracle driver, but it seems like just a wrapper for the C library. Have people solved this in an elegant way? So far I've just worked around the problem by putting {0}, {1} as the parameters in the unit tests, but it sure would be nice to be able to use them normally as a map[string]interface{} or something. Does anyone have an idea or an implementation that seems idiomatic?

For reference, here is a test:

db := testConn()
stmt, err := db.Prepare("return {0} as int1, {1} as int2")
if err != nil {
   t.Fatal(err)
}
rows, err := stmt.Query(123, 456)
if err != nil {
   t.Fatal(err)
}
rows.Next()

var test int
var test2 int
err = rows.Scan(&test, &test2)
if err != nil {
   t.Fatal(err)
}
if test != 123 {
   t.Fatal("test != 123;", test)
}
if test2 != 456 {
   t.Fatal("test2 != 456;", test2)
}

And what I'm doing in Query is:

func (stmt *cypherStmt) Query(args []driver.Value) (driver.Rows, error) {
   cyphReq := cypherRequest{
      Query: stmt.query,
   }
   if len(args) > 0 {
      cyphReq.Params = make(map[string]interface{})
   }
   for idx, e := range args {
      cyphReq.Params[strconv.Itoa(idx)] = e
   }
...

答案1

得分: 8

我正在使用一个名为sqlx的数据库/sql的封装库。你可以在这里查看他是如何实现的。

下面是一个选择并将结果存入元组的示例:

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
jason := Person{}
err := db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
fmt.Printf("%#v\n", jason)
// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

下面是一个插入元组的示例:

dude := Person{
    FirstName: "Jason",
    LastName:  "Moiron",
    Email:     "jmoiron@jmoiron.net",
}
_, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, dude)

希望对你有帮助!

英文:

I'm using wrapper on top of database/sql called sqlx
https://github.com/jmoiron/sqlx
You can check here how he did it.

Example on how to select into a tuple

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
jason = Person{}
err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
fmt.Printf("%#v\n", jason)
// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

Example on how to insert a tuple

dude := Person{
    FirstName:"Jason", 
    LastName:"Moiron", 
    Email:"jmoiron@jmoiron.net"
}
 _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, dude)

答案2

得分: 4

据我所知,没有驱动程序本身提供命名参数的功能。我个人使用 gorp ,它允许你从结构体或映射中绑定查询:

_, err = dbm.Select(&users,
	"select * from PersistentUser where mykey = :Key",
	map[string]interface{}{
		"Key": 43,
	}
)

或者

_, err = dbm.Select(&users,
	"select * from PersistentUser where mykey = :Key",
	User{Key: 43},
)
英文:

As far as I know, no driver natively provides for named parameters. I personally use gorp which allows you to bind queries from structs or maps:

_, err = dbm.Select(&users,
	"select * from PersistentUser where mykey = :Key",
	map[string]interface{}{
		"Key": 43,
	}
)

or

_, err = dbm.Select(&users,
	"select * from PersistentUser where mykey = :Key",
	User{Key: 43},
)

答案3

得分: 1

可以创建一个实现driver.Valuer{}接口的map[string]interface{}类型,将其序列化为[]byte,然后在驱动程序中进行转换。

但这样做效率低下且不符合惯例。由于您的驱动程序将以非标准方式使用,最好的做法可能是放弃使用database/sql,而是编写一个完全自定义接口的包。

英文:

It would be possible to create a map[string]interface{} type that implements driver.Valuer{} to serialize it as a []byte, and then convert it back in the driver.

But that would be inefficient and unidiomatic. Since your driver would then be used in a nonstandard way anyway, it would probably be better to just forget about database/sql and write a package with a totally custom interface.

huangapple
  • 本文由 发表于 2013年12月9日 00:54:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/20456053.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定