Golang 使用SQLX实现可选条件查询-526互联

package main

import (
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type CityQuery struct {
	query  string
	opts   cityQueryOptions
	params []any
}

type cityQueryOptions struct {
	Name        string
	CountryCode string
}

// func NewQueryBuilder(query string) *CityQuery {
// query = "SELECT * FROM city"
func NewQueryBuilder(query string) *CityQuery {
	query += " WHERE 1 = 1 "
	qb := &CityQuery{
		query: query,
		opts:  cityQueryOptions{},
	}
	return qb
}

type CustomQueryResult struct {
	ID          int    `db:"ID"`
	Name        string `db:"Name"`
	CountryCode string `db:"CountryCode"`
	District    string `db:"District"`
	Population  int    `db:"Population"`
}

func (qb *CityQuery) addOption(option string, value any) *CityQuery {
	qb.query += fmt.Sprintf(" AND %s = ?", option)
	qb.params = append(qb.params, value)
	return qb
}

func (qb *CityQuery) WithName(name string) *CityQuery {
	return qb.addOption("Name", name)
}
func (qb *CityQuery) WithCountryCode(code string) *CityQuery {
	return qb.addOption("CountryCode", code)
}

func (qb *CityQuery) Query(db *sqlx.DB) ([]CustomQueryResult, error) {
	log.Println(qb.query)
	stmt, err := db.Preparex(qb.query)
	if err != nil {
		log.Println(err)
		return nil, err
	}
	defer stmt.Close()
	var results []CustomQueryResult
	err = stmt.Select(&results, qb.params...)
	if err != nil {
		log.Println(err)
		return nil, err
	}

	return results, nil
}

func main() {
	db := sqlx.MustOpen("mysql", "root:password@tcp(127.0.0.1:3306)/world?charset=utf8mb4&parseTime=True")
	query := NewQueryBuilder("SELECT * FROM city").WithCountryCode("CHN").WithName("1=1")
	results, err := query.Query(db)

	if err != nil {
		log.Println(err)
		return
	}
	for idx := range results {
		fmt.Println(results[idx])
	}
}