dxp-core/dxp-engine/dao/db_helper.go

package dao

import (
	"DataShare/global"
	"DataShare/model"
	"database/sql"
	"encoding/json"
	"errors"
	_ "gitee.com/chunanyong/dm"
	_ "github.com/go-sql-driver/mysql"
	"regexp"
	"strings"
	"time"

	_ "github.com/lib/pq"
	"github.com/sirupsen/logrus"
	_ "kingbase.com/gokb"
)

func DbConnect(sqlType string,connInfo string)(*sql.DB,error){
	//test:root:123456@(127.0.0.1:3306)/rw_system_db?charset=utf8&parseTime=True&loc=Local
	//  connInfo := "root:123456@(127.0.0.1:3306)/mysql_test?charset=utf8&parseTime=True&loc=Local
	//	connInfo := "dm://SYSDBA:SYSDBA@localhost:5236"  ==>dm8
	//	connInfo := "host=127.0.0.1 port=8000 user=rooot password=123456 dbname=pg_test sslmode=disable"  ==>pg

	var err error
	var db *sql.DB

	if sqlType == "mysql"{
		db, err = sql.Open("mysql", connInfo)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"open mysql failed, err:%s",err.Error())
			return nil,err
		}
	}else if sqlType == "dm8"{
		db, err = sql.Open("dm", connInfo)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"open mysql failed, err:%s",err.Error())
			return nil,err
		}
	}else if sqlType == "postgres"{
		db, err = sql.Open("postgres", connInfo)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"open postgres failed, err:%s",err.Error())
			return nil,err
		}
	}else if sqlType == "kingbase"{
		db, err = sql.Open("kingbase", connInfo)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"open mysql failed, err:%s",err.Error())
			return nil,err
		}
	}else{
		return nil, errors.New("不支持的数据库类型")
	}

	err = db.Ping()
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"ping mysql failed, err:%s",err.Error())
		return nil,err
	}
	//todo：并发配置
	if sqlType == "mysql"{
		db.SetMaxIdleConns(global.SystemConfig.Mysql.Conn.MaxIdleConn)
		db.SetMaxOpenConns(global.SystemConfig.Mysql.Conn.MaxOpenConn)
		db.SetConnMaxLifetime(5 * time.Minute)
	}

	return db,nil
}


func GetCount(db *sql.DB,sqlString string, params ...interface{}) (int,error){

	total := 0

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return 0, err
	}
	defer stmt.Close()
	var rows *sql.Rows

	rows, err = stmt.Query(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"query sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return 0, err
	}
	defer rows.Close()

	_, err = rows.Columns()
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"rows.Columns sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return 0, err
	}
	if rows.Next() {
		err = rows.Scan(&total)
	}

	return total, err
}

func GetOneData(db *sql.DB, sqlString string, params ...interface{}) (map[string]interface{}, error) {

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return nil, err
	}
	defer stmt.Close()

	var rows *sql.Rows
	rows, err = stmt.Query(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"query sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil, err
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"rows.Columns sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil, err
	}

	tableData := make([]map[string]interface{}, 0)
	count := len(columns)
	values := make([]interface{}, count)
	scanArgs := make([]interface{}, count)
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"rows.Scan sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
			return nil, err
		}
		entry := make(map[string]interface{})
		for i, col := range columns {
			v := values[i]
			b, ok := v.([]byte)
			if (ok) {
				entry[col] = string(b)
			} else {
				//所有都转换成字符串输出
				//strVal := fmt.Sprintf("%v",v)
				//entry[col] = strVal
				//原类型输出
				entry[col] = v
			}
		}
		tableData = append(tableData, entry)
	}
	if len(tableData)>0{
		return tableData[0],nil
	}

	return nil, nil
}

func GetData(db *sql.DB, sqlString string, params ...interface{}) ([]map[string]interface{},[]byte,string,int, error) {

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return nil,nil,"", 0,err
	}
	defer stmt.Close()

	var rows *sql.Rows
	rows, err = stmt.Query(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"query sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil,nil,"",0, err
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"rows.Columns sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil,nil,"",0, err
	}

	tableData := make([]map[string]interface{}, 0)
	count := len(columns)
	values := make([]interface{}, count)
	scanArgs := make([]interface{}, count)
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"rows.Scan sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
			return nil,nil,"",0, err
		}
		entry := make(map[string]interface{})
		for i, col := range columns {
			v := values[i]
			b, ok := v.([]byte)
			if (ok) {
				entry[col] = string(b)
			} else {
				//所有都转换成字符串输出
				//strVal := fmt.Sprintf("%v",v)
				//entry[col] = strVal
				//原类型输出
				entry[col] = v
			}
		}
		tableData = append(tableData, entry)
	}

	jsonData, err := json.Marshal(tableData)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"json.Marshal failed, err:%s ",err.Error())
		return nil,nil,"",0, err
	}
	total := 0
	if strings.Contains(sqlString,"SQL_CALC_FOUND_ROWS") {
		total,err = GetCount(db,"SELECT FOUND_ROWS() as total")
	}
	return tableData,jsonData,string(jsonData),total, nil
}

