osm

package module
v2.0.5 Latest Latest
Warning

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

 Go to latest Published: Jun 26, 2023 License: MIT Imports: 10 Imported by: 2

README

osm

osm(Object Sql Mapping) 是用 go 编写的极简 sql 工具，目前已在生产环境中使用，支持 MySQL、PostgreSQL 和 SQL Server。

设计的目的就是提供一种简单查询接口：

_, err = o.SelectXXX(sql, params...)(&result...)

特性

  • 不依赖标准库以外的三方库

  • 灵活的 SQL 参数 #{ParamName}

    • 可以按参数顺序匹配
    • 可以匹配 map[string]interface{}
    • 可以匹配 struct
    • 可以使用 in

  • 灵活的 SQL 结果接收

    • value (&username, &email) 查出的结果为单行,并存入不定长的变量上(...)
    • values (&usernameList, &emailList) 查出的结果为多行,并存入不定长的变量上(...，每个都为 array)
    • struct (&user) 查出的结果为单行,并存入 struct
    • structs (&users) 查出的结果为多行,并存入 struct array
    • kvs (&emailUsernameMap) 查出的结果为多行,每行有两个字段,前者为 key,后者为 value,存入 map (双列)
    • strings (&columns, &datas) 查出的结果为多行,并存入 columns，和 datas。columns 为[]string，datas 为[][]string（常用于数据交换，如给 python 的 pandas 提供数据源）

  • 默认的 struct 字段名与 SQL 列名对应关系

go.mod

require (
	github.com/yinshuwei/osm/v2 v2.0.4
)

api doc

https://pkg.go.dev/github.com/yinshuwei/osm

Quickstart

创建数据库

    create database test;
    use test;

创建 user 表

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(255) DEFAULT NULL,
      `nickname` varchar(45) DEFAULT NULL,
      `create_time` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='user table';

执行 SQL 示例

osm_demo.go

package main

import (
    "encoding/json"
    "fmt"
    "time"

    _ "github.com/go-sql-driver/mysql"
    "github.com/yinshuwei/osm/v2"
    "go.uber.org/zap"
)

// InfoLogger 适配zap logger
type InfoLogger struct {
	zapLogger *zap.Logger
}

// WarnLoggor 适配zap logger
type WarnLoggor struct {
	zapLogger *zap.Logger
}

// ErrorLogger 适配zap logger
type ErrorLogger struct {
	zapLogger *zap.Logger
}

func loggerFields(data map[string]string) []zap.Field {
	var fields []zap.Field
	for key, val := range data {
		fields = append(fields, zap.String(key, val))
	}
	return fields
}

func (l *ErrorLogger) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Error(msg, loggerFields(data)...)
}

func (l *InfoLogger) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Info(msg, loggerFields(data)...)
}

func (l *WarnLoggor) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Warn(msg, loggerFields(data)...)
}

// User 用户Model
type User struct {
    ID         int64
    Email      string
    Nickname   string
    CreateTime time.Time
}

func main() {
	logger, _ := zap.NewDevelopment()
	o, err := osm.New("mysql", "root:123456@/test?charset=utf8", osm.Options{
		MaxIdleConns:    0,                    // int
		MaxOpenConns:    0,                    // int
		ConnMaxLifetime: 0,                    // time.Duration
		ConnMaxIdleTime: 0,                    // time.Duration
		WarnLogger:      &WarnLoggor{logger},  // Logger
		ErrorLogger:     &ErrorLogger{logger}, // Logger
		InfoLogger:      &InfoLogger{logger},  // Logger
		ShowSQL:         true,                 // bool
		SlowLogDuration: 0,                    // time.Duration
	})
	if err != nil {
		fmt.Println(err.Error())
	}

	//添加
	user := User{
		Email:      "test@foxmail.com",
		Nickname:   "haha",
		CreateTime: time.Now(),
	}
	id, count, err := o.Insert("INSERT INTO user (email,nickname,create_time) VALUES (#{Email},#{Nickname},#{CreateTime});", user)
	if err != nil {
		logger.Error("insert error", zap.Error(err))
	}
	logger.Info("test insert", zap.Int64("id", id), zap.Int64("count", count))

	//更新
	user = User{
		Email:    "test@foxmail.com",
		Nickname: "hello",
	}
	count, err = o.Update("UPDATE user SET nickname=#{Nickname} WHERE email=#{Email}", user)
	if err != nil {
		logger.Error("update error", zap.Error(err))
	}
	logger.Info("test update", zap.Int64("count", count))

	//查询
	user = User{
		Email: "test@foxmail.com",
	}
	var results []User
	count, err = o.SelectStructs("SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};", user)(&results)
	if err != nil {
		logger.Error("test select", zap.Error(err))
	}
	resultBytes, _ := json.Marshal(results)
	logger.Info("test select", zap.Int64("count", count), zap.ByteString("result", resultBytes))

	//删除
	count, err = o.Delete("DELETE FROM user WHERE email=#{Email}", user)
	if err != nil {
		logger.Error("test delete", zap.Error(err))
	}
	logger.Info("test delete", zap.Int64("count", count))

	//关闭
	err = o.Close()
	if err != nil {
		logger.Error("close", zap.Error(err))
	}
}

