WebGin 简单框架 - dao/mysql

2020-12-10 | 129 浏览

WebGin简单框架-dao/mysql

Web目录结构

C:.
├─.idea
│  └─dictionaries
├─controllers
├─dao
│  ├─mysql
│  └─redis
├─dist
│  └─static
│      ├─css
│      ├─fonts
│      └─js
├─logger
├─logic
├─models
├─pkg
│  ├─myJWT
│  └─snowflake
├─routers
├─setting
└─main.go

1.mysql.go

mysql.go

package mysql

import (
	"fmt"

	"github.com/spf13/viper"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

// Init 初始化MySQL连接
func Init() (err error) {
	// "user:password@tcp(host:port)/dbname"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true&loc=Local",
		viper.GetString("mysql.user"),
		viper.GetString("mysql.password"),
		viper.GetString("mysql.host"),
		viper.GetInt("mysql.port"),
		viper.GetString("mysql.db"),
	)
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		return
	}
	// 设置最大连接数
	db.SetMaxOpenConns(200)
	// 设置最大空闲连接数
	db.SetMaxIdleConns(20)
	return
}

// Close 程序退出时释放MySQL连接
// 不直接对外暴露db变量，而是对外暴露一个Close方法
func Close() {
	err := db.Close()
	fmt.Println(err)
}

2.answer.go

answer.go

package mysql

import (
	"webapp/models"

	"go.uber.org/zap"
)

const (
	createAnswer = `insert into answer(answer_id, content, author_id, question_id) values(?,?,?,?)`
)

func CreateAnswer(answer *models.Answer) (err error) {

	_, err = db.Exec(createAnswer, answer.AnswerID, answer.Content, answer.AuthorID, answer.QuestionID)
	if err != nil {
		zap.L().Error("create answer failed", zap.Error(err))
		return
	}
	return
}

func GetAnswerList(questionID uint64, offset, limit int) (answerList []*models.Answer, err error) {

	sqlStr := `select 
					answer_id, content,
					vote_up_count, author_id, status,
					create_time, update_time
				 from 
				 	answer
				where question_id=? order by id desc
				limit ?, ?`

	err = db.Select(&answerList, sqlStr, questionID, offset, limit)
	if err != nil {
		zap.L().Error("GetAnswerList failed", zap.Error(err))
		return
	}
	return
}

func GetAnswerCount(questionID uint64) (answerCount int, err error) {

	sqlStr := `select 
							count(answer_id)
						from 
							answer
						where question_id=?`

	err = db.Get(&answerCount, sqlStr, questionID)
	if err != nil {
		zap.L().Error("GetAnswerCount failed", zap.Error(err))
		return
	}
	return
}

3.category.go

category.go

package mysql

import (
	"database/sql"
	"webapp/models"

	"github.com/jmoiron/sqlx"
	"go.uber.org/zap"
)

func GetCategoryList() (categoryList []*models.Category, err error) {

	sqlStr := "select category_id, category_name from category"
	err = db.Select(&categoryList, sqlStr)
	if err == sql.ErrNoRows {
		err = nil
		return
	}
	return
}
func MGetCategory(categoryIDs []int32) (categoryMap map[int32]*models.Category, err error) {

	sqlStr := "select category_id, category_name from category where category_id in(?)"

	inSqlStr, params, err := sqlx.In(sqlStr, categoryIDs)
	if err != nil {
		zap.L().Error("sqlx.In failed", zap.String("sqlStr", sqlStr), zap.Error(err))
		return
	}

	categoryMap = make(map[int32]*models.Category, len(categoryIDs))
	var categoryList []*models.Category
	err = db.Select(&categoryList, inSqlStr, params...)
	if err != nil {
		zap.L().Error("MGetCategory failed", zap.Any("categoryIDs", categoryIDs), zap.Error(err))
		return
	}

	for _, v := range categoryList {
		categoryMap[v.CategoryID] = v
	}
	return
}

4.error_code.go

error_code.go

package mysql

import "errors"

var (
	ErrorUserExit      = errors.New("用户已存在")
	ErrorUserNotExit   = errors.New("用户不已存在")
	ErrorPasswordWrong = errors.New("密码错误")
	ErrorGenIDFailed   = errors.New("创建用户ID失败")
	ErrorInvalidID     = errors.New("无效的ID")
	ErrorQueryFailed   = errors.New("查询数据失败")
	ErrorInsertFailed  = errors.New("插入数据失败")
)

5.question.go

question.go

package mysql

import (
	"webapp/models"

	"go.uber.org/zap"
)

