Gorm操作Oracle数据库

前置条件
首先安装oci8和oracle数据库客户端，之后配置好环境变量LD_LIBRARY_PATH

获取Gorm-Oracle驱动

go get github.com/CengSin/oracle

在文件加载的时候初始化数据库连接

package main

import (
	"GoModuleDemo/oracle/oci8/model"
	"encoding/json"
	"fmt"
	"github.com/cengsin/oracle"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"gorm.io/gorm/logger"
	"log"
	"math/rand"
	"os"
	"strconv"
	"time"
)

var (
	db *gorm.DB
)

func init() {
	log.Println("initial database connect……")
	con, err := gorm.Open(oracle.Open("system/oracle@127.0.0.1:1521/XE"), &gorm.Config{
		Logger: logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{
			SlowThreshold: 1 * time.Millisecond,
			LogLevel:      logger.Warn,
			Colorful:      true,
		}),
		//SkipDefaultTransaction: true,
	})

	if err != nil {
		log.Fatalln(err)
	}

	// 初始化各种结构
	db = con
	if err = db.AutoMigrate(
		&model.ClassInfo{},
		&model.UserInfo{},
		&model.Email{},
		&model.UserModel{},
		&model.CreditCard{},
		&model.Product{},
		&model.Company{},
	); err != nil {
		log.Fatalln(err.Error())
	}
}

定义结构模型

package model

import (
	"fmt"
	"gorm.io/gorm"
	"time"
)

type ClassInfo struct {
	ClassId   int64  `gorm:"column:CLASSID;primaryKey"`
	ClassName string `gorm:"column:CLASSNAME;size:50"`
}

type UserInfo struct {
	Id       int64     `gorm:"column:ID;primaryKey"`
	Name     string    `gorm:"column:USER_NAME;not null"`
	Age      int64     `gorm:"column:USER_AGE"`
	Sex      string    `gorm:"column:USER_SEX"`
	Total    int64     `gorm:"-"`
	EmailId  string    `gorm:"column:EMAIL_ID"`
	Birthday time.Time `gorm:"column:BIRTHDAY"`
}

type Email struct {
	Id        int64          `gorm:"column:ID;primaryKey;AUTOINCREMENT"`
	EmailStr  string         `gorm:"column:EMAIL;NOT NULL"`
	DeletedAt gorm.DeletedAt `gorm:"column:DELETED_AT;index"`
}

func (e *Email) TableName() string {
	return "EMAILS"
}

type CreditCard struct {
	ID          uint       `gorm:"column:ID;AUTOINCREMENT;primaryKey"`
	CreatedAt   time.Time  `gorm:"column:CREATED_AT"`
	UpdatedAt   time.Time  `gorm:"column:UPDATED_AT"`
	DeletedAt   *time.Time `gorm:"column:DELETED_AT;index"`
	CNumber     string     `gorm:"column:CNUMBER"`
	Description string     `gorm:"column:DESCRIPTION"`
	UserModelID uint       `gorm:"column:USERMODELID"`
	InsertID    int64      `gorm:"column:INSERT_ID;default:0"`
}

func (c *CreditCard) TableName() string {
	return "CREDITCARD"
}

type UserModel struct {
	ID          uint   `gorm:"column:ID;AUTOINCREMENT;primaryKey"`
	Name        string `gorm:"column:NAME"`
	CreditCard  CreditCard
	Age         int64  `gorm:"column:AGE"`
	Active      bool   `gorm:"column:ACTIVE;type:bool"`
	CompanyId   int64  `gorm:"column:COMPANY_ID"`
	CompanyName string `gorm:"column:COMPANY_NAME"`
}

func (u *UserModel) TableName() string {
	return "USERMODEL"
}

func (e *Email) BeforeCreate(g *gorm.DB) error {
	fmt.Println("BeforeCreateData---")
	e.EmailStr += "_run_before_create"
	return nil
}

func (u UserInfo) TableName() string {
	return "USERINFO"
}

func (c ClassInfo) TableName() string {
	return `CLASSINFO`
}

type Product struct {
	ID       int64  `gorm:"column:ID;primaryKey;AutoIncrement"`
	Name     string `gorm:"column:NAME;size:50"`
	Price    int64  `gorm:"column:PRICE;size:3"`
	Quantity int64  `gorm:"column:QUANTITY;size:3"`
}

func (p *Product) TableName() string {
	return "PRODUCT"
}

type Company struct {
	Id   int64  `gorm:"column:ID;primaryKey;AUTOINCREMENT"`
	Name string `gorm:"column:NAME"`
}

func (c *Company) TableName() string {
	return "COMPANIES"
}

对数据库进行操作

func QueryCaseTest() {
	classInfo := new(model.ClassInfo)
	// 获取第一条记录（主键升序）
	db.First(classInfo)
	fmt.Printf("获取第一条记录（主键升序）%+v\n", classInfo)
	// SELECT * FROM users ORDER BY id LIMIT 1;

	// 获取第一条记录（主键升序）
	classInfo = new(model.ClassInfo)
	db.Take(classInfo)
	fmt.Printf("获取第一条记录（主键升序）%+v\n", classInfo)
	// SELECT * FROM users LIMIT 1;
}

func CreateEmail(splitStr string) model.Email {
	rand.Seed(time.Now().UnixNano())

	fmt.Println(splitStr + "Email Create Row" + splitStr)
	email := model.Email{EmailStr: strconv.Itoa(rand.Int()) + "@163.com"}
	emailResult := db.Create(&email)
	fmt.Println("插入新数据之后的ID为：", email.Id)                // 返回插入数据的主键
	fmt.Println("返回的Error为：", emailResult.Error)         // 返回 error
	fmt.Println("返回插入记录的条数为：", emailResult.RowsAffected) // 返回插入记录的条数
	return email
}


func UpdateMultiplyColumn(splitStr string, user *model.UserModel) {
	// 根据 `struct` 更新属性，只会更新非零值的字段
	db.Model(&user).Updates(model.UserModel{Name: "hello", Age: 18, Active: false})
	// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

	// 根据 `map` 更新属性
	db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
	// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
}

func UpdateSingleColumn(spli string, user *model.UserModel) {
	fmt.Println(spli + "更新指定的列" + spli)
	if err := db.Model(&model.UserModel{}).Where("ID = ?", user.ID).Update("NAME", "jinzhu 4").Error; err != nil {
		log.Fatalln(err)
	}
}

调用修改方法

func main() {
	var splitStr = "----------------"
	// 调用方法即可
	// ……
}