gorm的简单应用

gorm 简单应用

创建数据库testdb:

create database testdb;
use testdb;

创建三张数据库表：

 CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `password` LONGTEXT,
  PRIMARY KEY (`id`)
);

CREATE TABLE `permission` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `info` longtext,
  PRIMARY KEY (`id`)
);

CREATE TABLE `user_permission` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint DEFAULT NULL,
  `permission_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
);

三张表如下：

使用gorm创建数据库链接

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

var DB *gorm.DB

func init() {
	url := "127.0.0.1"
	database := "testdb"
	port := "3306"
	username := "xxxx"
	password := "xxxx"

	//dsn := "xxxx:xxxx@tcp(127.0.0.1:3306)/test_db?charset=utf8mb4&parseTime=True&loc=Local"

	dsn := username + ":" + password + "@tcp(" + url + ":" + port + ")/" + database + "?charset=utf8mb4&parseTime=True&loc=Local"

	var err error
	DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			//TablePrefix:   "", // table name prefix, table for `User` would be `t_users`
			SingularTable: true, // use singular table name, table for `User` would be `user` with this option enabled
			//NoLowerCase:   true,                              // skip the snake_casing of names
			//NameReplacer:  strings.NewReplacer("CID", "Cid"), // use name replacer to change struct/field name before convert it to db name
		},
	})
	if err != nil {
		panic(err)
	}
	
	const RunMode = "dev"
	if RunMode == "dev" {
		DB = DB.Debug()
	}
}

创建对应模型

type User struct {
	ID       int    `gorm:"primaryKey"`
	Name     string `gorm:"type:varchar(20);"`
	Password string
}

func (User) TableName() string {
	return "user"
}

type Permission struct {
	ID   int `gorm:"primaryKey"`
	Info string
}

func (Permission) TableName() string {
	return "permission"
}

type UserPermission struct {
	ID           int `gorm:"primaryKey"`
	UserID       int
	PermissionID int
}

func (UserPermission) TableName() string {
	return "user_permission"
}

AutoMigrate 可自动根据model创建表

err := DB.AutoMigrate(User{}, Permission{}, UserPermission{})
	if err != nil {
		panic(err)
	}

插入数据

func InsertUsers() error {
	users := []User{
		{Name: "张三", Password: "123456"},
		{Name: "李四", Password: "123456"},
		{Name: "王二麻子", Password: "123456"},
		{Name: "赵三狗子", Password: "123456"},
		{Name: "李大旁提", Password: "123456"},
	}
	return DB.Create(users).Error
}

func InsertUserPermissions() error {
	up := []UserPermission{
		{UserID: 1, PermissionID: 1},
		{UserID: 1, PermissionID: 2},
		{UserID: 1, PermissionID: 3},
		{UserID: 1, PermissionID: 4},
		{UserID: 2, PermissionID: 2},
		{UserID: 3, PermissionID: 3},
		{UserID: 4, PermissionID: 4},
	}
	return DB.Create(up).Error
}

func InsertPermissions() error {
	perm := []Permission{
		{ID: 1, Info: "修改相册"},
		{ID: 2, Info: "创建相册"},
		{ID: 3, Info: "删除相册"},
		{ID: 4, Info: "查看相册"},
	}
	return DB.Create(perm).Error
}

更新数据，未发现批量更新一组数据的API

func UpdateUserPasswordByCondition(name, NewPassword string) error {
	//  UPDATE `user` SET `password`='9999999' WHERE name = '张三'
	return DB.Model(User{}).Debug().
		Where("name = ?", "张三").
		Update("password", NewPassword).Error
}

func UpdateUserForID(id int) error {
	// UPDATE `user` SET `password`='123456' WHERE `id` = 1
	return DB.Debug().Model(&User{ID: id}).Update("password", "123456").Error
}

func UpdateUsersPasswordOfAll() error {
	// UPDATE `user` SET `password`=''
	DB.AllowGlobalUpdate = true // 开启全局更新,出了当前作用域，则自动关闭
	return DB.Debug().Model(&User{}).Update("password", "").Error
}

func UpdateUsers() error { // 更新一批数据，
	users, err := SelectUsers()
	if err != nil {
		return err
	}
	for i := range users {
		users[i].Password += "_123"
		err1 := DB.Updates(&users[i]).Error
		if err == nil {
			err = err1
		}
	}
	return err
}

// UpdateUsersForArray 暂时没有发现直接更新一批数据的API，只可以根据指定的条件，更新表中多行数据
func UpdateUsersForArray() error { // TODO 不支持的操作
	users, err := SelectUsers()
	if err != nil {
		return err
	}
	for i := range users {
		users[i].Password += "_123"
	}

	return DB.Updates(&users).Debug().Error
}

删除数据

func DeleteUserById(id int) error {
	return DB.Delete(User{ID: id}).Error
}

查询数据

func SelectUsers() (users []User, err error) {
	err = DB.Find(&users).Error
	return users, err
}

复杂查询，个人不习惯构造复杂的orm结构，所以直接使用原生的sql来写

func SelectUserNameAndPermissionInfoById(id int) []UserPerInfo {
	/*
		# 查询用户ID为1 的用户名，和其所有权限信息
		SELECT u.name,p.info FROM user AS u
		LEFT JOIN user_permission AS up ON up.user_id = u.id
		LEFT JOIN permission AS p ON up.permission_id = p.id
		WHERE u.id = 1;
	*/

	var upi []UserPerInfo
	sql := "SELECT u.name,p.info FROM `user` AS u " +
		"LEFT JOIN user_permission AS up ON up.user_id = u.id " +
		"LEFT JOIN permission AS p ON up.permission_id = p.id " +
		"WHERE u.id = ?"
	DB.Raw(sql, id).Scan(&upi)
	return upi
}

left join 的方式不会忽略左表的数据

		SELECT u.name,p.info FROM user AS u
		LEFT JOIN user_permission AS up ON up.user_id = u.id
		LEFT JOIN permission AS p ON up.permission_id = p.id
		WHERE u.id = 1;

from 后跟多张表的方式则忽略所有NULL数据

SELECT u.name, p.info 
FROM
`user` AS u, permission AS p, user_permission AS up 
WHERE 
u.id = up.user_id AND p.id = up.permission_id AND u.id = 1;

零值处理，零值状态不参与更新

type Zero struct {
	ID    int
	State int
}

err := DB.AutoMigrate(Zero{})
	if err != nil {
		panic(err)
	}

	DB.Create(&Zero{State: 1})
	DB.Updates(&Zero{ID:1,State: 0})

	zero := &Zero{}
	DB.First(zero)
	fmt.Println(zero) // &{1 1}  未更新

解决方式1，使用指针定义字段类型

type Zero struct {
	ID    int
	State *int
}

	err := DB.AutoMigrate(Zero{})
	if err != nil {
		panic(err)
	}

	s := new(int)
	*s = 1
	DB.Create(&Zero{State: s})
	DB.Updates(&Zero{ID: 1, State: new(int)})

	zero := &Zero{}
	DB.First(zero)
	fmt.Println(zero) // &{3 0x40001c9220}

解决方式2，使用sql基础库提供的空值类型

type Zero struct {
	ID    int
	State *sql.NullInt64
}

	err := DB.AutoMigrate(Zero{})
	if err != nil {
		panic(err)
	}

	s := new(int)
	*s = 1
	DB.Create(&Zero{State: sql.NullInt64{
		Int64: 1,
		Valid: true,
	}})
	DB.Updates(&Zero{ID: 1, State: sql.NullInt64{
		Int64: 0,
		Valid: true,
	}})

	zero := &Zero{}
	DB.First(zero)
	fmt.Println(zero) //&{1 {0 true}}