在Golang函数中，我可以在不先扫描它的情况下返回数据库中的行结果吗？

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

Can I return rows result from db without scan it first in Golang func?

问题

我有一个使用Golang的小项目，我的计划是创建一个基础函数，该函数将从模型中调用以执行SQL查询，然后返回结果行而不先进行扫描。我使用这种方式是为了防止忘记编写defer rows.Close()和在模型中执行查询的代码更简单。我尝试过这种方式，但是当尝试打印结果时，我得到了一个空值而没有任何错误。这是我的截图。当查询执行时，结果存在，并且扫描的结果行在同一个函数中。也许我漏掉了什么？这是我的第一个问题，抱歉它太长了。谢谢。

执行SQL查询并返回结果的基础模型

  1. package model
  3. import "database/sql"
  4. import "hb-backend-v1/config/database"
  5. import "fmt"
  7. func Query(query string) (*sql.Rows, error){
  8.     connect, err := database.Connect()
  10.     if err != nil{
  11.         fmt.Println("连接失败")
  12.         return nil, err
  13.     }
  14.     fmt.Println("连接成功")
  15.     defer connect.Close()
  17.     rows, err := connect.Query(query)
  19.     defer rows.Close()
  20.     if err != nil{
  21.         return nil, err
  22.     }
  24.     return rows, nil
  25. }

调用基础模型并返回结果的地方

  1. package product
  3. import "database/sql"
  4. import _ "fmt"
  5. import "hb-backend=v1/model"
  7. type Hasil struct{
  8.     Id_alamat_store int
  9.     Id_tk           int
  10.     Alamat          string
  11.     Id_wil          int
  12.     Latitude        sql.NullString
  13.     Longitude       sql.NullString
  14.     
  15. }
  17. func ProductList() ([]Hasil, error){
  18.     rows, err := model.Query("SELECT * FROM alamat_store")
  19.     if err != nil{
  20.         return nil, err
  21.     }
  22.     
  23.     var result []Hasil
  24.     for rows.Next(){
  25.         var each = Hasil{}
  26.         var err = rows.Scan(&each.Id_alamat_store, &each.Id_tk, &each.Alamat, &each.Id_wil, &each.Latitude, &each.Longitude)
  27.         if err != nil{
  28.             return nil, err
  29.         }
  30.         result = append(result, each)
  31.     }
  32.     return result, nil
  33. }
英文:

I have mini project using Golang, my plan is make a base function which it will be called from Model to execute sql query, then return the rows result without Scan it first. I'm using this way to prevent forget write defer rows.Close() and the code for execute the Query in model more simple. I had tried this way, but when try to print the result, I got nil without any error. here my screenshoot. The result exists when the query executed and the rows result scanned are in same function. Maybe I miss something? This is my first question, sorry it's too long. Thank you

The base model where the SQL query will be executed and return the result

  1. package model
  3. import "database/sql"
  4. import "hb-backend-v1/config/database"
  5. import "fmt"
  7. func Query(query string) (*sql.Rows, error){
  8. 	connect, err := database.Connect()
  10. 	if err != nil{
  11. 		fmt.Println("Connection Failed")
  12. 		return nil, err
  13. 	}
  14. 	fmt.Println("Connection Success")
  15. 	defer connect.Close()
  17. 	rows, err := connect.Query(query)
  19. 	defer rows.Close()
  20. 	if err != nil{
  21. 		return nil, err
  22. 	}
  24. 	return rows, nil
  25. }

This is where the base model will be called and give the result

  1. package product
  3. import "database/sql"
  4. import _"fmt"
  5. import "hb-backend=v1/model"
  7. type Hasil struct{
  8. 	Id_alamat_store int
  9. 	Id_tk 			int
  10. 	Alamat 			string
  11. 	Id_wil 			int
  12. 	Latitude 		sql.NullString
  13. 	Longitude 		sql.NullString
  14. 	
  15. }
  17. func ProductList() ([]Hasil, error){
  18. 	rows, err := model.Query("SELECT * FROM alamat_store")
  19. 	if err != nil{
  20. 		return nil, err
  21. 	}
  22. 	
  23. 	var result []Hasil
  24. 	for rows.Next(){
  25. 		var each = Hasil{}
  26. 		var err = rows.Scan(&each.Id_alamat_store, &each.Id_tk, &each.Alamat, &each.Id_wil, &each.Latitude, &each.Longitude)
  27. 		if err != nil{
  28. 			return nil, err
  29. 		}
  30. 		result = append(result, each)
  31. 	}
  32. 	return result, nil
  33. }

答案1

得分: 1

connection和rows在Query退出后将被关闭，一旦这两个被关闭，你就不能再使用rows了。

解决这个问题的一种方法是将一个闭包传递给Query，并在关闭这两个资源之前让Query执行它：

  1. func Query(query string, scan func(*sql.Rows) error) error {
  2.     connect, err := database.Connect()
  3.     if err != nil{
  4.         return err
  5.     }
  6.     defer connect.Close()
  8.     rows, err := connect.Query(query)
  9.     if err != nil{
  10.         return err
  11.     }
  12.     defer rows.Close()
  14.     return scan(rows)
  15. }
  1. func ProductList() ([]Hasil, error) {
  2. 	var result []Hasil
  3. 	err := model.Query("SELECT * FROM alamat_store", func(rows *sql.Rows) error {
  4. 		for rows.Next() {
  5. 			var each = Hasil{}
  6. 			var err = rows.Scan(&each.Id_alamat_store, &each.Id_tk, &each.Alamat, &each.Id_wil, &each.Latitude, &each.Longitude)
  7. 			if err != nil {
  8. 				return err
  9. 			}
  10. 			result = append(result, each)
  11. 		}
  12.         return nil
  13. 	})
  14. 	if err != nil {
  15. 		return nil, err
  16. 	}
  17. 	return result, nil
  18. }
英文:

Both connection and rows will be closed once Query exits, after those two are closed you can't use rows anymore.

One approach to get around that would be to pass a closure to Query and have Query execute it before closing the two resources:

  1. func Query(query string, scan func(*sql.Rows) error) error {
  2.     connect, err := database.Connect()
  3.     if err != nil{
  4.         return err
  5.     }
  6.     defer connect.Close()
  8.     rows, err := connect.Query(query)
  9.     if err != nil{
  10.         return err
  11.     }
  12.     defer rows.Close()
  14.     return scan(rows)
  15. }
  1. func ProductList() ([]Hasil, error) {
  2. 	var result []Hasil
  3. 	err := model.Query("SELECT * FROM alamat_store", func(rows *sql.Rows) error {
  4. 		for rows.Next() {
  5. 			var each = Hasil{}
  6. 			var err = rows.Scan(&each.Id_alamat_store, &each.Id_tk, &each.Alamat, &each.Id_wil, &each.Latitude, &each.Longitude)
  7. 			if err != nil {
  8. 				return err
  9. 			}
  10. 			result = append(result, each)
  11. 		}
  12.         return nil
  13. 	})
  14. 	if err != nil {
  15. 		return nil, err
  16. 	}
  17. 	return result, nil
  18. }

huangapple
  • 本文由 发表于 2021年8月31日 23:36:06
  • 转载请务必保留本文链接：https://go.coder-hub.com/69001579.html
  • go
  • sql
匿名

发表评论

匿名网友

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

确定