第四期 · 将部分数据存储至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);

  });

最新文章

1. Android PopupWindow怎么合理控制弹出位置（showAtLocation）
2. HDU 5980 Find Small A（寻找小A）
3. IE8下获取iframe document EVENT对象的问题
4. SEO优化的黑帽手法是否值得使用?
5. hdu 3478（判断奇环）
6. PHP中的多态
7. [AngularJS] Html ngSanitize, $sce
8. 自动化运维——一键安装MySQL
9. Clang 与 LLVM
10. JavaScript实现网页右下角弹出窗口代码
11. SSH 5W学习
12. IE6/IE7浏览器中"float: right"自动换行的解决方法
13. 利刃 MVVMLight 10：Messenger 深入
14. Java实现网页抓取的一个Demo
15. 二进制中连续k个1-题解
16. JavaScript基础视频教程总结（121-130章）
17. JWT(Json Web Token—)的定义及组成
18. 给msde加装企业管理器
19. 关于jQuery Form Plugin使用心得
20. 【转】: 塞尔达组在GDC2017演讲的文字翻译：显示的力量

热门文章

1. HandInDevil 的头发 （分 块）
2. MyBatis快速上手与知识点总结
3. KingbaseFlySync 无主键过滤器custompkey配置
4. Bypass Windows Defender Dump Lsass（手法拙劣）
5. 《吐血整理》进阶系列教程-拿捏Fiddler抓包教程(18)-Fiddler如何接口测试，妈妈再也不担心我不会接口测试了
6. Dart 2.18 正式发布
7. 使用Pipeline抽象业务生命周期流程
8. MySQL数据库中配置文件 read_only 参数的有关说明
9. 100个Linux Shell脚本经典案例（附PDF）
10. Elasticsearch：跨集群搜索 Cross-cluster search（CCS）及安全