更简洁的在GO语言中获取SQL结果集的函数

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

More concise function to fetch SQL result set in GO Golang

问题

我想从MySQL数据库中检索一个应用程序ID的数组。我使用了http://go-database-sql.org的示例代码:

func QueryAppList() *[]int64 {
	var (
		appList []int64
		appid   int64
	)
	qry := "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);"
    // cfg.GetDb()提供了已经建立的数据库连接
	rows, err := cfg.GetDb().Query(qry) 
	if err != nil {
		logg.Error(err)
		return &appList
	}
	defer func(rows *sql.Rows) { 
        // 简单的defer不能捕获所有错误:https://www.joeshaw.org/dont-defer-close-on-writable-files/
		err := rows.Close()
		if err != nil {
			logg.Error(err)
		}
	}(rows)
	for rows.Next() {
		err := rows.Scan(&appid)
		if err != nil {
			logg.Error(err)
			return &appList
		}
		appidList = append(appList, appid)
	}
	err = rows.Err()
	if err != nil {
		logg.Error(err)
		return &appList
	}
	return &appidList
}

我的程序将充斥着像这样的查询。获取结果列表的所有方法以及如何防止失败使得这个小查询很难理解实际发生了什么。

有没有办法使查询更简洁?

以下是我简化代码的想法:

  • 使用函数处理错误,将错误处理减少到一行。
  • 如果我只想要一个列的数组,我可以将查询和列名作为参数传递,并重用查询函数。我宁愿重新编写一个查询函数,而不是处理复杂的抽象。
  • 是否有我错过的可以减少混乱的包?

使用像gorm这样的ORM不是一个选项。

我刚开始学习Go编程,对这门语言还不熟悉。

下面是在Node.js中使用相同结果的相同查询。它只有9行,而Go有34行,即长度上更简洁了65%。这是我理想的目标。

import {query} from "../db/pool"; // 从https://github.com/sidorares/node-mysql2导入连接池查询

export const queryAppList = async () => {
  try {
    const qry = "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);";
    const [appList] = await query(qry);
    return appList; 
  } catch (err) {
    console.error(err)
    return [];
  }
};
英文:

I want to retrieve an array of app IDs from a MySQL database. I used http://go-database-sql.org's example code:

func QueryAppList() *[]int64 {
	var (
		appList []int64
		appid   int64
	)
	qry := "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);"
    // cfg.GetDb() supplies the database connection already established
	rows, err := cfg.GetDb().Query(qry) 
	if err != nil {
		logg.Error(err)
		return &appList
	}
	defer func(rows *sql.Rows) { 
        // simple defer does not catch every error: https://www.joeshaw.org/dont-defer-close-on-writable-files/
		err := rows.Close()
		if err != nil {
			logg.Error(err)
		}
	}(rows)
	for rows.Next() {
		err := rows.Scan(&appid)
		if err != nil {
			logg.Error(err)
			return &appList
		}
		appidList = append(appList, appid)
	}
	err = rows.Err()
	if err != nil {
		logg.Error(err)
		return &appList
	}
	return &appidList
}

My programm will be littered with queries like this. All the ways of getting the result list and how it to prevent failure make this small query hard to read what is actually going on.

Is there a way to make queries more concise?

These are my thoughts to make the code less verbose:

  • Use functions to handle the errors reducing the error handling to one line.
  • If it's one column array I want, I could pass the query and column name as parameters and reuse the query function. I rather just rewrite a query function than to deal with complicated abstractions.
  • Are there packages I missed that help reduce the clutter?

Using an ORM like gorm is NOT an option.

I just started Go programming so I am lacking experience with the language.

Below is the same query in Node.js with the same result. It has 9 lines compared to Go's 34 i.e. 65% more concise in terms of length. That's where I ideally would like to get to.

import {query} from "../db/pool"; // connection pool query from https://github.com/sidorares/node-mysql2

export const queryAppList = async () => {
  try {
    const qry = "SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);";
    const [appList] = await query(qry);
    return appList; 
  } catch (err) {
    console.error(err)
    return [];
  }
};

答案1

得分: 3

你可以创建一个Query结构体,其中包含可重用的方法来执行这样的操作。

类似于这样:

type Query struct{
    conn *sql.DB
    rows *sql.Rows
    ...
}

func NewQuery(conn *sql.DB) *Query {
    return &Query{
        conn: conn,
        rows: nil,
    }
}

func (q Query) OpenSQL(sql string) error {
    q.rows, err = q.conn.Query(sql)
    if err != nil {
        log.Error("SQL error during query ("+sql+"). "+err.Error())
        return err
    }
    return nil
}

func (q Query)Close() (error) {
    err := q.rows.Close()
    if err != nil {
        log.Error("Error closing rows. "+err.Error())
        return err
    }
    return nil
}

//You can use generic functions to make the code even smaller
func FetchToSlice[T any](q Query) ([]T, error) {
    result := make([]T, 0)
    var value T
    for q.rows.Next() {
        err := q.rows.Scan(&value)
        if err != nil {
           log.Error("Error during fetching. "+err.Error())
           return nil, err
        }
        result = append(result, value)
    }
    return result, nil
}

使用这个结构体,你的代码将如下所示:

qry := NewQuery(cfg.GetDB())
err := qry.OpenSQL("SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);")
if err != nil {
    return err
}
defer qry.Close()
appidList, err := FetchToSlice[int](qry)
if err != nil {
    return err
}

你可以随后添加更多的方法到你的Query结构体中,以处理更复杂的情况,甚至可以使用sync.Pool来缓存你的查询结构体等。

英文:

You can make a Query struct which has reusable methods for do such things.

Something like this:

type Query struct{
    conn *sql.DB
    rows *sql.Rows
    ...
}

func NewQuery(conn *sql.DB) *Query {
    return &Query{
        conn: conn,
        rows: nil,
    }
}

func (q Query) OpenSQL(sql string) error {
    q.rows, err = q.conn.Query(sql)
    if err != nil {
        log.Error("SQL error during query ("+sql+"). "+err.Error())
        return err
    }
	return nil
}

func (q Query)Close() (error) {
    err := q.rows.Close()
    if err != nil {
        log.Error("Error closing rows. "+err.Error())
        return err
    }
	return nil
}

//You can use generic functions to make the code even smaller
func FetchToSlice[T any](q Query) ([]T, error) {
    result := make([]T, 0)
    var value T
    for q.rows.Next() {
        err := q.rows.Scan(&value)
        if err != nil {
           log.Error("Error during fetching. "+err.Error())
           return nil, err
        }
		result = append(result, value)
	}
	return result, nil
} 

With this you code will look something like this:

qry := NewQuery(cfg.GetDB())
err := qry.OpenSQL("SELECT a.appid FROM app a WHERE a.app_status IN (1, 2);")
if err != nil {
	return err
}
defer qry.Close()
appidList, err := FetchToSlice[int](qry)
if err != nil {
	return err
}

You can later add more methods to your Query to handle more complex cases, even you can use a sync.Pool to cache your query structs and so on.

huangapple
  • 本文由 发表于 2022年4月10日 21:16:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/71816948.html
匿名

发表评论

匿名网友

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

确定