go-db/functions/query_json.go

package functions

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"git.x2erp.com/qdy/go-base/ctx"
	"git.x2erp.com/qdy/go-base/types"
	"github.com/jmoiron/sqlx"
)

// QueryParamsNameToJSON 执行带命名参数的查询
func QueryParamsNameToJSON(db *sqlx.DB, sql string, params map[string]interface{}, reqCtx *ctx.RequestContext) *types.QueryResult {
	startTime := time.Now()

	if sql == "" {
		return createErrorResult("SQL query cannot be empty", startTime, reqCtx)
	}

	// 处理命名参数
	query, args, err := sqlx.Named(sql, params)
	if err != nil {
		return createErrorResult(fmt.Sprintf("Failed to process named parameters: %v", err), startTime, reqCtx)
	}

	// 执行查询
	rows, err := db.Query(sqlx.Rebind(sqlx.DOLLAR, query), args...)
	//rows, err := sqlx.NamedQuery(db, sql, params)
	if err != nil {
		return createErrorResult(fmt.Sprintf("Query execution failed: %v", err), startTime, reqCtx)
	}
	defer rows.Close()

	// 处理结果集
	return processQueryResult(rows, startTime)
}

// QueryPositionalToJSON 执行带位置参数的查询
func QueryPositionalToJSON(db *sqlx.DB, sql string, params []interface{}, reqCtx *ctx.RequestContext) *types.QueryResult {
	startTime := time.Now()

	//fmt.Printf("positionalParams: %s", params)
	//fmt.Printf("sql: %s", sql)
	if sql == "" {
		return createErrorResult("SQL query cannot be empty", startTime, reqCtx)
	}

	// 执行查询
	rows, err := db.Query(sql, params...)
	if err != nil {
		return createErrorResult(fmt.Sprintf("Query execution failed: %v", err), startTime, reqCtx)
	}
	defer rows.Close()

	// 处理结果集
	return processQueryResult(rows, startTime)
}

// QueryToJSON 执行无参数的查询
func QueryToJSON(db *sqlx.DB, sql string, reqCtx *ctx.RequestContext) *types.QueryResult {
	startTime := time.Now()

	if sql == "" {
		return createErrorResult("SQL query cannot be empty", startTime, reqCtx)
	}

	// 执行查询
	rows, err := db.Query(sql)
	if err != nil {
		return createErrorResult(fmt.Sprintf("Query execution failed: %v", err), startTime, reqCtx)
	}
	defer rows.Close()

	// 处理结果集
	return processQueryResult(rows, startTime)
}

// processQueryResult 处理查询结果（公共部分）
func processQueryResult(rows *sql.Rows, startTime time.Time) *types.QueryResult {
	result := &types.QueryResult{}

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		result.Success = false
		result.Error = fmt.Sprintf("Failed to get columns: %v", err)
		result.Time = time.Since(startTime).String()
		return result
	}

	var results []map[string]interface{}
	count := 0

	// 遍历所有行
	for rows.Next() {
		count++
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range columns {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			result.Success = false
			result.Error = fmt.Sprintf("Failed to scan row: %v", err)
			result.Time = time.Since(startTime).String()
			return result
		}

		resultMap := make(map[string]interface{})
		for i, col := range columns {
			// 完全不处理类型，直接赋值，让 json.Marshal 自己处理
			resultMap[col] = values[i]
		}
		results = append(results, resultMap)
	}

	// 检查行遍历错误
	if err := rows.Err(); err != nil {
		result.Success = false
		result.Error = fmt.Sprintf("Row iteration error: %v", err)
		result.Time = time.Since(startTime).String()
		return result
	}

	// 转换为JSON
	jsonData, err := json.Marshal(results)
	if err != nil {
		result.Success = false
		result.Error = fmt.Sprintf("JSON marshal failed: %v", err)
		result.Time = time.Since(startTime).String()
		return result
	}

	// 构建成功结果
	result.Success = true
	result.Data = map[string]interface{}{
		"json":  string(jsonData),
		"rows":  results,
		"count": count,
	}
	result.Count = count
	result.Time = time.Since(startTime).String()
	return result
}

// createErrorResult 创建错误结果的辅助函数
func createErrorResult(errorMsg string, startTime time.Time, reqCtx *ctx.RequestContext) *types.QueryResult {
	return &types.QueryResult{
		Success:  false,
		Error:    errorMsg,
		Time:     time.Since(startTime).String(),
		Metadata: reqCtx,
	}
}