1.依赖包
|
1 2 3 4 5 |
import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" ) |
如果忘记导入mysql依赖包会打不开mysql
2.main.go
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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示例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
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 } |