func GetData2(db *sql.DB, sqlString string,cols []model.Access_Data_Colnums, params ...interface{}) ([]map[string]interface{},[]byte,string,int, error) {

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return nil,nil,"", 0,err
	}
	defer stmt.Close()

	var rows *sql.Rows
	rows, err = stmt.Query(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"query sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil,nil,"",0, err
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"rows.Columns sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return nil,nil,"",0, err
	}

	tableData := make([]map[string]interface{}, 0)
	count := len(columns)
	values := make([]interface{}, count)
	scanArgs := make([]interface{}, count)
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,"rows.Scan sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
			return nil,nil,"",0, err
		}
		entry := make(map[string]interface{})
		for i, col := range columns {
			for zz:=0;zz<len(cols);zz++{
				if col == cols[zz].Name {
					v := values[i]
					b, ok := v.([]byte)
					if (ok) {
						entry[col] = string(b)
					} else {
						//所有都转换成字符串输出
						//strVal := fmt.Sprintf("%v",v)
						//entry[col] = strVal
						//原类型输出
						entry[col] = v
					}
					//这里断言如果不正确，则把它洛道上面的[]byte类型中,省去断言
					_,ok = entry[col].(string)
					if ok {

						if cols[zz].DesensitizationRule!=nil{
							//脱敏处理
							//fmt.Println("val:",entry[col])
							//fmt.Println("regstr:",cols[zz].DesensitizationRule.RegStr)
							//fmt.Println("FillStr:",cols[zz].DesensitizationRule.FillStr)

							reg := regexp.MustCompile(cols[zz].DesensitizationRule.RegStr)
							entry[col] = reg.ReplaceAllString(entry[col].(string),cols[zz].DesensitizationRule.FillStr)
						}
					}
					break;
				}
			}


			//v := values[i]
			//b, ok := v.([]byte)
			//if (ok) {
			//	entry[col] = string(b)
			//} else {
			//	//所有都转换成字符串输出
			//	//strVal := fmt.Sprintf("%v",v)
			//	//entry[col] = strVal
			//	//原类型输出
			//	entry[col] = v
			//}
			////这里断言如果不正确，则把它洛道上面的[]byte类型中,省去断言
			//_,ok = entry[col].(string)
			//if ok {
			//	for j:=0;j<len(cols);j++{
			//		if col == cols[j].Name && cols[j].DesensitizationRule!=nil{
			//			//脱敏处理
			//			reg := regexp.MustCompile(cols[j].DesensitizationRule.RegStr)
			//			entry[col] = reg.ReplaceAllString(entry[col].(string),cols[j].DesensitizationRule.FillStr)
			//			break;
			//		}
			//	}
			//}
		}
		tableData = append(tableData, entry)
	}

	jsonData, err := json.Marshal(tableData)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"json.Marshal failed, err:%s ",err.Error())
		return nil,nil,"",0, err
	}
	total := 0
	if strings.Contains(sqlString,"SQL_CALC_FOUND_ROWS") {
		total,err = GetCount(db,"SELECT FOUND_ROWS() as total")
	}

	return tableData,jsonData,string(jsonData),total, nil
}

func Exec(db *sql.DB, sqlString string, params ...interface{}) (error) {

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"stmt Exec sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return err
	}

	return  err
}

func Insert(db *sql.DB, sqlString string, params ...interface{}) (int64,error) {

	stmt, err := db.Prepare(sqlString)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"Prepare(%s) failed, err:%s ",sqlString,err.Error())
		return 0,err
	}
	defer stmt.Close()

	result, err := stmt.Exec(params...)
	if err != nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"stmt Exec sql(%s) ,param(%v) failed, err:%s ",sqlString,params,err.Error())
		return 0,err
	}

	return result.LastInsertId()
}