Go语言中查询SqlServer数据库

59519751

　　一、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 = new(interface{})
fmt.Print(cols)
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 = 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] = convertRow(*(cols.(*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
}