英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论