You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
starainrt/database.go

965 lines
19 KiB
Go

5 years ago
package starainrt
import (
"database/sql"
"errors"
"reflect"
5 years ago
"strconv"
"time"
5 years ago
)
// DBRes 单连接下的数据库资源句柄
var DBRes *sql.DB
// DBRows 单连接下的数据库查询句柄
var DBRows *sql.Rows
// StarDB 一个简单封装的DB库
type StarDB struct {
DB *sql.DB
Rows *sql.Rows
}
// StarRows 为查询结果集(按行)
type StarRows struct {
Rows *sql.Rows
Length int
StringResult []map[string]string
Columns []string
ColumnsType []reflect.Type
columnref map[string]int
result [][]interface{}
}
// StarResult 为查询结果集(总)
type StarResult struct {
Result []interface{}
Columns []string
columnref map[string]int
ColumnsType []reflect.Type
}
// StarResultCol 为查询结果集(按列)
type StarResultCol struct {
Result []interface{}
}
// MustBytes 列查询结果转Bytes
func (star *StarResultCol) MustBytes() [][]byte {
var res [][]byte
for _, v := range star.Result {
res = append(res, v.([]byte))
}
return res
}
// MustBool 列查询结果转Bool
func (star *StarResultCol) MustBool() []bool {
var res []bool
5 years ago
var tmp bool
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = false
case bool:
tmp = vtype
case float64:
if vtype > 0 {
tmp = true
} else {
tmp = false
}
case float32:
if vtype > 0 {
tmp = true
} else {
tmp = false
}
case int:
if vtype > 0 {
tmp = true
} else {
tmp = false
}
case int32:
if vtype > 0 {
5 years ago
tmp = true
} else {
tmp = false
}
case int64:
if vtype > 0 {
5 years ago
tmp = true
} else {
tmp = false
}
case string:
tmp, _ = strconv.ParseBool(vtype)
default:
tmp, _ = strconv.ParseBool(string(vtype.([]byte)))
}
res = append(res, tmp)
}
return res
}
// MustFloat32 列查询结果转Float32
func (star *StarResultCol) MustFloat32() []float32 {
var res []float32
5 years ago
var tmp float32
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = 0
case float32:
tmp = vtype
case float64:
tmp = float32(vtype)
5 years ago
case string:
tmps, _ := strconv.ParseFloat(vtype, 32)
tmp = float32(tmps)
case int:
tmp = float32(vtype)
case int32:
tmp = float32(vtype)
case int64:
tmp = float32(vtype)
case time.Time:
tmp = float32(vtype.Unix())
5 years ago
default:
tmpt := string(vtype.([]byte))
tmps, _ := strconv.ParseFloat(tmpt, 32)
tmp = float32(tmps)
5 years ago
}
res = append(res, tmp)
}
return res
}
// MustFloat64 列查询结果转Float64
func (star *StarResultCol) MustFloat64() []float64 {
var res []float64
5 years ago
var tmp float64
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = 0
case float64:
tmp = vtype
case float32:
tmp = float64(vtype)
5 years ago
case string:
tmp, _ = strconv.ParseFloat(vtype, 64)
case int:
tmp = float64(vtype)
case int32:
tmp = float64(vtype)
case int64:
tmp = float64(vtype)
case time.Time:
tmp = float64(vtype.Unix())
5 years ago
default:
tmpt := string(vtype.([]byte))
tmps, _ := strconv.ParseFloat(tmpt, 64)
tmp = float64(tmps)
5 years ago
}
res = append(res, tmp)
}
return res
}
5 years ago
// MustString 列查询结果转String
func (star *StarResultCol) MustString() []string {
var res []string
var tmp string
for _, v := range star.Result {
switch vtype := v.(type) {
case nil:
tmp = ""
case string:
tmp = vtype
5 years ago
case int64:
tmp = strconv.FormatInt(vtype, 10)
case int32:
tmp = strconv.Itoa(int(vtype))
case bool:
tmp = strconv.FormatBool(vtype)
case float64:
tmp = strconv.FormatFloat(vtype, 'f', 10, 64)
case float32:
tmp = strconv.FormatFloat(float64(vtype), 'f', 10, 32)
case int:
tmp = strconv.Itoa(vtype)
case time.Time:
tmp = vtype.String()
default:
5 years ago
tmp = string(vtype.([]byte))
}
res = append(res, tmp)
}
return res
}
// MustInt32 列查询结果转Int32
func (star *StarResultCol) MustInt32() []int32 {
var res []int32
5 years ago
var tmp int32
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = 0
case float64:
tmp = int32(vtype)
case float32:
tmp = int32(vtype)
5 years ago
case string:
tmps, _ := strconv.ParseInt(vtype, 10, 32)
tmp = int32(tmps)
case int:
tmp = int32(vtype)
case int64:
tmp = int32(vtype)
case int32:
tmp = vtype
case time.Time:
tmp = int32(vtype.Unix())
5 years ago
default:
tmpt := string(vtype.([]byte))
tmps, _ := strconv.ParseInt(tmpt, 10, 32)
tmp = int32(tmps)
5 years ago
}
res = append(res, tmp)
}
return res
}
// MustInt64 列查询结果转Int64
func (star *StarResultCol) MustInt64() []int64 {
var res []int64
5 years ago
var tmp int64
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = 0
case float64:
tmp = int64(vtype)
case float32:
tmp = int64(vtype)
5 years ago
case string:
tmps, _ := strconv.ParseInt(vtype, 10, 64)
tmp = int64(tmps)
case int:
tmp = int64(vtype)
case int32:
tmp = int64(vtype)
case int64:
tmp = vtype
case time.Time:
tmp = vtype.Unix()
5 years ago
default:
tmpt := string(vtype.([]byte))
tmp, _ = strconv.ParseInt(tmpt, 10, 64)
5 years ago
}
res = append(res, tmp)
}
return res
}
// MustInt 列查询结果转Int
func (star *StarResultCol) MustInt() []int {
var res []int
5 years ago
var tmp int
for _, v := range star.Result {
5 years ago
switch vtype := v.(type) {
case nil:
tmp = 0
case float64:
tmp = int(vtype)
case float32:
tmp = int(vtype)
5 years ago
case string:
tmps, _ := strconv.ParseInt(vtype, 10, 64)
tmp = int(tmps)
case int:
tmp = vtype
case int32:
tmp = int(vtype)
case int64:
tmp = int(vtype)
case time.Time:
tmp = int(vtype.Unix())
default:
tmpt := string(vtype.([]byte))
tmps, _ := strconv.ParseInt(tmpt, 10, 64)
tmp = int(tmps)
}
res = append(res, tmp)
}
return res
}
// MustDate 列查询结果转Date(time.Time)
func (star *StarResultCol) MustDate(layout string) []time.Time {
var res []time.Time
var tmp time.Time
for _, v := range star.Result {
switch vtype := v.(type) {
case nil:
tmp = time.Time{}
case float64:
tmp = time.Unix(int64(vtype), int64(vtype-float64(int64(vtype)))*1000000000)
case float32:
tmp = time.Unix(int64(vtype), int64(vtype-float32(int64(vtype)))*1000000000)
case string:
tmp, _ = time.Parse(layout, vtype)
case int:
tmp = time.Unix(int64(vtype), 0)
case int32:
tmp = time.Unix(int64(vtype), 0)
case int64:
tmp = time.Unix(vtype, 0)
case time.Time:
tmp = vtype
default:
tmpt := string(vtype.([]byte))
tmp, _ = time.Parse(layout, tmpt)
}
res = append(res, tmp)
}
return res
}
// IsNil 检测是不是nil 列查询结果是不是nil
func (star *StarResultCol) IsNil(name string) []bool {
var res []bool
var tmp bool
for _, v := range star.Result {
switch v.(type) {
case nil:
tmp = true
5 years ago
default:
tmp = false
5 years ago
}
res = append(res, tmp)
}
return res
}
// IsNil 检测是不是nil
func (star *StarResult) IsNil(name string) bool {
num, ok := star.columnref[name]
if !ok {
return false
}
tmp := star.Result[num]
switch tmp.(type) {
case nil:
return true
default:
return false
}
}
// MustDate 列查询结果转Date
func (star *StarResult) MustDate(name, layout string) time.Time {
var res time.Time
num, ok := star.columnref[name]
if !ok {
return time.Time{}
}
tmp := star.Result[num]
switch vtype := tmp.(type) {
case nil:
res = time.Time{}
case float64:
res = time.Unix(int64(vtype), int64(vtype-float64(int64(vtype)))*1000000000)
case float32:
res = time.Unix(int64(vtype), int64(vtype-float32(int64(vtype)))*1000000000)
case string:
res, _ = time.Parse(layout, vtype)
case int:
res = time.Unix(int64(vtype), 0)
case int32:
res = time.Unix(int64(vtype), 0)
case int64:
res = time.Unix(vtype, 0)
case time.Time:
res = vtype
default:
res, _ = time.Parse(layout, string(tmp.([]byte)))
}
return res
}
// MustInt64 列查询结果转int64
func (star *StarResult) MustInt64(name string) int64 {
5 years ago
var res int64
num, ok := star.columnref[name]
if !ok {
return 0
}
tmp := star.Result[num]
5 years ago
switch vtype := tmp.(type) {
case nil:
res = 0
case float64:
res = int64(vtype)
case float32:
res = int64(vtype)
5 years ago
case string:
res, _ = strconv.ParseInt(vtype, 10, 64)
case int:
res = int64(vtype)
case int32:
res = int64(vtype)
case int64:
res = vtype
case time.Time:
res = int64(vtype.Unix())
5 years ago
default:
res, _ = strconv.ParseInt(string(tmp.([]byte)), 10, 64)
}
return res
}
// MustInt32 列查询结果转Int32
func (star *StarResult) MustInt32(name string) int32 {
5 years ago
var res int32
num, ok := star.columnref[name]
if !ok {
return 0
}
tmp := star.Result[num]
5 years ago
switch vtype := tmp.(type) {
case nil:
res = 0
case float64:
res = int32(vtype)
case float32:
res = int32(vtype)
5 years ago
case string:
ress, _ := strconv.ParseInt(vtype, 10, 32)
res = int32(ress)
case int:
res = int32(vtype)
case int32:
res = vtype
case int64:
res = int32(vtype)
case time.Time:
res = int32(vtype.Unix())
5 years ago
default:
ress, _ := strconv.ParseInt(string(tmp.([]byte)), 10, 32)
res = int32(ress)
}
return res
}
// MustString 列查询结果转string
func (star *StarResult) MustString(name string) string {
var res string
num, ok := star.columnref[name]
if !ok {
return ""
}
switch vtype := star.Result[num].(type) {
case nil:
res = ""
case string:
res = vtype
5 years ago
case int64:
res = strconv.FormatInt(vtype, 10)
case int32:
res = strconv.Itoa(int(vtype))
case bool:
res = strconv.FormatBool(vtype)
case float64:
res = strconv.FormatFloat(vtype, 'f', 10, 64)
case float32:
res = strconv.FormatFloat(float64(vtype), 'f', 10, 32)
case int:
res = strconv.Itoa(vtype)
case time.Time:
res = vtype.String()
5 years ago
default:
res = string(vtype.([]byte))
}
return res
}
5 years ago
// MustFloat64 列查询结果转float64
func (star *StarResult) MustFloat64(name string) float64 {
5 years ago
var res float64
num, ok := star.columnref[name]
if !ok {
return 0
}
switch vtype := star.Result[num].(type) {
5 years ago
case nil:
res = 0
case string:
res, _ = strconv.ParseFloat(vtype, 64)
case float64:
res = vtype
case int:
res = float64(vtype)
case int64:
res = float64(vtype)
case int32:
res = float64(vtype)
case float32:
res = float64(vtype)
case time.Time:
res = float64(vtype.Unix())
5 years ago
default:
res, _ = strconv.ParseFloat(string(vtype.([]byte)), 64)
}
return res
}
5 years ago
// MustFloat32 列查询结果转float32
func (star *StarResult) MustFloat32(name string) float32 {
5 years ago
var res float32
num, ok := star.columnref[name]
if !ok {
return 0
}
switch vtype := star.Result[num].(type) {
5 years ago
case nil:
res = 0
case string:
tmp, _ := strconv.ParseFloat(vtype, 32)
res = float32(tmp)
case float64:
res = float32(vtype)
case float32:
res = vtype
case int:
res = float32(vtype)
case int64:
res = float32(vtype)
case int32:
res = float32(vtype)
case time.Time:
res = float32(vtype.Unix())
5 years ago
default:
tmp, _ := strconv.ParseFloat(string(vtype.([]byte)), 32)
res = float32(tmp)
}
return res
}
5 years ago
// MustInt 列查询结果转int
func (star *StarResult) MustInt(name string) int {
5 years ago
var res int
num, ok := star.columnref[name]
if !ok {
return 0
}
tmp := star.Result[num]
5 years ago
switch vtype := tmp.(type) {
case nil:
res = 0
case float64:
res = int(vtype)
case float32:
res = int(vtype)
5 years ago
case string:
ress, _ := strconv.ParseInt(vtype, 10, 64)
res = int(ress)
case int:
res = vtype
case int32:
res = int(vtype)
case int64:
res = int(vtype)
case time.Time:
res = int(vtype.Unix())
5 years ago
default:
ress, _ := strconv.ParseInt(string(tmp.([]byte)), 10, 64)
res = int(ress)
}
return res
}
// MustBool 列查询结果转bool
func (star *StarResult) MustBool(name string) bool {
5 years ago
var res bool
num, ok := star.columnref[name]
if !ok {
return false
}
tmp := star.Result[num]
5 years ago
switch vtype := tmp.(type) {
case nil:
res = false
case bool:
res = vtype
case float64:
if vtype > 0 {
res = true
} else {
res = false
}
case float32:
if vtype > 0 {
res = true
} else {
res = false
}
case int:
if vtype > 0 {
res = true
} else {
res = false
}
case int32:
if vtype > 0 {
5 years ago
res = true
} else {
res = false
}
case int64:
if vtype > 0 {
5 years ago
res = true
} else {
res = false
}
case string:
res, _ = strconv.ParseBool(vtype)
default:
res, _ = strconv.ParseBool(string(vtype.([]byte)))
}
return res
}
// MustBytes 列查询结果转byte
func (star *StarResult) MustBytes(name string) []byte {
num, ok := star.columnref[name]
if !ok {
return []byte{}
}
res := star.Result[num].([]byte)
return res
}
// Rescan 重新分析结果集
func (star *StarRows) Rescan() {
star.parserows()
}
// Col 选择需要进行操作的数据结果列
func (star *StarRows) Col(name string) *StarResultCol {
result := new(StarResultCol)
if _, ok := star.columnref[name]; !ok {
return result
}
var rescol []interface{}
for _, v := range star.result {
rescol = append(rescol, v[star.columnref[name]])
}
result.Result = rescol
return result
}
// Row 选择需要进行操作的数据结果行
func (star *StarRows) Row(id int) *StarResult {
result := new(StarResult)
if id+1 > len(star.result) {
return result
}
result.Result = star.result[id]
result.Columns = star.Columns
result.ColumnsType = star.ColumnsType
result.columnref = star.columnref
return result
}
// Close 关闭打开的结果集
func (star *StarRows) Close() error {
return star.Rows.Close()
}
func (star *StarRows) parserows() {
star.result = [][]interface{}{}
star.columnref = make(map[string]int)
star.StringResult = []map[string]string{}
star.Columns, _ = star.Rows.Columns()
types, _ := star.Rows.ColumnTypes()
for _, v := range types {
star.ColumnsType = append(star.ColumnsType, v.ScanType())
}
scanArgs := make([]interface{}, len(star.Columns))
values := make([]interface{}, len(star.Columns))
for i := range values {
star.columnref[star.Columns[i]] = i
scanArgs[i] = &values[i]
}
for star.Rows.Next() {
if err := star.Rows.Scan(scanArgs...); err != nil {
return
}
record := make(map[string]string)
var rescopy []interface{}
for i, col := range values {
rescopy = append(rescopy, col)
switch vtype := col.(type) {
case float32:
record[star.Columns[i]] = strconv.FormatFloat(float64(vtype), 'f', -1, 64)
case float64:
record[star.Columns[i]] = strconv.FormatFloat(vtype, 'f', -1, 64)
case int64:
record[star.Columns[i]] = strconv.FormatInt(vtype, 10)
case int32:
record[star.Columns[i]] = strconv.FormatInt(int64(vtype), 10)
case int:
record[star.Columns[i]] = strconv.Itoa(vtype)
case string:
record[star.Columns[i]] = vtype
case bool:
record[star.Columns[i]] = strconv.FormatBool(vtype)
case time.Time:
record[star.Columns[i]] = vtype.String()
case nil:
record[star.Columns[i]] = ""
default:
record[star.Columns[i]] = string(vtype.([]byte))
}
}
star.result = append(star.result, rescopy)
star.StringResult = append(star.StringResult, record)
}
star.Length = len(star.StringResult)
}
// Query 进行Query操作
func (star *StarDB) Query(args ...interface{}) (*StarRows, error) {
var err error
effect := new(StarRows)
if err = star.DB.Ping(); err != nil {
return effect, err
}
if len(args) == 0 {
return effect, errors.New("no args")
}
if len(args) == 1 {
sql := args[0]
if star.Rows, err = star.DB.Query(sql.(string)); err != nil {
return effect, err
}
effect.Rows = star.Rows
effect.parserows()
return effect, nil
}
sql := args[0]
stmt, err := star.DB.Prepare(sql.(string))
if err != nil {
return effect, err
}
defer stmt.Close()
var para []interface{}
for k, v := range args {
if k != 0 {
switch vtype := v.(type) {
default:
para = append(para, vtype)
}
}
}
if star.Rows, err = stmt.Query(para...); err != nil {
return effect, err
}
effect.Rows = star.Rows
effect.parserows()
return effect, nil
}
// Open 打开一个新的数据库
func (star *StarDB) Open(Method, ConnStr string) error {
var err error
star.DB, err = sql.Open(Method, ConnStr)
if err != nil {
return err
}
err = star.DB.Ping()
return err
}
// Close 关闭打开的数据库
func (star *StarDB) Close() error {
if err := star.DB.Close(); err != nil {
return err
}
return star.DB.Close()
}
// Exec 执行Exec操作
func (star *StarDB) Exec(args ...interface{}) (sql.Result, error) {
var err error
var effect sql.Result
if err = star.DB.Ping(); err != nil {
return effect, err
}
if len(args) == 0 {
return effect, errors.New("no args")
}
if len(args) == 1 {
sql := args[0]
if _, err = star.DB.Exec(sql.(string)); err != nil {
return effect, err
}
return effect, nil
}
sql := args[0]
stmt, err := star.DB.Prepare(sql.(string))
if err != nil {
return effect, err
}
defer stmt.Close()
var para []interface{}
for k, v := range args {
if k != 0 {
switch vtype := v.(type) {
default:
para = append(para, vtype)
}
}
}
if effect, err = stmt.Exec(para...); err != nil {
return effect, err
}
return effect, nil
}
// FetchAll 把结果集全部转为key-value型<string>数据
5 years ago
func FetchAll(rows *sql.Rows) (error, map[int]map[string]string) {
var ii int = 0
records := make(map[int]map[string]string)
columns, err := rows.Columns()
if err != nil {
return err, records
}
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
for rows.Next() {
if err := rows.Scan(scanArgs...); err != nil {
return err, records
}
record := make(map[string]string)
for i, col := range values {
switch vtype := col.(type) {
case float64:
record[columns[i]] = strconv.FormatFloat(vtype, 'f', -1, 64)
case int64:
record[columns[i]] = strconv.FormatInt(vtype, 10)
case string:
record[columns[i]] = vtype
case nil:
record[columns[i]] = ""
5 years ago
default:
record[columns[i]] = string(vtype.([]byte))
}
}
records[ii] = record
ii++
}
return nil, records
}
// OpenDB 打开一个数据库
5 years ago
func OpenDB(Method, ConnStr string) error {
var err error
DBRes, err = sql.Open(Method, ConnStr)
if err != nil {
return err
}
err = DBRes.Ping()
return err
}
// CloseDB 关闭打开的数据库
5 years ago
func CloseDB() {
DBRes.Close()
DBRows.Close()
}
// Query 进行数据库查询操作
5 years ago
func Query(args ...interface{}) (error, map[int]map[string]string) {
var err error
records := make(map[int]map[string]string)
if err = DBRes.Ping(); err != nil {
return err, records
}
if len(args) == 0 {
return errors.New("no args"), records
}
if len(args) == 1 {
sql := args[0]
if DBRows, err = DBRes.Query(sql.(string)); err != nil {
return err, records
}
return FetchAll(DBRows)
}
sql := args[0]
stmt, err := DBRes.Prepare(sql.(string))
defer stmt.Close()
if err != nil {
return err, records
}
var para []interface{}
for k, v := range args {
if k != 0 {
switch vtype := v.(type) {
default:
para = append(para, vtype)
}
}
}
if DBRows, err = stmt.Query(para...); err != nil {
return err, records
}
return FetchAll(DBRows)
}
// DBExec 进行数据库EXEC查询操作
5 years ago
func DBExec(args ...interface{}) error {
var err error
if err = DBRes.Ping(); err != nil {
return err
}
if len(args) == 0 {
return errors.New("no args")
}
if len(args) == 1 {
sql := args[0]
if _, err = DBRes.Exec(sql.(string)); err != nil {
return err
}
return nil
}
sql := args[0]
stmt, err := DBRes.Prepare(sql.(string))
defer stmt.Close()
if err != nil {
return err
}
var para []interface{}
for k, v := range args {
if k != 0 {
switch vtype := v.(type) {
default:
para = append(para, vtype)
}
}
}
if _, err = stmt.Exec(para...); err != nil {
return err
}
return nil
}