golang查询sql动态封装并转json

 

package main

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

	_ "github.com/microsoft/go-mssqldb"
)

func TestToJson1(t *testing.T) {
	// 连接数据库
	db, err := sql.Open("mssql", "connection_string")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 执行查询
	rows, err := db.Query("SELECT * FROM your_table")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		panic(err)
	}

	// 构建结果集
	var results []map[string]interface{}

	// 处理查询结果
	for rows.Next() {
		// 创建一个存储每列值的切片
		values := make([]interface{}, len(columns))
		// 创建一个存储每列指针的切片
		pointers := make([]interface{}, len(columns))
		for i := range values {
			pointers[i] = &values[i]
		}
		/*for idx, _ := range pointers {
			pointers[idx] = new(interface{})
		}*/
		// 读取行数据
		err := rows.Scan(pointers...)
		if err != nil {
			fmt.Println("读取行数据出错:", err)
			return
		}

		// 将每一行的值转换为 map[string]interface{}
		rowData := make(map[string]interface{})
		for i, colName := range columns {
            colName = underscoreToCamelCase(colName)
			// 获取每列的值
			val := values[i]
			// 检查值的类型并进行类型转换
			switch v := val.(type) {
			case nil:
				rowData[colName] = nil
			case []byte:
				rowData[colName] = string(v)
			default:
				rowData[colName] = v
			}
		}

		// 将每一行的数据添加到结果集中
		results = append(results, rowData)
	}

	// 将结果集转换为 JSON
	jsonData, err := json.Marshal(results)
	if err != nil {
		fmt.Println("转换为 JSON 出错:", err)
		return
	}

	// 打印 JSON 结果
	fmt.Println(string(jsonData))
}
func underscoreToCamelCase(underscore string) string {
	words := strings.Split(underscore, "_")
	for i := 1; i < len(words); i++ {
		if words[i] == "" {
			words[i] = ""
			continue
		}
		words[i] = strings.ToUpper(words[i][0:1]) + words[i][1:]
	}
	return strings.Join(words, "")
}