func CreateQuestion(question *models.Question) (err error) {

	sqlStr := `insert into question(
		question_id,  caption, content, author_id, category_id)
		values(?,?,?,?,?)`

	_, err = db.Exec(sqlStr, question.QuestionID, question.Caption,
		question.Content, question.AuthorID, question.CategoryID)
	if err != nil {
		zap.L().Error("create question failed", zap.Any("question", question), zap.Error(err))
		return
	}
	return
}

func GetQuestionList(offset, limit int) (questionList []*models.Question, err error) {
	sqlStr := `select 
						question_id, caption, content, author_id, category_id, create_time
					from 
						question order by id desc
						limit ?, ?`
	err = db.Select(&questionList, sqlStr, offset, limit)
	if err != nil {
		zap.L().Error("get question list failed", zap.Error(err))
		return
	}
	return
}

func GetQuestionCount() (answerCount int, err error) {

	sqlStr := `select 
							count(question_id)
						from 
							question`
	err = db.Get(&answerCount, sqlStr)
	if err != nil {
		zap.L().Error("GetQuestionCount failed", zap.Error(err))
		return
	}
	return
}

func CheckQuestionIDExist(questionID uint64) bool {
	sqlStr := `select count(id) from question where question_id=?`
	count := 0
	db.Get(&count, sqlStr, questionID)
	return count > 0
}

func GetQuestion(questionID uint64) (question *models.Question, err error) {

	question = &models.Question{}
	sqlStr := `select 
							question_id, caption, content, author_id, category_id, create_time
						from 
							question
						where question_id=?`
	err = db.Get(question, sqlStr, questionID)
	if err != nil {
		zap.L().Error("get question failed", zap.Error(err))
		return
	}
	return
}

6.user.go

user.go

package mysql

import (
	"crypto/md5"
	"database/sql"
	"encoding/hex"
	"webapp/models"
	"webapp/pkg/snowflake"

	"github.com/jmoiron/sqlx"
	"go.uber.org/zap"
)

// 存放数据库相关的操作，增删改查

var secret = "夏天夏天悄悄过去"

// encryptPassword 加密
func encryptPassword(data []byte) (result string) {
	h := md5.New()
	h.Write([]byte(secret))                // 先加盐
	return hex.EncodeToString(h.Sum(data)) // 再md5并转为十六进制字符串
}

// Register 将指定用户注册
func Register(user *models.User) (err error) {
	// 1. 先判断当前用户名是否已经被注册过
	if CheckUserIsExist(user.UserName) {
		return ErrorUserExit
	}
	// 2. 明文的密码要加盐加密处理处理才能入库
	password := encryptPassword([]byte(user.Password))
	// user_id
	userID, err := snowflake.GenID()
	if err != nil {
		zap.L().Error("snowflake.GenID failed", zap.Error(err))
		return err
	}
	// 3. 入库
	sqlStr := `insert into user (user_id, username, password) values (?, ?,?)`
	_, err = db.Exec(sqlStr, userID, user.UserName, password)
	return
	// 4. 返回
}

func CheckUserIsExist(username string) bool {
	sqlStr := "select count(user_id) from user where username = ?"
	var count int64
	err := db.Get(&count, sqlStr, username)
	if err != nil && err != sql.ErrNoRows {
		zap.L().Error("query user exist failed", zap.Error(err))
		return true
	}
	return count > 0
}

func Login(user *models.User) (err error) {
	originPassword := user.Password // 记录一下原始密码
	sqlStr := "select user_id, username, password from user where username = ?"
	err = db.Get(user, sqlStr, user.UserName)
	if err != nil && err != sql.ErrNoRows {
		// 查询数据库出错
		return
	}
	if err == sql.ErrNoRows {
		// 用户不存在
		return ErrorUserNotExit
	}
	// 生成加密密码与查询到的密码比较
	password := encryptPassword([]byte(originPassword))
	if user.Password != password {
		return ErrorPasswordWrong
	}
	return
}

func GetUserInfoList(idList []uint64) (userList []*models.User, err error) {
	userList = make([]*models.User, 0, len(idList))
	sqlStr := `select user_id, username from user where user_id in (?)`
	inSql, args, err := sqlx.In(sqlStr, idList)
	if err != nil {
		zap.L().Error("build sqlx.In failed", zap.Error(err))
		return
	}
	queryStr := db.Rebind(inSql)
	err = db.Select(&userList, queryStr, args...)
	if err != nil {
		zap.L().Error("db.Select failed", zap.Error(err))
		return
	}
	return
}

func GetUserByID(idStr string) (user *models.User, err error) {
	user = new(models.User)
	sqlStr := `select user_id, username from user where user_id = ?`
	err = db.Get(user, sqlStr, idStr)
	return
}

DevOps