golang xorm 示例 返回临时变量的引用

golang xorm

http://www.xorm.io/docs/

// var engine *xorm.Engine

var everyone []Userinfo
err := engine.Find(&everyone)

golang 可以返回临时变量的引用, 不会panic

 

xorm 数据库表 实体类定义

实体类Entity中有  func (* Entity) TableName() string { return "EntityTableName"; } 方法, 那么

// engine.Table(this.TableName()) 不需要再指定表名

2.4.Column definition

Struct tag defines something for column as basic sql concepts, such as :

type User struct {
    Id   int64
    Name string  `xorm:"varchar(25) not null unique 'usr_name'"`
}

Data types are different in different DBMS. So xorm makes own data types definition to keep compatible. Details is in document Column Types.

The following table is field mapping rules, the keyword is not case sensitive except column name：

name or 'name'	Column Name, optional
pk	If column is Primary Key
support over 30 kinds of column types, details in [Column Types](http://gobook.io/read/github.com/go-xorm/manual-en-US/chapter-02/1.mapping.html)	column type
autoincr	If autoincrement column
[not ]null | notnull	if column could be blank
unique/unique(uniquename)	column is Unique index; if add (uniquename), the column is used for combined unique index with the field that defining same uniquename.
index/index(indexname)	column is index. if add (indexname), the column is used for combined index with the field that defining same indexname.
extends	use for anonymous field, map the struct in anonymous field to database
-	This field will not be mapping
->	only write into database
<-	only read from database
created	This field will be filled in current time on insert
updated	This field will be filled in current time on insert or update
version	This field will be filled 1 on insert and autoincrement on update
default 0 | default 'name'	column default value

Some default mapping rules：

• 1. If field is name of Id and type of int64, xorm makes it as auto increment primary key. If another field, use struct tag xorm:"pk".
• 1. String is corresponding to varchar(255).
• 1. Support custom type as type MyString string，slice, map as field type. They are saving as Text column type and json-encode string. Support Blob column type with field type []byte or []uint8.
• 1. You can implement Conversion interface to define your custom mapping rule between field and database data.

type Conversion interface {
    FromDB([]byte) error
    ToDB() ([]byte, error)
}

golang xorm 实体类中定义 Find方法，根据id主键查找对应的行

func UserFind(id int64) (*UserModel, error) {
	var user = UserModel{Id:id}
	var has bool
	var err error
	has, err = DbDefault().Get(&user)
	if ! has {
		return nil, err
	}
	return &user, err
}

对于  // var engine *xorm.Engine

Insert方法返回  int64, error

int64 指的是受影响的行数，不是 last insert id

 

struct定义与数据库表结构

type StudyCard struct {
	Id int64 `xorm:"not null pk autoincr INT(10)"`
	SerialNumber string    `json:"serialnumber" xorm:"not null 'serialnumber'"`
	ActiveCode   string    `json:"activecode" xorm:"not null 'activecode'"`
	PkgID        int32     `json:"pkgid" xorm:"not null 'pkgid'"`
	ActiveDate   time.Time `json:"activedate" xorm:"not null 'activedate'" binding:"required"`
	RestrictData string    `json:"restrictdata" xorm:"not null 'restrictdata'" binding:"required"`
	CardType     int       `json:"cardtype" xorm:"not null 'cardtype'" binding:"required"`
	IsValid      int8      `json:"isvalid" xorm:"not null 'isvalid'" binding:"required"`
	UserID       int64     `json:"userid" xorm:"not null 'userid'" binding:"required"`
	CreatedAt    time.Time `json:"inserttime" xorm:"not null 'inserttime'" binding:"required"`
	Expiretime   string    `json:"expiretime" xorm:"not null 'expiretime'" binding:"required"`
}

show create table W_StudyCardInfo

CREATE TABLE `W_StudyCardInfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `serialnumber` varchar(255) NOT NULL,
  `activecode` varchar(255) NOT NULL,
  `pkgid` mediumint(8) unsigned NOT NULL,
  `activedate` timestamp NULL DEFAULT NULL,
  `restrictdata` text NOT NULL,
  `cardtype` int(11) NOT NULL DEFAULT '1',
  `isvalid` tinyint(1) NOT NULL DEFAULT '1',
  `userid` int(10) unsigned DEFAULT NULL,
  `inserttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiretime` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_studycardinfo_pkgid` (`pkgid`),
  KEY `idx_studycardinfo_userid` (`userid`),
  KEY `idx_studycardinfo_activecode` (`activecode`),
  KEY `idx_studycardinfo_serialnumber` (`serialnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 

Id int64 `xorm:"not null pk autoincr INT(10)"`

必须跟数据库定义完全一致，或者不定义
如果一个地方是int(10), 另一个地方是int(11)会出错， 报一个反射 reflect的错误

 

 

* UserValidateRecord.go

package models

import (
	"fmt"
	"github.com/spf13/viper"
	"io/ioutil"
	"math/rand"
	"net/http"
	"strings"
	"time"
)

type UserValidateRecord struct {
	Id int64 `xorm:"not null pk autoincr INT(11)"`
	Isvalid      int8      `xorm:"not null 'isvalid'" binding:"required"`
	Mobile       string    `xorm:"not null 'mobile'" binding:"required"`
	Sendnum      int       `xorm:"not null 'sendnum'" binding:"required"`
	Userid       int64     `xorm:"not null 'userid'" binding:"required"`
	Expirytime   time.Time `xorm:"not null 'expirytime'" binding:"required"`
	Validatecode string    `xorm:"not null 'validatecode'" binding:"required"`
	CreatedAt    time.Time `xorm:"not null 'inserttime'"`
}

func (*UserValidateRecord) TableName() string {
	return "W_UserValidateRecord"
}

func (this *UserValidateRecord) Create() error {
	var err error
	this.CreatedAt = time.Now()
	_, err = DbDefault().Insert(this)
	return err
}

func (this *UserValidateRecord) Save() error {
	var engine *xorm.Engine = nil
	engine = DbDefault()
	// insert
	if this.Id == 0 {
		this.CreatedAt = time.Now()
		_, err := engine.Insert(this)
		return err
	}
	// update
	_ ,err := engine.ID(this.Id).Update(this)
	return err
}

// update columns sendnum, isvalid
func (this *UserValidateRecord) Update() error {
	var engine *xorm.Engine = nil
	engine = DbDefault()
	_, err := engine.Table(this.TableName()).Where("id=?", this.Id).Update(this)
	return err
}

func GetEffectiveValidateCodeByUserIDAndMobile(userid uint64, mobile string) (*UserValidateRecord, error) {
	var all []UserValidateRecord
	var engine *xorm.Engine = nil
	engine = DbDefault()
	now := time.Now().Format("2006-01-02 15:04:05")
	err := engine.Where("userid=? and mobile=? and expirytime>=? and isvalid=1",
		userid, mobile, now).Limit(1).Desc("id").Find(&all)
	if err != nil {
		return nil, err
	}
	if all == nil || len(all) == 0 {
		return nil, err
	}
	return &all[0], err
}

func GenValidateCode(width int) string {
	numeric := [10]byte{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
	r := len(numeric)
	rand.Seed(time.Now().UnixNano())

	var sb strings.Builder
	for i := 0; i < width; i++ {
		fmt.Fprintf(&sb, "%d", numeric[rand.Intn(r)])
	}
	return sb.String()
}

func GetLastRecordBy(column string) func(value string) (*UserValidateRecord, error) {
	return func(value string) (*UserValidateRecord, error) {
		var u []UserValidateRecord
		err := DbDefault().Where(fmt.Sprintf("%s = ?", column), value).Desc("id").Limit(1).Find(&u)
		return &u[0], err
	}
}

// 给用户发送6位短信验证码 开发过程不发短信
func (this *UserValidateRecord) SendValidateCode() (string, error) {
	fmt.Printf("Sending validate code [%s] to mobile %s\n", this.Validatecode, this.Mobile)
	var SMS_ENABLED bool = viper.GetBool("sms_enabled")
	if !SMS_ENABLED {
		return "-1234123", nil
	}
	content := fmt.Sprintf("欢迎您使用xxxxx服务，您的验证码为%s, "+
		"请在xxxxxx上输入验证码完成验证，有效期为30分钟。", this.Validatecode)

	return MongateSendSubmit(this.Mobile, content)
}

// 给用户发送激活码  开发过程不发短信
func SendActiveCode(mobile string, activeCode string) (string, error) {
	fmt.Println("Enter send active code")
	var SMS_ENABLED bool = viper.GetBool("sms_enabled")
	if !SMS_ENABLED {
		return "-1234123", nil
	}
	content := fmt.Sprintf(`您的课程激活码为：%s，请完成激活，
	   下载app请戳https://www.baidu.com`, activeCode)
	return MongateSendSubmit(mobile, content)
}

const SMS_ASMX = "http://xx.xx.xx.xx:8016/MWGate/wmgw.asmx" // 梦网请求url前缀
const SMS_HOST = "xx.xx.xx.xx:8016"                         // 梦网host

func MongateSendSubmit(mobile string, content string) (string, error) {
	client := &http.Client{}

	url := SMS_ASMX + "/MongateSendSubmit"
	fmt.Println(url)

	var SMS_USERID = viper.GetString("sms_userid")     // 梦网充值账号
	var SMS_PASSWORD = viper.GetString("sms_password") // 梦网充值密码
	postString := fmt.Sprintf("userId=%s&password=%s", SMS_USERID, SMS_PASSWORD) + "&" +
		fmt.Sprintf("pszMobis=%s", mobile) + "&" +
		fmt.Sprintf("pszMsg=%s", content) + "&iMobiCount=1&pszSubPort=*&MsgId=111"

	req, err := http.NewRequest("POST", url, strings.NewReader(postString))
	if err != nil {
		return "", err
	}

	req.Header.Set("Host", SMS_HOST)
	req.Header.Set("Content-Type", "application/x-www-form-urlencoded")
	req.Header.Set("Content-Length", fmt.Sprintf("%d", len(postString)))

	resp, err := client.Do(req)
	defer resp.Body.Close()

	if err != nil {
		return "", err
	}
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return "", err
	}
	return string(body), nil
}

