需要直接在 PostgreSQL 数据库中插入结构体。

huangapple go评论95阅读模式
英文:

Need to insert struct directly in a PostgreSQL DB

问题

我正在使用Go语言作为后端,PostgreSQL作为数据库。我使用"github.com/lib/pq"驱动程序与数据库连接。我有一个包含许多字段和一些JSON的结构体。

我的结构体如下所示:

ApplyLeave1 struct {
    LeaveId           int       
    EmpId             string    
    SupervisorEmpId   string    
    LeaveDays         float64   
    MDays             float64   
    LeaveType         string    
    DayType           string    
    LeaveFrom         time.Time 
    LeaveTo           time.Time 
    AppliedDate       time.Time 
    LeaveStatus       string    
    ResultDate        time.Time     
    Certificate       []*CertificateInfo
}

CertificateInfo struct {
    Id           int64  
    FileName     string 
    FileType     string 
    FileLocation string 
}

这个结构体很大,里面有一些JSON数组,我的数据库模式与结构体相同。根据我的研究,唯一的插入方式是使用查询,并将mystruct中的每个字段逐个插入到数据库中,例如:

var leave ApplyLeave1

db.Exec("INSERT INTO TABLENAME(leaveid,empid,supervisorid,....) VALUES($1,$2,$3,$4,....)",leave.LeaveId,leave.EmpId,leave.SupervisorId,.....)

这样写太长了,因为我的结构体非常大。是否可能一次性插入整个结构体,请指导我如何插入JSON数组到数据库中。感谢任何帮助。谢谢

更新的信息:

CREATE TABLE IF NOT EXISTS apply_leave1 
(
    leaveid serial PRIMARY KEY NOT NULL ,
    empid varchar(10) NOT NULL REFERENCES employee(empid),
    leavedays double precision NOT NULL DEFAULT 0 ,
    mdays double precision NOT NULL DEFAULT 0 ,
    leavetype varchar(20) NOT NULL DEFAULT '' ,
    daytype text NOT NULL DEFAULT '',
    leavefrom timestamp with time zone NOT NULL,
    leaveto timestamp with time zone NOT NULL,
    applieddate timestamp with time zone NOT NULL,
    leavestatus varchar(15) NOT NULL DEFAULT ''  ,
    resultdate timestamp with time zone,
    certificatestatus bool NOT NULL DEFAULT FALSE,
    certificate json[])

在certificate(json[])中,我将拥有certificateinfo结构体的所有字段,你可以在上面的结构体信息中看到。这里我将certificate定义为json[]。我不知道哪个更好,是json还是json[]用于检索。
我将执行以下操作,我不知道是否可以在PostgreSQL中实现。

  • 我需要为每个请假存储两个以上的证书信息
  • 我想将证书ID设置为自动递增和唯一,以便于检索(我不知道是否可能,因为我的请假ID既是主键又是自动递增的)。
  • 我将使用请假ID和证书ID搜索证书信息。
  • 我只需要使用请假ID和证书ID检索证书信息的某些字段。例如,只检索文件名和文件类型。
  • 我需要根据证书ID更新特定证书的信息。

对于我如此详细的描述表示抱歉。由于我刚开始使用Postgres中的JSON,我有很多疑问。请谅解我的无知问题。谢谢...感谢您的帮助。谢谢

英文:

I am using go lang as my back end and postgreSQL as my database.I use "github.com/lib/pq" driver to connect with my database. I have a struct which have lot of field and it has some JSON inside it.

My struct looks like this

ApplyLeave1 struct {
		LeaveId           int       
		EmpId             string    
		SupervisorEmpId   string    
		LeaveDays         float64   
		MDays             float64   
		LeaveType         string    
		DayType           string    
		LeaveFrom         time.Time 
		LeaveTo           time.Time 
		AppliedDate       time.Time 
		LeaveStatus       string    
		ResultDate        time.Time     
		Certificate       []*CertificateInfo
	}

	CertificateInfo struct {
		Id           int64  
		FileName     string 
		FileType     string 
		FileLocation string 
	} 

The struct is big and have some json array inside it and my database schema is as same as the struct. As for as I researched the only way to insert is to use query and insert one by one from mystruct in to the database for example like this

var leave ApplyLeave1

    db.Exec("INSERT INTO TABLENAME(leaveid,empid,supervisorid,....) VALUES($1,$2,$3,$4,....)",leave.LeaveId,leave.EmpId,leave.SupervisorId,.....)

This is too long to write as my struct is very big.Is it possible to insert the whole struct at one time and please direct me how to insert a json array inside the database.Appreciate any help.Thanks

