本文为博主原创,转载请注明出处:
创建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:
