前文
golang接触也有一段时间,项目中有用到web api,基本上就是post json格式的,本想用java来写,刚下手想到java太臃肿,各种繁琐。
觉得用golang小试一把,于是github一把,还是发现很多go rest 插件,选了一个https://github.com/ant0ine/go-json-rest
一根烟后,go-json-rest demo开始跑起来,使用curl命令模拟了一把,正确运行。
关于go-json-rest的使用,本文不做描述,官方文档有很详细的说明
https://github.com/ant0ine/go-json-rest
正文
这是封装数据库的连接的核心代码(其实大部分是网上copy的)
此方法是将sql的查询结果封装成json格式输出(当然是方便post返回值)
func openDbString(sqlstring string) string {
conn := openDb()
defer conn.Close()
stmt, err := conn.Prepare(sqlstring)
if err != nil {
fmt.Println("Query Error", err)
return "Error"
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
fmt.Println("Query Error", err)
return "Error"
}
defer rows.Close()
// Get column names
columns, err := rows.Columns()
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Make a slice for the values
values := make([]sql.RawBytes, len(columns))
// rows.Scan wants '[]interface{}' as an argument, so we must copy the
// references into such a slice
// See http://code.google.com/p/go-wiki/wiki/InterfaceSlice for details
scanArgs := make([]interface{}, len(values))
for i := range values {
scanArgs[i] = &values[i]
}
// Fetch rows
var jsonstring string
jsonstring = "{\"timestamp\": \"" + time.Now().Format("2006-01-02 15:04:05") + "\",\"data\":["
allcount := 0
for rows.Next() {
jsonstring += "{"
// get RawBytes from data
err = rows.Scan(scanArgs...)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// Now do something with the data.
// Here we just print each column as a string.
var value string
for i, col := range values {
// Here we can check if the value is nil (NULL value)
if col == nil {
value = "NULL"
} else {
value = string(col)
}
// fmt.Println(columns[i], ": ", value)
if i == len(values)-1 {
jsonstring += "\"" + columns[i] + "\":\"" + value + "\""
} else {
jsonstring += "\"" + columns[i] + "\":\"" + value + "\","
}
// fmt.Println(" :", i, ": ", col, len(values))
}
//fmt.Println("replace before :", jsonstring, ": ", len(jsonstring))
//jsonstring = strings.Replace(jsonstring, ",", " ", len(jsonstring))
//fmt.Println("replace after :", jsonstring, ": ", len(jsonstring))
// fmt.Println("-----------------------------------", allcount)
jsonstring += "},"
allcount++
}
if allcount > 0 {
jsonstring = Substr(jsonstring, 0, len(jsonstring)-1)
}
jsonstring += "]}"
if err = rows.Err(); err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
return jsonstring
}
接下来来记录sql查询结果的坑
网上的有很多golang查询数据库的栗子,但是都是简单的使用
而我却被坑了好几次,细述如下
1.时间函数的坑
由于在sql字段定义的datetime,直接使用getdate()运行起来报错
sql: Scan error on column index 0: unsupported driver -> Scan pair: time.Time -> *sql.RawBytes
OK,抽了根去,将datetime转换层char,于是
returndata := openDbString("select top 1 CONVERT(CHAR(23), createtime, 121) as createtime from ATRes ")
fmt.Println("result:", returndata)
返回结果如下
{"timestamp": "2015-06-11 11:51:22","data":[{"createtime":"2015-05-06 1"}]}
结果是时间被截断了,再次尝试
returndata := openDbString("select top 1 CONVERT(CHAR(36), createtime, 121) as createtime from ATRes ")
fmt.Println("result:", returndata)
返回结果如下,这次OK
{"timestamp": "2015-06-11 11:53:53","data":[{"createtime":"2015-05-06 16:15:42"}]}
2.长文本被截断的坑
returndata := openDbString("select top 1 data from ATRes ")
fmt.Println("result:", returndata)
返回结果如下
{"timestamp": "2015-06-11 11:57:10","data":[{"data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}
实际上data字段在数据库里面是一个图片的url,但是输出结果里面,url被截断了(.jpg丢失了)
猜测下,可能是sql数据类型和golang的读取数据类型不一致导致的,由于sql里面data是nvacher(100)的类型,而输出的是截断的长度,哥哥我果断数了一下data的长度,刚好是50,so 50=?100/2
再次修改查询语句验证猜想
returndata := openDbString("select top 1 cast(data as CHAR(200)) as datacopy,data from ATRes ")
fmt.Println("result:", returndata)
返回结果如下
{"timestamp": "2015-06-11 12:01:54","data":[{"datacopy":"http://jixieshi999.github.io/ilife/images/mamabeat.jpg。。。。。。。。。。。。。。。","data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}
注意,datacopy后面的若干空格我用。代替方便查看,这种结果还是不满足我的胃口,不可能有这么多空格在json里面,这不科学
于是我又倒腾了一把
returndata := openDbString("select top 1 rtrim(cast(data as CHAR(200))) as datacopy,data from ATRes ")
fmt.Println("result:", returndata)
返回结果如下,终于OK了
{"timestamp": "2015-06-11 12:05:22","data":[{"datacopy":"http://jixieshi999.github.io/ilife/images/mamabeat.jpg","data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}
关于golang获取当前时间的坑,百度下就有了
后记
总结下来golang对于数据库的支持还是没有java方便,也有本人对golang的了解不够深入的问题
不过用golang做前文说的restful api实在是太方便了
又臭又长的源代码就不上传了
有疑问加站长微信联系(非本文作者)