结果

2022-02-18T18:46:54.347+0800    INFO    osm@v1.0.12-0.20220218102134-94591d978e3d/sql.go:311    readSQLParamsBySQL showSql, sql: INSERT INTO user (email,nickname,create_time) VALUES (#{Email},#{Nickname},#{CreateTime});, params: {"ID":0,"Email":"test@foxmail.com","Nickname":"haha","CreateTime":"2022-02-18T18:46:54.346897988+08:00"}, dbSql: INSERT INTO user (email,nickname,create_time) VALUES (?,?,?);, dbParams: ["test@foxmail.com","haha","2022-02-18 18:46:54"]
2022-02-18T18:46:54.348+0800    INFO    osm_demo/main.go:40     test insert     {"id": 2, "count": 1}
2022-02-18T18:46:54.348+0800    INFO    osm@v1.0.12-0.20220218102134-94591d978e3d/sql.go:311    readSQLParamsBySQL showSql, sql: UPDATE user SET nickname=#{Nickname} WHERE email=#{Email}, params: {"ID":0,"Email":"test@foxmail.com","Nickname":"hello","CreateTime":"0001-01-01T00:00:00Z"}, dbSql: UPDATE user SET nickname=? WHERE email=?, dbParams: ["hello","test@foxmail.com"]
2022-02-18T18:46:54.349+0800    INFO    osm_demo/main.go:51     test update     {"count": 1}
2022-02-18T18:46:54.350+0800    INFO    osm@v1.0.12-0.20220218102134-94591d978e3d/sql.go:311    readSQLParamsBySQL showSql, sql: SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};, params: {"ID":0,"Email":"test@foxmail.com","Nickname":"","CreateTime":"0001-01-01T00:00:00Z"}, dbSql: SELECT id,email,nickname,create_time FROM user WHERE email=?;, dbParams: ["test@foxmail.com"]
2022-02-18T18:46:54.350+0800    INFO    osm_demo/main.go:63     test select     {"count": 1, "result": "[{\"ID\":2,\"Email\":\"test@foxmail.com\",\"Nickname\":\"hello\",\"CreateTime\":\"2022-02-18T18:46:54+08:00\"}]"}
2022-02-18T18:46:54.350+0800    INFO    osm@v1.0.12-0.20220218102134-94591d978e3d/sql.go:311    readSQLParamsBySQL showSql, sql: DELETE FROM user WHERE email=#{Email}, params: {"ID":0,"Email":"test@foxmail.com","Nickname":"","CreateTime":"0001-01-01T00:00:00Z"}, dbSql: DELETE FROM user WHERE email=?, dbParams: ["test@foxmail.com"]
2022-02-18T18:46:54.351+0800    INFO    osm_demo/main.go:70     test delete     {"count": 1}

指针类型支持 nil 示例

osm_demo2.go

package main

import (
	"encoding/json"
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/yinshuwei/osm/v2"
	"go.uber.org/zap"
)

// InfoLogger 适配zap logger
type InfoLogger struct {
	zapLogger *zap.Logger
}

// WarnLoggor 适配zap logger
type WarnLoggor struct {
	zapLogger *zap.Logger
}

// ErrorLogger 适配zap logger
type ErrorLogger struct {
	zapLogger *zap.Logger
}

