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
}
到此这篇关于如何利用golang运用mysql数据库的文章就介绍到这了,更多相关golang运用mysql数据库内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！