Golang与关系型数据库集成：性能优化与数据访问策略

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql" // 导入MySQL驱动
)

type User struct {
    ID   int
    Name string
    Email string
}

func main() {
    // 连接数据库
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database_name?parseTime=true")
    if err != nil {
        log.Fatalf("Failed to open database: %v", err)
    }
    defer db.Close()

    // 验证数据库连接
    err = db.Ping()
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    fmt.Println("Successfully connected to MySQL!")

    // 插入数据（使用预处理语句）
    stmt, err := db.Prepare("INSERT INTO users(name, email) VALUES(?, ?)")
    if err != nil {
        log.Fatalf("Failed to prepare statement: %v", err)
    }
    defer stmt.Close()

    res, err := stmt.Exec("Alice", "alice@example.com")
    if err != nil {
        log.Fatalf("Failed to insert user: %v", err)
    }
    lastID, _ := res.LastInsertId()
    fmt.Printf("Inserted user with ID: %d\n", lastID)

    // 查询数据
    rows, err := db.Query("SELECT id, name, email FROM users WHERE id = ?", lastID)
    if err != nil {
        log.Fatalf("Failed to query users: %v", err)
    }
    defer rows.Close()

    var user User
    if rows.Next() {
        err := rows.Scan(&user.ID, &user.Name, &user.Email)
        if err != nil {
            log.Fatalf("Failed to scan user: %v", err)
        }
        fmt.Printf("Found user: %+v\n", user)
    }
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

// User 实体结构体
type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

// UserDS (User DataStore) 定义用户数据存储接口
type UserDS interface {
    CreateUser(user User) (int, error)
    GetUserByID(id int) (*User, error)
    // Add other CRUD operations like UpdateUser, DeleteUser, ListUsers
}

// UserDB 是 UserDS 接口的 SQL 实现
type UserDB struct {
    db *sql.DB
}

// NewUserDB 创建一个新的 UserDB 实例
func NewUserDB(db *sql.DB) *UserDB {
    return &UserDB{db: db}
}

// CreateUser 实现了 UserDS 接口的 CreateUser 方法
func (u *UserDB) CreateUser(user User) (int, error) {
    stmt, err := u.db.Prepare("INSERT INTO users(name, email) VALUES(?, ?)")
    if err != nil {
        return 0, fmt.Errorf("prepare statement failed: %w", err)
    }
    defer stmt.Close()

    res, err := stmt.Exec(user.Name, user.Email)
    if err != nil {
        return 0, fmt.Errorf("execute statement failed: %w", err)
    }
    lastID, err := res.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("get last insert ID failed: %w", err)
    }
    return int(lastID), nil
}

// GetUserByID 实现了 UserDS 接口的 GetUserByID 方法
func (u *UserDB) GetUserByID(id int) (*User, error) {
    row := u.db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id)
    var user User
    err := row.Scan(&user.ID, &user.Name, &user.Email)
    if err == sql.ErrNoRows {
        return nil, nil // 用户不存在
    }
    if err != nil {
        return nil, fmt.Errorf("scan user failed: %w", err)
    }
    return &user, nil
}

// 业务逻辑层
type UserService struct {
    userDS UserDS
}

func NewUserService(userDS UserDS) *UserService {
    return &UserService{userDS: userDS}
}

func (s *UserService) RegisterUser(name, email string) (*User, error) {
    newUser := User{Name: name, Email: email}
    id, err := s.userDS.CreateUser(newUser)
    if err != nil {
        return nil, fmt.Errorf("failed to register user: %w", err)
    }
    newUser.ID = id
    return &newUser, nil
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database_name?parseTime=true")
    if err != nil {
        log.Fatalf("Failed to open database: %v", err)
    }
    defer db.Close()

    if err = db.Ping(); err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }

    userDB := NewUserDB(db)
    userService := NewUserService(userDB)

    registeredUser, err := userService.RegisterUser("Bob", "bob@example.com")
    if err != nil {
        log.Fatalf("Error registering user: %v", err)
    }
    fmt.Printf("Registered user: %+v\n", registeredUser)

    foundUser, err := userService.GetUserByID(registeredUser.ID)
    if err != nil {
        log.Fatalf("Error getting user: %v", err)
    }
    if foundUser != nil {
        fmt.Printf("Found user by ID: %+v\n", foundUser)
    }
}