func MongateQueryBalance() (string, error) {
	client := &http.Client{}

	url := SMS_ASMX + "/MongateQueryBalance"
	fmt.Println(url)

	var SMS_USERID = viper.GetString("sms_userid")     // 梦网充值账号
	var SMS_PASSWORD = viper.GetString("sms_password") // 梦网充值密码

	postString := fmt.Sprintf("userId=%s&password=%s", SMS_USERID, SMS_PASSWORD)
	req, err := http.NewRequest("POST", url, strings.NewReader(postString))
	if err != nil {
		return "", err
	}

	req.Header.Set("Host", SMS_HOST)
	req.Header.Set("Content-Type", "application/x-www-form-urlencoded")
	req.Header.Set("Accept", "text/xml")
	req.Header.Set("Content-Length", fmt.Sprintf("%d", len(postString)))

	resp, err := client.Do(req)
	defer resp.Body.Close()

	if err != nil {
		return "", err
	}
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return "", err
	}
	return string(body), nil
}

 

* 创建连接数据库句柄

package models

import (
	"github.com/go-xorm/xorm"
	"github.com/spf13/viper"
	"gitlab.easytech-main.com/mingzhanghui/ginplus/db"
)

const (
	DbNameEasyWeb = "easyweb"
)

func DbDefault() *xorm.Engine {
	dbConn := db.Use(DbNameEasyWeb)
	if viper.GetInt("sql_show") == 1 {
		dbConn.ShowSQL(true)
	} else {
		dbConn.ShowSQL(false)
	}

	return dbConn
}

