<p>I used a function closure to abstract my SQL item table schema from the logic:</p>
<pre><code>func InsertItems(db *sql.DB) (func(*Item) (int, error), error) {
stmt, _ = db.Prepare("INSERT INTO...")
return func(item *Item) (int, error) {
res, _ := stmt.Exec(&item.Field1, &item.Field2)
return res.LastInsertId(), nil
}, nil
}
</code></pre>
<p>It was really fun to use:</p>
<pre><code>insertItem, _ := model.InsertItems(env.db)
for _, item range items {
insertItem(item)
}
</code></pre>
<p>But it seems closing Statements is important, <a href="https://groups.google.com/forum/#!topic/golang-nuts/ISh22XXze-s" rel="nofollow">https://groups.google.com/forum/#!topic/golang-nuts/ISh22XXze-s</a>.</p>
<p>I can't see a way to call <code>stmt.Close()</code> with this scheme.</p>
<p>Is there a way to return "anonymous structs" or "structs closures" in Go?</p>
<p>Something like (warning: it might be ugly):</p>
<pre><code>return struct {
stmt: stmt,
Insert(*Item): func(item *Item) (int, error) {
res, _ := stmt.Exec(&item.Field1, &item.Field2)
return res.LastInsertId(), nil
},
Close(): func() {
stmt.Close()
},
}
</code></pre>
<p>Thanks, don't hesitate to share your way of abstracting SQL schema.</p>
<p>Following this post: <a href="https://www.reddit.com/r/golang/comments/3pmx50/does_preparing_sql_queries_increase_performance/" rel="nofollow">https://www.reddit.com/r/golang/comments/3pmx50/does_preparing_sql_queries_increase_performance/</a></p>
<hr/>**评论：**<br/><br/>TheMerovius: <pre><p>You can, but it won't make you happy. Because you have to return it as an interface type and as it's an anonymous struct you can only use interface{}. Otherwise, your function needs to have a return type and that can't be an anonymous struct.</p>
<p>So, just make that anonymous struct into a proper named type with methods and return that. That's what types are for :)</p></pre>TheMerovius: <pre><p>Forgot to mention: Also, to actually use that type, you'd of course need to use reflection. Which is slow and ugly. Just don't do it, make it a named type :)</p></pre>grutoc: <pre><p>Okay so I need to make a type for update, insert, delete, and that for every database object, right?</p></pre>TheMerovius: <pre><p>Hm, the fact that you have multiple of these of course makes it far less… good.</p>
<p>Another thing you could do (off the top of my head and using your stuff as a starting point) would be to do:</p>
<pre><code>type Preparer interface {
Prepare(query string) (*sql.Stmt, error)
}
func InsertItems(db Preparer) (insert func(*Item) (int, error), close func() error, err error) {
stmt, err = db.Prepare("INSERT INTO...")
return func(item *Item) (int, error) {
res, err := stmt.Exec(&item.Field1, &item.Field2)
return res.LastInsertId(), err
}, stmt.Close, err
}
</code></pre>
<p>The consumer of your API would then do (error checking elided, as in your example code):</p>
<pre><code>insert, close, _ := model.InsertItems(env.db)
for _, item range items {
insert(item)
}
close()
</code></pre>
<p>Several thinks are to note: a) I replaced the *sql.DB as a parameter with a custom interface that is fulfilled both by *sql.DB and *sql.Tx. This makes this function interact well with transactions too, while at the same time being a drop-in replacement :) b) I returned the closing function as an additional closure.</p>
<p>I think this should solve most of the problems with the code. I am still not entirely convinced I like the pattern, though :)</p></pre>grutoc: <pre><p>I did think of that, but it just can't do in my personal use case, as I need to perform sub queries:</p>
<pre><code>func InsertItems(db Preparer) (insert func(*Item) (int, error), close func() error, err error) {
stmt, err = db.Prepare("INSERT INTO...")
return func(item *Item) (int, error) {
res, err := stmt.Exec(&item.Field1, &item.Field2)
return res.LastInsertId(), err
}, stmt.Close, err
}
// And it becomes quite hell very fast
func InsertSuperItems(db Preparer) (insert func(*SuperItem) (int, error), close func() error, err error) {
insertItem, close, _ := InsertItems(db)
stmt, err = db.Prepare("INSERT INTO...")
return func(superItem *SuperItem) (int, error) {
insertItem(superItem.Item)
res, err := stmt.Exec(&item.Field1, &item.Field2)
return res.LastInsertId(), err
}, func() error {
close()
return stmt.Close()
}, err
}
</code></pre>
<p>I was already using a similar interface to handle both sql.DB and sql.Tx, great idea ;)</p></pre>TheMerovius: <pre><p>The hairiness points towards this pattern not being very elegant after all, tbh. You should probably replace it with a <code>function InsertItems(db Preparer, items []*Item) (ids []int, err error)</code> and similar.</p></pre>grutoc: <pre><p>This is fancy to implement on the database side, but not interesting to use in the logic side, I was doing this at the very beginning.</p>
<p>Here is a new pattern:</p>
<pre><code>type ItemOp struct {
stmt *sql.Stmt
Exec func(*Item) error
End func()
}
func InsertItems(db Preparer) (*ItemOp, error) {
stmt, _ := db.Prepare("INSERT INTO...")
return &ItemOp{
stmt: stmt,
Exec: func(item *Item) error {
res, err := stmt.Exec(item.Field1, item.Field2)
item.Id = res.LastInsertId()
return err
},
End: func() {
stmt.Close()
},
}
insertItem, _ := InsertItems(env.db)
for _, item range := items {
insertItem.Exec(item)
}
insertItem.End()
</code></pre>
<p>Please let me know what you think about it, and thanks for the good ideas ;)</p></pre>