Updated information:

CREATE TABLE IF NOT EXISTS apply_leave1 
(
    leaveid serial PRIMARY KEY NOT NULL ,
    empid varchar(10) NOT NULL REFERENCES employee(empid),
    leavedays double precision NOT NULL DEFAULT 0 ,
    mdays double precision NOT NULL DEFAULT 0 ,
    leavetype varchar(20) NOT NULL DEFAULT '' ,
    daytype text NOT NULL DEFAULT '',
    leavefrom timestamp with time zone NOT NULL,
    leaveto timestamp with time zone NOT NULL,
    applieddate timestamp with time zone NOT NULL,
    leavestatus varchar(15) NOT NULL DEFAULT ''  ,
    resultdate timestamp with time zone,
    certificatestatus bool NOT NULL DEFAULT FALSE,
    certificate json[])

Inside certificate(json[]) i will have all the fields of certificateinfo struct which you can see in the above struct information.Here i give certificate as json[]. I don't know which one is better json or json[] for the retrieval.
I will be doing following operations and I don't if it is possible with the PostgreSQL.

  • I need to store more than two certificate information per leave
  • I would like to give certificate id as the auto increment and unique so it is easy for retrieval( i don`t know if its possible as my leave id is primary key and auto increment as well.
  • I will searching the certificate info using leave id and certificate id.
  • I need to retrieve only some fields of certificate info.For example only filename and file type using the leave id and certificate id.
  • I need to update the certificate information of the particular certificate using its id.

Sorry for being so elaborate.Since i m new to use json in Postgres I have lots of doubts.So please bear my unawareness questions.Thanks...Appreciate your help.Thanks

答案1

得分: 11

你可以使用https://github.com/jmoiron/sqlx库。它有一个NamedExec函数,你可以将变量传递给它。但首先,你需要为每个结构字段定义db标签。

简化示例:

import (
    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
    "log"
)

type ApplyLeave1 struct {
    LeaveId           int       `db:"leaveid"`
    EmpId             string    `db:"empid"`
    SupervisorEmpId   string    `db:"supervisorid"`
}

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

query := `INSERT INTO TABLENAME(leaveid, empid, supervisorid) 
          VALUES(:leaveid, :empid, :supervisorid)`

var leave1 ApplyLeave1
_, err := db.NamedExec(query, leave1)
if err != nil {
    log.Fatalln(err)
}
英文:

You can use https://github.com/jmoiron/sqlx library. It has NamedExec function and you can pass variable to it. But first you need to define db tag for each of your struct field.

Simplified Example:

import (
    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
    "log"
)

type ApplyLeave1 struct {
    LeaveId           int       `db:"leaveid"`
    EmpId             string    `db:"empid"`
    SupervisorEmpId   string    `db:"supervisorid"`
}

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

query := `INSERT INTO TABLENAME(leaveid, empid, supervisorid) 
          VALUES(:leaveid, :empid, :supervisorid)`

var leave1 ApplyLeave1
_, err := db.NamedExec(query, leave1)
if err != nil {
    log.Fatalln(err)
}

答案2

得分: 2

另一种方法是使用github.com/knq/xo包,该包依赖于代码生成(go tool generate)。

该包附带一个工具,它接收目标数据库的模式,并创建一组包含类型和代码的Go源代码文件,这些类型和代码与数据库模式相对应(双向映射)。

github.com/jmoiron/sqlx相比,优缺点如下:

  • sqlx较慢,因为它依赖于运行时反射来处理提交给它的值的类型。xo生成的静态代码理论上是最快的,可以在繁重的工作负载下产生一些差异。
  • xo需要单独的生成步骤,而sqlx则不需要。
  • sqlx允许您在类型上使用其他标签,例如json序列化的标签。我不确定xo是否尝试保留您对其生成内容所做的更改。
英文:

Another approach is to use the github.com/knq/xo package which relies on code generation (go tool generate).

This package comes with a tool which takes the schema of your target database and creates a set of Go source code files containing types and code which map to the DB schema (and back).

Compared to github.com/jmoiron/sqlx, the pros and cons are as follows:

  • sqlx is slower because it relies on run-time reflection over the types of the values you submit to it. xo generates static code which is as fast as you can theoretically get which can make some difference under heavy workloads.
  • xo requires a separate generation step while sqlx doesn't.
  • sqlx allows you to also use other tags on your types—so you may have, say, tags for json serialization as well. I'm not sure whether xo tries to preserve your changes done to what it generates.

huangapple
  • 本文由 发表于 2016年11月30日 17:07:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/40884184.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定