Golang操作PostgreSQL中的BLOB数据实战指南

Golang操作PostgreSQL中的BLOB数据实战指南

package main

import (
	"fmt"
	"io"
	"io/ioutil"
	"net/http"
	"github.com/jinzhu/gorm"
	_ "github.com/lib/pq"
)

type StudentDocuments struct {
	ID             int `gorm:"primary_key,AUTO_INCREMENT"`
	ApplicationID int
	FileName      string
	FileData      byte
}

func main() {
	db, err := gorm.Open("postgres", "port=5432 user=postgres dbname=studentdocumentsdb password=postgres")
	if err != nil {
		fmt.Println(err.Error())
		panic("failed to connect Database")
	}
	defer db.Close()

	if (!db.HasTable("StudentDocuments") && !db.HasTable(&StudentDocuments{})) {
		db.CreateTable(&StudentDocuments{})
	}

	http.HandleFunc("/", uploadfile)
	http.ListenAndServe(":8080", nil)
}

func uploadfile(w http.ResponseWriter, req *http.Request) {
	var s string
	fmt.Println(req.Method)
	if req.Method == http.MethodPost {
		// open
		f, h, err := req.FormFile("q")
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		defer f.Close()

		// for your information
		fmt.Println("\nfile:", f, "\nheader:", h, "\nerr", err)

		// read
		bs, err := ioutil.ReadAll(f)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		s = string(bs)
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	io.WriteString(w, `<form method="POST" enctype="multipart/form-data">
<input type="file" name="q">
<input type="submit">
</form><br>`+s)
}

//File stored in Fileserver

如何使用Golang创建Blob


更多关于Golang操作PostgreSQL中的BLOB数据实战指南的实战教程也可以访问 https://www.itying.com/category-94-b0.html

2 回复

FormFile 返回一个实现了 Reader 接口的 multpart.File。创建一个与文件大小相同的字节切片，将文件内容读取到该切片中，然后就可以在调用 sql.Exec 时将该切片作为参数使用。

更多关于Golang操作PostgreSQL中的BLOB数据实战指南的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html


以下是针对在Golang中操作PostgreSQL BLOB数据的专业回答，包括示例代码：

首先，需要修正您的代码结构。在PostgreSQL中，BLOB数据通常使用bytea类型存储。您的FileData字段定义为byte类型不正确，应改为[]byte类型。以下是完整的实现示例：

package main

import (
	"fmt"
	"io"
	"log"
	"net/http"

	"github.com/jinzhu/gorm"
	_ "github.com/lib/pq"
)

type StudentDocuments struct {
	ID            int    `gorm:"primary_key;AUTO_INCREMENT"`
	ApplicationID int
	FileName      string
	FileData      []byte `gorm:"type:bytea"` // 使用bytea类型存储BLOB数据
}

var db *gorm.DB

func main() {
	var err error
	db, err = gorm.Open("postgres", "host=localhost port=5432 user=postgres dbname=studentdocumentsdb password=postgres sslmode=disable")
	if err != nil {
		log.Fatal("Failed to connect to database:", err)
	}
	defer db.Close()

	// 自动迁移创建表
	db.AutoMigrate(&StudentDocuments{})

	http.HandleFunc("/", uploadFile)
	log.Println("Server starting on :8080")
	log.Fatal(http.ListenAndServe(":8080", nil))
}

func uploadFile(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodPost {
		// 解析多部分表单
		err := r.ParseMultipartForm(32 << 20) // 32MB最大内存
		if err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}

		// 获取上传的文件
		file, header, err := r.FormFile("q")
		if err != nil {
			http.Error(w, "Error retrieving the file", http.StatusBadRequest)
			return
		}
		defer file.Close()

		// 读取文件数据到字节切片
		fileData, err := io.ReadAll(file)
		if err != nil {
			http.Error(w, "Error reading file", http.StatusInternalServerError)
			return
		}

		// 创建记录并保存到数据库
		document := StudentDocuments{
			ApplicationID: 1, // 根据实际需求设置
			FileName:      header.Filename,
			FileData:      fileData,
		}

		if err := db.Create(&document).Error; err != nil {
			http.Error(w, "Failed to save to database", http.StatusInternalServerError)
			return
		}

		fmt.Fprintf(w, "File uploaded successfully: %s, ID: %d", header.Filename, document.ID)
		return
	}

	// 显示上传表单
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	io.WriteString(w, `<!DOCTYPE html>
<html>
<head>
    <title>Upload File</title>
</head>
<body>
    <form method="POST" enctype="multipart/form-data">
        <input type="file" name="q">
        <input type="submit" value="Upload">
    </form>
</body>
</html>`)
}

要检索BLOB数据，可以添加以下函数：

func downloadFile(w http.ResponseWriter, r *http.Request) {
	id := r.URL.Query().Get("id")
	if id == "" {
		http.Error(w, "ID parameter required", http.StatusBadRequest)
		return
	}

	var document StudentDocuments
	if err := db.First(&document, id).Error; err != nil {
		http.Error(w, "File not found", http.StatusNotFound)
		return
	}

	// 设置响应头
	w.Header().Set("Content-Disposition", "attachment; filename="+document.FileName)
	w.Header().Set("Content-Type", "application/octet-stream")
	w.Write(document.FileData)
}

在main函数中注册下载路由：

func main() {
	// ... 数据库连接代码同上
	
	http.HandleFunc("/", uploadFile)
	http.HandleFunc("/download", downloadFile)
	log.Fatal(http.ListenAndServe(":8080", nil))
}

关键点说明：

  1. 使用[]byte类型和gorm:"type:bytea"标签来正确映射PostgreSQL的bytea类型
  2. 通过io.ReadAll()读取上传文件的字节数据
  3. 使用GORM的Create方法将包含BLOB数据的记录插入数据库
  4. 检索时直接读取FileData字段并写入HTTP响应

这样即可在Golang中完整实现PostgreSQL BLOB数据的存储和检索功能。

回到顶部