这篇文章主要介绍怎么用golang运用mysql数据库,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" )
如果忘记导入mysql依赖包会打不开mysql
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() }
数据库的一些设置
需要用到数据库的模块需要传递db对象
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 }
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)
当逻辑比较简单可以直接略过service,controller直接调用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数据库”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注亿速云行业资讯频道!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。