数据库 model 使用文档

jaronnie大约 4 分钟

前言

jzero 支持通过 desc/sql 文件夹下的 sql 文件和远程的 mysql 地址生成代码

自动生成数据库代码配置

重要

为了实现生成数据库代码的逻辑统一, 当选择使用远程数据库地址生成代码时

jzero 会将 sql 文件自动生成在 desc/sql 目录下, 并以 table 命名, 生成完之后默认会自动删除

生成的 model 代码路径为 internal/model/xx

基于本地 sql 文件

gen:
  # 是否生成带缓存的数据库代码
  model-mysql-cache: true
  # Ignore columns while creating or updating rows, 默认为 create_at,created_at,create_time,update_at,updated_at,update_time
  model-mysql-ignore-columns: []

基于远程 mysql 地址

gen:
  # 是否生成带缓存的数据库代码
  model-mysql-cache: true
  # 是否使用远程 mysql 数据源生成代码
  model-mysql-datasource: true
  # Ignore columns while creating or updating rows, 默认为 create_at,created_at,create_time,update_at,updated_at,update_time
  model-mysql-ignore-columns: []
  # mysql 数据源配置
  model-mysql-datasource-url: "remote:b3YJ2d9fIE0TlMNeqMRf@tcp(192.168.135.180:3306)/ntls"
  # 使用哪些 table, 默认为 *(所有)
  model-mysql-datasource-table:
    - manage_user

自动生成代码

以 manage_user 为例

