go项目实现mysql接入以及web api

go,项目,实现,mysql,接入,以及,web,api · 浏览次数 : 197

小编点评

## 内容生成指南 **1. 工具导入** ```go import ( "encoding/json" "go_test/models" "go_test/utils" "log" "net/http" "go_test/database" "go_test/repositories" "go_test/services" ) ``` **2. 错误处理** ```go func HandleError(w http.ResponseWriter, err error) { if err != nil { w.WriteHeader(http.StatusInternalServerError) response := models.ErrorResponse{ Message: err.Error(), } json.NewEncoder(w).Encode(response) return } } ``` **3. 错误响应** ```go func RespondJSON(w http.ResponseWriter, data interface{}, statusCode int) { w.Header().Set("Content-Type", "application/json") w.WriteHeader(statusCode) json.NewEncoder(w).Encode(data) } ``` **4. 日志记录** ```go log.Println("coming main method ...\") ``` **5. 数据库连接** ```go db, err := database.NewMySQLDB() if err != nil { log.Fatal(err) } ``` **6. 用户仓库** ```go userRepository := &repositories.UserRepository{ DB: db, } ``` **7. 服务接口** ```go userService := &services.UserService{ UserRepository: userRepository, } ``` **8. API路由** ```go http.HandleFunc(\"/users\", func(w http.ResponseWriter, r *http.Request) { // ...其他逻辑 ... } ``` **9. 主类** ```go func main() { log.Println("coming main method ...\") db, err := database.NewMySQLDB() if err != nil { log.Fatal(err) } userRepository := &repositories.UserRepository{ DB: db, } userService := &services.UserService{ UserRepository: userRepository, } http.HandleFunc(\"/users\", func(w http.ResponseWriter, r *http.Request) { // ...其他逻辑 ... } log.Println("Server is running on port 8000\") log.Fatal(http.ListenAndServe(\":8000\", nil))} } ``` **10. 构建项目** ```go go build main.go ``` **11. 部署项目** ```go // ...部署代码 ... ``` **12. 运行项目** ```go // ...运行代码 ... ``` **13. 测试项目** ```go // ...测试代码 ... ```

正文

本文为博主原创，转载请注明出处：

　　创建go项目，并在go项目中接入mysql，将mysql的配置项单独整理放到一个胚子和文件中，支持项目启动时，通过加载配置文件中的值，然后创建数据库连接。

　　之后使用net/http相关的库，创建路由，并在路由中通过不同的http方法，实现mysql连接的test数据库中users表的增删改查的 web api

1.在idea中创建go项目

　　其目录文件结构如下

2.创建配置文件，并初始化数据库

　　在项目的根目录下创建数据库的配置文件：config.json

{
    "host": "192.168.118.46",
    "port": 3306,
    "user": "root",
    "password": "root",
    "dbname": "terra_no"
}

　　创建数据库以及表，并插入数据

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(12) DEFAULT '18',
  PRIMARY KEY (`id`)
); 
insert into users value (1,"aa",12);
insert into users value (2,"bb",22);

3.创建加载mysql，并创建数据库连接的类

　　在项目的根目录下创建database目录，之后创建 mysql.go 文件

package database

import (
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "io/ioutil"
    "log"
)

type MySQLConfig struct {
    Host     string `json:"host"`
    Port     int    `json:"port"`
    User     string `json:"user"`
    Password string `json:"password"`
    DBName   string `json:"dbname"`
}

func NewMySQLDB() (*sql.DB, error) {
    log.Println("coming NewMySQLDB ...")
    config, err := loadMySQLConfig()
    log.Println("coming NewMySQLDB config...", config.User, config.Password)
    if err != nil {
        log.Fatal(err)
        return nil, err
    }
    log.Println("coming NewMySQLDB config...", config)
    dbSource := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8", config.User, config.Password, config.Host, config.Port, config.DBName)
    log.Println("coming NewMySQLDB config dbSource is ...", dbSource)
    db, err := sql.Open("mysql", dbSource)
    if err != nil {
        log.Fatal(err)
        return nil, err
    }

    if err := db.Ping(); err != nil {
        log.Fatal(err)
        return nil, err
    }

    return db, nil
}

func loadMySQLConfig() (*MySQLConfig, error) {
    configFile, err := ioutil.ReadFile("config.json")
    if err != nil {
        log.Fatal("----------error-----------loadMySQLConfig err...", err)
        return nil, err
    }

    var config MySQLConfig
    err = json.Unmarshal(configFile, &config)
    log.Println("loadMySQLConfig Unmarshal err...", err)
    if err != nil {
        log.Fatal("---------error----------------loadMySQLConfig Unmarshal err...", config)
        return nil, err
    }

    return &config, nil
}

4.创建数据库实体类 user 以及项目所需实体类 ErrorResponse

　　user.go 的文件内容如下：

package models

type User struct {
    ID   uint   `json:"id"`
    Name string `json:"name"`
    Age  int    `json:"age"`
}

ErrorResponse.go 中的文件内容如下:

package models

type ErrorResponse struct {
    Message string `json:"message"`
}

5.编写user表中数据库交互的逻辑

package repositories

import (
    "database/sql"
    "go_test/models"
)

type UserRepository struct {
    DB *sql.DB
}

func (ur *UserRepository) CreateUser(user *models.User) error {
    query := "INSERT INTO users (name, age) VALUES (?, ?)"
    _, err := ur.DB.Exec(query, user.Name, user.Age)
    if err != nil {
        return err
    }
    return nil
}