func loggerFields(data map[string]string) []zap.Field {
	var fields []zap.Field
	for key, val := range data {
		fields = append(fields, zap.String(key, val))
	}
	return fields
}

func (l *ErrorLogger) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Error(msg, loggerFields(data)...)
}

func (l *InfoLogger) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Info(msg, loggerFields(data)...)
}

func (l *WarnLoggor) Log(msg string, data map[string]string) {
	if l == nil || l.zapLogger == nil {
		return
	}
	l.zapLogger.Warn(msg, loggerFields(data)...)
}

// User 用户Model
type User struct {
	ID         *int64
	Email      *string
	Nickname   *string
	CreateTime *time.Time
}

func stringPoint(t string) *string {
	return &t
}

func timePoint(t time.Time) *time.Time {
	return &t
}

func main() {
	logger, _ := zap.NewDevelopment()
	o, err := osm.New("mysql", "root:123456@/test?charset=utf8", osm.Options{
		MaxIdleConns:    0,                    // int
		MaxOpenConns:    0,                    // int
		ConnMaxLifetime: 0,                    // time.Duration
		ConnMaxIdleTime: 0,                    // time.Duration
		WarnLogger:      &WarnLoggor{logger},  // Logger
		ErrorLogger:     &ErrorLogger{logger}, // Logger
		InfoLogger:      &InfoLogger{logger},  // Logger
		ShowSQL:         true,                 // bool
		SlowLogDuration: 0,                    // time.Duration
	})
	if err != nil {
		fmt.Println(err.Error())
	}

	{ //添加
		user := User{
			Email:      stringPoint("test@foxmail.com"),
			Nickname:   nil,
			CreateTime: timePoint(time.Now()),
		}
		id, count, err := o.Insert("INSERT INTO user (email,nickname,create_time) VALUES (#{Email},#{Nickname},#{CreateTime});", user)
		if err != nil {
			logger.Error("insert error", zap.Error(err))
		}
		logger.Info("test insert", zap.Int64("id", id), zap.Int64("count", count))
	}

	{ //查询
		user := User{
			Email: stringPoint("test@foxmail.com"),
		}
		var results []User
		count, err := o.SelectStructs("SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};", user)(&results)
		if err != nil {
			logger.Error("test select", zap.Error(err))
		}
		resultBytes, _ := json.Marshal(results)
		logger.Info("test select", zap.Int64("count", count), zap.ByteString("result", resultBytes))
	}

	{ // 更新
		user := User{
			Email:    stringPoint("test@foxmail.com"),
			Nickname: stringPoint("hello"),
		}
		count, err := o.Update("UPDATE user SET nickname=#{Nickname} WHERE email=#{Email}", user)
		if err != nil {
			logger.Error("update error", zap.Error(err))
		}
		logger.Info("test update", zap.Int64("count", count))
	}

	{ //查询
		user := User{
			Email: stringPoint("test@foxmail.com"),
		}
		var results []User
		count, err := o.SelectStructs("SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};", user)(&results)
		if err != nil {
			logger.Error("test select", zap.Error(err))
		}
		resultBytes, _ := json.Marshal(results)
		logger.Info("test select", zap.Int64("count", count), zap.ByteString("result", resultBytes))
	}

	{ //删除
		user := User{
			Email: stringPoint("test@foxmail.com"),
		}
		count, err := o.Delete("DELETE FROM user WHERE email=#{Email}", user)
		if err != nil {
			logger.Error("test delete", zap.Error(err))
		}
		logger.Info("test delete", zap.Int64("count", count))
	}

	{ //关闭
		err = o.Close()
		if err != nil {
			logger.Error("close", zap.Error(err))
		}
	}
}

结果

