Golang查询SqlServer数据库

Go语言中查询SqlServer数据库

一、Go语言中查询MsSQL数据库:

// 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()

//产生查询语句的Statement

stmt, err := conn.Prepare(`select * from [account_region]`)

if err != nil {

log.Fatal("Prepare failed:", err.Error())

}

defer stmt.Close()

//通过Statement执行查询

rows, err := stmt.Query()

if err != nil {

log.Fatal("Query failed:", err.Error())

}

//建立一个列数组

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...) //将查到的数据写入到这行中

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

三、使用实体实现的方法:

// 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()

//通过连接对象执行查询

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)

}

//打印数组

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的实现方式

// 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())

}

//通过连接对象执行查询

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)

}

//打印数组

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()

}

}

五、最终转为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())

}

//通过连接对象执行查询

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)

}

//打印数组

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

}

有疑问加站长微信联系(非本文作者)

• 请尽量让自己的回复能够对别人有帮助
• 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
• 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
• 图片支持拖拽、截图粘贴等方式上传