day5 | 设计模式之 Database/SQL 与 GORM 实践 | 第三届字节跳动青训营笔记

package main

import (
 "database/sql"
 _ "github.com/go-sql-driver/mysql"

)

func main() {
 db, err := sql.Open ("mysqL", "user:password@tcp(127.0.0.1:3306])/hello")
 rows, err := db.Query("select id, name from users where id = ?", 1)
 if err == nil {
  //xxx
 }
 defer rows.Close()
 var users []User
 for rows.Next() {
  var user User
  err := rows.Scan(&user. ID, &user,Name)
  if err!=nil{
  //..
  }
 }
  users = append(users, user)
  if rows.Err() != nil {
  }
}

type DB struct {
 // Atomic access only. At top of struct to prevent mis-alignment
 // on 32-bit platforms. Of type time.Duration.
 waitDuration int64 // Total time waited for new connections.

 connector driver.Connector
 // numClosed is an atomic counter which represents a total number of
 // closed connections. Stmt.openStmt checks it before cleaning closed
 // connections in Stmt.css.
 numClosed uint64

 mu           sync.Mutex // protects following fields
 freeConn     []*driverConn
 connRequests map[uint64]chan connRequest
 nextRequest  uint64 // Next key to use in connRequests.
 numOpen      int    // number of opened and pending open connections
 // Used to signal the need for new connections
 // a goroutine running connectionOpener() reads on this chan and
 // maybeOpenNewConnections sends on the chan (one send per needed connection)
 // It is closed during db.Close(). The close tells the connectionOpener
 // goroutine to exit.
 openerCh          chan struct{}
 resetterCh        chan *driverConn
 closed            bool
 dep               map[finalCloser]depSet
 lastPut           map[*driverConn]string // stacktrace of last conn's put; debug only
 maxIdle           int                    // zero means defaultMaxIdleConns; negative means 0
 maxOpen           int                    // <= 0 means unlimited
 maxLifetime       time.Duration          // maximum amount of time a connection may be reused
 cleanerCh         chan struct{}
 waitCount         int64 // Total number of connections waited for.
 maxIdleClosed     int64 // Total number of connections closed due to idle.
 maxLifetimeClosed int64 // Total number of connections closed due to max free limit.

 stop func() // stop cancels the connection opener and the session resetter.
}

func (db *DB) SetConnMaxIdleTime(d time.Duration)
func (db *DB) SetConnMaxLifetime(d time.Duration)
func (db *DB) SetMaxIdleConns(n int)
func (db *DB) SetMaxOpenConns(n int)

