使用Golang操作PostgreSQL数据库的增删改查

package main

/*
Author: Chen Jian
Blog:   https://www.o-my-chenjian.com
Date:   2018-11-20
*/

import (
	"fmt"
	"log"

	"database/sql"

	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

// PGs数据库信息
const (
	pg_host     = "10.16.11.95"
	pg_port     = 5432
	pg_user     = "postgres"
	pg_password = "postgres"
	pg_dbname   = "postgres"
)

func InsertNodeInfo() error {
	stmt, err := db.Prepare("INSERT INTO \"node_infos\"(\"node_name\", \"node_ip\", \"node_port\", \"node_username\", \"node_password\") VALUES ($1, $2, $3, $4, $5)")
	if err != nil {
		log.Fatal("PG Statements Wrong: ", err)
	}

	res, err := stmt.Exec("nicai", "10.51.42.66", "9999", "nicai", "gophdddeer")
	if err != nil {
		log.Fatal("PG Statements Exec Wrong: ", err)
	}

	id, err := res.RowsAffected()
	if err != nil {
		log.Fatal("PG Affecte Wrong: ", err)
	}

	fmt.Println(id)
	return nil
}

func DeleteNodeInfo() error {
	stmt, err := db.Prepare("DELETE FROM \"node_infos\" WHERE \"node_name\" = $1")
	if err != nil {
		log.Fatal("PG Statements Wrong: ", err)
	}

	res, err := stmt.Exec("nicai")
	if err != nil {
		log.Fatal("PG Statements Exec Wrong: ", err)
	}

	id, err := res.RowsAffected()
	if err != nil {
		log.Fatal("PG Affecte Wrong: ", err)
	}

	fmt.Println(id)
	return nil
}

func SelectAllNodeInfo() error {
	rows, err := db.Query("SELECT * FROM  \"node_infos\"")
	if err != nil {
		log.Fatal("PG Statements Wrong: ", err)
	}

	for rows.Next() {
		var nodename string
		var nodeip string
		var nodeport string
		var nodeusername string
		var nodepassword string

		if err := rows.Scan(&nodename, &nodeip, &nodeport, &nodeusername, &nodepassword); err != nil {
			log.Fatal("PG Rows Scan Failed: ", err)
		}

		fmt.Printf("node_name = \"%s\", "+
			"node_ip = \"%s\", "+
			"node_port = \"%s\", "+
			"node_username = \"%s\", "+
			"node_password = \"%s\"\n", nodename, nodeip, nodeport, nodeusername, nodepassword)
	}

	if err := rows.Err(); err != nil {
		log.Fatal("PG Query Failed: ", err)
	}

	rows.Close()
	db.Close()
	return nil
}

func UpdateNodeInfo() error {
	stmt, err := db.Prepare("UPDATE \"node_infos\" SET \"node_username\" = $1 WHERE \"node_name\" = $2")
	if err != nil {
		log.Fatal("PG Statements Wrong: ", err)
	}

	res, err := stmt.Exec("blabla", "blabla")
	if err != nil {
		log.Fatal("PG Statements Exec Wrong: ", err)
	}

	id, err := res.RowsAffected()
	if err != nil {
		log.Fatal("PG Affecte Wrong: ", err)
	}

	fmt.Println(id)
	return nil
}

func main() {
	// 链接PostgreSQL数据库
	log.Println("Connecting PostgreSQL....")

	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", pg_host, pg_port, pg_user, pg_password, pg_dbname)
	db, err = sql.Open("postgres", psqlInfo)
	if err != nil {
		log.Fatal("Connect PG Failed: ", err)
	}

	db.SetMaxOpenConns(2000)
	db.SetMaxIdleConns(1000)
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatal("Ping GP Failed: ", err)
	}
	fmt.Println("PG Successfull Connected!")

	// 插入数据
	//err := InsertNodeInfo()
	//if err != nil {
	//	log.Fatal("Insert Data Failed: ", err)
	//}

	// 删除数据
	//err := DeleteNodeInfo()
	//if err != nil {
	//	log.Fatal("Delete Data Failed: ", err)
	//}

	// 查询所有数据
	//err := SelectAllNodeInfo()
	//if err != nil {
	//	log.Fatal("Select All Data Failed: ", err)
	//}

	// 更新某一数据
	err := UpdateNodeInfo()
	if err != nil {
		log.Fatal("Update Data Failed: ", err)
	}
}

