sqlca

package module
v2.11.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 7, 2025 License: MIT Imports: 33 Imported by: 6

README

快速开始

数据库表模型生成

  • 创建数据库
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;


CREATE TABLE `inventory_data` (
                                `id` bigint unsigned NOT NULL COMMENT '产品ID',
                                `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                                `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                                `create_time` datetime NOT NULL COMMENT '创建时间',
                                `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                                `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                                `update_time` datetime NOT NULL COMMENT '更新时间',
                                `is_frozen` tinyint(1) NOT NULL DEFAULT '0' COMMENT '冻结状态(0: 未冻结 1: 已冻结)',
                                `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '产品名称',
                                `serial_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '产品编号',
                                `quantity` decimal(16,3) NOT NULL DEFAULT '0.000' COMMENT '产品库存',
                                `price` decimal(16,2) NOT NULL DEFAULT '0.00' COMMENT '产品均价',
                                `product_extra` json DEFAULT NULL COMMENT '产品附带数据(JSON文本)',
                                PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存数据表';

CREATE TABLE `inventory_in` (
                              `id` bigint unsigned NOT NULL COMMENT '主键ID',
                              `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                              `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                              `create_time` datetime NOT NULL COMMENT '创建时间',
                              `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                              `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                              `update_time` datetime NOT NULL COMMENT '更新时间',
                              `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态(0: 未删除 1: 已删除)',
                              `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
                              `product_id` bigint unsigned NOT NULL COMMENT '产品ID',
                              `order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '入库单号',
                              `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '交货人ID',
                              `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '交货人姓名',
                              `quantity` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '数量',
                              `weight` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '净重',
                              `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
                              PRIMARY KEY (`id`) USING BTREE,
                              UNIQUE KEY `UNIQ_ORDER_NO` (`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='入库主表';

CREATE TABLE `inventory_out` (
                               `id` bigint unsigned NOT NULL COMMENT '主键ID',
                               `create_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建人ID',
                               `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
                               `create_time` datetime NOT NULL COMMENT '创建时间',
                               `update_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新人ID',
                               `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人姓名',
                               `update_time` datetime NOT NULL COMMENT '更新时间',
                               `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态(0: 未删除 1: 已删除)',
                               `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
                               `product_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '产品ID',
                               `order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '出库单号',
                               `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '收货人ID',
                               `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '收货人姓名',
                               `quantity` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '数量',
                               `weight` decimal(16,6) NOT NULL DEFAULT '0.000000' COMMENT '净重',
                               `remark` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
                               PRIMARY KEY (`id`) USING BTREE,
                               UNIQUE KEY `UNIQ_ORDER_NO` (`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='出库主表';
  • 安装db2go工具
$ go install github.com/civet148/db2go@latest
  • 自动生成go代码脚本
@echo off


rem 数据模型(models)和数据库操作对象(dao)文件输出基础目录
set OUT_DIR=.
rem 数据模型包名(数据模型文件目录名)
set PACK_NAME="models"
rem 指定某表的某字段为指定类型,多个表字段以英文逗号分隔（例如：user.create_time=time.Time表示指定user表create_time字段为time.Time类型; 如果不指定表名则所有表的create_time字段均为time.Time类型；支持第三方包类型，例如：user.weight=github.com/shopspring/decimal.Decimal）
set SPEC_TYPES="inventory_data.product_extra=ProductExtraData"
rem 指定其他orm的标签和值(以空格分隔)
set COMMON_TAGS="id=gorm:\"primarykey\" create_time=gorm:\"autoCreateTime\" update_time=gorm:\"autoUpdateTime\""
set DEPLOY_SQL="test.sql"

rem 判断本地系统是否已安装db2go工具，没有则进行安装
echo "searching db2go.exe ..."
echo "--------------------------------------------"
where db2go.exe
echo "--------------------------------------------"

IF "%errorlevel%" == "0" (
    echo db2go already installed.
) ELSE (
    echo db2go not found in system %%PATH%%, installing...
    go install github.com/civet148/db2go@latest
    If "%errorlevel%" == "0" (
        echo db2go install successfully.
    ) ELSE (
        rem 安装失败: Linux/Mac请安装gcc工具链，Windows系统可以安装msys64进行源码编译或通过链接直接下载二进制(最新版本v2.13 https://github.com/civet148/release/tree/master/db2go/v2)
        echo ERROR: Linux/Mac please install gcc tool-chain and windows download from https://github.com/civet148/release/tree/master/db2go/v2 (latest version is v2.13)
    )
)

rem ---------------------- 导出数据库表结构-------------------------
set DSN_URL="mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=true&loc=Local"
db2go   --url %DSN_URL% --out %OUT_DIR% --spec-type %SPEC_TYPES% --package %PACK_NAME%  --common-tags %COMMON_TAGS% --export %DEPLOY_SQL%
gofmt -w %OUT_DIR%/%PACK_NAME%

pause
  • 生成的代码示例
// Code generated by db2go. DO NOT EDIT.
// https://github.com/civet148/db2go

package models

const TableNameInventoryData = "`inventory_data`" //库存数据表

const (
	INVENTORY_DATA_COLUMN_ID            = "id"
	INVENTORY_DATA_COLUMN_CREATE_ID     = "create_id"
	INVENTORY_DATA_COLUMN_CREATE_NAME   = "create_name"
	INVENTORY_DATA_COLUMN_CREATE_TIME   = "create_time"
	INVENTORY_DATA_COLUMN_UPDATE_ID     = "update_id"
	INVENTORY_DATA_COLUMN_UPDATE_NAME   = "update_name"
	INVENTORY_DATA_COLUMN_UPDATE_TIME   = "update_time"
	INVENTORY_DATA_COLUMN_IS_FROZEN     = "is_frozen"
	INVENTORY_DATA_COLUMN_NAME          = "name"
	INVENTORY_DATA_COLUMN_SERIAL_NO     = "serial_no"
	INVENTORY_DATA_COLUMN_QUANTITY      = "quantity"
	INVENTORY_DATA_COLUMN_PRICE         = "price"
	INVENTORY_DATA_COLUMN_PRODUCT_EXTRA = "product_extra"
)

type InventoryData struct {
	Id           uint64           `json:"id" db:"id" gorm:"primarykey"`                       //产品ID
	CreateId     uint64           `json:"create_id" db:"create_id" `                          //创建人ID
	CreateName   string           `json:"create_name" db:"create_name" `                      //创建人姓名
	CreateTime   string           `json:"create_time" db:"create_time" gorm:"autoCreateTime"` //创建时间
	UpdateId     uint64           `json:"update_id" db:"update_id" `                          //更新人ID
	UpdateName   string           `json:"update_name" db:"update_name" `                      //更新人姓名
	UpdateTime   string           `json:"update_time" db:"update_time" gorm:"autoUpdateTime"` //更新时间
	IsFrozen     int8             `json:"is_frozen" db:"is_frozen" `                          //冻结状态(0: 未冻结 1: 已冻结)
	Name         string           `json:"name" db:"name" `                                    //产品名称
	SerialNo     string           `json:"serial_no" db:"serial_no" `                          //产品编号
	Quantity     float64          `json:"quantity" db:"quantity" `                            //产品库存
	Price        float64          `json:"price" db:"price" `                                  //产品均价
	ProductExtra ProductExtraData `json:"product_extra" db:"product_extra" sqlca:"isnull"`    //产品附带数据(JSON文本)
}

func (do *InventoryData) GetId() uint64                      { return do.Id }
func (do *InventoryData) SetId(v uint64)                     { do.Id = v }
func (do *InventoryData) GetCreateId() uint64                { return do.CreateId }
func (do *InventoryData) SetCreateId(v uint64)               { do.CreateId = v }
func (do *InventoryData) GetCreateName() string              { return do.CreateName }
func (do *InventoryData) SetCreateName(v string)             { do.CreateName = v }
func (do *InventoryData) GetCreateTime() string              { return do.CreateTime }
func (do *InventoryData) SetCreateTime(v string)             { do.CreateTime = v }
func (do *InventoryData) GetUpdateId() uint64                { return do.UpdateId }
func (do *InventoryData) SetUpdateId(v uint64)               { do.UpdateId = v }
func (do *InventoryData) GetUpdateName() string              { return do.UpdateName }
func (do *InventoryData) SetUpdateName(v string)             { do.UpdateName = v }
func (do *InventoryData) GetUpdateTime() string              { return do.UpdateTime }
func (do *InventoryData) SetUpdateTime(v string)             { do.UpdateTime = v }
func (do *InventoryData) GetIsFrozen() int8                  { return do.IsFrozen }
func (do *InventoryData) SetIsFrozen(v int8)                 { do.IsFrozen = v }
func (do *InventoryData) GetName() string                    { return do.Name }
func (do *InventoryData) SetName(v string)                   { do.Name = v }
func (do *InventoryData) GetSerialNo() string                { return do.SerialNo }
func (do *InventoryData) SetSerialNo(v string)               { do.SerialNo = v }
func (do *InventoryData) GetQuantity() float64               { return do.Quantity }
func (do *InventoryData) SetQuantity(v float64)              { do.Quantity = v }
func (do *InventoryData) GetPrice() float64                  { return do.Price }
func (do *InventoryData) SetPrice(v float64)                 { do.Price = v }
func (do *InventoryData) GetProductExtra() ProductExtraData  { return do.ProductExtra }
func (do *InventoryData) SetProductExtra(v ProductExtraData) { do.ProductExtra = v }

连接数据库

package main

import (
    "github.com/civet148/log"
    "github.com/civet148/sqlca/v2"
)

const (
	//MysslDSN = "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4"
    MysqlDSN = "mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4"
	//PostgresDSN  = "postgres://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public")
	//GaussDSN  = "opengauss://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public")
	//MssqlDSN  = "mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false")
	//SqliteDSN  = "sqlite:///var/lib/test.db")
)

func main() {
	var err error
	var db *sqlca.Engine
	var options = &sqlca.Options{
		Debug: true, //是否开启调试日志输出
		Max:   150,  //最大连接数
		Idle:  5,    //空闲连接数
		//DefaultLimit: 100,  //默认查询条数限制
		SnowFlake: &sqlca.SnowFlake{ //雪花算法配置（不使用可以赋值nil）
			NodeId: 1, //雪花算法节点ID 1-1023
		},
	}
	db, err = sqlca.NewEngine(MysqlDSN, options)
	if err != nil {
		log.Errorf("connect database error: %s", err)
		return
	}
	_ = db
}

数据库CURD示例

单条插入

func InsertSingle(db *sqlca.Engine) error {
	
	now := time.Now().Format("2006-01-02 15:04:05")
	var do = &models.InventoryData{
		Id:         uint64(db.NewID()),
		CreateId:   1,
		CreateName: "admin",
		CreateTime: now,
		UpdateId:   1,
		UpdateName: "admin",
		UpdateTime: now,
		IsFrozen:   0,
		Name:       "齿轮",
		SerialNo:   "SNO_001",
		Quantity:   1000,
		Price:      10.5,
	}

	var err error
	/*
		INSERT INTO inventory_data (`id`,`create_id`,`create_name`,`create_time`,`update_id`,`update_name`,`update_time`,`is_frozen`,`name`,`serial_no`,`quantity`,`price`,`product_extra`)
		VALUES ('1859078192380252161','1','admin','2024-11-20 11:35:55','1','admin','2024-11-20 11:35:55','0','轮胎','SNO_002','2000','210','{}')
	*/
	_, err = db.Model(&do).Insert()
	if err != nil {
		return log.Errorf("数据插入错误: %s", err)
	}
	return nil
}

批量插入

func InsertBatch(db *sqlca.Engine) error {
now := time.Now().Format("2006-01-02 15:04:05")
var dos = []*models.InventoryData{
        {
            Id:         uint64(db.NewID()),
            CreateId:   1,
            CreateName: "admin",
            CreateTime: now,
            UpdateId:   1,
            UpdateName: "admin",
            UpdateTime: now,
            IsFrozen:   0,
            Name:       "齿轮",
            SerialNo:   "SNO_001",
            Quantity:   1000,
            Price:      10.5,
            ProductExtra: models.ProductExtraData{
                SpecsValue: "齿数：32",
                AvgPrice:   sqlca.NewDecimal(30.8),
            },
        },
        {
            Id:         uint64(db.NewID()),
            CreateId:   1,
            CreateName: "admin",
            CreateTime: now,
            UpdateId:   1,
            UpdateName: "admin",
            UpdateTime: now,
            IsFrozen:   0,
            Name:       "轮胎",
            SerialNo:   "SNO_002",
            Quantity:   2000,
            Price:      210,
            ProductExtra: models.ProductExtraData{
                SpecsValue: "17英寸",
                AvgPrice:   sqlca.NewDecimal(450.5),
            },
        },
    }

    var err error
    /*
        INSERT INTO inventory_data
            (`id`,`create_id`,`create_name`,`create_time`,`update_id`,`update_name`,`update_time`,`is_frozen`,`name`,`serial_no`,`quantity`,`price`,`product_extra`)
        VALUES
            ('1867379968636358656','1','admin','2024-12-13 09:24:13','1','admin','2024-12-13 09:24:13','0','齿轮','SNO_001','1000','10.5','{\"avg_price\":\".8\",\"specs_value\":\"齿数：32\"}'),
            ('1867379968636358657','1','admin','2024-12-13 09:24:13','1','admin','2024-12-13 09:24:13','0','轮胎','SNO_002','2000','210','{\"avg_price\":\"450.5\",\"specs_value\":\"17英寸\"}')
    */
    _, err = db.Model(&dos).Insert()
    if err != nil {
        return log.Errorf("数据插入错误: %s", err)
    }
    return nil
}

普通查询带LIMIT限制

func QueryLimit(db *sqlca.Engine) error {
	
    var err error
    var count int64
    var dos []*models.InventoryData
    
    //SELECT * FROM inventory_data ORDER BY create_time DESC LIMIT 1000
    count, err = db.Model(&dos).
        Limit(1000).
        Desc("create_time").
        Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("查询结果数据条数: %d", count)
    return nil
}

查询无数据则报错

func QueryErrNotFound(db *sqlca.Engine) error {
	
	var err error
	var count int64
	var do *models.InventoryData //如果取数对象是切片则不报错

	//SELECT * FROM inventory_data WHERE id=1899078192380252160
	count, err = db.Model(&do).Id(1899078192380252160).Find()
	if err != nil {
		if errors.Is(err, sqlca.ErrRecordNotFound) {
			return log.Errorf("根据ID查询数据库记录无结果：%s", err)
		}
		return log.Errorf("数据库错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

分页查询

func QueryByPage(db *sqlca.Engine) error {
	
	var err error
	var count, total int64
	var dos []*models.InventoryData

	//SELECT  * FROM inventory_data WHERE 1=1 ORDER BY create_time DESC LIMIT 0,20
	count, total, err = db.Model(&dos).
		Page(1, 20).
		Desc("create_time").
		QueryEx()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果条数: %d 数据库总数：%v", count, total)
	return nil
}

多条件查询

func QueryByCondition(db *sqlca.Engine) error {
	
	var err error
	var count int64
	var dos []*models.InventoryData
	//SELECT * FROM inventory_data WHERE `quantity` > 0 and is_frozen=0 AND create_time >= '2024-10-01 11:35:14' ORDER BY create_time DESC
	count, err = db.Model(&dos).
		Gt("quantity", 0).
		Eq("is_frozen", 0).
		Gte("create_time", "2024-10-01 11:35:14").
		Desc("create_time").
		Query()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

查询JSON内容字段到数据对象

/*
   models.InventoryData对象的ProductExtra是一个跟数据库JSON内容对应的结构体, 数据库中product_extra字段是json类型或text文本

    type ProductExtraData struct {
        AvgPrice   sqlca.Decimal `json:"avg_price"`   //均价
        SpecsValue string        `json:"specs_value"` //规格
    }
*/
func QueryWithJsonColumn(db *sqlca.Engine) error {
    var err error
    var do models.InventoryData
    var id = uint64(1867379968636358657)
    
    /*
        SELECT * FROM inventory_data WHERE id=1867379968636358657
    
        +-----------------------+-----------------------+-----------------------+------------------------------------------------+
        | id	                | name	| serial_no	    | quantity	| price	    |                 product_extra                  |
        +-----------------------+-------+---------------+-----------+-----------+------------------------------------------------+
        | 1867379968636358657	| 轮胎  	| SNO_002		| 2000.000 	| 210.00	| {"avg_price": "450.5", "specs_value": "17英寸"} |
        +------------------------------------------------------------------------------------------------------------------------+
    */
    _, err = db.Model(&do).
                Table("inventory_data").
                Select("id", "name", "serial_no", "quantity","price", "product_extra").
                Id(id).
                Find()
    if err != nil {
    return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("ID: %v 数据：%+v", id, do)
    /*
        2024-12-18 15:15:03.560732 PID:64764 [INFO] {goroutine 1} <main.go:373 QueryWithJsonColumn()> ID: 1867379968636358657 数据：{Id:1867379968636358657 Name:轮胎 SerialNo:SNO_002 Quantity:2000 Price:210 ProductExtra:{AvgPrice:450.5 SpecsValue:17英寸}}
    */
    return nil
}

常规SQL查询

func QueryRawSQL(db *sqlca.Engine) error {
	
	var rows []*models.InventoryData
	var sb = sqlca.NewStringBuilder()

	//SELECT * FROM inventory_data  WHERE is_frozen =  '0' AND quantity > '10'

	sb.Append("SELECT * FROM %s", "inventory_data")
	sb.Append("WHERE is_frozen = ?", 0)
	sb.Append("AND quantity > ?", 10)
	strQuery := sb.String()
	_, err := db.Model(&rows).QueryRaw(strQuery)
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	return nil
}

查询带多个OR条件(map类型)

func QueryOr(db *sqlca.Engine) error {
	
	var err error
	var count int64
	var dos []*models.InventoryData

	//SELECT * FROM inventory_data WHERE create_id=1 AND name = '配件' OR serial_no = 'SNO_001' ORDER BY create_time DESC
	count, err = db.Model(&dos).
		And("create_id = ?", 1).
		Or("name = ?", "配件").
		Or("serial_no = ?", "SNO_001").
		Desc("create_time").
		Query()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)

	//SELECT * FROM inventory_data WHERE create_id=1 AND is_frozen = 0 AND (name = '配件' OR serial_no = 'SNO_001') ORDER BY create_time DESC
	var andConditions = make(map[string]interface{})
	var orConditions = make(map[string]interface{})

	andConditions["create_id = ?"] = 1
	andConditions["is_frozen = ?"] = 0

	orConditions["name = ?"] = "配件"
	orConditions["serial_no = ?"] = "SNO_001"

	count, err = db.Model(&dos).
		And(andConditions).
		Or(orConditions).
		Desc("create_time").
		Query()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

分组查询

func QueryByGroup(db *sqlca.Engine) error {
	
	var err error
	var count int64
	var dos []*models.InventoryData
	/*
		SELECT  create_id, SUM(quantity) AS quantity
		FROM inventory_data
		WHERE 1=1 AND quantity>'0' AND is_frozen='0' AND create_time>='2024-10-01 11:35:14'
		GROUP BY create_id
	*/
	count, err = db.Model(&dos).
		Select("create_id", "SUM(quantity) AS quantity").
		Gt("quantity", 0).
		Eq("is_frozen", 0).
		Gte("create_time", "2024-10-01 11:35:14").
		GroupBy("create_id").
		Query()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

联表查询

func QueryJoins(db *sqlca.Engine) error {
	
	/*
		SELECT a.id as product_id, a.name AS product_name, b.quantity, b.weight
		FROM inventory_data a
		LEFT JOIN inventory_in b
		ON a.id=b.product_id
		WHERE a.quantity > 0 AND a.is_frozen=0 AND a.create_time>='2024-10-01 11:35:14'
	*/
	var do struct{}
	count, err := db.Model(&do).
		Select("a.id as product_id", "a.name AS product_name", "b.quantity", "b.weight").
		Table("inventory_data a").
		LeftJoin("inventory_in b").
		On("a.id=b.product_id").
		Gt("a.quantity", 0).
		Eq("a.is_frozen", 0).
		Gte("a.create_time", "2024-10-01 11:35:14").
		Query()
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	log.Infof("查询结果数据条数: %d", count)
	return nil
}

普通变量取值查询

func QueryByNormalVars(db *sqlca.Engine) error {
	
    var err error
    var name, serialNo string
    var id = uint64(1859078192380252160)
    //SELECT name, serial_no FROM inventory_data WHERE id=1859078192380252160
    _, err = db.Model(&name, &serialNo).
                Table("inventory_data").
                Select("name, serial_no").
                Id(id).
                Find()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    log.Infof("数据ID: %v name=%s serial_no=%s", id, name, serialNo)
	
	var ids []uint64
    //SELECT id FROM inventory_data LIMIT 10
    _, err = db.Model(&ids).
                Table("inventory_data").
                Select("id").
                Limit(10).
                Query()
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    return nil
}

数据更新

  • 通过数据模型对象更新数据
/*
[数据更新]

SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
*/
func UpdateSingleByModel(db *sqlca.Engine) error {
	
    var err error
    var do *models.InventoryData
    var id = uint64(1858759254329004032)
    _, err = db.Model(&do).Id(id).Find() //Find方法如果是单条记录没找到则提示ErrNotFound错误（Query方法不会报错）
    if err != nil {
        return log.Errorf("数据查询错误：%s", err)
    }
    
    do.Quantity = 2300 //更改库存
    _, err = db.Model(do).Select("quantity").Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
	return nil
}
  • 通过变量/常量更新数据
/*
[数据更新]
*/
func UpdateSingleByVars(db *sqlca.Engine) error {
	
    var err error
    var id = uint64(1858759254329004032)
    var quantity = 2300 //更改库存数
	
	//UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
    _, err = db.Model(&quantity).Table("inventory_data").Id(id).Select("quantity").Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
    //UPDATE inventory_data SET `quantity`='2300' WHERE `id`='1858759254329004032'
    _, err = db.Model(2300).Table("inventory_data").Id(id).Select("quantity").Update()
    if err != nil {
        return log.Errorf("更新错误：%s", err)
    }
	return nil
}

事务处理

func Transaction(db *sqlca.Engine) error {

	/*
		-- TRANSACTION BEGIN

			INSERT INTO inventory_in (`user_id`,`quantity`,`remark`,`create_id`,`user_name`,`weight`,`create_time`,`update_name`,`is_deleted`,`product_id`,`id`,`create_name`,`update_id`,`update_time`,`order_no`) VALUES ('3','20','产品入库','1','lazy','200.3','2024-11-27 11:35:14','admin','0','1858759254329004032','1861614736295071744','admin','1','2024-11-27 1114','202407090000001')
			SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
			UPDATE inventory_data SET `quantity`='2320' WHERE `id`='1858759254329004032'

		-- TRANSACTION END
	*/

	now := time.Now().Format("2006-01-02 15:04:05")
	tx, err := db.TxBegin()
	if err != nil {
		return log.Errorf("开启事务失败：%s", err)
	}
	defer tx.TxRollback()

	productId := uint64(1858759254329004032)
	strOrderNo := time.Now().Format("20060102150405.000000000")
	//***************** 执行事务操作 *****************
	quantity := float64(20)
	weight := float64(200.3)
	_, err = tx.Model(&models.InventoryIn{
		Id:         uint64(db.NewID()),
		CreateId:   1,
		CreateName: "admin",
		CreateTime: now,
		UpdateId:   1,
		UpdateName: "admin",
		UpdateTime: now,
		ProductId:  productId,
		OrderNo:    strOrderNo,
		UserId:     3,
		UserName:   "lazy",
		Quantity:   quantity,
		Weight:     weight,
		Remark:     "产品入库",
	}).Insert()
	if err != nil {
		return log.Errorf("数据插入错误: %s", err)
	}
	var inventoryData = &models.InventoryData{}
	_, err = db.Model(&inventoryData).Id(productId).Find() //Find方法如果是单条记录没找到则提示ErrNotFound错误（Query方法不会报错）
	if err != nil {
		return log.Errorf("数据查询错误：%s", err)
	}
	inventoryData.Quantity += quantity
	_, err = tx.Model(&inventoryData).Id(productId).Select("quantity").Update()
	if err != nil {
		return log.Errorf("更新错误：%s", err)
	}
	//***************** 提交事务 *****************
	err = tx.TxCommit()
	if err != nil {
		return log.Errorf("提交事务失败：%s", err)
	}
	return nil
}

事务处理封装

func TransactionWrapper(db *sqlca.Engine) error {

    /*
	   -- TRANSACTION BEGIN

	   	INSERT INTO inventory_in (`user_id`,`quantity`,`remark`,`create_id`,`user_name`,`weight`,`create_time`,`update_name`,`is_deleted`,`product_id`,`id`,`create_name`,`update_id`,`update_time`,`order_no`) VALUES ('3','20','产品入库','1','lazy','200.3','2024-11-27 11:35:14','admin','0','1858759254329004032','1861614736295071744','admin','1','2024-11-27 1114','202407090000002')
	   	SELECT * FROM inventory_data  WHERE `id`='1858759254329004032'
	   	UPDATE inventory_data SET `quantity`='2320' WHERE `id`='1858759254329004032'

	   -- TRANSACTION END
	*/
	strOrderNo := time.Now().Format("20060102150405.000000000")
	err := db.TxFunc(func(tx *sqlca.Engine) error {
		var err error
		productId := uint64(1858759254329004032)
		now := time.Now().Format("2006-01-02 15:04:05")

		//***************** 执行事务操作 *****************
		quantity := float64(20)
		weight := float64(200.3)
		_, err = tx.Model(&models.InventoryIn{
			Id:         uint64(db.NewID()),
			CreateId:   1,
			CreateName: "admin",
			CreateTime: now,
			UpdateId:   1,
			UpdateName: "admin",
			UpdateTime: now,
			ProductId:  productId,
			OrderNo:    strOrderNo,
			UserId:     3,
			UserName:   "lazy",
			Quantity:   quantity,
			Weight:     weight,
			Remark:     "产品入库",
		}).Insert()
		if err != nil {
			return log.Errorf("数据插入错误: %s", err)
		}
		var inventoryData = &models.InventoryData{}
		_, err = db.Model(&inventoryData).Id(productId).Find() //Find方法如果是单条记录没找到则提示ErrNotFound错误（Query方法不会报错）
		if err != nil {
			return log.Errorf("数据查询错误：%s", err)
		}
		inventoryData.Quantity += quantity
		_, err = tx.Model(&inventoryData).Id(productId).Select("quantity").Update()
		if err != nil {
			return log.Errorf("更新错误：%s", err)
		}
		return nil
	})

	//***************** 事务处理结果 *****************
	if err != nil {
		return log.Errorf("事务失败：%s", err)
	}
	return nil
}

Documentation

Index

Constants

View Source 
const (
	DefaultConnMax  = 150
	DefaultConnIdle = 5
)
View Source 
const (
	JoinType_Inner = 0 //inner join
	JoinType_Left  = 1 //left join
	JoinType_Right = 2 //right join
)

Variables

View Source 
var (
	ErrRecordNotFound = errors.New("record not found")
)

Functions

func Url2MySql added in v2.7.3 

func Url2MySql(strUrl string) (string, error)

Types

type CaseWhen 

type CaseWhen struct {
	// contains filtered or unexported fields
}

func (*CaseWhen) Case 

func (c *CaseWhen) Case(strThen string, strWhen string, args ...interface{}) *CaseWhen

func (*CaseWhen) Else 

func (c *CaseWhen) Else(strElse string) *CaseWhen

func (*CaseWhen) End 

func (c *CaseWhen) End(strName string) *Engine

type Decimal 

type Decimal struct {
	// contains filtered or unexported fields
}

func NewDecimal 

func NewDecimal(v interface{}) (d Decimal)

func (Decimal) Abs 

func (d Decimal) Abs() Decimal

Abs returns the absolute value of the decimal.

func (Decimal) Add 

func (d Decimal) Add(d2 interface{}) Decimal

Add returns d + d2

func (Decimal) Amount2Btc added in v2.2.0 

func (d Decimal) Amount2Btc() Decimal

func (Decimal) Amount2Coin added in v2.2.0 

func (d Decimal) Amount2Coin(prec int) Decimal

func (Decimal) Amount2Ether added in v2.2.0 

func (d Decimal) Amount2Ether() Decimal

func (Decimal) Amount2FIL added in v2.2.9 

func (d Decimal) Amount2FIL() Decimal

func (Decimal) BigInt added in v2.2.0 

func (d Decimal) BigInt() (b *big.Int, ok bool)

func (Decimal) Btc2Amount added in v2.2.0 

func (d Decimal) Btc2Amount() Decimal

func (Decimal) Cmp 

func (d Decimal) Cmp(d2 interface{}) int

Cmp compares the numbers represented by d and d2 and returns: 

-1 if d <  d2
 0 if d == d2
+1 if d >  d2

func (Decimal) Coin2Amount added in v2.2.0 

func (d Decimal) Coin2Amount(prec int) Decimal

func (Decimal) Cos 

func (d Decimal) Cos() Decimal

Cos returns the cosine of the radian argument x.

func (Decimal) Div 

func (d Decimal) Div(d2 interface{}) Decimal

Div returns d / d2. If it doesn't divide exactly, the result will have DivisionPrecision digits after the decimal point.

func (Decimal) Equal 

func (d Decimal) Equal(d2 interface{}) bool

Equal returns whether the numbers represented by d and d2 are equal.

func (Decimal) Ether2Amount added in v2.2.0 

func (d Decimal) Ether2Amount() Decimal

func (Decimal) FIL2Amount added in v2.2.9 

func (d Decimal) FIL2Amount() Decimal

func (Decimal) Float64 

func (d Decimal) Float64() (f float64)

Float64 returns the nearest float64 value for d and a bool indicating whether f represents d exactly.

func (*Decimal) FromFloat 

func (d *Decimal) FromFloat(v float64)

func (*Decimal) FromInt 

func (d *Decimal) FromInt(v int64)

func (*Decimal) FromString 

func (d *Decimal) FromString(v string)

func (Decimal) GetDecimal added in v2.8.3 

func (d Decimal) GetDecimal() decimal.Decimal

GetDecimal returns the decimal.Decimal type

func (Decimal) GreaterThan 

func (d Decimal) GreaterThan(d2 interface{}) bool

GreaterThan (GT) returns true when d is greater than d2.

func (Decimal) GreaterThanOrEqual 

func (d Decimal) GreaterThanOrEqual(d2 interface{}) bool

GreaterThanOrEqual (GTE) returns true when d is greater than or equal to d2.

func (Decimal) IntPart 

func (d Decimal) IntPart() int64

IntPart returns the integer component of the decimal.

func (Decimal) IsNegative 

func (d Decimal) IsNegative() bool

IsNegative return 

true if d < 0
false if d == 0
false if d > 0

func (Decimal) IsPositive 

func (d Decimal) IsPositive() bool

IsPositive return 

true if d > 0
false if d == 0
false if d < 0

func (Decimal) IsZero 

func (d Decimal) IsZero() bool

IsZero return 

true if d == 0
false if d > 0
false if d < 0

func (Decimal) LessThan 

func (d Decimal) LessThan(d2 interface{}) bool

LessThan (LT) returns true when d is less than d2.

func (Decimal) LessThanOrEqual 

func (d Decimal) LessThanOrEqual(d2 interface{}) bool

LessThanOrEqual (LTE) returns true when d is less than or equal to d2.

func (Decimal) Marshal added in v2.4.0 

func (d Decimal) Marshal() ([]byte, error)

func (Decimal) MarshalBSON added in v2.4.0 

func (d Decimal) MarshalBSON() ([]byte, error)

MarshalBSON implements the bson.Marshaler interface.

func (Decimal) MarshalBinary 

func (d Decimal) MarshalBinary() (data []byte, err error)

MarshalBinary implements the encoding.BinaryMarshaler interface.

func (Decimal) MarshalJSON 

func (d Decimal) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface.

func (Decimal) MarshalText 

func (d Decimal) MarshalText() (text []byte, err error)

MarshalText implements the encoding.TextMarshaler interface for XML serialization.

func (Decimal) Max 

func (d Decimal) Max(rest ...Decimal) Decimal

Max returns the largest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Max with 0 arguments.

func (Decimal) Min 

func (d Decimal) Min(rest ...Decimal) Decimal

Min returns the smallest Decimal that was passed in the arguments. To call this function with an array, you must do: This makes it harder to accidentally call Min with 0 arguments.

func (Decimal) Mod 

func (d Decimal) Mod(d2 interface{}) Decimal

Mod returns d % d2.

func (Decimal) Mul 

func (d Decimal) Mul(d2 interface{}) Decimal

Mul returns d * d2.

func (Decimal) Neg 

func (d Decimal) Neg() Decimal

Neg returns -d.

func (Decimal) Pow 

func (d Decimal) Pow(d2 interface{}) Decimal

Pow returns d to the power d2

func (Decimal) Round 

func (d Decimal) Round(places int32) Decimal

Round rounds the decimal to places decimal places. If places < 0, it will round the integer part to the nearest 10^(-places).

Example: 

NewFromFloat(5.45).Round(1).String() // output: "5.5"
NewFromFloat(545).Round(-1).String() // output: "550"

func (*Decimal) Scan 

func (d *Decimal) Scan(src interface{}) error

Scan implements the sql.Scanner interface for database deserialization.

func (Decimal) Sign 

func (d Decimal) Sign() int

Sign returns: 

-1 if d <  0
 0 if d == 0
+1 if d >  0

func (Decimal) Sin 

func (d Decimal) Sin() Decimal

Sin returns the sine of the radian argument x.

func (Decimal) String 

func (d Decimal) String() string

String returns the string representation of the decimal with the fixed point.

Example: 

d := New(-12345, -3)
println(d.String())

Output: 

-12.345

func (Decimal) StringFixed 

func (d Decimal) StringFixed(places int32) string

StringFixed returns a rounded fixed-point string with places digits after the decimal point.

Example: 

NewFromFloat(0).StringFixed(2) // output: "0.00"
NewFromFloat(0).StringFixed(0) // output: "0"
NewFromFloat(5.45).StringFixed(0) // output: "5"
NewFromFloat(5.45).StringFixed(1) // output: "5.5"
NewFromFloat(5.45).StringFixed(2) // output: "5.45"
NewFromFloat(5.45).StringFixed(3) // output: "5.450"
NewFromFloat(545).StringFixed(-1) // output: "550"

func (Decimal) StringScaled 

func (d Decimal) StringScaled(exp int32) string

StringScaled first scales the decimal then calls .String() on it. NOTE: buggy, unintuitive, and DEPRECATED! Use StringFixed instead.

func (Decimal) Sub 

func (d Decimal) Sub(d2 interface{}) Decimal

Sub returns d - d2.

func (Decimal) Sum 

func (d Decimal) Sum(rest ...Decimal) Decimal

Sum returns the combined total of the provided first and rest Decimals

func (Decimal) Tan 

func (d Decimal) Tan() Decimal

Tan returns the tangent of the radian argument x.

func (Decimal) Truncate 

func (d Decimal) Truncate(precision int32) Decimal

Truncate truncates off digits from the number, without rounding.

NOTE: precision is the last digit that will not be truncated (must be >= 0).

Example: 

decimal.NewFromString("123.456").Truncate(2).String() // "123.45"

func (*Decimal) Unmarshal added in v2.4.0 

func (d *Decimal) Unmarshal(data []byte) error

func (*Decimal) UnmarshalBSON added in v2.4.0 

func (d *Decimal) UnmarshalBSON(data []byte) error

func (*Decimal) UnmarshalBinary 

func (d *Decimal) UnmarshalBinary(data []byte) error

UnmarshalBinary implements the encoding.BinaryUnmarshaler interface. As a string representation is already used when encoding to text, this method stores that string as []byte

func (*Decimal) UnmarshalJSON 

func (d *Decimal) UnmarshalJSON(decimalBytes []byte) error

UnmarshalJSON implements the json.Unmarshaler interface.

func (*Decimal) UnmarshalText 

func (d *Decimal) UnmarshalText(text []byte) error

UnmarshalText implements the encoding.TextUnmarshaler interface for XML deserialization.

func (Decimal) Value 

func (d Decimal) Value() (driver.Value, error)

Value implements the driver.Valuer interface for database serialization.

type Engine 

type Engine struct {
	// contains filtered or unexported fields
}

func NewEngine 

func NewEngine(strUrl string, options ...*Options) (*Engine, error)

// [mysql] "mysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4" // [postgres] "postgres://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public" // [opengauss] "opengauss://root:123456@127.0.0.1:5432/test?sslmode=disable&search_path=public" // [mssql] "mssql://sa:123456@127.0.0.1:1433/mydb?instance=SQLExpress&windows=false" // [sqlite] "sqlite:///var/lib/test.db"

func (*Engine) And 

func (e *Engine) And(query interface{}, args ...interface{}) *Engine

func (*Engine) Asc 

func (e *Engine) Asc(columns ...string) *Engine

Asc order by [field1,field2...] asc

func (*Engine) Attach 

func (e *Engine) Attach(strDatabaseName string, db *sqlx.DB) *Engine

Attach attach from a exist sqlx db instance

func (*Engine) AutoRollback 

func (e *Engine) AutoRollback() *Engine

func (*Engine) Avg 

func (e *Engine) Avg(strColumn string, strAS ...string) *Engine

func (*Engine) Case 

func (e *Engine) Case(strThen string, strWhen string, args ...interface{}) *CaseWhen

func (*Engine) Close added in v2.4.0 

func (e *Engine) Close() *Engine

Close disconnect all database connections

func (*Engine) Count 

func (e *Engine) Count(strColumn string, strAS ...string) *Engine

func (*Engine) Counter 

func (e *Engine) Counter() *counter

func (*Engine) Debug 

func (e *Engine) Debug(ok bool)

Debug log debug mode on or off

func (*Engine) Delete 

func (e *Engine) Delete() (rowsAffected int64, err error)

Delete orm delete record(s) from db

func (*Engine) Desc 

func (e *Engine) Desc(columns ...string) *Engine

Desc order by [field1,field2...] desc

func (*Engine) Distinct 

func (e *Engine) Distinct() *Engine

Distinct set distinct when select

func (*Engine) Eq added in v2.5.0 

func (e *Engine) Eq(strColumn string, value interface{}) *Engine

Eq alias of Equal

func (*Engine) Equal added in v2.0.14 

func (e *Engine) Equal(strColumn string, value interface{}) *Engine

func (*Engine) Exclude 

func (e *Engine) Exclude(columns ...string) *Engine

Exclude exclude orm select/update columns

func (*Engine) ExecRaw 

func (e *Engine) ExecRaw(strQuery string, args ...interface{}) (rowsAffected, lastInsertId int64, err error)

ExecRaw use raw sql to insert/update database, results can not be cached to redis/memcached/memory... return rows affected and error, if err is not nil must be something wrong

func (*Engine) Find 

func (e *Engine) Find() (rowsAffected int64, err error)

Find orm find data records, returns error if data model is not slice

func (*Engine) ForUpdate added in v2.10.0 

func (e *Engine) ForUpdate() *Engine

func (*Engine) Force 

func (e *Engine) Force() *Engine

Force force update/insert read only column(s)

func (*Engine) From added in v2.7.0 

func (e *Engine) From(exprs ...string) *Engine

From alias of Table method

func (*Engine) GeoHash 

func (e *Engine) GeoHash(lng, lat float64, precision int) (strGeoHash string, strNeighbors []string)

GeoHash encode geo hash string (precision 1~8) 

returns geo hash and neighbors areas

func (*Engine) GetAdapter 

func (e *Engine) GetAdapter() types.AdapterType

func (*Engine) GetPkName 

func (e *Engine) GetPkName() string

func (*Engine) GreaterEqual added in v2.2.5 

func (e *Engine) GreaterEqual(strColumn string, value interface{}) *Engine

func (*Engine) GreaterThan added in v2.2.5 

func (e *Engine) GreaterThan(strColumn string, value interface{}) *Engine

func (*Engine) GroupBy 

func (e *Engine) GroupBy(columns ...string) *Engine

GroupBy group by [field1,field2...]

func (*Engine) Gt added in v2.5.0 

func (e *Engine) Gt(strColumn string, value interface{}) *Engine

Gt alias of GreaterThan

func (*Engine) Gte added in v2.5.0 

func (e *Engine) Gte(strColumn string, value interface{}) *Engine

Gte alias of GreaterEqual

func (*Engine) GteLte added in v2.5.0 

func (e *Engine) GteLte(strColumn string, value1, value2 interface{}) *Engine

GteLte greater than equal and less than equal

func (*Engine) Having 

func (e *Engine) Having(strFmt string, args ...interface{}) *Engine

Having having [condition]

func (*Engine) Id 

func (e *Engine) Id(value interface{}) *Engine

Id set orm primary key's value

func (*Engine) In 

func (e *Engine) In(strColumn string, args ...interface{}) *Engine

In `field_name` IN ('1','2',...)

func (*Engine) InnerJoin 

func (e *Engine) InnerJoin(strTableName string) *Join

func (*Engine) Insert 

func (e *Engine) Insert() (lastInsertId int64, err error)

Insert orm insert return last insert id and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) IsNULL added in v2.5.1 

func (e *Engine) IsNULL(strColumn string) *Engine

func (*Engine) IsNull added in v2.9.0 

func (e *Engine) IsNull(strColumn string) *Engine

func (*Engine) JsonContainArray added in v2.8.0 

func (e *Engine) JsonContainArray(strColumn string, value interface{}) *Engine

SELECT * FROM news WHERE JSON_CONTAINS(tags, JSON_ARRAY("#Blockchain"))

func (*Engine) JsonEqual added in v2.2.5 

func (e *Engine) JsonEqual(strColumn, strPath string, value interface{}) *Engine

func (*Engine) JsonGreater added in v2.2.5 

func (e *Engine) JsonGreater(strColumn, strPath string, value interface{}) *Engine

func (*Engine) JsonGreaterEqual added in v2.2.5 

func (e *Engine) JsonGreaterEqual(strColumn, strPath string, value interface{}) *Engine

func (*Engine) JsonLess added in v2.2.5 

func (e *Engine) JsonLess(strColumn, strPath string, value interface{}) *Engine

func (*Engine) JsonLessEqual added in v2.2.5 

func (e *Engine) JsonLessEqual(strColumn, strPath string, value interface{}) *Engine

func (*Engine) JsonMarshal 

func (e *Engine) JsonMarshal(v interface{}) (strJson string)

func (*Engine) JsonUnmarshal 

func (e *Engine) JsonUnmarshal(strJson string, v interface{}) (err error)

func (*Engine) LeftJoin 

func (e *Engine) LeftJoin(strTableName string) *Join

func (*Engine) LessEqual added in v2.0.14 

func (e *Engine) LessEqual(strColumn string, value interface{}) *Engine

func (*Engine) LessThan added in v2.0.14 

func (e *Engine) LessThan(strColumn string, value interface{}) *Engine

func (*Engine) Like added in v2.0.14 

func (e *Engine) Like(strColumn, strSub string) *Engine

func (*Engine) Likes added in v2.10.0 

func (e *Engine) Likes(kvs map[string]interface{}) *Engine

func (*Engine) Limit 

func (e *Engine) Limit(args ...int) *Engine

Limit query limit Limit(10) - query records limit 10 (mysql/postgres)

func (*Engine) LockShareMode added in v2.10.0 

func (e *Engine) LockShareMode() *Engine

func (*Engine) Lt added in v2.5.0 

func (e *Engine) Lt(strColumn string, value interface{}) *Engine

Lt alias of LessThan

func (*Engine) Lte added in v2.5.0 

func (e *Engine) Lte(strColumn string, value interface{}) *Engine

Lte alias of LessEqual

func (*Engine) Max 

func (e *Engine) Max(strColumn string, strAS ...string) *Engine

func (*Engine) Min 

func (e *Engine) Min(strColumn string, strAS ...string) *Engine

func (*Engine) Model 

func (e *Engine) Model(args ...interface{}) *Engine

Model orm model use to get result set, support single struct object or slice [pointer type] notice: will clone a new engine object for orm operations(query/update/insert/upsert)

func (*Engine) Ne added in v2.6.1 

func (e *Engine) Ne(strColumn string, value interface{}) *Engine

Ne not equal

func (*Engine) NearBy 

func (e *Engine) NearBy(strLngCol, strLatCol, strAS string, lng, lat, distance float64) *Engine

NearBy -- select geo point as distance where distance <= n km (float64) SELECT 

a.*,
(
6371 * ACOS (
COS( RADIANS( a.lat ) ) * COS( RADIANS( 28.8039097230 ) ) * COS(
  RADIANS( 121.5619236231 ) - RADIANS( a.lng )
 ) + SIN( RADIANS( a.lat ) ) * SIN( RADIANS( 28.8039097230 ) )
)
) AS distance

FROM 

t_address a

HAVING distance <= 200 -- less than or equal 200km ORDER BY 

distance
LIMIT 10

func (*Engine) NewFromTx added in v2.10.0 

func (e *Engine) NewFromTx(tx *sql.Tx) *Engine

func (*Engine) NewID added in v2.6.0 

func (e *Engine) NewID() ID

func (*Engine) NoVerbose added in v2.0.8 

func (e *Engine) NoVerbose() *Engine

func (*Engine) NotIn added in v2.6.2 

func (e *Engine) NotIn(strColumn string, args ...interface{}) *Engine

NotIn `field_name` NOT IN ('1','2',...)

func (*Engine) NotNULL added in v2.5.1 

func (e *Engine) NotNULL(strColumn string) *Engine

func (*Engine) Offset 

func (e *Engine) Offset(offset int) *Engine

Offset query offset (for mysql/postgres)

func (*Engine) Omit added in v2.9.0 

func (e *Engine) Omit(columns ...string) *Engine

Omit same as Exclude

func (*Engine) OnConflict 

func (e *Engine) OnConflict(columns ...string) *Engine

OnConflict set the conflict columns for upsert only for postgresql

func (*Engine) Or 

func (e *Engine) Or(query interface{}, args ...interface{}) *Engine

func (*Engine) OrderBy 

func (e *Engine) OrderBy(orders ...string) *Engine

OrderBy order by [field1,field2...] [ASC]

func (*Engine) Page 

func (e *Engine) Page(pageNo, pageSize int) *Engine

Page page query 

SELECT ... FROM ... WHERE ... LIMIT (pageNo*pageSize), pageSize

func (*Engine) Ping 

func (e *Engine) Ping() (err error)

Ping ping database

func (*Engine) Query 

func (e *Engine) Query() (rowsAffected int64, err error)

Query orm query return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function if slave == true, try query from a slave connection, if not exist query from master

func (*Engine) QueryEx 

func (e *Engine) QueryEx() (rowsAffected, total int64, err error)

QueryEx orm query with total count return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function if slave == true, try query from a slave connection, if not exist query from master

func (*Engine) QueryJson 

func (e *Engine) QueryJson() (s string, err error)

QueryJson query result marshal to json

func (*Engine) QueryMap 

func (e *Engine) QueryMap(strQuery string, args ...interface{}) (rowsAffected int64, err error)

QueryMap use raw sql to query results into a map slice (model type is []map[string]string) return results and error NOTE: Model function is must be called before call this function

func (*Engine) QueryRaw 

func (e *Engine) QueryRaw(strQuery string, args ...interface{}) (rowsAffected int64, err error)

QueryRaw use raw sql to query results return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) RightJoin 

func (e *Engine) RightJoin(strTableName string) *Join

func (*Engine) Round added in v2.0.13 

func (e *Engine) Round(strColumn string, round int, strAS ...string) *Engine

func (*Engine) Select 

func (e *Engine) Select(columns ...string) *Engine

Select orm select/update columns

func (*Engine) SetCustomTag 

func (e *Engine) SetCustomTag(tagNames ...string) *Engine

SetCustomTag set your customer tag for db query/insert/update (eg. go structure generated by protobuf not contain 'db' tag) this function must calls before Model()

func (*Engine) SetLogFile 

func (e *Engine) SetLogFile(strPath string)

SetLogFile set log file

func (*Engine) SetPkName 

func (e *Engine) SetPkName(strName string) *Engine

SetPkName set orm primary key's name, default named 'id'

func (*Engine) SetReadOnly 

func (e *Engine) SetReadOnly(columns ...string)

SetReadOnly set read only columns

func (*Engine) SlowQuery 

func (e *Engine) SlowQuery(on bool, ms int)

SlowQuery slow query alert on or off 

on -> true/false
ms -> milliseconds (can be 0 if on is false)

func (*Engine) Sum 

func (e *Engine) Sum(strColumn string, strAS ...string) *Engine

func (*Engine) Table 

func (e *Engine) Table(exprs ...string) *Engine

Table set orm query table name(s) expression when your struct type name is not a table name

func (*Engine) ToSQL 

func (e *Engine) ToSQL(operType types.OperType) (strSql string)

make SQL from orm model and operation type

func (*Engine) TxBegin 

func (e *Engine) TxBegin() (*Engine, error)

func (*Engine) TxCommit 

func (e *Engine) TxCommit() error

func (*Engine) TxExec 

func (e *Engine) TxExec(strQuery string, args ...interface{}) (lastInsertId, rowsAffected int64, err error)

func (*Engine) TxFunc 

func (e *Engine) TxFunc(fn func(tx *Engine) error) (err error)

TxFunc execute transaction by customize function 

auto rollback when function return error

func (*Engine) TxFuncContext 

func (e *Engine) TxFuncContext(ctx context.Context, fn func(ctx context.Context, tx *Engine) error) (err error)

TxFuncContext execute transaction by customize function with context 

auto rollback when function return error

func (*Engine) TxGet 

func (e *Engine) TxGet(dest interface{}, strQuery string, args ...interface{}) (count int64, err error)

func (*Engine) TxHandle 

func (e *Engine) TxHandle(handler TxHandler) (err error)

TxHandle execute transaction by customize handler auto rollback when handler return error

func (*Engine) TxRollback 

func (e *Engine) TxRollback() error

func (*Engine) Update 

func (e *Engine) Update() (rowsAffected int64, err error)

Update orm update from model columns... if set, columns will be updated, if none all columns in model will be updated except primary key return rows affected and error, if err is not nil must be something wrong NOTE: Model function is must be called before call this function

func (*Engine) Upsert 

func (e *Engine) Upsert(strCustomizeUpdates ...string) (lastInsertId int64, err error)

Upsert orm insert or update if key(s) conflict return last insert id and error, if err is not nil must be something wrong, if your primary key is not a int/int64 type, maybe id return 0 NOTE: Model function is must be called before call this function and call OnConflict function when you are on postgresql updates -> customize updates condition when key(s) conflict [MySQL] INSERT INTO messages(id, message_type, unread_count) VALUES('10000', '2', '1', '3') ON DUPLICATE KEY UPDATE message_type=values(message_type), unread_count=unread_count+values(unread_count) --------------------------------------------------------------------------------------------------------------------------------------- e.Model(&do).Table("messages").Upsert("message_type=values(message_type)", "unread_count=unread_count+values(unread_count)") ---------------------------------------------------------------------------------------------------------------------------------------

func (*Engine) Use added in v2.4.0 

func (e *Engine) Use(strDatabaseName string) (*Engine, error)

Use switch database (returns a new instance)

func (*Engine) Where 

func (e *Engine) Where(query interface{}, args ...interface{}) *Engine

Where orm where condition

type Fetcher 

type Fetcher struct {
	// contains filtered or unexported fields
}

type ID added in v2.6.0 

type ID = snowflake.ID

type Id added in v2.10.0 

type Id = ID

type Join 

type Join struct {
	// contains filtered or unexported fields
}

func (*Join) On 

func (j *Join) On(strOn string, args ...interface{}) *Engine

type JoinType 

type JoinType int

func (JoinType) GoString 

func (t JoinType) GoString() string

func (JoinType) String 

func (t JoinType) String() string

func (JoinType) ToKeyWord 

func (t JoinType) ToKeyWord() string

type ModelReflector 

type ModelReflector struct {
	Dict    map[string]interface{} //dictionary of structure tag and value
	Columns []string               //column names
	// contains filtered or unexported fields
}

func (*ModelReflector) ParseModel added in v2.11.1 

func (s *ModelReflector) ParseModel(tagNames ...string) *ModelReflector

parse struct tag and value to map

type Options 

type Options struct {
	Debug         bool       //enable debug mode
	Max           int        //max active connections
	Idle          int        //max idle connections
	SSH           *SSH       //ssh tunnel server config
	SnowFlake     *SnowFlake //snowflake id config
	DisableOffset bool       //disable page offset for LIMIT (default page no is 1, if true then page no start from 0)
	DefaultLimit  int32      //limit default (0 means no limit)
}

type SSH 

type SSH struct {
	User       string //SSH tunnel server login account
	Password   string //SSH tunnel server login password
	PrivateKey string //SSH tunnel server private key, eg. "/home/test/.ssh/private-key.pem"
	Host       string //SSH tunnel server host [ip or domain], default port 22 if not specified
	// contains filtered or unexported fields
}

func (*SSH) GoString 

func (s *SSH) GoString() string

func (*SSH) String 

func (s *SSH) String() string

type SnowFlake added in v2.6.0 

type SnowFlake struct {
	NodeId int64 //node id (0~1023)
}

type StringBuilder added in v2.10.0 

type StringBuilder struct {
	// contains filtered or unexported fields
}

func NewStringBuilder added in v2.10.0 

func NewStringBuilder() *StringBuilder

func (*StringBuilder) Append added in v2.10.0 

func (s *StringBuilder) Append(query string, args ...interface{}) *StringBuilder

func (*StringBuilder) String added in v2.10.0 

func (s *StringBuilder) String() string

type TxHandler 

type TxHandler interface {
	OnTransaction(tx *Engine) error
}

type UrlInfo 

type UrlInfo struct {
	Scheme     string
	Host       string // host name and port like '127.0.0.1:3306'
	User       string
	Password   string
	Path       string
	Fragment   string
	Opaque     string
	ForceQuery bool
	Queries    map[string]string
}

func ParseUrl 

func ParseUrl(strUrl string) (ui *UrlInfo)

URL have some special characters in password(支持URL中密码包含特殊字符)

func (*UrlInfo) Url added in v2.4.0 

func (ui *UrlInfo) Url() string

Source Files

View all Source files

Directories

Path Synopsis
models

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.