第九章：数据库操作与ORM
9.1 使用database/sql
连接数据库
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
// 打开数据库
db, err := sql.Open("sqlite3", "./test.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 测试连接
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("数据库连接成功")
}
CRUD操作
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
type User struct {
ID int
Name string
Email string
}
func main() {
db, _ := sql.Open("sqlite3", "./test.db")
defer db.Close()
// 创建表
createTable := `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT
);`
_, err := db.Exec(createTable)
if err != nil {
log.Fatal(err)
}
// 插入数据
result, _ := db.Exec(
"INSERT INTO users (name, email) VALUES (?, ?)",
"张三", "zhangsan@example.com")
lastID, _ := result.LastInsertId()
fmt.Println("插入ID:", lastID)
// 查询单行
var user User
row := db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", 1)
err = row.Scan(&user.ID, &user.Name, &user.Email)
if err != nil {
log.Fatal(err)
}
fmt.Println(user)
// 查询多行
rows, _ := db.Query("SELECT id, name, email FROM users")
defer rows.Close()
for rows.Next() {
var u User
rows.Scan(&u.ID, &u.Name, &u.Email)
fmt.Println(u)
}
// 更新
db.Exec("UPDATE users SET name = ? WHERE id = ?", "张三丰", 1)
// 删除
db.Exec("DELETE FROM users WHERE id = ?", 1)
}
事务处理
func transfer(db *sql.DB, fromID, toID int, amount float64) error {
tx, err := db.Begin()
if err != nil {
return err
}
// 扣款
_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?",
amount, fromID)
if err != nil {
tx.Rollback()
return err
}
// 收款
_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, toID)
if err != nil {
tx.Rollback()
return err
}
return tx.Commit()
}
9.2 使用GORM
安装与连接
go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite
package main
import (
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"log"
)
type User struct {
gorm.Model
Name string
Email string
Age int
}
func main() {
// 连接数据库
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
if err != nil {
log.Fatal("连接失败:", err)
}
// 自动迁移
db.AutoMigrate(&User{})
}
CRUD操作
package main
import (
"fmt"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type Product struct {
gorm.Model
Code string
Price uint
}
func main() {
db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
db.AutoMigrate(&Product{})
// 创建
db.Create(&Product{Code: "D42", Price: 100})
// 批量创建
products := []Product{
{Code: "D43", Price: 200},
{Code: "D44", Price: 300},
}
db.Create(&products)
// 查询
var product Product
db.First(&product, 1) // 按主键
db.First(&product, "code = ?", "D42") // 按条件
// 查询多条
var products2 []Product
db.Find(&products2)
db.Where("price > ?", 100).Find(&products2)
// 更新
db.Model(&product).Update("Price", 200)
db.Model(&product).Updates(Product{Price: 200, Code: "F42"})
db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})
// 删除
db.Delete(&product, 1)
// 软删除（需要gorm.Model）
db.Delete(&product)
// 查询软删除的数据
db.Unscoped().Where("deleted_at IS NOT NULL").Find(&products2)
// 永久删除
db.Unscoped().Delete(&product)
}
高级查询
// 条件查询
db.Where("name = ?", "张三").First(&user)
db.Where("name <> ?", "张三").Find(&users)
db.Where("name IN ?", []string{"张三", "李四"}).Find(&users)
db.Where("name LIKE ?", "%张%").Find(&users)
db.Where("age > ? AND email IS NOT NULL", 20).Find(&users)
// 结构体条件
db.Where(&User{Name: "张三", Age: 20}).First(&user)
// Map条件
db.Where(map[string]interface{}{"name": "张三", "age": 20}).Find(&users)
// 排序
db.Order("age desc").Find(&users)
db.Order("age desc, name").Find(&users)
// 分页
var users []User
db.Offset(0).Limit(10).Find(&users)
// 聚合
var count int64
db.Model(&User{}).Count(&count)
type Result struct {
Date string
Total int
}
var results []Result
db.Model(&User{}).Select("date(created_at) as date, count(*) as total").
Group("date(created_at)").Find(&results)
关联关系
package main
import (
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
// 一对一
type User struct {
gorm.Model
Name string
CreditCard CreditCard
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
// 一对多
type User2 struct {
gorm.Model
Name string
Emails []Email
}
type Email struct {
gorm.Model
Email string
UserID uint
}
// 多对多
type User3 struct {
gorm.Model
Name string
Languages []Language `gorm:"many2many:user_languages;"`
}
type Language struct {
gorm.Model
Name string
}
func main() {
db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// 自动迁移
db.AutoMigrate(&User{}, &CreditCard{})
db.AutoMigrate(&User2{}, &Email{})
db.AutoMigrate(&User3{}, &Language{})
// 创建关联数据
user := User{
Name: "张三",
CreditCard: CreditCard{Number: "4111111111111111"},
}
db.Create(&user)
// 预加载查询
var users []User
db.Preload("CreditCard").Find(&users)
// 一对多
user2 := User2{
Name: "李四",
Emails: []Email{
{Email: "lisi@example.com"},
{Email: "lisi2@example.com"},
},
}
db.Create(&user2)
// 查询并加载关联
var users2 []User2
db.Preload("Emails").Find(&users2)
}
9.3 项目实战：用户管理系统
package main
import (
"net/http"
"strconv"
"github.com/gin-gonic/gin"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
var db *gorm.DB
type User struct {
gorm.Model
Name string `json:"name" binding:"required"`
Email string `json:"email" gorm:"unique"`
Age int `json:"age"`
}
func initDB() {
var err error
db, err = gorm.Open(sqlite.Open("user.db"), &gorm.Config{})
if err != nil {
panic("数据库连接失败")
}
db.AutoMigrate(&User{})
}
func main() {
initDB()
r := gin.Default()
api := r.Group("/api/users")
{
api.GET("", getUsers)
api.GET("/:id", getUser)
api.POST("", createUser)
api.PUT("/:id", updateUser)
api.DELETE("/:id", deleteUser)
}
r.Run(":8080")
}
func getUsers(c *gin.Context) {
var users []User
db.Find(&users)
c.JSON(http.StatusOK, gin.H{"data": users})
}
func getUser(c *gin.Context) {
id, _ := strconv.Atoi(c.Param("id"))
var user User
if err := db.First(&user, id).Error; err != nil {
c.JSON(http.StatusNotFound, gin.H{"error": "用户不存在"})
return
}
c.JSON(http.StatusOK, gin.H{"data": user})
}
func createUser(c *gin.Context) {
var user User
if err := c.ShouldBind
