Go语言使用sqlx查询多条数据并返回给前端

假设现在有一个需求，在某网站上，用户访问某个社区链接，就可以得到该社区的全部内容，用Go语言后端实现如下（使用Gin框架）：

首先先创建一个社区表并添加一些默认的内容

create database mydb;
use mydb;
CREATE TABLE `community` (
                             `id` int(11) primary key  NOT NULL AUTO_INCREMENT,
                             `community_id` int(10) unsigned NOT NULL,
                             `community_name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
                             `introduction` varchar(256) COLLATE utf8mb4_general_ci NOT NULL,
                             `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                             UNIQUE KEY `idx_community_id` (`community_id`),
                             UNIQUE key `idx_community_name` (`community_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO `community` VALUES ('1', '1', '科幻', '带你走进科幻的世界', '2021-7-31 08:00:00', '2023-3-31 08:30:00');
INSERT INTO `community` VALUES ('2', '2', '游戏', '上百款超值游戏任你选', '2022-1-23 08:30:00', '2023-3-31 08:30:00');
INSERT INTO `community` VALUES ('3', '3', '综艺', '让你开心是我们的职责', '2022-2-03 07:00:00', '2023-3-31 08:30:00');
INSERT INTO `community` VALUES ('4', '4', '学习', '保持学习，终生成长', '2023-1-21 14:37:00', '2023-3-31 08:30:00');

在这里我们默认在数据库mydb中创建了一个叫community的表格，表格里的数据如下：

接着就可以编写Go语言代码进行查询了，首先来看一下项目的结构

这里一共有5个.go文件,内容分别如下：

main.go

package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"myProject/mysql"
)

func main() {
	if err := mysql.Init(); err != nil {
		fmt.Printf("init mysql failed, err:%v\n", err)
		return
	}
	defer mysql.Close()
	r := gin.Default()
	r.GET("/community", CommunityHandler)
	r.Run(":8080")

}

func CommunityHandler(c *gin.Context) {
	data, _ := mysql.SelectCommunity()
	ResponseSuccess(c, data)
}

response.go

package main

import (
	"github.com/gin-gonic/gin"
	"net/http"
)

type ResCode int64

const CodeSuccess ResCode = 100

var codeMsgMap = map[ResCode]string{
	CodeSuccess: "success",
}

type ResponseData struct {
	Code ResCode     `json:"code"`
	Msg  interface{} `json:"msg"`
	Data interface{} `json:"data,omitempty"`
}

func (rc ResCode) Msg() string {
	msg, _ := codeMsgMap[rc]

	return msg
}

func ResponseSuccess(c *gin.Context, data interface{}) {
	c.JSON(http.StatusOK, &ResponseData{
		Code: CodeSuccess,
		Msg:  CodeSuccess.Msg(),
		Data: data,
	})
}

mysql.go（注意：这里的数据库配置要改成自己的）

package mysql

import (
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"github.com/spf13/viper"
	"go.uber.org/zap"
)

var db *sqlx.DB

func Init() (err error) {

	dsn := "root:123456@tcp(127.0.0.1:3306)/mydb"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		zap.L().Error("connect DB failed, err:%v\n", zap.Error(err))
		return
	}
	db.SetMaxOpenConns(viper.GetInt("mysql.max_open_conns"))
	db.SetMaxIdleConns(viper.GetInt("mysql.max_idle_conns"))
	return
}

func Close() {
	_ = db.Close()
}

mysql/community.go

package mysql

import (
	"database/sql"
	"go.uber.org/zap"
	"myProject/model"
)

func SelectCommunity() (communityList []*model.Community, err error) {
	sqlStr := "select community_id, community_name, introduction from community"
	if err := db.Select(&communityList, sqlStr); err != nil {
		if err == sql.ErrNoRows {
			zap.L().Warn("there is no community in db")
			err = nil
		}
	}

	return
}

model/community.go

package model

type Community struct {
	ID           int64  `json:"id" db:"community_id"`
	Name         string `json:"name" db:"community_name"`
	Introduction string `json:"introduction" db:"introduction" `
}

代码编写完后用postman测试一下

GET方法访问:http://localhost:8080/community，就可以查看到返回的数据了