如何利用golang运用mysql数据库
时间：2023-02-24 09:16:00|栏目：Golang|点击： 次
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
}
