怎么用golang运用mysql数据库

怎么用golang运用mysql数据库

1.依赖包

import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql")登录后复制

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

2.main.go

package mainimport ( _ "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()}// mysqlconst ( 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 v1alpha1import ( "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 serviceimport ( "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}登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。