go项目实现mysql接入以及web api
本文为博主原创，转载请注明出处：
创建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：
