在Golang中动态传递SQL参数

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

Dynamically passing SQL parameters in Golang

问题

我可以帮你翻译这段内容。以下是翻译结果:

我在数据库表中有一个如下的SELECT查询语句。

select * from dbo.student where dob > @date_of_birth and grade = @grade

我将从Kafka消息中获取参数和值,如下所示,并且需要在Golang中用这个Kafka消息的值替换这两个参数。数据库表中的表名和参数可能对于每个查询都不同。因此,我需要编写一个通用函数来替换参数,无论是哪个表或者需要传递多少个参数。

{
    "message": [{
        "sql_table": "student",
        "input_params": [{
                "key": "@date_of_birth",
                "value": "2012-03-03"
            },
            {
                "key": "@grade",
                "value": "5"
            }
        ]
    }]
}
英文:

I have a SELECT query like below in the database table as constant.

select * from dbo.student where dob > @date_of_birth and grade = @grade

I will be getting the parameters and values from a Kafka message for like below and I needs to replace the two parameters with this Kafka message value in Golang. This table and parameters can be different for each query in the database table. So i need to write a generic function to replace the params if any table or any number parameters to be passed.

{
	"message": [{
		"sql_table": "student",
		"input_params": [{
				"key": "@date_of_birth",
				"value": "2012-03-03"
			},
			{
				"key": "@grade",
				"value": "5"
			}
		]
	}
  ]
}

答案1

得分: 1

首先,你需要确保你正在使用的数据库驱动程序支持命名参数(例如,go-mssqldb),然后可以按照以下步骤进行操作:

将 Kafka 消息解组为一个结构体,循环遍历输入参数以创建参数列表(类型为 []any)。在循环体中,使用每个输入参数对象创建一个 sql.NamedArg 值,然后在调用 db.Query() 方法时使用 ... 来“解包”参数。

type KafkaMessage struct {
	Message []Message `json:"message"`
}

type Message struct {
	SQLTable    string        `json:"sql_table"`
	InputParams []InputParams `json:"input_params"`
}

type InputParams struct {
	Key   string `json:"key"`
	Value any    `json:"value"`
}
func ExecQuery(db *sql.DB, m Message) (*sql.Rows, error) {
	queryString := "..." // 根据 m.SQLTable 获取正确的 SQL 查询字符串

	args := make([]any, len(m.InputParams))
	for i, pp := range m.InputParams {
		name := pp.Key[1:] // 去掉开头的 "@"
		args[i] = sql.Named(name, pp.Value)
	}

	return db.Query(queryString, args...)
}

// ...

var km KafkaMessage // 将 Kafka 消息解组为 km
rows, err := ExecQuery(db, km.Message[0])
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    // ...
}
英文:

First you need to make sure the db driver that you're using does have support for named parameters (go-mssqldb does, for example), then you can do the following:

Unmarshal the kafka message into a struct, loop over the input params to create a list of args (of type []any), in the loop's body use each input params object to create an sql.NamedArg value, then use ... to "unpack" the args when calling the db.Query() method.

type KafkaMessage struct {
	Message []Message `json:"message"`
}

type Message struct {
	SQLTable    string        `json:"sql_table"`
	InputParams []InputParams `json:"input_params"`
}

type InputParams struct {
	Key   string `json:"key"`
	Value any    `json:"value"`
}
func ExecQuery(db *sql.DB, m Message) (*sql.Rows, error) {
	queryString := "..." // based on m.SQLTable get the correct SQL query string

	args := make([]any, len(m.InputParams))
	for i, pp := range m.InputParams {
		name := pp.Key[1:] // drop the leading "@"
		args[i] = sql.Named(name, pp.Value)
	}

	return db.Query(queryString, args...)
}

// ...

var km KafkaMessage // unmarshal the kafka message into km
rows, err := ExecQuery(db, km.Message[0])
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    // ...
}

huangapple
  • 本文由 发表于 2023年4月4日 17:38:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75927824.html
匿名

发表评论

匿名网友

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

确定