GORM

加载数据库连接池

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package db

import (
	"fmt"

	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

var ORM *gorm.DB

func LoadDB() error {
	var e error
	ORM, e = gorm.Open("mysql", "root:pwd@tcp(localhost:3306)/testdb?charset=utf8&parseTime=True&loc=Local")
	if e != nil {
		return e
	}
	e = ORM.DB().Ping()
	if e != nil {
		return e
	}
	ORM.DB().SetMaxOpenConns(100)
	ORM.DB().SetMaxIdleConns(10)
	ORM.SingularTable(true)
	return nil
}

定义model

1
2
3
4
5
6
7
8
// CodeSnippet model of snippet
type CodeSnippet struct {
	ID        string    `gorm:"column:id;primary_key;type:char(12)"`
	AuthorID  string    `gorm:"column:author_id;type:char(12);not null"`
	Title     string    `gorm:"column:title;type:varchar(500);not null"`
	Snippet   string    `gorm:"column:snippet;type:text;not null"`
	CreatedAt time.Time `gorm:"column:created_at;type:datetime;not null"`
}
1
2
3
4
5
6
7
8
9
10
11
mysql> show create table code_snippet\G;
*************************** 1. row ***************************
       Table: code_snippet
Create Table: CREATE TABLE `code_snippet` (
  `id` char(12) NOT NULL,
  `author_id` char(12) NOT NULL,
  `title` varchar(500) NOT NULL,
  `snippet` text NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

column: 列名
type: 数据类型

创建一条数据

1
2
3
func CreateSnippet(snippet *CodeSnippet) error {
	return db.ORM.Create(snippet).Error
}

查询 by id

1
2
3
4
5
6
7
8
func GetSnippetByID(id string) (*CodeSnippet, error) {
	s := CodeSnippet{}
	if err := db.ORM.Where("id=?", id).First(&s).Error; err != nil {
		return nil, err
	}

	return &s, nil
}

查询列表

1
2
3
4
5
6
7
8
9
func GetSnippetsByAuthor(AuthorID string, limit, offset int32) ([]*CodeSnippet, error) {
	var snippets []*CodeSnippet
	err := db.ORM.Where("author_id=?", AuthorID).Order("created_at desc").Limit(limit).Offset(offset).Find(&snippets).Error
	if err != nil {
		return nil, err
	}

	return snippets, nil
}

Order by, limit, offset

1
2
3
4
5
6
7
8
9
func GetSnipeptsByAuthor(authorID string, limit, offset int32) ([]*Collection, error) {
	var snippets []*Snippets
	err := db.Db.Where("author_id=?", authorID).Order("created_at desc").Limit(limit).Offset(offset).Find(&snippets).Error
	if err != nil {
		return nil, err
	}

	return snippets, nil
}

更新

1
2
3
4
5
6
7
8
9
10
11
12
func UpdateSnippet(snippet *CodeSnippet) (string, error) {
	fieldUpdates := map[string]interface{}{
		"title":    snippet.Title,
		"snippet":  snippet.Snippet,
	}
	err := tx.Model(snippet).Where("id = ? and author_id = ?", snippet.ID, snippet.AuthorID).Updates(fieldUpdates).Error
	if err != nil {
		return "", err
    }
    
	return action.ID, nil
}

删除

1
2
3
4
func DeleteSnippet(id string) error {
    s := Snippet{ID:id}
    return db.ORM.Delete(&s).Error
}

事务

1
2
3
4
5
6
tx := db.ORM.Begin()

// tx operations

tx.Rollback()
tx.Commit()