golang数据库操作之gorm

gorm快速上手 Quick start

数据库连接

1.Sqlite数据库

package main

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

func main() {
    //打开数据库连接
    var db, _ = gorm.Open("sqlite3", "demo.db")

    //关闭数据库连接
    defer db.Close()
}

2.MySQL数据库

数据库连接字符串格式即DSN (Data Source Name)格式为：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

详情请参考https://github.com/go-sql-driver/mysql#dsn-data-source-name

package main

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

func main() {
    //打开数据库连接
    db, _ := gorm.Open("mysql", "demo:123456@(localhost)/demo?charset=utf8&parseTime=True&loc=Local")

    //关闭数据库连接
    defer db.Close()
}

操作示例

1.CURD基础操作

package main

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

type User struct {
    gorm.Model
    Name string
    Age  int
}

func main() {
    //打开数据库连接
    var db, _ = gorm.Open("sqlite3", "demo.db")

    //关闭数据库连接
    defer db.Close()

    //添加数据库表名前缀
    gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
        return "demo_" + defaultTableName
    }

    //创建表
    db.AutoMigrate(&User{})

    user := User{
        Name: "demo",
        Age:  20,
    }
    //插入数据
    db.Create(&user)

    //查询数据
    db.First(&user, "name = ?", "demo")
    println(fmt.Sprintf("%v", user))

    //更新数据
    user.Age = 21
    db.Save(&user)

    //查询数据
    db.First(&user, "name = ?", "demo")
    println(fmt.Sprintf("%v", user))

    //删除数据，软删除(存在deleted_at字段自动软删除，给deleted_at字段赋值就标记为删除)
    db.Delete(&user)

    //物理删除
    db.Unscoped().Delete(&user)

    var count = 0
    //查询总数
    db.Model(&User{}).Count(&count)

    println("user count: ", count)

    println("OK")
}

2.表关联

假设场景
有个学生Student，该Student有多本书Book

package main

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

type Book struct {
    gorm.Model
    StudentId int //关联Student表
    Name      string
    ISBN      string
    Price     float32
}

type Student struct {
    gorm.Model
    Name  string
    Age   int
    Books []Book
}

func main() {
    //打开数据库连接
    db, _ := gorm.Open("mysql", "demo:123456@(localhost)/demo?charset=utf8&parseTime=True&loc=Local")

    //关闭数据库连接
    defer db.Close()

    //创建表
    db.AutoMigrate(&Student{}, &Book{})

    book1 := Book{
        Name:  "Go语言实战",
        ISBN:  "9787115445353",
        Price: 59.00,
    }
    book2 := Book{
        Name:  "Go程序设计语言",
        ISBN:  "9787111558422",
        Price: 79.00,
    }
    student := Student{
        Name:  "demo",
        Age:   25,
        Books: []Book{book1, book2},
    }

    //插入数据，将写入一条student记录和两条与之关联的book记录
    db.Create(&student)

    //查询student关联的所有book信息
    var books []Book
    db.Model(&student).Related(&books)

    for _, book := range books {
        println(book.Name)
    }

    println("OK")
}

连接池配置

只有在面向连接的数据库才能设置连接池，比如MySQL或PostgreSQL
而基于文件的数据库，如Sqlite是不能设置连接池的

db.DB()返回*sql.DB通用接口，通过该接口可以配置底层数据库连接参数

// SetMaxIdleCons 设置连接池中的最大闲置连接数。
db.DB().SetMaxIdleConns(10)

// SetMaxOpenCons 设置数据库的最大连接数量。
db.DB().SetMaxOpenConns(100)