CREATE TABLE `manage_user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `create_by` bigint DEFAULT NULL,
  `update_by` bigint DEFAULT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(100) NOT NULL,
  `nickname` varchar(30) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `status` varchar(1) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_manage_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

默认实现以下增删改查接口

type ManageUser struct {
    Id         uint64         `db:"id"`
    CreateTime time.Time      `db:"create_time"`
    UpdateTime time.Time      `db:"update_time"`
    CreateBy   sql.NullInt64  `db:"create_by"`
    UpdateBy   sql.NullInt64  `db:"update_by"`
    Username   string         `db:"username"`
    Password   string         `db:"password"`
    Nickname   string         `db:"nickname"`
    Gender     string         `db:"gender"`
    Phone      sql.NullString `db:"phone"`
    Status     string         `db:"status"`
    Email      sql.NullString `db:"email"`
}

manageUserModel interface {
	Insert(ctx context.Context, session sqlx.Session, data *ManageUser) (sql.Result, error)
	InsertWithCache(ctx context.Context, session sqlx.Session, data *ManageUser) (sql.Result, error)
	FindOne(ctx context.Context, session sqlx.Session, id uint64) (*ManageUser, error)
	FindOneWithCache(ctx context.Context, session sqlx.Session, id uint64) (*ManageUser, error)
	FindOneByUsername(ctx context.Context, session sqlx.Session, username string) (*ManageUser, error)
	FindOneByUsernameWithCache(ctx context.Context, session sqlx.Session, username string) (*ManageUser, error)
	Update(ctx context.Context, session sqlx.Session, data *ManageUser) error
	UpdateWithCache(ctx context.Context, session sqlx.Session, data *ManageUser) error
	Delete(ctx context.Context, session sqlx.Session, id uint64) error
	DeleteWithCache(ctx context.Context, session sqlx.Session, id uint64) error
	// custom interface generated by jzero
	BulkInsert(ctx context.Context, session sqlx.Session, datas []*ManageUser) error
	FindByCondition(ctx context.Context, session sqlx.Session, conds ...condition.Condition) ([]*ManageUser, error)
	FindOneByCondition(ctx context.Context, session sqlx.Session, conds ...condition.Condition) (*ManageUser, error)
	PageByCondition(ctx context.Context, session sqlx.Session, conds ...condition.Condition) ([]*ManageUser, int64, error)
	UpdateFieldsByCondition(ctx context.Context, session sqlx.Session, field map[string]any, conds ...condition.Condition) error
	DeleteByCondition(ctx context.Context, session sqlx.Session, conds ...condition.Condition) error
}

Insert: 插入一条数据
InsertWithCache
FindOne: 根据主键查询一条数据
FindOneWithCache
FindOneByUsername: 根据 username(unique 唯一键) 查询一条数据
FindOneByUsernameWithCache
Update: 根据主键更新一条数据
UpdateWithCache
Delete: 根据主键删除一条数据
DeleteWithCache
BulkInsert: 批量插入数据
FindByCondition: 根据条件查询多条数据
FindOneByCondition: 根据条件查询一条数据
PageByCondition: 根据条件查询分页数据
UpdateFieldsByCondition: 根据条件和需要更新的列更新多条数据
DeleteByCondition: 根据条件删除多条记录

同时为了后续操作方便, jzero 将会自动生成 internal/model/model.go:

// Code generated by jzero. DO NOT EDIT.

package model

import (
	"github.com/jzero-io/jzero-admin/server/internal/model/manage_user"

	"github.com/eddieowens/opts"
	"github.com/jzero-io/jzero-contrib/modelx"
	"github.com/zeromicro/go-zero/core/stores/sqlx"
)

type Model struct {
	ManageUser manage_user.ManageUserModel
}

func NewModel(conn sqlx.SqlConn, op ...opts.Opt[modelx.ModelOpts]) Model {
	return Model{
		ManageUser: manage_user.NewManageUserModel(conn, op...),
	}
}

使用最佳实践

type ServiceContext struct {
	Config         configurator.Configurator[config.Config]
	SqlxConn       sqlx.SqlConn
	Model          model.Model
	Cache          cache.Cache
	Custom *custom.Custom
}

后续在 logic 中就可以直接使用 l.svcCtx.Model.xx.Insert 操作数据库了

if _, err = l.svcCtx.Model.ManageUser.Insert(l.ctx, nil, &manage_user.ManageUser{
		CreateTime: time.Now(),
		UpdateTime: time.Now(),
		Username:   req.Username,
		Password:   req.Password,
		Nickname:   req.NickName,
		Gender:     req.UserGender,
		Phone:      null.StringFrom(req.UserPhone).NullString,
		Status:     req.Status,
		Email:      null.StringFrom(req.UserEmail).NullString,
	}); err != nil {
		return nil, err
}

如何使用 condition 查询

condition 源码 condition

构建 []condition

package user

import (
	"context"
	"net/http"
	"time"

	"github.com/jzero-io/jzero-admin/server/internal/svc"
	types "github.com/jzero-io/jzero-admin/server/internal/types/manage/user"
	"github.com/jzero-io/jzero-contrib/condition"
	"github.com/jzero-io/jzero-contrib/nullx"
	"github.com/zeromicro/go-zero/core/logx"
)

type List struct {
	logx.Logger
	ctx    context.Context
	svcCtx *svc.ServiceContext
	r      *http.Request
}

func NewList(ctx context.Context, svcCtx *svc.ServiceContext, r *http.Request) *List {
	return &List{
		Logger: logx.WithContext(ctx),
		ctx:    ctx,
		svcCtx: svcCtx, r: r,
	}
}

func (l *List) List(req *types.ListRequest) (resp *types.ListResponse, err error) {
	users, total, err := l.svcCtx.Model.ManageUser.PageByCondition(l.ctx, nil, condition.Condition{
		Operator: condition.Limit,
		Value:    req.Size,
	}, condition.Condition{
		Operator: condition.Offset,
		Value:    (req.Current - 1) * req.Size,
	}, condition.Condition{
		Skip:     req.Username == "",
		Field:    "username",
		Operator: condition.Like,
		Value:    "%" + req.Username + "%",
	}, condition.Condition{
		Skip:     req.UserGender == "",
		Field:    "gender",
		Operator: condition.Equal,
		Value:    req.UserGender,
	}, condition.Condition{
		Skip:     req.NickName == "",
		Field:    "nickname",
		Operator: condition.Like,
		Value:    "%" + req.NickName + "%",
	}, condition.Condition{
		Skip:     req.UserPhone == "",
		Field:    "phone",
		Operator: condition.Like,
		Value:    "%" + req.UserPhone + "%",
	}, condition.Condition{
		Skip:     req.UserEmail == "",
		Field:    "email",
		Operator: condition.Like,
		Value:    "%" + req.UserEmail + "%",
	}, condition.Condition{
		Skip:     req.Status == "",
		Field:    "status",
		Operator: condition.Equal,
		Value:    req.Status,
	})

	var records []types.ManageUser
	for _, user := range users {
		records = append(records, types.ManageUser{
			Id:         user.Id,
			Username:   user.Username,
			UserGender: user.Gender,
			NickName:   user.Nickname,
			UserPhone:  nullx.NewString(user.Phone).ValueOrZero(),
			UserEmail:  nullx.NewString(user.Email).ValueOrZero(),
			Status:     user.Status,
			CreateTime: user.CreateTime.Format(time.DateTime),
			UpdateTime: user.UpdateTime.Format(time.DateTime),
		})
	}

	resp = &types.ListResponse{
		Records: records,
		PageResponse: types.PageResponse{
			Current: req.Current,
			Size:    req.Size,
			Total:   total,
		},
	}
	return
}

构建 condition chain 链式调用

package user

import (
	"context"
	"net/http"
	"time"

	"github.com/jzero-io/jzero-admin/server/internal/svc"
	types "github.com/jzero-io/jzero-admin/server/internal/types/manage/user"
	"github.com/jzero-io/jzero-contrib/condition"
	"github.com/jzero-io/jzero-contrib/nullx"
	"github.com/zeromicro/go-zero/core/logx"
)

type List struct {
	logx.Logger
	ctx    context.Context
	svcCtx *svc.ServiceContext
	r      *http.Request
}

func NewList(ctx context.Context, svcCtx *svc.ServiceContext, r *http.Request) *List {
	return &List{
		Logger: logx.WithContext(ctx),
		ctx:    ctx,
		svcCtx: svcCtx, r: r,
	}
}

func (l *List) List(req *types.ListRequest) (resp *types.ListResponse, err error) {
	conditions := condition.NewChain().
		Page(req.Current, req.Size).
		Equal("username", req.Username, condition.WithSkip(req.Username == "")).
		Equal("gender", req.UserGender, condition.WithSkip(req.UserGender == "")).
		Equal("nickname", req.NickName, condition.WithSkip(req.NickName == "")).
		Equal("phone", req.UserPhone, condition.WithSkip(req.UserPhone == "")).
		Equal("email", req.UserEmail, condition.WithSkip(req.UserEmail == "")).
		Equal("status", req.Status, condition.WithSkip(req.Status == "")).
		Build()

	users, total, err := l.svcCtx.Model.ManageUser.PageByCondition(l.ctx, nil, conditions...)

	var records []types.ManageUser
	for _, user := range users {
		records = append(records, types.ManageUser{
			Id:         user.Id,
			Username:   user.Username,
			UserGender: user.Gender,
			NickName:   user.Nickname,
			UserPhone:  nullx.NewString(user.Phone).ValueOrZero(),
			UserEmail:  nullx.NewString(user.Email).ValueOrZero(),
			Status:     user.Status,
			CreateTime: user.CreateTime.Format(time.DateTime),
			UpdateTime: user.UpdateTime.Format(time.DateTime),
		})
	}

	resp = &types.ListResponse{
		Records: records,
		PageResponse: types.PageResponse{
			Current: req.Current,
			Size:    req.Size,
			Total:   total,
		},
	}
	return
}