如何利用golang运用mysql数据库

目录

• 1.依赖包
• 2.main.go
• 3.db对象注入ApiRouter
• 4.register层将db传给controller
• 5.controller层将db传给service或者mapper
• 6.架构分析图
• 7.mapper示例

1.依赖包

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

如果忘记导入mysql依赖包会打不开mysql

2.main.go

package main

import (
    _ "container_cloud/pkg/config"
    "container_cloud/pkg/utils/httputil"
    "container_cloud/routers"
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "net/http"
    "time"
)

func init() {
    httputil.InitHttpTool()
}

// mysql
const (
    USERNAME = "root"
    PASSWORD = "Admin123"
    NETWORK  = "tcp"
    // TODO  本地调试时放开
    /*SERVER   = "192.168.103.48"
    PORT     = 43306*/

    // TODO 部署到环境时放开
    SERVER   = "192.168.66.4"
    PORT     = 3306
    DATABASE = "container_cloud"
)

func main() {
    var err error
    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s?parseTime=1&multiStatements=1&charset=utf8mb4&collation=utf8mb4_unicode_ci", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("Open mysql failed,err:%v\n", err)
        return
    }
    //最大连接周期，超过时间的连接就close
    db.SetConnMaxLifetime(100 * time.Second)
    //设置最大连接数
    db.SetMaxOpenConns(100)
    //设置闲置连接数
    db.SetMaxIdleConns(16)

    defer db.Close()

    container := routers.InitApiRouter(db)
    server := &http.Server{Addr: ":8090", Handler: container}
    server.ListenAndServe()
}

数据库的一些设置

3.db对象注入ApiRouter

需要用到数据库的模块需要传递db对象

4.register层将db传给controller

package v1alpha1

import (
    "container_cloud/pkg/api"
    "container_cloud/pkg/apiserver/query"
    "container_cloud/pkg/apiserver/runtime"
    "container_cloud/pkg/controller"
    "container_cloud/pkg/domain"
    "database/sql"
    "github.com/emicklei/go-restful"
    "k8s.io/apimachinery/pkg/runtime/schema"
    "net/http"
)

const (
    GroupName = "order.ictnj.io"
    Version   = "v1alpha1"
)

var GroupVersion = schema.GroupVersion{Group: GroupName, Version: Version}

func AddToContainer(db *sql.DB) *restful.WebService{
    ws := runtime.NewWebService(GroupVersion)
    orderController := controller.NewOrderController(db)

    // 创建订单接口，pvc创建、负载创建的时候，是在特定命名空间下。（其实请求入参中也有命名空间字段，资源创建的时候也可以从入参中获取）
    ws.Route(ws.POST("/namespaces/{namespace}/orders").
        To(orderController.CreateOrder).
        Param(ws.PathParameter("namespace", "namespace name")).
        Returns(http.StatusOK, api.StatusOK, map[string]string{}).
        Doc("create order."))

    return ws
}

5.controller层将db传给service或者mapper

type orderController struct {
    Db *sql.DB
}

func NewOrderController(db *sql.DB) *orderController{
    return &orderController{Db: db}
}

// 再创建订单
    orderService := service.NewOrderService(o.Db)
    orderService.CreateOrder(order)
    result := map[string]string{"message": "success"}
    response.WriteEntity(result)

6.架构分析图

当逻辑比较简单可以直接略过service,controller直接调用mapper

7.mapper示例

package service

import (
    "container_cloud/pkg/api"
    "container_cloud/pkg/apiserver/query"
    "container_cloud/pkg/domain"
    "database/sql"
    "encoding/json"
    "fmt"
    "github.com/google/uuid"
    "k8s.io/klog"
    "strings"
    "time"
)

type OrderService struct {
    Db *sql.DB
}

