创建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：