func DB(dbName string) *xorm.Engine {
	dbConn := db.Use(dbName)
	if viper.GetInt("sql_show") == 1 {
		dbConn.ShowSQL(true)
	} else {
		dbConn.ShowSQL(false)
	}

	return dbConn
}

 

* 切换数据库
 

package db

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"gitlab.easytech-main.com/mingzhanghui/ginplus/log"
	"gopkg.in/yaml.v2"
	"io/ioutil"
	"strings"
	"time"
)

var (
	dbCfg     *dbConfig
	DataGroup map[string]*xorm.EngineGroup
)

type dbGroupConfig struct {
	OpenConns       int           `yaml:"openConns"`
	IdleConns       int           `yaml:"idleConns"`
	ConnMaxLifetime int           `yaml:"maxLifetime"`
	Master          *engineConfig `yaml:"master`
	Slaves          []string      `yaml:"slaves"`
}
type dbConfig struct {
	Adapter string                    `yaml:"adapter"`
	Db      map[string]*dbGroupConfig `yaml:"db"`
}
type engineConfig struct {
	Dsn      string            `yaml:"dsn"`
	Username string            `yaml:"username"`
	Password string            `yaml:"password"`
	Protocol string            `yaml:"protocol"`
	Addr     string            `yaml:"addr"`
	Host     string            `yaml:"host"`
	Port     int               `yaml:"port"`
	Params   map[string]string `yaml:"params"`
}

