12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322 |
- package restful
- import (
- "database/sql"
- "fmt"
- "log"
- "math"
- "reflect"
- "strconv"
- "strings"
- "time"
- "unsafe"
- "github.com/pkg/errors"
- _ "github.com/go-sql-driver/mysql"
- "github.com/jmoiron/sqlx"
- "github.com/patrickmn/go-cache"
- "github.com/tealeg/xlsx"
- "github.com/xormplus/xorm/schemas"
- "kpt.xdmy/pkg/setting"
- )
- var (
- Dbs *sqlx.DB
- Apisql_cache *cache.Cache
- )
- // SQLInit 初始化数据库操作句柄,这里要提供:
- // driverName string: 数据库类型,例如mysql、sqlite等,参考github.com/go-sql-driver/mysql官方介绍
- // dataSourceName string: 数据库地址,参考github.com/go-sql-driver/mysql官方介绍
- // MaxOpenConns int: 最大缓存连接数,这个数值包含了MaxIdleConns
- // MaxIdleConns int:预备的最大空闲连接数
- func SQLInit(driverName, dataSourceName string, maxOpenConns, maxIdleConns int, showlog bool) error {
- if Dbs == nil {
- var err error
- if Dbs, err = sqlx.Open(driverName, dataSourceName); err != nil {
- return err
- }
- Dbs.SetMaxOpenConns(maxOpenConns)
- Dbs.SetMaxIdleConns(maxIdleConns)
- Dbs.SetConnMaxLifetime(300 * time.Second)
- }
- if Apisql_cache == nil {
- Apisql_cache = cache.New(600*time.Second, 10*time.Second)
- }
- // SetCacheSql()
- return nil
- }
- func SetCacheSql() {
- rows, err := Dbs.Queryx("SELECT sqlname, CONCAT(sqlstr,'|',IFNULL(params,'')) AS sqlstr FROM apisql WHERE ENABLE>0 ")
- if err != nil {
- return
- }
- valuesMap, err := Rows2Strings(rows)
- if err == nil && len(valuesMap) > 0 {
- for _, v := range valuesMap {
- Apisql_cache.Set(v["sqlname"], v["sqlstr"], cache.DefaultExpiration)
- }
- }
- }
- func sqlCheckParam(param string) error {
- if strings.Contains(param, ";") {
- return errors.New("can not have ;")
- }
- return nil
- }
- func GetSqlByName(name string, tx *sqlx.Tx) (string, string) {
- sqls := ""
- param := ""
- if sql, ok := Apisql_cache.Get(name); ok {
- tempstrb := strings.IndexAny(sql.(string), "|")
- sqls = sql.(string)[0:tempstrb]
- param = sql.(string)[tempstrb+1:]
- } else {
- valuesMap, err := MapStr("SELECT sqlstr, params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
- if err != nil {
- return err.Error(), "err"
- }
- if len(valuesMap) > 0 {
- sqls = valuesMap["sqlstr"]
- param = valuesMap["params"]
- // Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
- }
- }
- if setting.DatabaseSetting.ShowGetSqllog {
- fmt.Println("==============")
- fmt.Println("apisql:" + sqls)
- fmt.Println("--------------")
- fmt.Println("param:" + param)
- fmt.Println("==============")
- }
- return sqls, param
- }
- func GetSqlByNameDB(name string) (string, string) {
- var sqls, param string
- if sql, ok := Apisql_cache.Get(name); ok {
- tempstrb := strings.IndexAny(sql.(string), "|")
- sqls = sql.(string)[0:tempstrb]
- param = sql.(string)[tempstrb+1:]
- } else {
- rows, err := Dbs.Queryx("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
- if err != nil {
- return err.Error(), "err"
- }
- valuesMap, err := Rows2Strings(rows)
- if err == nil && len(valuesMap) > 0 {
- sqls = valuesMap[0]["sqlstr"]
- param = valuesMap[0]["params"]
- // Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
- }
- }
- if setting.DatabaseSetting.ShowGetSqllog {
- fmt.Println("==============")
- fmt.Println("apisql:" + sqls)
- fmt.Println("--------------")
- fmt.Println("param:" + param)
- fmt.Println("==============")
- }
- return sqls, param
- }
- func GetSqlByNameDBT(name string, tx *sqlx.Tx) (string, string) {
- var sqls, param string
- if sql, ok := Apisql_cache.Get(name); ok {
- tempstrb := strings.IndexAny(sql.(string), "|")
- sqls = sql.(string)[0:tempstrb]
- param = sql.(string)[tempstrb+1:]
- } else {
- rows, err := Dbs.Queryx("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
- if err != nil {
- log.Default().Printf("GetSqlByNameDBT :%v", err)
- return "", ""
- }
- if rows.Next() {
- rows.Scan(&sqls, ¶m)
- }
- if sqls != "" {
- // Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
- }
- }
- if setting.DatabaseSetting.ShowGetSqllog {
- fmt.Println("==============")
- fmt.Println("apisql:" + sqls)
- fmt.Println("--------------")
- fmt.Println("param:" + param)
- fmt.Println("==============")
- }
- return sqls, param
- }
- // sqlGetValues 根据结构体中指向实际数据的指针获取出数据,并存储到另一张表中返回
- func sqlGetValues(pvs []interface{}, columnsType []*sql.ColumnType, columnsLen int) map[string]interface{} {
- result := make(map[string]interface{}, columnsLen)
- for a := 0; a < columnsLen; a++ {
- switch s := pvs[a].(type) {
- case *int8:
- result[columnsType[a].Name()] = *s
- case *int16:
- result[columnsType[a].Name()] = *s
- case *int32:
- result[columnsType[a].Name()] = *s
- case *int64:
- result[columnsType[a].Name()] = *s
- case *float32:
- result[columnsType[a].Name()] = *s
- case *float64:
- result[columnsType[a].Name()] = *s
- case *string:
- result[columnsType[a].Name()] = *s
- case *sql.NullInt64:
- result[columnsType[a].Name()] = *s
- case *sql.NullString:
- result[columnsType[a].Name()] = *s
- }
- }
- return result
- }
- // 这里返回的是原始数组的基础上加上了字段名标识
- func sqlQuery(columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) ([]map[string]interface{}, error) {
- jsondata := make([]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- jsondata[k1] = sqlGetValues(v1, columnsType, columnsLen)
- }
- return jsondata, nil
- }
- func sqlQueryByTinyIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int8]map[string]interface{}, error) {
- jsondata := make(map[int8]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*int8); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryBySmallIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int16]map[string]interface{}, error) {
- jsondata := make(map[int16]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*int16); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryByIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int32]map[string]interface{}, error) {
- jsondata := make(map[int32]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*int32); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryByBigIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int64]map[string]interface{}, error) {
- jsondata := make(map[int64]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*int64); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryByFloatIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[float32]map[string]interface{}, error) {
- jsondata := make(map[float32]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*float32); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryByDoubleMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[float64]map[string]interface{}, error) {
- jsondata := make(map[float64]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*float64); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlQueryByStringMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[string]map[string]interface{}, error) {
- jsondata := make(map[string]map[string]interface{}, queryCount)
- for k1, v1 := range queryData {
- if k1 >= queryCount {
- break
- }
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == columnName {
- if value, ok := v1[a].(*string); ok {
- jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
- }
- break
- }
- }
- }
- return jsondata, nil
- }
- func sqlGetColumnType(columnsType []*sql.ColumnType, columnsLen int, valueName string) string {
- for a := 0; a < columnsLen; a++ {
- if columnsType[a].Name() == valueName {
- return columnsType[a].DatabaseTypeName()
- }
- }
- return ""
- }
- func round(num float64) int {
- return int(num + math.Copysign(0.5, num))
- }
- func toFixed(num float64, precision int) float64 {
- output := math.Pow(10, float64(precision))
- return float64(round(num*output)) / output
- }
- // QueryByMap 将查询到的数据,按照指定字段的值做为索引构建map并返回
- // columnName string: 作为索引的字段名称
- // sql string: 查询需要获取哪些字段的值,就是select后面跟着的部分,一般用"*"
- // where string: 过滤条件,就是where后面跟着的部分
- // order string: 排序条件,就是order by后面跟着的部分。默认是ASC排序,除非"-"开头则DESC排序
- // offset string: limit后面逗号相隔的两个数值,前者就是offset,后者就是count
- // count string: limit后面逗号相隔的两个数值,前者就是offset,后者就是count
- func QueryByMap(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- recordcount := int64(0)
- if count > 1 {
- sqlstr = fmt.Sprintf("SELECT COUNT(*) as count FROM (%s) a", sqlstr)
- err := BasicList(sqlstr, params, &recordcount)
- if err != nil {
- fmt.Println("s1", err)
- return nil, err
- }
- }
- queryData := make([]map[string]interface{}, 1)
- var err error
- if count == 0 {
- rows, err := Dbs.Queryx(sqlstr, params...)
- if err != nil {
- return err.Error(), err
- }
- queryData, err = rows2mapObjects(rows)
- } else {
- if count > 0 && offset > 0 {
- offset = offset * count
- }
- rows, err := Dbs.Queryx(fmt.Sprintf(sqlstr+" limit %d , %d", offset, count), params...)
- if err != nil {
- return err.Error(), err
- }
- queryData, err = rows2mapObjects(rows)
- }
- if err != nil {
- return nil, err
- }
- if recordcount == 0 {
- recordcount = int64(len(queryData))
- }
- data := make(map[string]interface{})
- data["list"] = queryData
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- // func QueryByMapT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
- func QueryByMapT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- recordcount := int64(0)
- if count > 1 {
- sql := "SELECT COUNT(*) as count FROM (" + sqlstr + ") a"
- err := BasicList(sql, params, &recordcount)
- if err != nil {
- fmt.Println("s1", err)
- return nil, err
- }
- }
- queryData := make([]map[string]interface{}, 1)
- // queryData := make([]map[string]string, 1)
- var err error
- if count == 0 {
- // queryData, err = MapStrList(sqlstr, params...)
- queryData, err = MapInterList(sqlstr, params...)
- } else {
- // if count > 0 && offset > 0 {
- // offset = offset * count
- // }
- sqllist := sqlstr + fmt.Sprintf(" limit %d offset %d", count, offset*count)
- // queryData, err = MapStrList(sqllist, params...)
- queryData, err = MapInterList(sqllist, params...)
- }
- if err != nil {
- return nil, err
- }
- if recordcount == 0 {
- recordcount = int64(len(queryData))
- }
- data := make(map[string]interface{})
- data["list"] = queryData
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- // QueryByXlsx 将查询到的数据,按照指定字段的值做为索引构建map并返回
- // sql string: 查询需要获取哪些字段的值,就是select后面跟着的部分,一般用"*"
- // params string: 过滤条件,就是where后面跟着的部分
- func QueryByXlsx(sqlstr string, params []interface{}) (*xlsx.File, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return nil, err
- }
- rows, err := Dbs.Queryx(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- columnsType, _ := rows.ColumnTypes()
- columnsLen := len(columnsType)
- columns, _ := rows.Columns()
- values := make([]interface{}, columnsLen)
- valuePtrs := make([]interface{}, columnsLen)
- var sheet *xlsx.Sheet
- var row *xlsx.Row
- var cell *xlsx.Cell
- file := xlsx.NewFile()
- sheet, err = file.AddSheet("Sheet1")
- if err != nil {
- fmt.Printf(err.Error())
- }
- row = sheet.AddRow()
- for _, columnname := range columns {
- cell = row.AddCell()
- cell.Value = columnname
- }
- for rows.Next() {
- for a := 0; a < columnsLen; a++ {
- valuePtrs[a] = &values[a]
- }
- rows.Scan(valuePtrs...)
- row = sheet.AddRow()
- for i := range columns {
- var v interface{}
- val := values[i]
- b, ok := val.([]byte)
- cell = row.AddCell()
- if ok {
- v1 := string(b)
- switch columnsType[i].DatabaseTypeName() {
- case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT":
- {
- v, _ = strconv.ParseInt(v1, 10, 32)
- cell.Value = fmt.Sprintf("%v", v)
- }
- case "FLOAT", "DOUBLE", "DECIMAL":
- {
- cindex := len(val.([]byte)) - strings.Index(v1, ".") - 1
- v2, _ := strconv.ParseFloat(v1, 32)
- v = toFixed(v2, cindex)
- cell.Value = fmt.Sprintf("%v", v)
- }
- case "BOOL":
- {
- v, _ = strconv.ParseBool(v1)
- cell.Value = fmt.Sprintf("%v", v)
- }
- default:
- {
- v = v1
- cell.Value = fmt.Sprintf("%v", v)
- }
- }
- } else {
- v = val
- cell.Value = fmt.Sprintf("%v", v)
- }
- if cell.Value == "<nil>" {
- cell.Value = ""
- }
- }
- }
- return file, nil
- }
- // QueryByMap 将查询到的数据,按照指定字段的值做为索引构建map并返回
- // columnName string: 作为索引的字段名称
- // sql string: 查询需要获取哪些字段的值,就是select后面跟着的部分,一般用"*"
- // where string: 过滤条件,就是where后面跟着的部分
- // order string: 排序条件,就是order by后面跟着的部分。默认是ASC排序,除非"-"开头则DESC排序
- // offset string: limit后面逗号相隔的两个数值,前者就是offset,后者就是count
- // count string: limit后面逗号相隔的两个数值,前者就是offset,后者就是count
- func QueryByMapMenu(sqlstr string, offset, count int, params []interface{}) ([]map[string]interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return nil, err
- }
- if offset < 0 {
- offset = 0
- }
- if count <= 0 {
- count = 0
- }
- if count > 0 {
- sqlstr += " limit " + strconv.Itoa(offset) + "," + strconv.Itoa(count)
- }
- rows, err := Dbs.Query(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- columnsType, _ := rows.ColumnTypes()
- columnsLen := len(columnsType)
- columns, _ := rows.Columns()
- queryData := make([]map[string]interface{}, 0)
- values := make([]interface{}, columnsLen)
- valuePtrs := make([]interface{}, columnsLen)
- for rows.Next() {
- for a := 0; a < columnsLen; a++ {
- valuePtrs[a] = &values[a]
- }
- rows.Scan(valuePtrs...)
- entry := make(map[string]interface{})
- for i, col := range columns {
- entry[col] = getValue(values[i], columnsType[i].DatabaseTypeName())
- }
- queryData = append(queryData, entry)
- }
- data := queryData
- return data, nil
- }
- func QueryByList(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- if count == 0 {
- count = 1000
- }
- recordcount := int64(0)
- if count > 1 {
- trow, err := Dbs.Queryx("SELECT COUNT(1) as count FROM ("+sqlstr+") a", params...)
- if err != nil {
- return nil, err
- }
- tt, err := rows2mapObjects(trow)
- if err != nil {
- return nil, err
- }
- recordcount = tt[0]["count"].(int64)
- }
- queryData := make([]map[string]interface{}, 1)
- var err error
- if count == 0 {
- tqueryData, err := Dbs.Queryx(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- queryData, err = rows2mapObjects(tqueryData)
- } else {
- // if count > 0 && offset > 0 {
- // offset = offset * count
- // }
- sqlstr += fmt.Sprintf(" limit %d offset %d", count, offset*count)
- tqueryData, err := Dbs.Queryx(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- queryData, err = rows2mapObjects(tqueryData)
- }
- if err != nil {
- return nil, err
- }
- columnsname := make([]string, 0)
- listdata := make(map[string][]interface{})
- if len(queryData) > 0 {
- for i := range queryData {
- if i == 0 {
- for i := range queryData[0] {
- columnsname = append(columnsname, i)
- }
- }
- for _, v := range columnsname {
- listdata[v] = append(listdata[v], queryData[i][v])
- }
- }
- recordcount = int64(len(queryData))
- }
- data := make(map[string]interface{})
- data["list"] = listdata
- //data["columnsname"] = columnsname
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- func QueryByListList(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- if count == 0 {
- count = 1000
- }
- recordcount := int64(0)
- if count > 1 {
- trow, err := Dbs.Queryx("SELECT COUNT(1) as count FROM ("+sqlstr+") a", params...)
- if err != nil {
- return nil, err
- }
- tt, err := rows2mapObjects(trow)
- if err != nil {
- return nil, err
- }
- recordcount = tt[0]["count"].(int64)
- }
- queryData := make([]map[string]interface{}, 1)
- var err error
- if count == 0 {
- tqueryData, err := Dbs.Queryx(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- queryData, err = rows2mapObjects(tqueryData)
- } else {
- if count > 0 && offset > 0 {
- offset = offset * count
- }
- tqueryData, err := Dbs.Queryx(fmt.Sprintf(sqlstr+" limit %d , %d", offset, count), params...)
- if err != nil {
- return nil, err
- }
- queryData, err = rows2mapObjects(tqueryData)
- }
- if err != nil {
- return nil, err
- }
- columnsname := make([]string, 0)
- listdata := make([]interface{}, 0)
- if len(queryData) > 0 {
- for i := range queryData {
- if i == 0 {
- for i := range queryData[0] {
- columnsname = append(columnsname, i)
- }
- }
- listdatarow := make([]interface{}, 0)
- for _, v := range columnsname {
- listdatarow = append(listdatarow, queryData[i][v])
- }
- listdata = append(listdata, listdatarow)
- }
- recordcount = int64(len(queryData))
- }
- data := make(map[string]interface{})
- data["list"] = listdata
- data["columnsname"] = columnsname
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- // func QueryByListT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
- func QueryByListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- if count == 0 {
- count = 1000
- }
- recordcount := int64(0)
- if count > 1 {
- tt, err := Dbs.Queryx("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...)
- if err != nil {
- return nil, err
- }
- tt.Next()
- tt.Scan(&recordcount)
- }
- var err error
- var sqllist string
- if count > 0 {
- sqllist = sqlstr + fmt.Sprintf(" limit %d offset %d", count, offset*count)
- }
- listdata, err := MapInterList(sqllist, params...)
- if err != nil {
- log.Default().Printf("QueryByListT MapInterList : %v", err)
- }
- data := make(map[string]interface{})
- data["lists"] = listdata
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- // func QueryByListListT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
- func QueryByListListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- if count == 0 {
- count = 1000
- }
- recordcount := int64(0)
- if count > 1 {
- sqlstr = fmt.Sprintf("SELECT COUNT(*) as count FROM (%s) a", sqlstr)
- err := BasicList(sqlstr, params, &recordcount)
- if err != nil {
- return nil, err
- }
- }
- queryData := make([]map[string]string, 1)
- var err error
- if count == 0 {
- queryData, err = MapStrList(sqlstr, params...)
- } else {
- // if count > 0 && offset > 0 {
- // offset = offset * count
- // }
- sqllist := sqlstr + fmt.Sprintf(" limit %d offset %d", count, offset*count)
- queryData, err = MapStrList(sqllist, params...)
- }
- if err != nil {
- return nil, err
- }
- columnsname := make([]string, 0)
- listdata := make([]interface{}, 0)
- if len(queryData) > 0 {
- for i := range queryData {
- if i == 0 {
- for i := range queryData[0] {
- columnsname = append(columnsname, i)
- }
- }
- listdatarow := make([]interface{}, 0)
- for _, v := range columnsname {
- listdatarow = append(listdatarow, queryData[i][v])
- }
- listdata = append(listdata, listdatarow)
- }
- recordcount = int64(len(queryData))
- }
- data := make(map[string]interface{})
- data["list"] = listdata
- data["columnsname"] = columnsname
- data["total"] = recordcount
- data["code"] = 0
- data["message"] = "ok"
- if count > 0 {
- data["pages"] = math.Ceil(float64(recordcount) / float64(count))
- data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
- }
- data["pageSize"] = count
- return data, nil
- }
- func ExecQuery(sqlstr string, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- rows, err := Dbs.Exec(sqlstr, params...)
- if err != nil {
- return nil, err
- }
- data := make(map[string]interface{})
- LastInsertId, _ := rows.LastInsertId()
- idPointer := (*int)(unsafe.Pointer(&LastInsertId))
- idd16 := *idPointer
- RowsAffected, _ := rows.RowsAffected()
- _idPointer := (*int)(unsafe.Pointer(&RowsAffected))
- _idd16 := *_idPointer
- data["result"] = rows
- data["LastInsertId"] = idd16
- data["RowsAffected"] = _idd16
- return data, nil
- }
- func ExecQueryT(sqlstr string, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- rows, err := tx.Exec(sqlstr, params...)
- if err != nil {
- fmt.Println("exe", err)
- return nil, err
- }
- LastInsertId, _ := rows.LastInsertId()
- idPointer := (*int)(unsafe.Pointer(&LastInsertId))
- idd16 := *idPointer
- data := make(map[string]interface{})
- RowsAffected, _ := rows.RowsAffected()
- _idPointer := (*int)(unsafe.Pointer(&RowsAffected))
- _idd16 := *_idPointer
- data["LastInsertId"] = idd16
- data["RowsAffected"] = _idd16
- data["result"] = rows
- return data, nil
- }
- func ExecQuerys(sqlstr string, params []interface{}) (interface{}, error) {
- if err := sqlCheckParam(sqlstr); err != nil {
- return 0, err
- }
- var res sql.Result
- var e error
- res, e = Dbs.Exec(sqlstr, params...)
- if e != nil {
- return nil, e
- }
- fmt.Printf("%#v\n", res)
- data := make(map[string]interface{})
- data["result"] = nil
- return data, nil
- }
- func getValue(value interface{}, CT string) interface{} {
- var v interface{}
- val := value
- b, ok := val.([]byte)
- if ok {
- v1 := string(b)
- switch CT {
- case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT":
- {
- v, _ = strconv.ParseInt(v1, 10, 32)
- }
- case "FLOAT", "DOUBLE", "DECIMAL":
- {
- cindex := len(val.([]byte)) - strings.Index(v1, ".") - 1
- v2, _ := strconv.ParseFloat(v1, 32)
- v = toFixed(v2, cindex)
- }
- case "BOOL":
- {
- v, _ = strconv.ParseBool(v1)
- }
- default:
- {
- v = v1
- }
- }
- } else {
- v = val
- }
- return v
- }
- func reflect2object(rawValue *reflect.Value) (value interface{}, err error) {
- aa := reflect.TypeOf((*rawValue).Interface())
- vv := reflect.ValueOf((*rawValue).Interface())
- switch aa.Kind() {
- case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
- value = vv.Int()
- case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
- value = vv.Uint()
- case reflect.Float32, reflect.Float64:
- value = vv.Float()
- case reflect.String:
- value = vv.String()
- case reflect.Array, reflect.Slice:
- switch aa.Elem().Kind() {
- case reflect.Uint8:
- data := rawValue.Interface().([]byte)
- value = string(data)
- default:
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- // time type
- case reflect.Struct:
- if aa.ConvertibleTo(schemas.TimeType) {
- value = vv.Convert(schemas.TimeType).Interface().(time.Time)
- } else {
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- case reflect.Bool:
- value = vv.Bool()
- case reflect.Complex128, reflect.Complex64:
- value = vv.Complex()
- /* TODO: unsupported types below
- case reflect.Map:
- case reflect.Ptr:
- case reflect.Uintptr:
- case reflect.UnsafePointer:
- case reflect.Chan, reflect.Func, reflect.Interface:
- */
- default:
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- return
- }
- func value2Object(rawValue *reflect.Value) (data interface{}, err error) {
- data, err = reflect2object(rawValue)
- if err != nil {
- return
- }
- return
- }
- func rows2mapObjects(rows *sqlx.Rows) (resultsSlice []map[string]interface{}, err error) {
- fields, err := rows.Columns()
- if err != nil {
- return nil, err
- }
- for rows.Next() {
- result, err := rows2mapObject(rows, fields)
- if err != nil {
- return nil, err
- }
- resultsSlice = append(resultsSlice, result)
- }
- return resultsSlice, nil
- }
- func rows2mapObject(rows *sqlx.Rows, fields []string) (resultsMap map[string]interface{}, err error) {
- result := make(map[string]interface{})
- scanResultContainers := make([]interface{}, len(fields))
- for i := 0; i < len(fields); i++ {
- var scanResultContainer interface{}
- scanResultContainers[i] = &scanResultContainer
- }
- if err := rows.Scan(scanResultContainers...); err != nil {
- return nil, err
- }
- for ii, key := range fields {
- rawValue := reflect.Indirect(reflect.ValueOf(scanResultContainers[ii]))
- //if row is null then ignore
- if rawValue.Interface() == nil {
- continue
- }
- if data, err := value2Object(&rawValue); err == nil {
- result[key] = data
- } else {
- return nil, err // !nashtsai! REVIEW, should return err or just error log?
- }
- }
- return result, nil
- }
- func row2mapStr(rows *sqlx.Rows, fields []string) (resultsMap map[string]string, err error) {
- result := make(map[string]string)
- scanResultContainers := make([]interface{}, len(fields))
- for i := 0; i < len(fields); i++ {
- var scanResultContainer interface{}
- scanResultContainers[i] = &scanResultContainer
- }
- if err := rows.Scan(scanResultContainers...); err != nil {
- return nil, err
- }
- for ii, key := range fields {
- rawValue := reflect.Indirect(reflect.ValueOf(scanResultContainers[ii]))
- // if row is null then as empty string
- if rawValue.Interface() == nil {
- result[key] = ""
- continue
- }
- if data, err := value2String(&rawValue); err == nil {
- result[key] = data
- } else {
- return nil, err
- }
- }
- return result, nil
- }
- func Rows2Strings(rows *sqlx.Rows) (resultsSlice []map[string]string, err error) {
- fields, err := rows.Columns()
- if err != nil {
- return nil, err
- }
- for rows.Next() {
- result, err := row2mapStr(rows, fields)
- if err != nil {
- return nil, err
- }
- resultsSlice = append(resultsSlice, result)
- }
- return resultsSlice, nil
- }
- func value2String(rawValue *reflect.Value) (str string, err error) {
- aa := reflect.TypeOf((*rawValue).Interface())
- vv := reflect.ValueOf((*rawValue).Interface())
- switch aa.Kind() {
- case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
- str = strconv.FormatInt(vv.Int(), 10)
- case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
- str = strconv.FormatUint(vv.Uint(), 10)
- case reflect.Float32, reflect.Float64:
- str = strconv.FormatFloat(vv.Float(), 'f', -1, 64)
- case reflect.String:
- str = vv.String()
- case reflect.Array, reflect.Slice:
- switch aa.Elem().Kind() {
- case reflect.Uint8:
- data := rawValue.Interface().([]byte)
- str = string(data)
- if str == "\x00" {
- str = "0"
- }
- default:
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- // time type
- case reflect.Struct:
- if aa.ConvertibleTo(schemas.TimeType) {
- str = vv.Convert(schemas.TimeType).Interface().(time.Time).Format(time.RFC3339Nano)
- } else {
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- case reflect.Bool:
- str = strconv.FormatBool(vv.Bool())
- case reflect.Complex128, reflect.Complex64:
- str = fmt.Sprintf("%v", vv.Complex())
- /* TODO: unsupported types below
- case reflect.Map:
- case reflect.Ptr:
- case reflect.Uintptr:
- case reflect.UnsafePointer:
- case reflect.Chan, reflect.Func, reflect.Interface:
- */
- default:
- err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
- }
- return
- }
- func MapStrList(sql string, param ...interface{}) ([]map[string]string, error) {
- ress := make([]map[string]string, 0, 0)
- rows, err := Dbs.Queryx(sql, param...)
- if err != nil {
- log.Default().Printf("MapStrList Queryx: %v", err)
- return ress, err
- }
- col, err := rows.Columns()
- if err != nil {
- log.Default().Printf("BigpartUse clolumns: %v", err)
- return ress, err
- }
- for rows.Next() {
- m := make(map[string]string)
- rs, err := rows.SliceScan()
- if err != nil {
- log.Default().Printf("BigpartUse SliceScan: %v", err)
- return ress, err
- }
- for i, r := range rs {
- switch r.(type) {
- case []uint8:
- m[col[i]] = string(r.([]uint8))
- case int64:
- m[col[i]] = strconv.FormatInt(r.(int64), 10)
- }
- }
- ress = append(ress, m)
- }
- defer func() {
- if err := rows.Close(); err != nil {
- log.Default().Printf("sqlx row close:%v", err)
- }
- }()
- return ress, nil
- }
- func MapInterList(sql string, param ...interface{}) ([]map[string]interface{}, error) {
- ress := make([]map[string]interface{}, 0, 0)
- rows, err := Dbs.Queryx(sql, param...)
- if err != nil {
- log.Default().Printf("MapInterList Queryx: %v", err)
- return ress, err
- }
- col, err := rows.Columns()
- if err != nil {
- log.Default().Printf("BigpartUse clolumns: %v", err)
- return ress, err
- }
- for rows.Next() {
- m := make(map[string]interface{})
- rs, err := rows.SliceScan()
- if err != nil {
- log.Default().Printf("BigpartUse SliceScan: %v", err)
- return ress, err
- }
- for i, r := range rs {
- switch r.(type) {
- case []uint8:
- m[col[i]] = string(r.([]uint8))
- case int64:
- m[col[i]] = r.(int64)
- }
- }
- ress = append(ress, m)
- }
- defer func() {
- if err := rows.Close(); err != nil {
- log.Default().Printf("sqlx row close:%v", err)
- }
- }()
- return ress, nil
- }
- func BasicList(sql string, params []interface{}, res ...interface{}) error {
- // fmt.Printf("%s\n", sql)
- if params == nil {
- params = make([]interface{}, 0)
- }
- // var count int
- tt, err := Dbs.Queryx(sql, params...)
- if err != nil {
- fmt.Println("s1", err)
- return err
- }
- if tt.Next() {
- tt.Scan(res...)
- // tt.Scan(&count)
- }
- defer func() {
- if err := tt.Close(); err != nil {
- log.Default().Printf("sqlx row close:%v", err)
- }
- }()
- return nil
- }
- func MapStr(sql string, param ...interface{}) (map[string]string, error) {
- m := make(map[string]string)
- rows, err := Dbs.Queryx(sql, param...)
- if err != nil {
- log.Default().Printf("MapStr Queryx: %v", err)
- return m, err
- }
- col, err := rows.Columns()
- if err != nil {
- log.Default().Printf("BigpartUse clolumns: %v", err)
- return m, err
- }
- if rows.Next() {
- rs, err := rows.SliceScan()
- if err != nil {
- log.Default().Printf("BigpartUse SliceScan: %v", err)
- return m, err
- }
- for i, r := range rs {
- switch r.(type) {
- case []uint8:
- m[col[i]] = string(r.([]uint8))
- case int64:
- m[col[i]] = strconv.FormatInt(r.(int64), 10)
- }
- }
- }
- defer func() {
- if err := rows.Close(); err != nil {
- log.Default().Printf("sqlx row close:%v", err)
- }
- }()
- return m, nil
- }
- func MapStrListTx(tx *sqlx.Tx, sql string, param ...interface{}) ([]map[string]string, error) {
- ress := make([]map[string]string, 0, 0)
- rows, err := tx.Queryx(sql, param...)
- if err != nil {
- log.Default().Printf("MapStrListTx Queryx: %v", err)
- return ress, err
- }
- col, err := rows.Columns()
- if err != nil {
- log.Default().Printf("BigpartUse clolumns: %v", err)
- return ress, err
- }
- for rows.Next() {
- m := make(map[string]string)
- rs, err := rows.SliceScan()
- if err != nil {
- log.Default().Printf("BigpartUse SliceScan: %v", err)
- return ress, err
- }
- for i, r := range rs {
- switch r.(type) {
- case []uint8:
- m[col[i]] = string(r.([]uint8))
- case int64:
- m[col[i]] = strconv.FormatInt(r.(int64), 10)
- }
- }
- ress = append(ress, m)
- }
- defer func() {
- if err := rows.Close(); err != nil {
- log.Default().Printf("sqlx row close:%v", err)
- }
- }()
- return ress, nil
- }
|