2022-02-21T11:42:44.591+0800    INFO    v2@v2.0.2/sql.go:311    readSQLParamsBySQL showSql, sql: INSERT INTO user (email,nickname,create_time) VALUES (#{Email},#{Nickname},#{CreateTime});, params: {"ID":null,"Email":"test@foxmail.com","Nickname":null,"CreateTime":"2022-02-21T11:42:44.591619385+08:00"}, dbSql: INSERT INTO user (email,nickname,create_time) VALUES (?,?,?);, dbParams: ["test@foxmail.com",null,"2022-02-21T11:42:44.591619385+08:00"]
2022-02-21T11:42:44.596+0800    INFO    osm_demo/main.go:48     test insert     {"id": 10, "count": 1}
2022-02-21T11:42:44.596+0800    INFO    v2@v2.0.2/sql.go:311    readSQLParamsBySQL showSql, sql: SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};, params: {"ID":null,"Email":"test@foxmail.com","Nickname":null,"CreateTime":null}, dbSql: SELECT id,email,nickname,create_time FROM user WHERE email=?;, dbParams: ["test@foxmail.com"]
2022-02-21T11:42:44.597+0800    INFO    osm_demo/main.go:61     test select     {"count": 1, "result": "[{\"ID\":10,\"Email\":\"test@foxmail.com\",\"Nickname\":\"\",\"CreateTime\":\"2022-02-21T03:42:44+08:00\"}]"}
2022-02-21T11:42:44.597+0800    INFO    v2@v2.0.2/sql.go:311    readSQLParamsBySQL showSql, sql: UPDATE user SET nickname=#{Nickname} WHERE email=#{Email}, params: {"ID":null,"Email":"test@foxmail.com","Nickname":"hello","CreateTime":null}, dbSql: UPDATE user SET nickname=? WHERE email=?, dbParams: ["hello","test@foxmail.com"]
2022-02-21T11:42:44.598+0800    INFO    osm_demo/main.go:73     test update     {"count": 1}
2022-02-21T11:42:44.598+0800    INFO    v2@v2.0.2/sql.go:311    readSQLParamsBySQL showSql, sql: SELECT id,email,nickname,create_time FROM user WHERE email=#{Email};, params: {"ID":null,"Email":"test@foxmail.com","Nickname":null,"CreateTime":null}, dbSql: SELECT id,email,nickname,create_time FROM user WHERE email=?;, dbParams: ["test@foxmail.com"]
2022-02-21T11:42:44.599+0800    INFO    osm_demo/main.go:86     test select     {"count": 1, "result": "[{\"ID\":10,\"Email\":\"test@foxmail.com\",\"Nickname\":\"hello\",\"CreateTime\":\"2022-02-21T03:42:44+08:00\"}]"}
2022-02-21T11:42:44.600+0800    INFO    v2@v2.0.2/sql.go:311    readSQLParamsBySQL showSql, sql: DELETE FROM user WHERE email=#{Email}, params: {"ID":null,"Email":"test@foxmail.com","Nickname":null,"CreateTime":null}, dbSql: DELETE FROM user WHERE email=?, dbParams: ["test@foxmail.com"]
2022-02-21T11:42:44.603+0800    INFO    osm_demo/main.go:97     test delete     {"count": 1}

struct 字段名与 SQL 列名对应关系

  • 正常的转换过程

    用"_"分隔 （例：XXX_YYY -> XXX,YYY）

    每个部分全部转为首字大写其余字符小写 （例：XXX,YYY -> Xxx,Yyy）

    拼接（例：Xxx,Yyy -> XxxYyy）

  • 常见缩写单词，下面这些单词两种形式都可以，struct 上可以任选其一。 比如"UserId"和"UserID"可以正常对应到"user_id"列上。但是同一个 struct 中不可以既有"UserId"成员又有"UserID"成员，如果同时存在只会有一个成员会被赋值。

    Acl  或   ACL
Api  或   API
Ascii  或 ASCII
Cpu  或   CPU
Css  或   CSS
Dns  或   DNS
Eof  或   EOF
Guid  或  GUID
Html  或  HTML
Http  或  HTTP
Https  或 HTTPS
Id  或    ID
Ip  或    IP
Json  或  JSON
Lhs  或   LHS
Qps  或   QPS
Ram  或   RAM
Rhs  或   RHS
Rpc  或   RPC
Sla  或   SLA
Smtp  或  SMTP
Sql  或   SQL
Ssh  或   SSH
Tcp  或   TCP
Tls  或   TLS
Ttl  或   TTL
Udp  或   UDP
Ui  或    UI
Uid  或   UID
Uuid  或  UUID
Uri  或   URI
Url  或   URL
Utf8  或  UTF8
Vm  或    VM
Xml  或   XML
Xmpp  或  XMPP
Xsrf  或  XSRF
Xss  或   XSS

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type DefaultLogger added in v2.0.4 