func initDataGroup() map[string]*xorm.EngineGroup {
	var groups = make(map[string]*xorm.EngineGroup)
	if dbCfg == nil {
		log.Error("db config setting error")
	}
	for g, e := range dbCfg.Db {
		dataSourceSlice := make([]string, 0)
		dataSourceSlice = append(dataSourceSlice, e.Master.parseDns(g))
		for _, sn := range dbCfg.Db[g].Slaves {
			dataSourceSlice = append(dataSourceSlice, sn)
		}
		if len(dataSourceSlice) > 0 {
			group, err := xorm.NewEngineGroup(dbCfg.Adapter, dataSourceSlice)
			if err != nil {
				log.Warn("创建数据组链接错误：" + err.Error())
			}
			group.SetMaxOpenConns(dbCfg.Db[g].OpenConns)
			group.SetMaxIdleConns(dbCfg.Db[g].IdleConns)
			group.SetConnMaxLifetime(time.Duration(dbCfg.Db[g].ConnMaxLifetime) * time.Second)
			//group.SetConnMaxLifetime(5*time.Minute)
			groups[g] = group
			log.Info(fmt.Sprintf("%s EngineGroup Opened", g))
		}
	}
	return groups
}

func Use(dbName string) *xorm.Engine {
	if DataGroup == nil {
		DataGroup = initDataGroup()
	}
	if g, ok := DataGroup[dbName]; ok {
		return g.Engine
	} else {
		log.Error(dbName + " - Database does not exist.")
	}
	return nil
}

func Init(dbCfgFile string) {
	buf, err := ioutil.ReadFile(dbCfgFile)
	if err != nil {
		log.Warn(dbCfgFile + "文件读取失败")
	}
	err = yaml.Unmarshal(buf, &dbCfg)
	if err != nil {
		log.Warn(dbCfgFile + "解析失败")
	}
	DataGroup = initDataGroup()
}
func (e *engineConfig) parseDns(dbname string) string {
	if e.Dsn == "" {
		var addr string
		switch e.Protocol {
		case "", "tcp":
			if e.Host == "" {
				e.Host = "127.0.0.1"
			}
			if e.Port == 0 {
				e.Port = 3306
			}
			addr = fmt.Sprintf("tcp(%s:%d)", e.Host, e.Port)
		default:
			addr = fmt.Sprintf("%s(%s)", e.Protocol, e.Addr)
		}
		var params = make([]string, 0)
		for k, v := range e.Params {
			params = append(params, fmt.Sprintf("%s=%v", k, v))
		}
		var dsnParams = ""
		if len(params) > 0 {
			dsnParams = "?" + strings.Join(params, "&")
		}
		e.Dsn = fmt.Sprintf("%s:%s@%s/%s%s",
			e.Username,
			e.Password,
			addr,
			dbname,
			dsnParams,
		)
	}
	return e.Dsn
}

func Close() {
	for n, db := range DataGroup {
		db.Close()
		log.Info(fmt.Sprintf("%s EngineGroup Closed", n))
	}
}