func NewOrderService(db *sql.DB) *OrderService{
    return &OrderService{Db: db}

}
func (o *OrderService) CreateOrder(order domain.Order) {
    order.CreateTime = time.Now()
    var orderType uint8 = 1
    order.OrderType = &orderType
    uuid,_ := uuid.NewRandom()
    order.Id = strings.ReplaceAll(uuid.String(), "-", "")

    jsonbyte, _ := json.Marshal(order.OrderItem)
    order.OrderItemJson = string(jsonbyte)

    o.insertData(order)
}

func (o *OrderService) insertData(order domain.Order) {
    stmt, _ := o.Db.Prepare(`INSERT INTO t_order (id, username, service_type, order_type, status, reason, order_item, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`)
    defer stmt.Close()

    ret, err := stmt.Exec(order.Id, order.Username, order.ServiceType, order.OrderType, order.Status, order.Reason, order.OrderItemJson, order.CreateTime)
    if err != nil {
        fmt.Printf("insert data error: %v\n", err)
        return
    }
    if LastInsertId, err := ret.LastInsertId(); nil == err {
        fmt.Println("LastInsertId:", LastInsertId)
    }
    if RowsAffected, err := ret.RowsAffected(); nil == err {
        fmt.Println("RowsAffected:", RowsAffected)
    }
}

func (o *OrderService) ListOrders(query *query.Query, username string) (*api.ListResult, error){
    // 查询总数量
    totalRow, err := o.Db.Query("SELECT COUNT(*) FROM t_order WHERE username = ?", username)
    if err != nil {
        klog.Error("query orders count error", err)
        return nil, err
    }
    total := 0
    for totalRow.Next() {
        err := totalRow.Scan(
            &total,
        )
        if err != nil {
            klog.Error("query orders count error", err)
            continue
        }
    }
    totalRow.Close()

    // 查询订单列表
    rows, err := o.Db.Query("select * from t_order where username = ? order by create_time desc limit ? offset ? ", username, query.Pagination.Limit, query.Pagination.Offset)
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()
    if err != nil {
        klog.Error("query orders error", err)
        return nil, err
    }

    items := make([]interface{}, 0)
    for rows.Next() {
        order := new(domain.Order)
        err = rows.Scan(&order.Id, &order.Username, &order.ServiceType, &order.OrderType, &order.Status, &order.Reason, &order.OrderItemJson, &order.CreateTime)
        if err != nil {
            klog.Error("query orders error", err)
            return nil, err
        }
        order.OrderItemJson = ""
        items = append(items, *order)
    }

    return &api.ListResult{
        TotalItems: total,
        Items:      items,
    }, nil

}

func (o *OrderService) GetOrder(id string) (*domain.Order, error) {
    order := new(domain.Order)
    row := o.Db.QueryRow("select order_item from t_order where id = ?", id)
    if err := row.Scan(&order.OrderItemJson); err != nil {
        klog.Error(err)
        return nil, err
    }
    orderItems := &[]domain.OrderItem{}
    json.Unmarshal([]byte(order.OrderItemJson), orderItems)

    order.OrderItemJson = ""
    order.OrderItem = *orderItems
    return order, nil
}

func (o *OrderService) ListUserOrders(username string) (*[]domain.Order, error){
    // 查询订单列表
    rows, err := o.Db.Query("select * from t_order where username = ? order by create_time desc", username)
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()
    if err != nil {
        klog.Error("query orders error", err)
        return nil, err
    }
    items :=  make([]domain.Order,0)
    for rows.Next() {
        order := new(domain.Order)
        err = rows.Scan(&order.Id, &order.Username, &order.ServiceType, &order.OrderType, &order.Status, &order.Reason, &order.OrderItemJson, &order.CreateTime)
        if err != nil {
            klog.Error("query orders error", err)
            return nil, err
        }
        order.OrderItemJson = ""
        items = append(items, *order)
    }

    return &items,nil
}

到此这篇关于如何利用golang运用mysql数据库的文章就介绍到这了,更多相关golang运用mysql数据库内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！