func (ur *UserRepository) GetUserByID(id uint) (*models.User, error) {
    query := "SELECT id, name, age FROM users WHERE id = ?"
    row := ur.DB.QueryRow(query, id)

    user := new(models.User)
    err := row.Scan(&user.ID, &user.Name, &user.Age)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, nil // 用户不存在
        }
        return nil, err
    }

    return user, nil
}

func (ur *UserRepository) GetUsers() ([]*models.User, error) {
    query := "SELECT id, name, age FROM "
    rows, err := ur.DB.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    users := []*models.User{}
    for rows.Next() {
        user := new(models.User)
        err := rows.Scan(&user.ID, &user.Name, &user.Age)
        if err != nil {
            return nil, err
        }
        users = append(users, user)
    }

    return users, nil
}

func (ur *UserRepository) UpdateUser(user *models.User) error {
    query := "UPDATE users SET name = ?, age = ? WHERE id = ?"
    _, err := ur.DB.Exec(query, user.Name, user.Age, user.ID)
    if err != nil {
        return err
    }
    return nil
}

func (ur *UserRepository) DeleteUser(id uint) error {
    query := "DELETE FROM users WHERE id = ?"
    _, err := ur.DB.Exec(query, id)
    if err != nil {
        return err
    }
    return nil
}

6.编写处理业务层的service

package services

import (
    "errors"

    "go_test/models"
    "go_test/repositories"
)

type UserService struct {
    UserRepository *repositories.UserRepository
}

func (us *UserService) CreateUser(user *models.User) error {
    if user.Name == "" {
        return errors.New("Name is required")
    }
    if user.Age <= 0 {
        return errors.New("Age should be greater than 0")
    }
    // ... 其他基础业务校验

    return us.UserRepository.CreateUser(user)
}

func (us *UserService) GetUserByID(id uint) (*models.User, error) {
    return us.UserRepository.GetUserByID(id)
}

func (us *UserService) GetUsers() ([]*models.User, error) {
    return us.UserRepository.GetUsers()
}

func (us *UserService) UpdateUser(user *models.User) error {
    if user.Name == "" {
        return errors.New("Name is required")
    }
    if user.Age <= 0 {
        return errors.New("Age should be greater than 0")
    }
    // ... 其他基础业务校验

    return us.UserRepository.UpdateUser(user)
}

func (us *UserService) DeleteUser(id uint) error {
    return us.UserRepository.DeleteUser(id)
}

7.编写两个项目常用的util

　　error_handler.go

package utils

import (
    "encoding/json"
    "go_test/models"
    _ "log"
    "net/http"
)

//
//type ErrorResponse struct {
//    Message string `json:"message"`
//}

func HandleError(w http.ResponseWriter, statusCode int, message string) {
    errResponse := models.ErrorResponse{Message: message}
    response, _ := json.Marshal(errResponse)

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(statusCode)
    w.Write(response)
}

func ErrorHandlerString(w http.ResponseWriter, message string) {
    errResponse := models.ErrorResponse{Message: message}
    response, _ := json.Marshal(errResponse)

    w.Header().Set("Content-Type", "application/json")
    //w.WriteHeader(statusCode)
    w.Write(response)
}

func ErrorHandler(w http.ResponseWriter, err error) {
    if err != nil {
        w.WriteHeader(http.StatusInternalServerError)
        response := models.ErrorResponse{
            Message: err.Error(),
        }
        json.NewEncoder(w).Encode(response)
        return
    }
}

　　json_utils.go

package utils

import (
    "encoding/json"
    "net/http"
)

func RespondJSON(w http.ResponseWriter, data interface{}, statusCode int) {
    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(statusCode)
    json.NewEncoder(w).Encode(data)
}

8.编写核心类：main.go

package main

import (
    _ "database/sql"
    "encoding/json"
    "go_test/models"
    "go_test/utils"
    "log"
    "net/http"

    "go_test/database"
    "go_test/repositories"
    "go_test/services"
)

func main() {
    log.Println("coming main method ...")
    db, err := database.NewMySQLDB()
    if err != nil {
        log.Fatal(err)
    }

    userRepository := &repositories.UserRepository{
        DB: db,
    }
    userService := &services.UserService{
        UserRepository: userRepository,
    }

    http.HandleFunc("/users", func(w http.ResponseWriter, r *http.Request) {
        switch r.Method {
        case http.MethodGet:
            users, err := userService.GetUsers()
            if err != nil {
                utils.ErrorHandler(w, err)
                return
            }
            utils.RespondJSON(w, users, http.StatusOK)

        case http.MethodPost:
            var user models.User
            err := json.NewDecoder(r.Body).Decode(&user)
            if err != nil {
                utils.ErrorHandler(w, err)
                return
            }
            err = userService.CreateUser(&user)
            if err != nil {
                utils.ErrorHandler(w, err)
                return
            }
            utils.RespondJSON(w, user, http.StatusCreated)

        default:
            w.WriteHeader(http.StatusMethodNotAllowed)
            response := models.ErrorResponse{
                Message: "Method not allowed",
            }
            json.NewEncoder(w).Encode(response)
        }
    })

    log.Println("Server is running on port 8000")
    log.Fatal(http.ListenAndServe(":8000", nil))
}

9.在服务器上部署和验证

　　linux上传代码并进行编译项目

　　linux上启动项目

　　调用api：