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

技术分享 3年前 (2023-08-12) 0 999+

关注

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

　　创建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)) }