读取数据库数据
1. 初始化数据库
package dao
import (
"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:root@tcp(localhost: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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2. 首页真实数据
2.1 分类查询
package dao
import (
"log"
"ms-go-blog/models"
)
func GetAllCategory() ([]models.Category,error){
rows ,err := DB.Query("select * from blog_category")
if err != nil {
log.Println("GetAllCategory 查询出错:",err)
return nil, err
}
var categorys []models.Category
for rows.Next() {
var category models.Category
err = rows.Scan(&category.Cid,&category.Name,&category.CreateAt,&category.UpdateAt)
if err != nil {
log.Println("GetAllCategory 取值出错:",err)
return nil, err
}
categorys = append(categorys,category)
}
return categorys,nil
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package service
import (
"ms-go-blog/config"
"ms-go-blog/dao"
"ms-go-blog/models"
)
func GetAllIndexInfo() (*models.HomeResponse,error){
categorys,err := dao.GetAllCategory()
if err != nil {
return nil, err
}
var posts = []models.PostMore{
{
Pid: 1,
Title: "go博客",
Content: "内容",
UserName: "码神",
ViewCount: 123,
CreateAt: "2022-02-20",
CategoryId:1,
CategoryName: "go",
Type:0,
},
}
var hr = &models.HomeResponse{
config.Cfg.Viewer,
categorys,
posts,
1,
1,
[]int{1},
true,
}
return hr,nil
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package views
import (
"errors"
"log"
"ms-go-blog/common"
"ms-go-blog/service"
"net/http"
)
func (*HTMLApi) Index(w http.ResponseWriter,r *http.Request) {
index := common.Template.Index
//页面上涉及到的所有的数据，必须有定义
//数据库查询
hr,err := service.GetAllIndexInfo()
if err != nil {
log.Println("Index获取数据出错：",err)
index.WriteError(w,errors.New("系统错误，请联系管理员!!"))
}
index.WriteData(w,hr)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2.2 文章查询
package dao
import "ms-go-blog/models"
func CountGetAllPost() (count int) {
rows := DB.QueryRow("select count(1) from blog_post")
_ = rows.Scan(&count)
return
}
func GetPostPage(page,pageSize int) ([]models.Post,error) {
page = (page-1) * pageSize
rows, err := DB.Query("select * from blog_post limit ?,?",page,pageSize)
if err != nil {
return nil,err
}
var posts []models.Post
for rows.Next() {
var post models.Post
err := 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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package service
import (
"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.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)
}
//11 10 2 10 1 9 1 21 3
// (11-1)/10 + 1 = 2
total := dao.CountGetAllPost()
pagesCount := (total-1)/10 + 1
var pages []int
for i := 0;i<pagesCount;i++ {
pages = append(pages,i+1)
}
var hr = &models.HomeResponse{
config.Cfg.Viewer,
categorys,
postMores,
total,
page,
pages,
page != pagesCount,
}
return hr,nil
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
3. 分类文章列表
package views
import (
"errors"
"log"
"ms-go-blog/common"
"ms-go-blog/service"
"net/http"
"strconv"
"strings"
)
func (*HTMLApi) Category(w http.ResponseWriter,r *http.Request) {
categoryTemplate := common.Template.Category
//http://localhost:8080/c/1 1参数 分类的id
path := r.URL.Path
cIdStr := 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 := 10
categoryResponse,err := service.GetPostsByCategoryId(cId,page,pageSize);
if err != nil {
categoryTemplate.WriteError(w,err)
return
}
categoryTemplate.WriteData(w,categoryResponse)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package router
import (
"ms-go-blog/api"
"ms-go-blog/views"
"net/http"
)
func Router() {
//1. 页面 views 2. api 数据（json） 3. 静态资源
http.HandleFunc("/",views.HTML.Index)
//http://localhost:8080/c/1 1参数 分类的id
http.HandleFunc("/c/",views.HTML.Category)
http.HandleFunc("/api/v1/post",api.API.SaveAndUpdatePost)
http.Handle("/resource/",http.StripPrefix("/resource/",http.FileServer(http.Dir("public/resource/"))))
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package dao
import "ms-go-blog/models"
func CountGetAllPostByCategoryId(cId int) (count int) {
rows := DB.QueryRow("select count(1) from blog_post where category_id=?",cId)
_ = rows.Scan(&count)
return
}
func CountGetAllPost() (count int) {
rows := DB.QueryRow("select count(1) from blog_post")
_ = rows.Scan(&count)
return
}
func GetPostPage(page,pageSize int) ([]models.Post,error) {
page = (page-1) * pageSize
rows, err := DB.Query("select * from blog_post limit ?,?",page,pageSize)
if err != nil {
return nil,err
}
var posts []models.Post
for rows.Next() {
var post models.Post
err := 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
}
func GetPostPageByCategoryId(cId,page,pageSize int) ([]models.Post,error) {
page = (page-1) * pageSize
rows, err := DB.Query("select * from blog_post where category_id = ? limit ?,?",cId,page,pageSize)
if err != nil {
return nil,err
}
var posts []models.Post
for rows.Next() {
var post models.Post
err := 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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74