type DefaultLogger struct {
}

func (*DefaultLogger) Log added in v2.0.4 

func (*DefaultLogger) Log(msg string, data map[string]string)

type Logger 

type Logger interface {
	Log(msg string, data map[string]string)
}

type Options added in v2.0.3 

type Options struct {
	MaxIdleConns    int
	MaxOpenConns    int
	ConnMaxLifetime time.Duration
	ConnMaxIdleTime time.Duration

	// Warn日志
	WarnLogger Logger
	// Error日志
	ErrorLogger Logger
	// Info日志
	InfoLogger Logger
	// ShowSQL 显示执行的sql，用于调试，使用logger打印
	ShowSQL bool
	// SlowLogDuration 慢查询时间阈值
	SlowLogDuration time.Duration
}

Options 连接选项和日志设置

type Osm 

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

Osm 对象，通过Struct、Map、Array、value等对象以及Sql Map来操作数据库。可以开启事务。

func New 

func New(driverName, dataSource string, options Options) (*Osm, error)

New 创建一个新的Osm，这个过程会打开数据库连接。

driverName 是数据库驱动名称如"mysql". dataSource 是数据库连接信息如"root:root@/text?charset=utf8". options 是数据连接选项和日志设置

如： 

o, err := osm.New("mysql", "root:root@/text?charset=utf8", osm.Options{
	MaxIdleConns:    50,
	MaxOpenConns:    100,
	ConnMaxLifetime: 5 * time.Minute,
	ConnMaxIdleTime: 5 * time.Minute,
	WarnLogger:      &WarnLoggor{errorLogger},  // Logger
	ErrorLogger:     &ErrorLogger{errorLogger}, // Logger
	InfoLogger:      &InfoLogger{infoLogger},   // Logger
	ShowSQL:         true,                      // bool
	SlowLogDuration: 500 * time.Millisecond,    // time.Duration
})

func (*Osm) Begin 

func (o *Osm) Begin() (*Tx, error)

Begin 打开事务

如： 

tx, err := o.Begin()

func (*Osm) Close 

func (o *Osm) Close() error

Close 与数据库断开连接，释放连接资源

如： 

err := o.Close()

func (*Osm) Delete 

func (o *Osm) Delete(sql string, params ...interface{}) (int64, error)

Delete 执行删除sql

代码 

  count, err := o.Delete(`DELETE FROM res_user WHERE id in #{Ids};`, []int64{1, 2})
  if err != nil {
	   log.Println(err)
  }
  log.Println("count:", count)

结果 

count: 2

删除id为1和2的用户数据

func (*Osm) Insert 

func (o *Osm) Insert(sql string, params ...interface{}) (int64, int64, error)

Insert 执行添加sql

代码 

  insertResUser := ResUser{
	  Email: "test@foxmail.com",
  }
  insertID, count, err := o.Insert("INSERT INTO res_user (email) VALUES(#{Email});", insertResUser)
  if err != nil {
	  log.Println(err)
  }
  log.Println("insertID:", insertID, "count:", count)

结果 

insertID: 3 count: 1

添加一个用户数据，email为"test@foxmail.com"

func (*Osm) SelectKVS 

