Go语言中查询SqlServer数据库

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

// main.go package mainimport ("database/sql""fmt""log""time"_ "github.com/denisenkom/go-mssqldb" )func main() {var isdebug = truevar server = "localhost"var port = 1433var 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()//产生查询语句的Statementstmt, 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 mainimport ("database/sql""fmt""log"_ "github.com/denisenkom/go-mssqldb" )type AccessRegion struct {region_id int64provider_id int64region_name stringsub_region_names stringbilling_region_name stringdescription string }func main() {var server = "localhost"var port = 1433var 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 mainimport ("database/sql""fmt""log"_ "github.com/alexbrainman/odbc" )type AccessRegion struct {region_id int64provider_id int64region_name stringsub_region_names stringbilling_region_name stringdescription 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 mainimport ("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 }

总结

以上是生活随笔为你收集整理的Go语言中查询SqlServer数据库的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。