package main

/*
Author: Chen Jian
Blog:   https://www.o-my-chenjian.com
Date:   2018-11-20
*/

import (
	"fmt"
	"log"

	"github.com/go-pg/pg"
	"github.com/go-pg/pg/orm"
)

// PGs数据库信息
const (
	pg_addr     = "10.16.11.95:5432"
	pg_user     = "postgres"
	pg_password = "postgres"
	pg_dbname   = "postgres"
)

// 定义表结构
type NodeInfo struct {
	NodeName     string `sql:"type:varchar(45),unique,notnull,pk"`
	NodeIp       string `sql:"type:varchar(45),notnull"`
	NodePort     string `sql:"type:varchar(45),notnull"`
	NodeUsername string `sql:"type:varchar(45),notnull"`
	NodePassword string `sql:"type:varchar(255),notnull"`
}

func CreateTable(db *pg.DB) error {
	for _, model := range []interface{}{(*NodeInfo)(nil)} {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			IfNotExists:   true,
			FKConstraints: true,
		})
		if err != nil {
			return err
		}
	}
	return nil
}

func DeleteTable(db *pg.DB) error {

	err := db.DropTable(&NodeInfo{}, &orm.DropTableOptions{
		IfExists: true,
		Cascade:  true,
	})

	return err
}

func InsertNodeInfo(db *pg.DB) error {
	// 插入数据方法一
	//nodeinfodata := &NodeInfo{
	//	NodeName:     "chenjian",
	//	NodeIp:       "10.0.0.5",
	//	NodePort:     "2222",
	//	NodeUsername: "chenjian",
	//	NodePassword: "1234321",
	//}
	//err := db.Insert(nodeinfodata)
	//if err!=nil {
	//	return err
	//}

	// 插入数据方法二
	err := db.Insert(&NodeInfo{
		NodeName:     "chenjian",
		NodeIp:       "10.0.0.5",
		NodePort:     "2222",
		NodeUsername: "chenjian",
		NodePassword: "1234321",
	})
	if err != nil {
		return err
	}
	return nil
}

func SelectAllNodeInfo(db *pg.DB) error {
	var nodeinfodata []NodeInfo
	err := db.Model(&nodeinfodata).Select()
	if err != nil {
		return err
	}
	fmt.Println(nodeinfodata)
	return nil
}

func SelectNodeInfo(db *pg.DB) error {
	nodeinfodata := &NodeInfo{
		NodeName: "chenjian",
	}
	err := db.Select(nodeinfodata)
	if err != nil {
		return err
	}
	fmt.Println(nodeinfodata)
	return nil
}

func UpdateNodeInfo(db *pg.DB) error {
	var nodeinfodata []NodeInfo

	updatedata := &NodeInfo{
		NodeName:     "chenjian",
		NodeIp:       "10.0.0.5",
		NodePort:     "3333",
		NodeUsername: "chenjian",
		NodePassword: "1234321",
	}

	_, err := db.Model(&nodeinfodata).
		Set("node_port = ?", updatedata.NodePort).
		Where("node_name = ?", updatedata.NodeName).
		Returning("*").
		Update()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	// 链接PostgreSQL数据库
	log.Println("Connecting PostgreSQL....")

	db := pg.Connect(&pg.Options{
		Addr:     pg_addr,
		User:     pg_user,
		Password: pg_password,
		Database: pg_dbname,
	})
	defer db.Close()

	fmt.Println("Successfull Connected!")

	// 创建表
	//err := CreateTable(db)
	//if err != nil {
	//	log.Fatal("Create Table Failed: ",err)
	//}

	// 删除表
	//err := DeleteTable(db)
	//if err != nil {
	//	log.Fatal("Delete Table Failed: ", err)
	//}

	// 插入数据
	//err := InsertNodeInfo(db)
	//if err != nil {
	//	log.Fatal("Insert Data Failed: ", err)
	//}

	// 查询所有数据
	//err := SelectAllNodeInfo(db)
	//if err != nil {
	//	log.Fatal("Select All Data Failed: ", err)
	//}

	// 查询某一数据
	//err := SelectNodeInfo(db)
	//if err != nil {
	//	log.Fatal("Select Data Failed: ", err)
	//}

	// 更新某一数据
	err := UpdateNodeInfo(db)
	if err != nil {
		log.Fatal("Update Data Failed: ", err)
	}
}