func (db *DB) Stats() DBStats

 for i := 0; i < maxBadConnRetries; i++ {
  // 从连接池获取连接或通过driver新建连接
  dc, err := db.conn(ctx, strategy)
  //有空闲连接-> reuse -> max life time
  //新建连接-> max open.. .
  //将连接放回连接池
  defer dc.db.putConn(dc, err, true)
  /// validateConnection有无错误
  // max life time, max idle conns检查
  //连接实现driver.Queryer; driver.Execer等interface
  if err == nil{
   err = dc.ci.Query(sql, args...)
  }
  isBadConn = errors.Is(err, driver. ErrBadConn)
  if !isBadConn {
   break
  }

// Open new Connection.
// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for how
// the DSN string is formatted
func (d MySQLDriver) Open(dsn string) (driver.Conn, error) {
 cfg, err := ParseDSN(dsn)
 if err != nil {
  return nil, err
 }
 c := &connector{
  cfg: cfg,
 }
 return c.Connect(context.Background())
}
// github.com/go-sql-driver/mysql/driver.go
// 注册驱动
func init() {
 sql.Register("mysql", &MySQLDriver{})
}

go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

package main

import (
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
)

type Product struct {
  gorm.Model
  Code  string
  Price uint
}

func main() {
  db, err := gorm.Open(sqlite.Open("deme.db"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  // Migrate the schema
  db.AutoMigrate(&Product{})

  // Create
  db.Create(&Product{Code: "D42", Price: 100})

  // Read
  var product Product
  db.First(&product, 1) // find product with integer primary key
  db.First(&product, "code = ?", "D42") // find product with code D42

  // Update - update product's price to 200
  db.Model(&product).Update("Price", 200)
  // Update - update multiple fields
  db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // non-zero fields
  db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})

  // Delete - delete product
  db.Delete(&product, 1)
}

package main

// gorm demo1

import (
 "fmt"
 "github.com/jinzhu/gorm"
 _ "github.com/jinzhu/gorm/dialects/mysql"
)

// UserInfo --> 数据表
type UserInfo struct {
 ID     uint
 Name   string
 Gender string
 Hobby  string
}

func main() {
 // 连接MySQL数据库
 db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
 if err != nil {
  panic(err)
 }
 defer db.Close()

 // 创建表 自动迁移（把结构体和数据表进行对应）
 db.AutoMigrate(&UserInfo{})

 // 创建数据行
 //u1 := UserInfo{1, "七米", "男", "蛙泳"}
 //db.Create(&u1)
 // 查询
 var u UserInfo
 db.First(&u) // 查询表中第一天数据保存到u中
 fmt.Printf("u:%#v\n", u)
 // 更新
 db.Model(&u).Update("hobby", "双色球")
 // 删除
 db.Delete(&u)
}

package main

import (
 "database/sql"
 "fmt"
 "github.com/jinzhu/gorm"
 _ "github.com/jinzhu/gorm/dialects/mysql"
)

// gorm demo03

// 1. 定义模型
type User struct {
 ID   int64
 Name sql.NullString `gorm:"default:'小王子'"`
 Age  int64
}

func main() {
 // 连接MySQL数据库
 db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
 if err != nil {
  panic(err)
 }
 defer db.Close()
 // 2. 把模型与数据库中的表对应起来
 db.AutoMigrate(&User{})

 // 3. 创建
 u := User{Name: sql.NullString{String: "", Valid: true}, Age: 98} // 在代码层面创建一个User对象
 fmt.Println(db.NewRecord(&u))                                     // 判断主键是否为空 true
 db.Debug().Create(&u)                                             // 在数据库中创建了一条q1mi 18的记录
 fmt.Println(db.NewRecord(&u))                                     // 判断主键是否为空 false
}

package main

// gorm demo2
import (
 "database/sql"
 "github.com/jinzhu/gorm"
 _ "github.com/jinzhu/gorm/dialects/mysql"
 "time"
)

// 定义模型
type User struct {
 gorm.Model   // 内嵌gorm.Model
 Name         string
 Age          sql.NullInt64 `gorm:"column:user_age"` // 零值类型
 Birthday     *time.Time
 Email        string  `gorm:"type:varchar(120);unique_index"`
 Role         string  `gorm:"size:255"`        // 设置字段大小为255
 MemberNumber *string `gorm:"unique;not null"` // 设置会员号（member number）唯一并且不为空
 Num          int     `gorm:"AUTO_INCREMENT"`  // 设置 num 为自增类型
 Address      string  `gorm:"index:addr"`      // 给address字段创建名为addr的索引
 IgnoreMe     int     `gorm:"-"`               // 忽略本字段
}

// 使用`AnimalID`作为主键
type Animal struct {
 AnimalID int64 `gorm:"primary_key"`
 Name     string
 Age      int64
}

// 唯一指定表名
func (Animal) TableName() string {
 return "qimi"
}

func main() {
 // 修改默认的表明规则
 gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
  return "SMS_" + defaultTableName
 }
 // 连接MySQL数据库
 db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
 if err != nil {
  panic(err)
 }
 defer db.Close()
 db.SingularTable(true) // 禁用复数

 db.AutoMigrate(&User{})
 db.AutoMigrate(&Animal{})

 // 使用User结构体创建名叫 xiaowangzi 的表
 //db.Table("xiaowangzi").CreateTable(&User{})
}