第四期 · 將部分資料存盤至Mysql，使用axios通過golang搭建的http服務器獲取資料，
新建資料庫
DROP DATABASE VUE;
create database if not exists vue;
use vue;
JSON TO MYSQL
JSON to MySQL (transform.tools)
DROP DATABASE VUE;
create database if not exists vue;
use vue;
CREATE TABLE gameblog (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
text VARCHAR(255),
img VARCHAR(255)
);
insert into gameblog(title,text,img) values
("Games of the Month: surrealist solitaire puzzles","What’s that? You need more games? I hear you, anonymous hapi fan.We’ve reached the part of the year when games start coming out fast","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_1.jpg"),
("Games of the Month: Puzzles!","Sometimes you need a good puzzle game, just something to throw all of your attention at and ignore anything else going on. Well if that sometime for you is right now, then you’re in luck because in this Games of the Month","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_2.jpg"),
("The next hapi Creator Day is July 29th!","I don’t think I’m allowed to make the entire body of this post “Thenext itch.io Creator Day is taking place on Friday July 29th.” I mean it’s true, we are hosting the next itch.io Creator Day on Friday July 29th but I should probably write more here.","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_3.jpg");
select * from gameblog;
CREATE TABLE game (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
text VARCHAR(255),
img VARCHAR(255),
price decimal(6,2) default 0,
web boolean default 0
# TODO 發布時間
# TODO 瀏覽量
# TODO 評論量
# TODO 熱度綜合指標
);
CREATE TABLE tag (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255)
);
CREATE TABLE gametag (
gameid INT,
tagid INT
);
# TODO 外鍵
insert into game(id,title,text,img,price,web) values
(1,"Late Night Mop","A haunted house cleaning simulator.","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_1.png",0,0),
(2,"an average day at the cat cafe","A haunted house cleaning simulator.","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_2.png",0,1),
(3,"Corebreaker","A fast-paced action-platform shooter game with roguelike elements.","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_3.png",19.99,0),
(4,"Atuel","Traverse a surrealist landscape inspired by the Atuel River in Argentina.","https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_5.png",0,0);
insert into tag values
(1,"Difficult"),
(2,"Fast-Paced");
insert into gametag values
(3,1),
(3,2),
(4,1);
DELIMITER $$
CREATE PROCEDURE gamelist()
BEGIN
# TODO
END $$
DELIMITER ;
select a.title,a.text,img,price,web,if(group_concat(c.title separator "#") is null ,"", group_concat(c.title separator "#")) as tag from game a left join gametag b on a.id = b.gameid left join tag c on b.tagid = c.id group by a.id;
本地圖片上傳OSS圖床得到靜態資源的持久地址，我使用的是PicGo圖床工具，
SQL TO GOLANG STRUCT
在線sql轉golang struct - 球兒工具 (qetool.com)
config.go
為了方便mysql服務器的配置，寫一個組態檔，
package mysql_vue
import "database/sql"
func GetMySQLDB() (db *sql.DB, err error) {
dbDriver := "mysql"
dbUser := "root"
dbPass := "sql2008"
dbName := "vue"
db, err = sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
return
}
gameblog.go
id暫時不需要，后期路由跳轉需要用到，可以先注釋，
package mysql_vue
import (
"encoding/json"
_ "github.com/go-sql-driver/mysql"
)
type Gameblog struct {
// ID int64 `db:"id" json:"id"`
Title string `db:"title" json:"title"`
Text string `db:"text" json:"text"`
Img string `db:"img" json:"img"`
}
func (Gameblog) TableName() string {
return "gameblog"
}
func (Gameblog) QueryGameblog() (json_ []byte, err error) {
// db, err := sql.Open("mysql", "root:sql2008@tcp(127.0.0.1:3306)/vue")
db, err := GetMySQLDB()
checkError(err)
defer db.Close()
// ^ 必須按照順序選取,下面的Scan需要一一對應,如果多了或少了欄位會導致Scan錯誤.
results, err := db.Query("SELECT title,text,img FROM gameblog order by id desc")
checkError(err)
var gameBlogs []Gameblog
for results.Next() {
var gameBlog Gameblog
err = results.Scan(&gameBlog.Title, &gameBlog.Text, &gameBlog.Img)
checkError(err)
gameBlogs = append(gameBlogs, gameBlog)
}
json_, err = json.Marshal(gameBlogs)
checkError(err)
return json_, nil
}
http
Simplify server.go
前面我們把評論相關的請求處理代碼寫在了 server.go，移出到 comment.go，并在init初始化中系結各個請求路徑處理函式，
comment.go
package server
import (
"fmt"
"net/http"
"strconv"
)
type Comment interface {
QueryComment(pid int64) (json_ []byte, err error)
InsertComment(uid, pid int64, text string) (json_ []byte, err error)
DeleteComment(id int64) error
}
func init() {
http.HandleFunc("/insertComment", insertComment)
http.HandleFunc("/deleteComment", deleteComment)
http.HandleFunc("/queryComment", queryComment)
}
func insertComment(w http.ResponseWriter, r *http.Request) {
....
}
func deleteComment(w http.ResponseWriter, r *http.Request) {
....
}
func queryComment(w http.ResponseWriter, r *http.Request) {
....
}
gameblog.go
介面用于確保某個資料庫物件實作了處理函式，否則編譯不通過，
package server
import (
"fmt"
"net/http"
)
type Gameblog interface {
QueryGameblog() (json_ []byte, err error)
}
func init() {
http.HandleFunc("/queryGameblog", QueryGameblog)
}
func QueryGameblog(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
fmt.Fprintf(w, "Only GET Method")
return
}
json, err := gameblog.QueryGameblog()
if err != nil {
fmt.Fprintf(w, "Error Delete")
return
}
fmt.Fprint(w, string(json))
}
server.go
package server
import (
"log"
"net/http"
mysql_vue "wolflong.com/vue_http/lib/mysql"
sq3_vue "wolflong.com/vue_http/lib/sqlite"
)
var comment Comment = sq3_vue.Comment{}
var gameblog Gameblog = mysql_vue.Gameblog{}
func StartServer() {
err := http.ListenAndServe(":1314", nil)
if err != nil {
log.Fatal("ListenAndServe: ", err)
}
}
postman test api
使用 postman 測驗當前介面，
Axios
修改 HomeView.vue 的選項卡api，在 created 鉤子函式添加axios請求訪問，
created() {
this.axios
.get("queryGameblog")
.then((response) => {
if (!response.data) {
this.gameBlog = [];
return;
}
this.gameBlog = response.data;
})
.catch((err) => {
console.log(err);
});
},
gamelist.go
查詢陳述句使用兩次左連接，并用 group_concat 聚合函式，聚合 tag，分解tag的程序可以從服務端遷移到客戶端進行降低性能消耗，
package mysql_vue
import (
"encoding/json"
"strings"
)
type Gamelist struct {
// ID int64 `db:"id" json:"id"`
Title string `db:"title" json:"title"`
Text string `db:"text" json:"text"`
Img string `db:"img" json:"img"`
Price float64 `db:"price" json:"price"`
Tag []string `db:"tag" json:"tag"` // 新添加
Web bool `db:"Web" json:"web"`
}
// type Tag struct {
// ID int64 `db:"id" json:"id"`
// Title string `db:"title" json:"title"`
// }
func (Gamelist) QueryGamelist() (json_ []byte, err error) {
db, err := GetMySQLDB()
checkError(err)
defer db.Close()
results, err := db.Query(`select a.title,a.text,img,price,web,if(group_concat(c.title separator "#") is null ,"", group_concat(c.title separator "#")) as tag from game a left join gametag b on a.id = b.gameid left join tag c on b.tagid = c.id group by a.id;`)
checkError(err)
var GameList []Gamelist
for results.Next() {
var g Gamelist
var tag string
err = results.Scan(&g.Title, &g.Text, &g.Img, &g.Price, &g.Web, &tag)
g.Tag = strings.Split(tag, "#") // 這里暫且由服務端完成分解
checkError(err)
GameList = append(GameList, g)
}
json_, err = json.Marshal(GameList)
checkError(err)
return json_, nil
}
HTTP
gamelist.go
package server
import (
"fmt"
"net/http"
)
type Gamelist interface {
QueryGamelist() (json_ []byte, err error)
}
func init() {
http.HandleFunc("/queryGamelist", QueryGamelist)
}
func QueryGamelist(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
fmt.Fprintf(w, "Only GET Method")
return
}
json, err := gamelist.QueryGamelist()
if err != nil {
fmt.Fprintf(w, "Error Delete")
return
}
fmt.Fprint(w, string(json))
}
server.go
添加陳述句 var gamelist Gamelist = mysql_vue.Gamelist{}
Axios
this.axios
.get("queryGamelist")
.then((response) => {
if (!response.data) {
this.latestGames.games = [];
this.mostFeatureGames.games = [];
return;
}
this.latestGames.games = response.data;
this.mostFeatureGames.games = response.data;
})
.catch((err) => {
console.log(err);
});
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/526028.html
標籤：其他
上一篇：html筆記2
下一篇：css3寫一個加載影片