網頁

▼

2022/1/12

Golang sql PostgreSQL查詢範例

Go程式查詢PostgreSQL資料庫範例如下。


範例環境：

  • Go 1.17
  • PostgreSQL 14

PostgreSQL的postgres資料庫的employee資料表如下。

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   created_at timestamp NOT NULL
);

PostgreSQL的postgres資料庫的employee資料表有一筆資料如下。

 id | name | age |         created_at
----+------+-----+----------------------------
  1 | john |  33 | 2022-01-06 10:28:51.979435

在專案根目錄執行go get github.com/lib/pq下載driver。

~/../go-demo$ go get github.com/lib/pq

下面程式連線到PostgresSQL資料庫並查詢employee資料表。需匯入PostgreSQL的Go sql driver github.com/lib/pq來連線PostgreSQL。Employee struct用來接收employee查詢結果。

GetAllEmployees()中執行DB.Query()查詢取得Rows即查詢結果result set後調用defer rows.Close()確保讀取結束後關閉連線。調用Rows.Next()遍歷result set並調用Rows.Scan()讀取欄位值到Employee中然後放入[]Employee slice返回。

GetEmployeeByID()執行DB.QueryRow()查詢帶入條件參數id取得單一列資料Row，調用Row.Scan()讀取欄位值到Employee中返回。

package main

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

    _ "github.com/lib/pq"
)

type Employee struct {
    ID        int64
    Name      string
    Age       int
    CreatedAt time.Time
}

const (
    HOST     = "localhost"
    PORT     = "5432"
    DATABASE = "postgres"
    USER     = "admin"
    PASSWORD = "12345"
    SSL      = "disable"
)

func connect() *sql.DB {
    driver := "postgres"
    dsn := fmt.Sprintf(
        "host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
        HOST, PORT, USER, PASSWORD, DATABASE, SSL)

    db, err := sql.Open(driver, dsn)
    if err != nil {
        panic("open database error")
    }
    return db
}

func main() {
    db := connect()
    emps, err := GetAllEmployees(db)
    if err != nil {
        panic(err)
    }
    fmt.Println(emps) // [{1 john 33 2022-01-06 10:28:51.979435 +0000 +0000}]

    emp, err := GetEmployeeByID(db, 1)
    if err != nil {
        panic(err)
    }
    fmt.Println(*emp) // {1 john 33 2022-01-06 10:28:51.979435 +0000 +0000}
}

func GetAllEmployees(db *sql.DB) ([]Employee, error) {
    rows, err := db.Query("SELECT id, name, age, created_at FROM employee")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var emps []Employee
    for rows.Next() {
        var e Employee
        err = rows.Scan(&e.ID, &e.Name, &e.Age, &e.CreatedAt)
        if err != nil {
            return nil, err
        }
        emps = append(emps, e)
    }
    return emps, nil
}

func GetEmployeeByID(db *sql.DB, id int64) (*Employee, error) {
    row := db.QueryRow("SELECT * FROM employee WHERE id = $1 LIMIT 1", id)
    var emp Employee
    err := row.Scan(
        &emp.ID,
        &emp.Name,
        &emp.Age,
        &emp.CreatedAt,
    )
    if err != nil {
        return nil, err
    }
    return &emp, nil
}

執行程式印出以下。

[{1 john 33 2022-01-06 10:28:51.979435 +0000 +0000}]
{1 john 33 2022-01-06 10:28:51.979435 +0000 +0000}

github。


參考「Golang 資料庫查詢package目錄分類」把程式分為多個package。


沒有留言:

張貼留言