Go語言中查詢SqlServer數(shù)據(jù)庫
一秦士、Go語言中查詢MsSQL數(shù)據(jù)庫：
// main.go
package main
import (
? ? "database/sql"
? ? "fmt"
? ? "log"
? ? "time"
? ? _ "github.com/denisenkom/go-mssqldb"
)
func main() {
? ? var isdebug = true
? ? var server = "localhost"
? ? var port = 1433
? ? var user = "sa"
? ? var password = "123456xx"
? ? var database = "MyTestDB"
? ? //連接字符串
? ? connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", server, port, database, user, password)
? ? if isdebug {
? ? ? ? fmt.Println(connString)
? ? }
? ? //建立連接
? ? conn, err := sql.Open("mssql", connString)
? ? if err != nil {
? ? ? ? log.Fatal("Open Connection failed:", err.Error())
? ? }
? ? defer conn.Close()
? ? //產(chǎn)生查詢語句的Statement
? ? stmt, err := conn.Prepare(`select * from [account_region]`)
? ? if err != nil {
? ? ? ? log.Fatal("Prepare failed:", err.Error())
? ? }
? ? defer stmt.Close()
? ? //通過Statement執(zhí)行查詢
? ? rows, err := stmt.Query()
? ? if err != nil {
? ? ? ? log.Fatal("Query failed:", err.Error())
? ? }
? ? //建立一個列數(shù)組
? ? cols, err := rows.Columns()
? ? var colsdata = make([]interface{}, len(cols))
? ? for i := 0; i < len(cols); i++ {
? ? ? ? colsdata[i] = new(interface{})
? ? ? ? fmt.Print(cols[i])
? ? ? ? fmt.Print("\t")
? ? }
? ? fmt.Println()
? ? //遍歷每一行
? ? for rows.Next() {
? ? ? ? rows.Scan(colsdata...) //將查到的數(shù)據(jù)寫入到這行中
? ? ? ? PrintRow(colsdata)? ? //打印此行
? ? }
? ? defer rows.Close()
}
//打印一行記錄羹铅，傳入一個行的所有列信息
func PrintRow(colsdata []interface{}) {
? ? for _, val := range colsdata {
? ? ? ? switch v := (*(val.(*interface{}))).(type) {
? ? ? ? case nil:
? ? ? ? ? ? fmt.Print("NULL")
? ? ? ? case bool:
? ? ? ? ? ? if v {
? ? ? ? ? ? ? ? fmt.Print("True")
? ? ? ? ? ? } else {
? ? ? ? ? ? ? ? fmt.Print("False")
? ? ? ? ? ? }
? ? ? ? case []byte:
? ? ? ? ? ? fmt.Print(string(v))
? ? ? ? case time.Time:
? ? ? ? ? ? fmt.Print(v.Format("2016-01-02 15:05:05.999"))
? ? ? ? default:
? ? ? ? ? ? fmt.Print(v)
? ? ? ? }
? ? ? ? fmt.Print("\t")
? ? }
? ? fmt.Println()
}
二僵朗、效果：
server=localhost;port1433;database=MyTestDB;user id=sa;password=123456xx
region_id? ? provider_id? ? region_name? ? billing_region_name? ? description? ?
1? ? 5? ? us-east-1? ? US-EAST? ? AWS US EAST Data Center? ?
2? ? 5? ? us-west-2? ? US_WEST_OREGON? ? AWS Oregon Data Center? ?
3? ? 5? ? ap-southeast-1? ? ASIA_SIGN? ? AWS Singapore Data Center? ?
4? ? 5? ? ap-southeast-2? ? ASIA_SYDENY? ? AWS Sydney Data Center? ?
5? ? 5? ? ap-northeast-1? ? ASIA_TOKYO? ? AWS Tokyo Data Center? ?
6? ? 5? ? eu-central-1? ? EU_FRANKFURT? ? AWS Frankfurt Data Center? ?
7? ? 5? ? eu-west-1? ? EU_IRELAND? ? AWS Europe Data Center? ?
8? ? 5? ? us-west-1? ? US_WEST_CA? ? AWS CA Data Center? ?
9? ? 5? ? sa-east-1? ? SOUA_SAOP? ? AWS Sao Paulo Data Center? ?
10? ? 5? ? ap-northeast-2? ? ASIA_SEOUL? ? AWS Seoul Data Center? ?
11? ? 5? ? ap-south-1? ? ASIA_MUMBAI? ? AWS Mumbai Data Center? ?
12? ? 2? ? Central US? ? US-CENTRAL? ? Azure Center US Data Center? ?
13? ? 2? ? North Central US? ? US-NORTH-CENTRAL? ? Azure North US Data Center? ?
14? ? 2? ? East US? ? US-EAST? ? Azure East US Data Center? ?
15? ? 2? ? South Central US? ? US-SOUTH-CENTRAL? ? Azure South US Data Center? ?
16? ? 2? ? West US? ? US-WEST? ? Azure West US Data Center? ?
17? ? 2? ? North Europe? ? EUROPE-NORTH? ? Azure North Europe Data Center? ?
18? ? 2? ? West Europe? ? EUROPE-WEST? ? Azure North Europe Data Center? ?
19? ? 2? ? East Asia? ? ASIA-PACIFIC-EAST? ? Azure East Aisa Data Center? ?
20? ? 2? ? Southeast Asia? ? ASIA-PACIFIC-SOUTHEAST? ? Azure Singapore Data Center? ?
21? ? 2? ? Japan East? ? JAPAN-EAST? ? Azure East Japan Data Center? ?
22? ? 2? ? Japan West? ? JAPAN-WEST? ? Azure West Japan Data Center? ?
23? ? 2? ? Brazil South? ? BRAZIL-SOUTH? ? Azure Sao Paulo Data Center? ?
24? ? 2? ? Australia East? ? AUSTRALIA-EAST? ? Azure East Australia Data Center? ?
25? ? 2? ? Australia Southeast? ? AUSTRALIA-SOUTHEAST? ? Azure Southeast Australia Data Center? ?
26? ? 2? ? East US 2? ? US-EAST-2? ? Azure East US Data Center 2? ?
27? ? 2? ? US Gov Virginia? ? USGOV-VIRGINIA? ? Azure US Virginia Government Data Center? ?
28? ? 2? ? US Gov Iowa? ? USGOV-IOWA? ? Azure US Iowa Government Data Center? ?
29? ? 2? ? Canada Central? ? CANADA-CENTRAL? ? Azure Central Canada? ?
30? ? 2? ? Canada East? ? CANADA-EAST? ? Azure East Canada? ?
31? ? 2? ? Germany Central? ? GERMANY-CENTRAL? ? Azure Central Germany? ?
32? ? 2? ? Germany Northeast? ? GERMANY-NORTHEAST? ? Azure Northeast Germany? ?
33? ? 2? ? Korea Central? ? KOREA-CENTRAL? ? Azure Central Korea? ?
34? ? 3? ? China North? ? CN-BEIJING? ? Azure Mooncake Beijing Data Center? ?
35? ? 3? ? China East? ? CN-SHANGHAI? ? Azure Mooncake Shanghai Data Center? ?
36? ? 4? ? cn-hangzhou? ? CN_HANGZHOU? ? Aliyun Hangzhou Data Center? ?
37? ? 4? ? cn-beijing? ? CN_BEIJING? ? Aliyun Beijing Data Center? ?
38? ? 4? ? cn-shenzhen? ? CN_SHENZHEN? ? Aliyun Shenzhen Data Center? ?
39? ? 4? ? cn-qingdao? ? CN_QINGDAO? ? Aliyun Qingdao Data Center? ?
40? ? 4? ? cn-hongkong? ? HONGKONG? ? Aliyun Hong Kong Data Center? ?
41? ? 4? ? us-silicon-valley? ? US-Silicon_Valley? ? Aliyun Silicon Valley Data Center? ?
三纽疟、使用實體實現(xiàn)的方法：
// main.go
package main
import (
? ? "database/sql"
? ? "fmt"
? ? "log"
? ? _ "github.com/denisenkom/go-mssqldb"
)
type AccessRegion struct {
? ? region_id? ? ? ? ? int64
? ? provider_id? ? ? ? int64
? ? region_name? ? ? ? string
? ? sub_region_names? ? string
? ? billing_region_name string
? ? description? ? ? ? string
}
func main() {
? ? var server = "localhost"
? ? var port = 1433
? ? var user = "sa"
? ? var password = "123456xxx"
? ? var database = "MyTestDB"
? ? //連接字符串
? ? connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", server, port, database, user, password)
? ? //建立連接
? ? db, err := sql.Open("mssql", connString)
? ? if err != nil {
? ? ? ? log.Fatal("Open Connection failed:", err.Error())
? ? }
? ? defer db.Close()
? ? //通過連接對象執(zhí)行查詢
? ? rows, err := db.Query(`select * from [account_region]`)
? ? if err != nil {
? ? ? ? log.Fatal("Query failed:", err.Error())
? ? }
? ? defer rows.Close()
? ? var rowsData []*AccessRegion
? ? //遍歷每一行
? ? for rows.Next() {
? ? ? ? var row = new(AccessRegion)
? ? ? ? rows.Scan(&row.region_id, &row.provider_id, &row.region_name, &row.billing_region_name, &row.description)
? ? ? ? rowsData = append(rowsData, row)
? ? }
? ? //打印數(shù)組
? ? for _, ar := range rowsData {
? ? ? ? fmt.Print(ar.region_id, "\t", ar.provider_id, "\t", ar.region_name, "\t", ar.billing_region_name, "\t", ar.description)
? ? ? ? fmt.Println()
? ? }
}
四斥滤、使用ODBC的實現(xiàn)方式
// main.go
package main
import (
? ? "database/sql"
? ? "fmt"
? ? "log"
? ? _ "github.com/alexbrainman/odbc"
)
type AccessRegion struct {
? ? region_id? ? ? ? ? int64
? ? provider_id? ? ? ? int64
? ? region_name? ? ? ? string
? ? sub_region_names? ? string
? ? billing_region_name string
? ? description? ? ? ? string
}
func main() {
? ? db, err := sql.Open("odbc", "driver={sql server};server=localhost;port=1433;uid=sa;pwd=123456xxx;database=MyTestDB")
? ? if err != nil {
? ? ? ? fmt.Printf(err.Error())
? ? }
? ? //通過連接對象執(zhí)行查詢
? ? rows, err := db.Query(`select * from [account_region]`)
? ? if err != nil {
? ? ? ? log.Fatal("Query failed:", err.Error())
? ? }
? ? defer rows.Close()
? ? var rowsData []*AccessRegion
? ? //遍歷每一行
? ? for rows.Next() {
? ? ? ? var row = new(AccessRegion)
? ? ? ? rows.Scan(&row.region_id, &row.provider_id, &row.region_name, &row.billing_region_name, &row.description)
? ? ? ? rowsData = append(rowsData, row)
? ? }
? ? //打印數(shù)組
? ? for _, ar := range rowsData {
? ? ? ? fmt.Print(ar.region_id, "\t", ar.provider_id, "\t", ar.region_name, "\t", ar.billing_region_name, "\t", ar.description)
? ? ? ? fmt.Println()
? ? }
}
五烂琴、最終轉(zhuǎn)為Map集合
// main.go
package main
import (
? ? "database/sql"
? ? "fmt"
? ? "log"
? ? _ "github.com/alexbrainman/odbc"
? ? "github.com/demdxx/gocast"
)
func main() {
? ? db, err := sql.Open("odbc", "driver={sql server};server=localhost;port=1433;uid=sa;pwd=123456xxx;database=MyTestDB")
? ? if err != nil {
? ? ? ? fmt.Printf(err.Error())
? ? }
? ? //通過連接對象執(zhí)行查詢
? ? rows, err := db.Query(`select * from [account_region]`)
? ? if err != nil {
? ? ? ? log.Fatal("Query failed:", err.Error())
? ? }
? ? defer rows.Close()
? ? //遍歷每一行
? ? colNames, _ := rows.Columns()
? ? var cols = make([]interface{}, len(colNames))
? ? for i := 0; i < len(colNames); i++ {
? ? ? ? cols[i] = new(interface{})
? ? }
? ? var maps = make([]map[string]interface{}, 0)
? ? for rows.Next() {
? ? ? ? err := rows.Scan(cols...)
? ? ? ? if err != nil {
? ? ? ? ? ? log.Fatal(err.Error())
? ? ? ? }
? ? ? ? var rowMap = make(map[string]interface{})
? ? ? ? for i := 0; i < len(colNames); i++ {
? ? ? ? ? ? rowMap[colNames[i]] = convertRow(*(cols[i].(*interface{})))
? ? ? ? }
? ? ? ? maps = append(maps, rowMap)
? ? }
? ? //打印數(shù)組
? ? for _, rowMap := range maps {
? ? ? ? for k, v := range rowMap {
? ? ? ? ? ? fmt.Print(k, ":", v, "\t")
? ? ? ? }
? ? ? ? fmt.Println()
? ? }
}
func convertRow(row interface{}) interface{} {
? ? switch row.(type) {
? ? case int:
? ? ? ? return gocast.ToInt(row)
? ? case string:
? ? ? ? return gocast.ToString(row)
? ? case []byte:
? ? ? ? return gocast.ToString(row)
? ? case bool:
? ? ? ? return gocast.ToBool(row)
? ? }
? ? return row
}