func (o *Osm) SelectKVS(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectKVS 执行查询sql

查出的结果为多行,每行有两个字段,前者为key,后者为value,存入map (双列)，Key、Value可以是指针，如var r map[string]time.Time、var r map[*string]time.Time、var r map[string]*time.Time

代码 

  var idEmailMap = map[int64]string{}
  _, err = o.SelectKVS(`SELECT id,email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&idEmailMap)
  if err != nil {
	  log.Println(err)
  }
  log.Printf("idEmailMap: %v \n", idEmailMap)

结果 

idEmailMap: map[1:test@foxmail.com 2:test@foxmail.com]

func (*Osm) SelectStrings 

func (o *Osm) SelectStrings(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStrings 执行查询sql

查出的结果为多行，查出的结果为多行,并存入columns，和datas。columns为[]string，datas为[][]string

代码 

  var columns []string
  var datas [][]string
  _, err = o.SelectStrings(`SELECT id,email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&columns, &datas)
  if err != nil {
	  log.Println(err)
  }
  log.Printf("columns: %v，datas: %v \n", columns, datas)

结果 

columns: ["id", "email"]
datas: [["1",'test@foxmail.com'],["2","test@foxmail.com"]]

func (*Osm) SelectStruct 

func (o *Osm) SelectStruct(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStruct 执行查询sql

查出的结果为单行,并存入struct，可以是指针，如var r User、var r *User

代码 

  var users []ResUser
  _, err = o.SelectStruct(`SELECT id,email,create_time FROM res_user WHERE id=#{Id};`, 1)(&users)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("user: %#v \n", users)

结果 

user: ResUser{ID:1, Email:"test@foxmail.com", Mobile:"", Nickname:""}

func (*Osm) SelectStructs 

func (o *Osm) SelectStructs(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStructs 执行查询sql

查出的结果为多行,并存入struct array，元素可以是指针，如var r []User、var r []*User

代码 

  var users []ResUser
  _, err = o.SelectStructs(`SELECT id,email,create_time FROM res_user WHERE id=#{Id};`, 1)(&users)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("users: %#v \n", users)

结果 

users: []ResUser{ResUser{ID:1, Email:"test@foxmail.com", Mobile:"", Nickname:""}}

func (*Osm) SelectValue 

func (o *Osm) SelectValue(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectValue 执行查询sql

查出的结果为单行,并存入不定长的变量上(...)，可以是指针，如var r1,r2 string、var r1,r2 *string

代码 

  var email string
  _, err = o.SelectValue(`SELECT email FROM res_user WHERE id=#{Id};`, 1)(&email)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("email: %s \n", email)

结果 

email: test@foxmail.com

func (*Osm) SelectValues 

func (o *Osm) SelectValues(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectValues 执行查询sql

查出的结果为多行,并存入不定长的变量上(...，每个都为array)，元素可以是指针，如var r1,r2 []string、var r1,r2 []*string都允许

代码 

  var emails []string
  _, err = o.SelectValues(`SELECT email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&emails)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("emails: %v \n", emails)

结果 

emails: [test@foxmail.com test@foxmail.com]

func (*Osm) Update 

func (o *Osm) Update(sql string, params ...interface{}) (int64, error)

Update 执行更新sql

代码 

  count, err := o.Update(`UPDATE res_user SET email=#{Email} WHERE id=#{ID};`, "test2@foxmail.com", 3)
  if err != nil {
	  log.Println(err)
  }
  log.Println("count:", count)

结果 

count: 1

将id为1的用户email更新为"test2@foxmail.com"

func (*Osm) UpdateMulti 

func (o *Osm) UpdateMulti(sql string, params ...interface{}) error

UpdateMulti 批量执行更新sql

代码 

user := User{Id: 3, Id2: 4, Email: "test@foxmail.com"}
err := o.UpdateMulti(`
     UPDATE user SET email='#{Email}' where id = #{Id};
     UPDATE user SET email='#{Email}' where id = #{Id2};`, user)

将id为3和4的用户email更新为"test@foxmail.com"

type Tx 

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

Tx 与Osm对象一样，不过是在事务中进行操作

func (*Tx) Commit 

func (o *Tx) Commit() error

Commit 提交事务

如： 

err := tx.Commit()

func (*Tx) Delete 

func (o *Tx) Delete(sql string, params ...interface{}) (int64, error)

Delete 执行删除sql

代码 

  count, err := o.Delete(`DELETE FROM res_user WHERE id in #{Ids};`, []int64{1, 2})
  if err != nil {
	   log.Println(err)
  }
  log.Println("count:", count)

结果 

count: 2

删除id为1和2的用户数据

func (*Tx) Insert 

func (o *Tx) Insert(sql string, params ...interface{}) (int64, int64, error)

Insert 执行添加sql

代码 

  insertResUser := ResUser{
	  Email: "test@foxmail.com",
  }
  insertID, count, err := o.Insert("INSERT INTO res_user (email) VALUES(#{Email});", insertResUser)
  if err != nil {
	  log.Println(err)
  }
  log.Println("insertID:", insertID, "count:", count)

结果 

insertID: 3 count: 1

添加一个用户数据，email为"test@foxmail.com"

func (*Tx) Rollback 

func (o *Tx) Rollback() error

Rollback 事务回滚

如： 

err := tx.Rollback()

func (*Tx) SelectKVS 

func (o *Tx) SelectKVS(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectKVS 执行查询sql

查出的结果为多行,每行有两个字段,前者为key,后者为value,存入map (双列)，Key、Value可以是指针，如var r map[string]time.Time、var r map[*string]time.Time、var r map[string]*time.Time

代码 

  var idEmailMap = map[int64]string{}
  _, err = o.SelectKVS(`SELECT id,email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&idEmailMap)
  if err != nil {
	  log.Println(err)
  }
  log.Printf("idEmailMap: %v \n", idEmailMap)

结果 

idEmailMap: map[1:test@foxmail.com 2:test@foxmail.com]

func (*Tx) SelectStrings 

func (o *Tx) SelectStrings(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStrings 执行查询sql

查出的结果为多行，查出的结果为多行,并存入columns，和datas。columns为[]string，datas为[][]string

代码 

  var columns []string
  var datas [][]string
  _, err = o.SelectStrings(`SELECT id,email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&columns, &datas)
  if err != nil {
	  log.Println(err)
  }
  log.Printf("columns: %v，datas: %v \n", columns, datas)

结果 

columns: ["id", "email"]
datas: [["1",'test@foxmail.com'],["2","test@foxmail.com"]]

func (*Tx) SelectStruct 

func (o *Tx) SelectStruct(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStruct 执行查询sql

查出的结果为单行,并存入struct，可以是指针，如var r User、var r *User

代码 

  var users []ResUser
  _, err = o.SelectStruct(`SELECT id,email,create_time FROM res_user WHERE id=#{Id};`, 1)(&users)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("user: %#v \n", users)

结果 

user: ResUser{ID:1, Email:"test@foxmail.com", Mobile:"", Nickname:""}

func (*Tx) SelectStructs 

func (o *Tx) SelectStructs(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectStructs 执行查询sql

查出的结果为多行,并存入struct array，元素可以是指针，如var r []User、var r []*User

代码 

  var users []ResUser
  _, err = o.SelectStructs(`SELECT id,email,create_time FROM res_user WHERE id=#{Id};`, 1)(&users)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("users: %#v \n", users)

结果 

users: []ResUser{ResUser{ID:1, Email:"test@foxmail.com", Mobile:"", Nickname:""}}

func (*Tx) SelectValue 

func (o *Tx) SelectValue(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectValue 执行查询sql

查出的结果为单行,并存入不定长的变量上(...)，可以是指针，如var r1,r2 string、var r1,r2 *string

代码 

  var email string
  _, err = o.SelectValue(`SELECT email FROM res_user WHERE id=#{Id};`, 1)(&email)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("email: %s \n", email)

结果 

email: test@foxmail.com

func (*Tx) SelectValues 

func (o *Tx) SelectValues(sql string, params ...interface{}) func(containers ...interface{}) (int64, error)

SelectValues 执行查询sql

查出的结果为多行,并存入不定长的变量上(...，每个都为array)，元素可以是指针，如var r1,r2 []string、var r1,r2 []*string都允许

代码 

  var emails []string
  _, err = o.SelectValues(`SELECT email FROM res_user WHERE id in #{Ids};`, []int64{1, 2})(&emails)
  if err != nil {
	   log.Println(err)
  }
  log.Printf("emails: %v \n", emails)

结果 

emails: [test@foxmail.com test@foxmail.com]

func (*Tx) Update 

func (o *Tx) Update(sql string, params ...interface{}) (int64, error)

Update 执行更新sql

代码 

  count, err := o.Update(`UPDATE res_user SET email=#{Email} WHERE id=#{ID};`, "test2@foxmail.com", 3)
  if err != nil {
	  log.Println(err)
  }
  log.Println("count:", count)

结果 

count: 1

将id为1的用户email更新为"test2@foxmail.com"

func (*Tx) UpdateMulti 

func (o *Tx) UpdateMulti(sql string, params ...interface{}) error

UpdateMulti 批量执行更新sql

代码 

user := User{Id: 3, Id2: 4, Email: "test@foxmail.com"}
err := o.UpdateMulti(`
     UPDATE user SET email='#{Email}' where id = #{Id};
     UPDATE user SET email='#{Email}' where id = #{Id2};`, user)

将id为3和4的用户email更新为"test@foxmail.com"

Source Files

View all Source files

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.