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/

golang 数据库操作

原文：https://www.cnblogs.com/peislin/p/11549680.html