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}
參考「Golang 資料庫查詢package目錄分類」把程式分為多個package。
沒有留言:
張貼留言