Golang 带数据库操作 - Golang教程网

大家好,

在这篇文章中,我将向您展示如何将 PostgreSQL 与 GoLang 一起使用。简单介绍一下 GoLang 的历史。实际上,您可以找到不止一份关于 GoLang 历史的不同文档,但是我在一个解释得很好的网站上找到了它。历史

我体验过 .Net Core 堆栈,但是我已经学习 GoLang 3 个月了。我相信 GoLang 是非常强大和流畅的语言。我想我们会一步一步走。

步骤1.安装Docker

Step 2. 运行 PostgreSQL 容器

docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=Password! -d postgres;

[](https://res.cloudinary.com/practicaldev/image/fetch/s--9gJMxCKe--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to-uploads .s3.amazonaws.com/uploads/articles/we53z45iy3xpd2mm7h9y.png)

步骤 3. 运行数据库脚本

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE public.users (
    firstname varchar NULL,
    lastname varchar NULL,
    id serial NOT NULL
);

Insert Into public.Users(firstname,lastname) values("FirstName-1","LastName-1")
Insert Into public.Users(firstname,lastname) values("FirstName-2","LastName-2")
Insert Into public.Users(firstname,lastname) values("FirstName-3","LastName-3")

进入全屏模式退出全屏模式

步骤 4. 创建新的 GoLang 解决方案

有不同的IDE 的开发代码。

VS代码
Vim-go
原子
崇高
LiteIDE 更多信息GoLang 官方

根据我的习惯,Visual Studio Code 非常适合我。现在安装 GoLang 的扩展。转分机

包装:

Web 框架:go get -u github.com/labstack/echo/v4
读取配置文件:go get -u github.com/spf13/viper
日志记录:去获取-u github.com/sirupsen/logrus
PostgreSQL:去获取-u github.com/lib/pq

看看项目结构

[](https://res.cloudinary.com/practicaldev/image/fetch/s--aXszQRQ0--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to-uploads .s3.amazonaws.com/uploads/articles/umgsek4q2fydgw8acumx.png)

cmd>api:包括配置文件和初始化,如 Startup.cs
cmd>utils:配置结构
pkg>api : 包括中间件和路由也向处理程序注册
pkg>entities:数据库实体
pkg>handlers:与请求和响应类型相关,也与存储库层交互
pkg>models : api 响应数据
pkg>repository:与数据库操作相关

go run main.go

让我们连接到数据库。此函数采用 postgresCustomerRepository 参数并返回 *sql.DB 指针。此方法与打开连接有关,使用 Ping 命令检查连接状态。

func openDatabaseConn(r *postgresCustomerRepository) *sql.DB {
    db, err := sql.Open("postgres", r.conn)
    if err != nil {
        log.Error("Connection failed")
    }

    pingError := db.Ping()
    if pingError != nil {
        log.Error("Ping != pong")
    }

    log.Info("Postgres connection success!!!")

    return db
}

进入全屏模式退出全屏模式

添加方法

$1 和 $2 持有 struct 的参数。在旧的数值方法中,使用语法 $n 引用参数:$1 引用第一个输入参数,$2 引用第二个输入参数,依此类推。无论是否使用名称声明了特定参数,这都将起作用。 [PostgreSQL][https://www.postgresql.org/docs/9.5/xfunc-sql.html]

    db := openDatabaseConn(r)
    defer db.Close()

    //add data
    query := "Insert into Users(FirstName, LastName) values($1,$2)"
    if _, err := db.ExecContext(ctx, query, customer.FirstName, customer.LastName); err != nil {
        return customer, err
    }

进入全屏模式退出全屏模式

列表法

    //connect database
    db := openDatabaseConn(r)
    defer db.Close()

    //read data from server
    rows, _ := db.QueryContext(ctx, "Select id,firstname,lastname from Users")
    defer rows.Close()

进入全屏模式退出全屏模式

删除方法

db := openDatabaseConn(r)
    defer db.Close()

    query := "Delete From Users Where Id=$1"
    affectedRow, err := db.ExecContext(ctx, query, id)
    if err != nil {
        return false, nil
    }

进入全屏模式退出全屏模式

得到一个

db := openDatabaseConn(r)
    defer db.Close()

    data := db.QueryRowContext(ctx, "Select id,firstname,lastname from Users Where Id=$1", id)

进入全屏模式退出全屏模式

customerRepository.go 文件

package repository

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    "github.com/bburaksseyhan/ctmapp/src/cmd/utils"
    "github.com/bburaksseyhan/ctmapp/src/pkg/entities"

    _ "github.com/lib/pq"
    log "github.com/sirupsen/logrus"
)

type CustomerRepository interface {
    List(cntxt context.Context, timeout int) ([]entities.CustomerEntity, error)
    Add(customer entities.CustomerEntity, cntxt context.Context, timeout int) (entities.CustomerEntity, error)
    Delete(id int, cntxt context.Context, timeout int) (bool, error)
    Get(id int, cntxt context.Context, timeout int) (entities.CustomerEntity, error)
}

type postgresCustomerRepository struct {
    dbSetting *utils.DbSettings
    conn      string
}

func NewPostgresCustomerRepository(dbSettings *utils.DbSettings) CustomerRepository {
    //initial log formatter
    log.SetFormatter(&log.JSONFormatter{})

    repo := &postgresCustomerRepository{
        dbSetting: dbSettings,
        conn: fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
            dbSettings.Host, dbSettings.Port, dbSettings.User, dbSettings.Password, dbSettings.DbName),
    }

    return repo
}

func (r *postgresCustomerRepository) List(cntxt context.Context, timeout int) ([]entities.CustomerEntity, error) {

    //context
    ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
    defer cancel()

    //connect database
    db := openDatabaseConn(r)
    defer db.Close()

    //read data from server
    rows, _ := db.QueryContext(ctx, "Select id,firstname,lastname from Users")
    defer rows.Close()

    //define slice for store customer information
    var customerEntity []entities.CustomerEntity

    //read data row by row
    for rows.Next() {
        var userId int
        var firstName string
        var lastName string

        _ = rows.Scan(&userId, &firstName, &lastName)

        customerEntity = append(customerEntity, entities.CustomerEntity{Id: userId, FirstName: firstName, LastName: lastName})
    }

    return customerEntity, nil
}

func (r *postgresCustomerRepository) Add(customer entities.CustomerEntity, cntxt context.Context, timeout int) (entities.CustomerEntity, error) {

    ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
    defer cancel()

    db := openDatabaseConn(r)
    defer db.Close()

    //add data
    query := "Insert into Users(FirstName, LastName) values($1,$2)"
    if _, err := db.ExecContext(ctx, query, customer.FirstName, customer.LastName); err != nil {
        return customer, err
    }

    return customer, nil
}

func (r *postgresCustomerRepository) Delete(id int, cntxt context.Context, timeout int) (bool, error) {

    ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
    defer cancel()

    db := openDatabaseConn(r)
    defer db.Close()

    query := "Delete From Users Where Id=$1"
    affectedRow, err := db.ExecContext(ctx, query, id)
    if err != nil {
        return false, nil
    }

    fmt.Println(affectedRow.LastInsertId())
    fmt.Println(affectedRow.RowsAffected())

    return true, nil
}

func (r *postgresCustomerRepository) Get(id int, cntxt context.Context, timeout int) (entities.CustomerEntity, error) {

    ctx, cancel := context.WithTimeout(cntxt, time.Duration(timeout)*time.Second)
    defer cancel()

    db := openDatabaseConn(r)
    defer db.Close()

    data := db.QueryRowContext(ctx, "Select id,firstname,lastname from Users Where Id=$1", id)

    var userId int
    var firstName string
    var lastName string

    _ = data.Scan(&userId, &firstName, &lastName)

    return entities.CustomerEntity{Id: userId, FirstName: firstName, LastName: lastName}, nil
}

func openDatabaseConn(r *postgresCustomerRepository) *sql.DB {
    db, err := sql.Open("postgres", r.conn)
    if err != nil {
        log.Error("Connection failed")
    }

    pingError := db.Ping()
    if pingError != nil {
        log.Error("Ping != pong")
    }

    log.Info("Postgres connection success!!!")

    return db
}

进入全屏模式退出全屏模式

测试

go run main.go

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--84RWI4TI--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/d85laoxngumuodvzn9yy.png)

路由

    e.GET("/health", customerHandler.Health)

    e.GET("/api/v1/customer", customerHandler.List)
    e.POST("/api/v1/customer", customerHandler.Add)
    e.DELETE("/api/v1/customer/:id", customerHandler.Delete)
    e.GET("api/v1/customer/:id", customerHandler.Get)

进入全屏模式退出全屏模式

创建用户

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--GKCbWkUM--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/sf7jf5zmdthfcq794sc6.png)

列出用户

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--IofRxE3P--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/9jaiyu9mnyxnxsshjy0k.png)

通过id获取

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--tQXYXTPm--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/yxp7bg9t7o6l8vj0qmmi.png)

删除

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--zKYRCkiv--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/59faez5xkidg14o1v979.png)

[图像描述">](https://res.cloudinary.com/practicaldev/image/fetch/s--R7mVHn7e--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to- uploads.s3.amazonaws.com/uploads/articles/2bspub9m8kaaxb9nfq47.png)

存储库

谢谢你。