【Go学习实战】03-2-博客查询及登录
- 读取数据库数据
- 初始化数据库
- 首页真实数据
- 分类查询
- 分类查询测试
- 文章查询
- 文章查询测试
- 分类文章列表
- 测试
- 登录功能
- 登录页面
- 登录接口
- 获取json参数
- 登录失败测试
- md5加密
- jwt工具
- 登录成功测试
- 文章详情
- 测试
读取数据库数据
因为我们之前的数据都是假数据,但是真实的场景都是真数据,需要从数据库查询出来的,所以我们需要进行数据库的操作。
初始化数据库
导入sql文件,创建我们的数据库
之后配置我们的mysql,创建dao/mysql.go,注意这里配置自己的用户名密码及ip和端口
package daoimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql""log""net/url""time"
)var DB *sql.DB
func init() {//执行main之前 先执行init方法dataSourceName := fmt.Sprintf("root:mysql@tcp(192.168.101.68:3306)/goblog?charset=utf8&loc=%s&parseTime=true",url.QueryEscape("Asia/Shanghai"))db, err := sql.Open("mysql", dataSourceName)if err != nil {log.Println("连接数据库异常")panic(err)}//最大空闲连接数,默认不配置,是2个最大空闲连接db.SetMaxIdleConns(5)//最大连接数,默认不配置,是不限制最大连接数db.SetMaxOpenConns(100)// 连接最大存活时间db.SetConnMaxLifetime(time.Minute * 3)//空闲连接最大存活时间db.SetConnMaxIdleTime(time.Minute * 1)err = db.Ping()if err != nil {log.Println("数据库无法连接")_ = db.Close()panic(err)}DB = db
}
首页真实数据
为了符合现在主流的MVC的架构,我们也是三层架构,dao层负责与数据库打交道,service层负责业务的具体逻辑
创建service文件夹
分类查询
创建分类查询的dao层,创建dao/category.go,负责分类查询的项目
package daoimport ("log""myWeb/models"
)func GetAllGategory() ([]models.Category, error) {row, err := DB.Query("select * from category")if err != nil {log.Println("查询分类异常")return nil, err}defer row.Close()var categorys []models.Categoryfor row.Next() {var category models.Categoryerr := row.Scan(&category.Cid, &category.Name, &category.CreateAt, &category.UpdateAt)if err != nil {log.Println("查询分类异常")return nil, err}categorys = append(categorys, category)}return categorys, nil
}
这样查询分类的时候就可以直接通过dao层来获取分类类别
categorys, err := dao.GetAllGategory()
在模板中添加一个统一的报错处理WriteError
func (t *TemplateBlog) WriteError(w io.Writer, err error) {if err != nil {log.Println(err)_, err := w.Write([]byte(err.Error()))if err != nil {log.Println(err)return}}
}func (t *TemplateBlog) WriteData(w io.Writer, data interface{}) {err := t.Execute(w, data)if err != nil {t.WriteError(w, err)}
}
其他层调用service查询index页面则也可以调用service层
func (api *HTMLApi) Index(w http.ResponseWriter, r *http.Request) {index := common.Template.Indexhr, err := service.GetAllIndexInfo()if err != nil {log.Printf("查询Index信息异常:%v", err)index.WriteError(w, errors.New("查询Index信息异常,请联系管理员"))}index.WriteData(w, hr)
}
分类查询测试
重启后发现我们的左下角多了个分类,说明我们写的没有问题
文章查询
因为文章可能有很多,所以我们要先分析下表单,然后拿到我们的分页参数
func (*HTMLApi) Index(w http.ResponseWriter, r *http.Request) {index := common.Template.Indexerr := r.ParseForm()if err != nil {log.Printf("解析请求参数异常:%v", err)index.WriteError(w, errors.New("解析请求参数异常,请联系管理员"))return}//获取分页信息pageStr := r.Form.Get("page")page := 1if pageStr != "" {page, _ = strconv.Atoi(pageStr)}//获取每页显示的条数limitStr := r.Form.Get("limit")limit := 10if limitStr != "" {limit, _ = strconv.Atoi(limitStr)}hr, err := service.GetAllIndexInfo(page, limit)if err != nil {log.Printf("查询Index信息异常:%v", err)index.WriteError(w, errors.New("查询Index信息异常,请联系管理员"))}index.WriteData(w, hr)
}
这样获取到分页信息就可以传入到GetAllIndexInfo进行分页查询了
创建dao/article.go
package daoimport "myWeb/models"func GetPostArticlePage(page, limit int) ([]models.Post, error) {row, err := DB.Query("select * from blog_post limit ?,?", (page-1)*limit, limit)if err != nil {return nil, err}defer row.Close()var posts []models.Postfor row.Next() {var post models.Posterr := row.Scan(&post.Pid, &post.Title, &post.Content, &post.Markdown, &post.CategoryId, &post.UserId, &post.ViewCount, &post.Type, &post.Slug, &post.CreateAt, &post.UpdateAt)if err != nil {return nil, err}posts = append(posts, post)}return posts, nil
}
因为我们返回的是post类型,而要求返回的是postMore类型,包含用户id等等,所以我们在service层还需要组装一下postMore
中间因为还要查询用户名称及分类名称所以我们先在dao层补全
创建dao/user.go#GetUserNameById
func GetUserNameById(uid int) string {var name stringerr := DB.QueryRow("SELECT user_name FROM blog_user WHERE uid = ?", uid).Scan(&name)if err != nil {if err == sql.ErrNoRows {log.Printf("未找到用户ID %d 的用户名", uid)return ""}log.Printf("查询用户名异常:%v", err)return ""}return name
}
在dao/category.go创建
func GetCategoryNameById(cid int) string {var name stringerr := DB.QueryRow("SELECT name FROM blog_category WHERE cid = ?", cid).Scan(&name)if err != nil {if err == sql.ErrNoRows {log.Printf("未找到分类 ID %d 的名称", cid)return ""}log.Printf("查询分类名称异常:%v", err)return ""}return name
}
组装PostMore
var postMores []models.PostMore
for _, post := range posts {categoryName := dao.GetCategoryNameById(post.CategoryId)userName := dao.GetUserNameById(post.UserId)content := []rune(post.Content)if len(content) > 100 {content = content[0:100]}postMore := models.PostMore{post.Pid,post.Title,post.Slug,template.HTML(content),post.CategoryId,categoryName,post.UserId,userName,post.ViewCount,post.Type,models.DateDay(post.CreateAt),models.DateDay(post.UpdateAt),}postMores = append(postMores, postMore)
}
因为最后返回的hr中还有总数及是否为当前页,因此这些也要进行准备
获取文章总页数
func CountGetAllPost() int {var count interr := DB.QueryRow("SELECT COUNT(1) FROM blog_post").Scan(&count)if err != nil {log.Printf("查询文章总数失败: %v", err)return 0}return count
}
调用获取文章总数及分页相关
total := dao.CountGetAllPost()
pagesCount := (total-1)/10 + 1
var pages []int
for i := 0; i < pagesCount; i++ {pages = append(pages, i+1)
}
package serviceimport ("html/template""ms-go-blog/config""ms-go-blog/dao""ms-go-blog/models"
)func GetAllIndexInfo(page,pageSize int) (*models.HomeResponse,error){categorys,err := dao.GetAllCategory()if err != nil {return nil, err}posts,err := dao.GetPostPage(page,pageSize)var postMores []models.PostMorefor _,post := range posts{categoryName := dao.GetCategoryNameById(post.CategoryId)userName := dao.GetUserNameById(post.UserId)content := []rune(post.Content)if len(content) > 100 {content = content[0:100]}postMore := models.PostMore{post.Pid,post.Title,post.Slug,template.HTML(content),post.CategoryId,categoryName,post.UserId,userName,post.ViewCount,post.Type,models.DateDay(post.CreateAt),models.DateDay(post.UpdateAt),}postMores = append(postMores,postMore)}//11 10 2 10 1 9 1 21 3// (11-1)/10 + 1 = 2total := dao.CountGetAllPost()pagesCount := (total-1)/limit + 1var pages []intfor i := 0; i < pagesCount; i++ {pages = append(pages, i+1)}var hr = &models.HomeResponse{config.Cfg.Viewer,categorys,postMores, //文章total, //文章总数page, //当前页pages, //页码,两页就是[]int{1,2}page != pagesCount, //是否有下一页}return hr,nil
}
文章查询测试
分类文章列表
因为我们请求分类文章列表的url路径是http://localhost:8080/c/1,所以我们也要对其进行相对应的路由,1是参数,代表分类的id,就需要把这个id取出来
在router.go中,我们用Category页面来匹配对应的逻辑和/c/路径
http.HandleFunc("/c/", views.HTML.Category)
我们所有的页面和逻辑都在views中,所以创建views/category.go
package viewsimport ("errors""log""myWeb/common""myWeb/service""net/http""strconv""strings"
)func (*HTMLApi) Category(w http.ResponseWriter, r *http.Request) {categoryTemplate := common.Template.Category//http://localhost:8080/c/1 1参数 分类的idpath := r.URL.PathcIdStr := strings.TrimPrefix(path, "/c/")cId, err := strconv.Atoi(cIdStr)if err != nil {categoryTemplate.WriteError(w, errors.New("不识别此请求路径"))return}if err := r.ParseForm(); err != nil {log.Println("表单获取失败:", err)categoryTemplate.WriteError(w, errors.New("系统错误,请联系管理员!!"))return}pageStr := r.Form.Get("page")if pageStr == "" {pageStr = "1"}page, _ := strconv.Atoi(pageStr)//每页显示的数量pageSize := 10categoryResponse, err := service.GetPostsByCategoryId(cId, page, pageSize)if err != nil {categoryTemplate.WriteError(w, err)return}categoryTemplate.WriteData(w, categoryResponse)
}
在对应的接口也要添加上方法
type HTMLRenderer interface {Index(w http.ResponseWriter, r *http.Request)Category(w http.ResponseWriter, r *http.Request)
}
观察我们的category.html,相比于index.html多了个{{.CategoryName}}
,因此我们的model也得相应的多一个
type CategoryResponse struct {*HomeResponseCategoryName string
}
业务service层也要加上对应的逻辑,创建service/category.go,返回值自然是我们刚刚创建的那个类型
package serviceimport ("log""myWeb/dao""myWeb/models"
)func GetPostsByCategoryId(cId, page, pageSize int) ([]models.Post, error) {posts, err := dao.GetPostPageByCategoryId(cId, page, pageSize)if err != nil {log.Printf("查询分类ID %d 文章失败: %v", cId, err)return nil, err}return posts, nil
}
查询分类名称逻辑
func GetCategoryNameById(cid int) string {var name stringerr := DB.QueryRow("SELECT name FROM blog_category WHERE cid = ?", cid).Scan(&name)if err != nil {if err == sql.ErrNoRows {log.Printf("未找到分类 ID %d 的名称", cid)return ""}log.Printf("查询分类名称异常:%v", err)return ""}return name
}
我们的数据也应该按照分类id进行查询
func CountGetAllPostByCategoryId(cId int) (count int) {err := DB.QueryRow("SELECT COUNT(1) FROM blog_post WHERE category_id = ?", cId).Scan(&count)if err != nil {log.Printf("查询文章总数失败: %v", err)return 0}return count
}func GetPostPageByCategoryId(cId, page, pageSize int) ([]models.Post, error) {page = (page - 1) * pageSizerows, err := DB.Query("select * from blog_post where category_id = ? limit ?,?", cId, page, pageSize)if err != nil {return nil, err}var posts []models.Postfor rows.Next() {var post models.Posterr := rows.Scan(&post.Pid,&post.Title,&post.Content,&post.Markdown,&post.CategoryId,&post.UserId,&post.ViewCount,&post.Type,&post.Slug,&post.CreateAt,&post.UpdateAt,)if err != nil {return nil, err}posts = append(posts, post)}return posts, nil
}
接下来就是组装数据了,HomeResponse和index中类似,我们只需要组装上CategoryName就可以了
package serviceimport ("html/template""log""myWeb/config""myWeb/dao""myWeb/models"
)func GetPostsByCategoryId(cId, page, pageSize int) (*models.CategoryResponse, error) {categorys, err := dao.GetAllCategory()if err != nil {log.Println("查询分类异常")return nil, err}posts, err := dao.GetPostPageByCategoryId(cId, page, pageSize)if err != nil {log.Println("查询文章异常")return nil, err}var postMores []models.PostMorefor _, post := range posts {categoryName := dao.GetCategoryNameById(post.CategoryId)userName := dao.GetUserNameById(post.UserId)content := []rune(post.Content)if len(content) > 100 {content = content[0:100]}postMore := models.PostMore{post.Pid,post.Title,post.Slug,template.HTML(content),post.CategoryId,categoryName,post.UserId,userName,post.ViewCount,post.Type,models.DateDay(post.CreateAt),models.DateDay(post.UpdateAt),}postMores = append(postMores, postMore)}total := dao.CountGetAllPostByCategoryId(cId)pagesCount := (total-1)/pageSize + 1var pages []intfor i := 0; i < pagesCount; i++ {pages = append(pages, i+1)}var hr = &models.HomeResponse{config.Cfg.Viewer,categorys,postMores, //文章total, //文章总数page, //当前页pages, //页码,两页就是[]int{1,2}page != pagesCount, //是否有下一页}categoryName := dao.GetCategoryNameById(cId)var categoryResponse = &models.CategoryResponse{hr,categoryName,}return categoryResponse, nil}
测试
重启页面
go分类
java分类
总条数也是正确的。
登录功能
当我们点击登录按钮,请求路径为http://localhost:8080/login,那么我们就要对这个路径进行路由映射
用户登录后,可以进行文章的编写,修改,以及删除
自然在router.go中进行路由
http.HandleFunc("/login/", views.HTML.Login)
登录页面
创建views/login.go,完善views的接口,我们login中需要的信息就是config中配的viewer的信息
package viewsimport ("myWeb/common""myWeb/config""net/http"
)func (*HTMLApi) Login(w http.ResponseWriter, r *http.Request) {login := common.Template.Loginlogin.WriteData(w, config.Cfg.Viewer)
}
type HTMLRenderer interface {Index(w http.ResponseWriter, r *http.Request)Category(w http.ResponseWriter, r *http.Request)Login(w http.ResponseWriter, r *http.Request)
}
这样我们的登录页面就做好了,点击登录发起的请求为http://localhost:8080/api/v1/login,发起的是POST请求,有两个参数,一个是passwd,一个是username
查看js中的返回逻辑
$(".login-submint").click(function () {var tipEle = $(".login-tip");var name = $(".login-name").val();var passwd = $(".login-passwd").val();if (!name) return tipEle.show().text("请输入用户名");if (!passwd) return tipEle.show().text("请输入密码");// md5加密var MD5Passwd = new Hashes.MD5().hex(passwd + SALT);$.ajax({url: "/api/v1/login",data: JSON.stringify({ username: name, passwd: MD5Passwd }),contentType: "application/json",type: "POST",success: function (res) {if (res.code !== 200) {return tipEle.show().text(res.error);}var data = res.data || {};localStorage.setItem(TOKEN_KEY, data.token);localStorage.setItem(USER_KEY, JSON.stringify(data.userInfo));location.href = "/";},error: function (err) {console.log("err", err);tipEle.show().text("登录错误,请重试");},});
});
如果返回是200,就会把token和用户信息保存在localStorage中,并且用location.href = "/";
跳转到首页
登录接口
点击登录发起的请求为http://localhost:8080/api/v1/login,发起的是POST请求,有两个参数,一个是passwd,一个是username,我们自然也要进行路由
http.HandleFunc("/api/v1/login", api.API.Login)
因为是要返回请求,不再是页面了,所以我们用api返回,创建api/login.go
package apiimport "net/http"func (*Api) Login(w http.ResponseWriter, r *http.Request) {}
完善api接口
type APIResponder interface {SaveAndUpdatePost(w http.ResponseWriter, r *http.Request)Login(w http.ResponseWriter, r *http.Request)
}
因为我们一般返回值有三个,code、date、err,所以我们在model中也要创建对应的基本返回值,创建models/result.go
package modelstype Result struct {Code int `json:"code"`Data interface{} `json:"data"`Error string `json:"error"`
}
那么我们login返回的时候返回的是result类型,我们所有api返回的时候都会这么组装数据,所以就可以把这样的操作放在common中
成功返回
func SuccessResult(w http.ResponseWriter, data interface{}) {var result models.Resultresult.Code = 200result.Error = ""result.Data = dataresultJson, _ := json.Marshal(result)w.Header().Set("Content-Type", "application/json")_, err := w.Write(resultJson)if err != nil {log.Printf("返回数据失败:%v", err)return}
}
那么在api/login.go中,我们只需要调用SuccessResult并且返回data就可以,那么怎么获取data呢,我们就要从POST请求中找到我们的两个参数,一个是passwd,一个是username,但是因为是POST请求,不能像GET一样直接从url中取
获取json参数
因为是一个公共方法,我们写在common中
func GetRequestJsonParam(r *http.Request) (map[string]interface{}, error) {var params map[string]interface{}// 使用 json.NewDecoder 来逐步解码请求体decoder := json.NewDecoder(r.Body)err := decoder.Decode(¶ms)if err != nil {log.Printf("解析请求参数失败:%v", err)return nil, err}return params, nil
}
自然解析的时候就会解析出来json参数,我们注意到最后的data有两部分组成,一个是token,一个是userInfo,我们也要封装一下到model中
先是userInfo
package modelsimport "time"type User struct {Uid int `json:"uid"`Username string `json:"userName"`Password string `json:"passwd"`Avatar string `json:"avatar"`CreatAt time.Time `json:"creatAt"`UpdateAt time.Time `json:"updateAt"`
}type UserInfo struct {Uid int `json:"uid"`Username string `json:"userName"`Avatar string `json:"avatar"`
}
再是LoginRes
type LoginRes struct {Token string `json:"token"`UserInfo UserInfo `json:"userInfo"`
}
我们在service进行实现,实现事前我们需要对用户名和密码做匹配所以先查数据库
func GetUser(userName string, passwd string) models.User {var user models.Usererr := DB.QueryRow("SELECT uid, user_name, passwd, avatar, creat_at, update_at FROM blog_user WHERE user_name = ? AND passwd = ?", userName, passwd).Scan(&user.Uid, &user.Username, &user.Password, &user.Avatar, &user.CreatAt, &user.UpdateAt)if err != nil {if err == sql.ErrNoRows {log.Printf("未找到用户 %s", userName)return models.User{}}log.Printf("查询用户异常:%v", err)return models.User{}}return user
}
这样调用的时候
func (*Api) Login(w http.ResponseWriter, r *http.Request) {params, err := common.GetRequestJsonParam(r)if err != nil {log.Printf("解析请求参数异常:%v", err)return}userName := params["username"].(string)passwd := params["passwd"].(string)data,err := service.Login(userName, passwd)if err != nil {log.Printf("登录异常:%v", err)common.ErrorResult(w, err)return}common.SuccessResult(w, data)
}
common.ErrorResult返回
func ErrorResult(w http.ResponseWriter, err error) {var result models.Resultresult.Code = 500result.Error = err.Error()result.Data = nilresultJson, _ := json.Marshal(result)w.Header().Set("Content-Type", "application/json")_, err = w.Write(resultJson)if err != nil {log.Printf("返回数据失败:%v", err)return}
}
登录失败测试
符合我们的预期
md5加密
我们对密码进行加密后进行比对,这些放在工具类utils中
package utilsimport ("crypto/md5""fmt""strings"
)//给字符串生成md5
//@params str 需要加密的字符串
//@params salt interface{} 加密的盐
//@return str 返回md5码
func Md5Crypt(str string, salt ...interface{}) (CryptStr string) {if l := len(salt); l > 0 {slice := make([]string, l+1)str = fmt.Sprintf(str+strings.Join(slice, "%v"), salt...)}return fmt.Sprintf("%x", md5.Sum([]byte(str)))
}
这样我们调用的时候再加一次盐,这样更加安全
func Login(userName, passwd string) (*models.LoginRes, error) {passwd = utils.Md5Crypt(passwd, "mszlu")user := dao.GetUser(userName, passwd)if user == nil {return nil, errors.New("用户名或密码错误")}var lr = &models.LoginRes{}return lr, nil
}
我们最后返回还有个token,这个是jwt令牌里的所以我们也要用jwt令牌
jwt_925">jwt工具
package utilsimport (gojwt "github.com/dgrijalva/jwt-go""os""time"
)var jwtKey []bytefunc init() {jwtKey = []byte(os.Getenv("JWT_SECRET"))
}type Claims struct {Uid intgojwt.StandardClaims
}// 生成Token
func Award(uid *int) (string, error) {// 过期时间 默认7天expireTime := time.Now().Add(7 * 24 * time.Hour)claims := &Claims{Uid: *uid,StandardClaims: gojwt.StandardClaims{ExpiresAt: expireTime.Unix(),IssuedAt: time.Now().Unix(),},}// 生成tokentoken := gojwt.NewWithClaims(gojwt.SigningMethodHS256, claims)tokenStr, err := token.SignedString(jwtKey)if err != nil {return "", err}return tokenStr, nil
}// 解析token
func ParseToken(tokenStr string) (*gojwt.Token, *Claims, error) {claims := &Claims{}token, err := gojwt.ParseWithClaims(tokenStr, claims, func(t *gojwt.Token) (interface{}, error) {return jwtKey, nil})if err != nil {return nil, nil, err}return token, claims, err
}
登录成功测试
我们可以看到我们的token由三部分构成,头部、载荷、签名
文章详情
我们随便点击一个文章,请求路径为http://localhost:8080/p/7.html,那么我们就要对这个路径进行路由映射
与获取分类文章列表类似,这里就不赘述了
views/detail.go
package viewsimport ("errors""myWeb/common""myWeb/service""net/http""strconv""strings"
)func (*HTMLApi) Detail(w http.ResponseWriter, r *http.Request) {detail := common.Template.Detail//http://localhost:8080/p/7.html 7参数 文章的idpath := r.URL.PathpIdStr := strings.TrimPrefix(path, "/p/")//7.htmlpIdStr = strings.TrimSuffix(pIdStr, ".html")pid, err := strconv.Atoi(pIdStr)if err != nil {detail.WriteError(w, errors.New("不识别此请求路径"))return}postRes, err := service.GetPostDetail(pid)if err != nil {detail.WriteError(w, errors.New("查询出错"))return}detail.WriteData(w, postRes)
}
service/detail.go
func GetPostDetail(pid int) (*models.PostRes, error) {post, err := dao.GetPostById(pid)if err != nil {return nil, err}categoryName := dao.GetCategoryNameById(post.CategoryId)userName := dao.GetUserNameById(post.UserId)postMore := models.PostMore{post.Pid,post.Title,post.Slug,template.HTML(post.Content),post.CategoryId,categoryName,post.UserId,userName,post.ViewCount,post.Type,models.DateDay(post.CreateAt),models.DateDay(post.UpdateAt),}var postRes = &models.PostRes{config.Cfg.Viewer,config.Cfg.System,postMore,}return postRes, nil
}
dao层查询
func GetPostById(pid int) (*models.Post, error) {row := DB.QueryRow("select * from blog_post where pid = ?", pid)var post models.Posterr := row.Scan(&post.Pid, &post.Title, &post.Content, &post.Markdown, &post.CategoryId, &post.UserId, &post.ViewCount, &post.Type, &post.Slug, &post.CreateAt, &post.UpdateAt)if err != nil {return nil, err}return &post, nil
}
测试
成功