如何利用golang运用mysql数据库

时间：2022-03-16 21:58 来源：未知作者：不及深情收藏

这篇文章主要介绍了如何利用golang运用mysql数据库，文章对依赖包、db对象注入ApiRouter等内容，需要的小伙伴可以参考一下

1.依赖包

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

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

如何利用golang运用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)/%sparseTime=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()
}

如何利用golang运用mysql数据库

数据库的一些设置

3.db对象注入ApiRouter

如何利用golang运用mysql数据库

需要用到数据库的模块需要传递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)

如何利用golang运用mysql数据库

6.架构分析图

如何利用golang运用mysql数据库

当逻辑比较简单可以直接略过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数据库内容请搜索源码搜藏网以前的文章或继续浏览下面的相关文章希望大家以后多多支持源码搜藏网！