golang 数据库操作

golang操作数据库一般使用开源项目gorm，该项目拥有15000多star，功能较全面。

简单增删改查

类似于java的hibernate将数据封装到结构体（java到对象）中进行操作

package models

import (
	"fmt"
	"github.com/astaxie/beego" "github.com/jinzhu/gorm" "riskcontrol/database" "riskcontrol/utils/snowflake" "time" ) const( score = "user_score" ) type ScoreModel struct { BaseModel UserId string `json:"user_id",gorm:"type:varchar(18);not null;comment:'用户id';"` Score int32 `json:"score",gorm:"type:int;null;comment:'分数'"` BindFacebook bool `json:"bind_facebook",gorm:"type:boolean;not null;comment:'中文名称'"` //IsValid bool `json:"-",gorm:"type:boolean;not null;default:1;comment:'是否有效(0：无效,1:有效)'"` } func init() { database.GetDB().AutoMigrate(&ScoreModel{}) } /** 设置匹配的表名称 */ func (ScoreModel) TableName() string { return score } func (userMode *ScoreModel)BeforeCreate (scope *gorm.Scope) error { id,err := snowflake.NewSnowFlake().Generate() if err != nil{ beego.Error("snowflake get id error",err) return err } currentTime := time.Now() scope.SetColumn("Id", id ) scope.SetColumn("CreateTime",currentTime) scope.SetColumn("UpdateTime",currentTime) return nil } func (userMode *ScoreModel)BeforeUpdate (scope *gorm.Scope) error { currentTime := time.Now() scope.SetColumn("UpdateTime",currentTime) return nil } func GetScoreByUserId(userId string) (ScoreModel,error) { var userScore ScoreModel err := database.GetDB().Where(&ScoreModel{UserId:userId}).Last(&userScore).Error return userScore,err } func InsertScore(userId string,score int32,bindFacebook bool,appId string) bool { scoreModel := ScoreModel{UserId:userId,Score:score,BindFacebook:bindFacebook} scoreModel.AppId = appId if err := database.GetDB().Create(&scoreModel).Error;err != nil{ beego.Error("AddScore error",err) return false } database.GetDB().NewRecord(scoreModel) return true } func UpdateScore(id int64, addScore int32,bindFacebook bool ) bool { ScoreModel := ScoreModel{Score:addScore,BindFacebook:bindFacebook} ScoreModel.ID = id database.GetDB().Model(&ScoreModel).Update(&ScoreModel) return false } 

 

自定义sql

更新

func UpdateScore(id int64, addScore int32,bindFacebook bool ) bool { sql := fmt.Sprintf("UPDATE user_score SET score = score + %d, bind_facebook = %t,update_time=now() where id = %d", addScore,bindFacebook,id) res := database.GetDB().Exec( sql ) num:= res.RowsAffected if num > 0{ return true } return false }

查询

func GetScoreHisByUserId(userId string,id string,up bool) ([]dto.ScoreHisDto,error) { var scoreHis []dto.ScoreHisDto partitionKey,_ := getPartitionKey() keyList := []int{partitionKey,partitionKey -1,partitionKey -2} sqlScript := " SELECT id, create_time ,remark,(score_after-score_before)/100 as add_score FROM score_his WHERE " if id != "" { if up == true { sqlScript += fmt.Sprintf("id > %s and ",id) }else{ sqlScript += fmt.Sprintf("id < %s and ",id) } } database.GetDB().Raw(sqlScript + " user_id = ? and partition_key in (?) order by id desc limit 20 ", userId, keyList ).Scan(&scoreHis) var err error return scoreHis,err }

ScoreHisDto的结构体为

type ScoreHisDto struct {

	Id       		string		`json:"id"`
	Remark       	string		`json:"remark"`
	CreateTime      time.Time	`json:"createTime"` AddScore float32 `json:"addScore"` }

注意，这里遇到一个坑，sql查询出的字段create_time查询出来后会被自动转为驼峰式createTime；这里和java mybatis不一样，mybatis在sql中需要把字段create_time设置别名createTime并映射到dto中到createTime上。

事务

var tx *gorm.DB
//开启事务
tx = database.GetDB().Begin()

sql1 := ""
tx.Exec( sql1 )

sql2 := ""
tx.Exec( sql2 )

//提交事务
tx.Commit()

 

异常处理

由于go没有try...catch...finally,在代码段每一个return前可能都要回滚事务，这样就很麻烦了。这时候可以使用golang到defer语句，该语句可在方法return前做一些操作，示例如下：

var err error
var tx *gorm.DB

//预定义方法 如果程序异常，则在方法return前回滚事务
defer func() { if err != nil && tx != nil{ beego.Error(err, "tx rollback...") tx.Rollback() } }()

 

参考文档

git地址如下https://github.com/jinzhu/gorm

中文文档地址http://gorm.book.jasperxu.com/

转载于:https://www.cnblogs.com/peislin/p/11549680.html