Golang + Oracle 11g + GORM(改) + go-oci8 的数据库驱动测试(超详细)

承诺一时的华丽 · · 2318 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

一、系统环境介绍:

  • Windows 10
  • Oracle 11g 64bit
  • OCI -> instantclient-basic-windows.x64-12.2.0.1.0
  • OCI SDK -> instantclient-sdk-windows.x64-12.1.0.2.0
  • MinGW 64bit
  • go1.11 windows/amd64

注意:我在Oracle 11g版本下使用 OCI 及 SDK 11.2.0.4.0版本会报错,改用 OCI 12.2.0.1.0 及 SDK 12.1.0.2.0就可以了。

二、安装MinGW,注意安装系统相应的32/64位的版本

1、下载地址:

https://sourceforge.net/projects/mingw-w64/files/

2、安装MinGW
  • 双击安装程序


    双击安装程序
  • 修改Architecture为:x86_64,点击 Next > 直到安装完成。注意:安装路径,后面需要用到
点击 Next >
  • 安装完成目录结构


    安装完成
  • 设置环境变量Path,添加:D:\Tool\MinGW64\mingw64\bin
设置环境变量
复制位置

三、下载Oracle 的 OCI和SDK

1、下载OCI和SDK

https://www.oracle.com/technetwork/topics/winx64soft-089540.html

下载OCI和SDK文件

2、创建存放目录,如:C:\instantclient_11_2,并将上面的两个压缩文件解压到C:\instantclient_11_2中,如图:
解压的OCI和SDK
  • 设置环境变量Path,添加:C:\instantclient_11_2

    设置环境变量

  • D:\Tool\MinGW64\mingw64\lib\pkg-config目录下新建:oci8.pc文件,pkg-config目录也需要新建。注意修改你的OCI目录地址
    新建的目录结构

    目录结构

    修改oci8.pc中的OCI路径C:\instantclient_11_2

# Package Information for pkg-config
prefix=C:/instantclient_11_2
exec_prefix=C:/instantclient_11_2
libdir=${exec_prefix}
includedir=${prefix}/sdk/include/

Name: OCI
Description: Oracle database engine
Version: 11.2
Libs: -L${libdir} -loci
Libs.private: 
Cflags: -I${includedir}

四、gorm新增Dialect方言

1、在dialects目录下新增oci8/oracle.go文件夹及文件
image.png
oracle.go
package oci8

import _ "github.com/mattn/go-oci8"
2、在根目录下新增dialects_oracle.go文件
image.png
dialects_oracle.go
package gorm

import (
    "crypto/sha1"
    "fmt"
    "reflect"
    "regexp"
    "strconv"
    "strings"
    "time"
    "unicode/utf8"
)

type oracle struct {
    commonDialect
}

func init() {
    RegisterDialect("oci8", &oracle{})
}

func (oracle) GetName() string {
    return "oci8"
}

func (oracle) Quote(key string) string {
    return fmt.Sprintf("\"%s\"", strings.ToUpper(key))
}

func (oracle) SelectFromDummyTable() string {
    return "FROM dual"
}

func (oracle) BindVar(i int) string {
    return fmt.Sprintf(":%d", i)
}

func (s *oracle) DataTypeOf(field *StructField) string {
    var dataValue, sqlType, size, additionalType = ParseFieldStructForDialect(field, s)

    if sqlType == "" {
        switch dataValue.Kind() {
        case reflect.Bool:
            sqlType = "CHAR(1)"
        case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uintptr:
            sqlType = "INTEGER"
        case reflect.Int64, reflect.Uint64:
            sqlType = "NUMBER"
        case reflect.Float32, reflect.Float64:
            sqlType = "FLOAT"
        case reflect.String:
            if size > 0 && size < 255 {
                sqlType = fmt.Sprintf("VARCHAR(%d)", size)
            } else {
                sqlType = "VARCHAR(255)"
            }
        case reflect.Struct:
            if _, ok := dataValue.Interface().(time.Time); ok {
                sqlType = "TIMESTAMP"
            }
        }
    }

    if sqlType == "" {
        panic(fmt.Sprintf("invalid sql type %s (%s) for oci8", dataValue.Type().Name(), dataValue.Kind().String()))
    }

    if strings.TrimSpace(additionalType) == "" {
        return sqlType
    }
    return fmt.Sprintf("%v %v", sqlType, additionalType)
}

func (s oracle) HasIndex(tableName string, indexName string) bool {
    var count int
    s.db.QueryRow("SELECT COUNT(*) FROM USER_INDEXES WHERE TABLE_NAME = :1 AND INDEX_NAME = :2", strings.ToUpper(tableName), strings.ToUpper(indexName)).Scan(&count)
    return count > 0
}

func (s oracle) HasForeignKey(tableName string, foreignKeyName string) bool {
    var count int
    s.db.QueryRow("SELECT COUNT(*) FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = :1 AND CONSTRAINT_NAME = :2", strings.ToUpper(tableName), strings.ToUpper(foreignKeyName)).Scan(&count)
    return count > 0
}

func (s oracle) HasTable(tableName string) bool {
    var count int
    s.db.QueryRow("SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = :1", strings.ToUpper(tableName)).Scan(&count)
    return count > 0
}

func (s oracle) HasColumn(tableName string, columnName string) bool {
    var count int
    s.db.QueryRow("SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :1 AND COLUMN_NAME = :2", strings.ToUpper(tableName), strings.ToUpper(columnName)).Scan(&count)
    return count > 0
}

func (oracle) LimitAndOffsetSQL(limit, offset interface{}) (sql string) {

    if limit != nil {
        if parsedLimit, err := strconv.ParseInt(fmt.Sprint(limit), 0, 0); err == nil && parsedLimit >= 0 {
            sql += fmt.Sprintf("ROWNUM <= %d", limit)
        }
    }
    return
}

func (s oracle) BuildKeyName(kind, tableName string, fields ...string) string {
    keyName := s.commonDialect.BuildKeyName(kind, tableName, fields...)
    if utf8.RuneCountInString(keyName) <= 30 {
        return keyName
    }
    h := sha1.New()
    h.Write([]byte(keyName))
    bs := h.Sum(nil)

    // sha1 is 40 digits, keep first 24 characters of destination
    destRunes := []rune(regexp.MustCompile("(_*[^a-zA-Z]+_*|_+)").ReplaceAllString(fields[0], "_"))
    result := fmt.Sprintf("%s%x", string(destRunes), bs)
    if len(result) <= 30 {
        return result
    }
    return result[:29]
}

五、数据操作测试

测试文件main_test.go,测试前注意先在ggs数据库中添加userinfo表及数据

package example

import (
    "database/sql"
    "encoding/json"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/oci8"
    _ "github.com/mattn/go-oci8"
    "testing"
)

type Userinfo struct {
    ID   int64  `gorm:"column:id" form:"id"`
    Name string `gorm:"column:name" form:"name"`
}

func (Userinfo) TableName() string {
    return "ggs.USERINFO"
}

var driverName = "oci8"
var dataSourceName = "system/123456@127.0.0.1:1521/ORCL"


// gorm model
func TestOracleDBByModel(t *testing.T) {
    db, err := gorm.Open(driverName, dataSourceName)
    defer db.Close()
    if err != nil {
        t.Error(err)
    }
    var list []Userinfo
    if err = db.Model(&Userinfo{}).Find(&list).Error; err != nil {
        t.Error(err)
    }
    if str, err := json.Marshal(list); err != nil {
        t.Error(err)
    } else {
        t.Log(string(str))
    }
    var count int64
    if err := db.Model(&Userinfo{}).Count(&count).Error; err != nil {
        t.Error(err)
    }
    t.Log("总记录数:", count)
}

// gorm sql
func TestOracleDB(t *testing.T) {
    db, err := gorm.Open(driverName, dataSourceName)
    defer db.Close()
    if err != nil {
        t.Error(err)
    }
    rows, err := db.Raw("select * from ggs.userinfo").Rows()
    if err != nil {
        t.Log(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        t.Log(id, name)  
    }
}

// 原生操作 database/sql
func TestMattnOracle(t *testing.T) {
    var db *sql.DB
    var err error
    if db, err = sql.Open(driverName, dataSourceName); err != nil {
        t.Error(err)
        return
    }
    var rows *sql.Rows
    if rows, err = db.Query("select * from ggs.userinfo"); err != nil {
        t.Error(err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        println(id, name)
    }
}
1、TestOracleDBByModel测试结果
=== RUN   TestOracleDBByModel
--- PASS: TestOracleDBByModel (0.05s)
    oracle_test.go:39: [{"ID":1,"Name":"张三"},{"ID":2,"Name":"李四"}]
    oracle_test.go:45: 总记录数: 2
PASS

Process finished with exit code 0
2、TestOracleDB测试结果
=== RUN   TestOracleDB
--- PASS: TestOracleDB (0.05s)
    oracle_test.go:64: 1 张三
    oracle_test.go:64: 2 李四
PASS

Process finished with exit code 0

3、TestMattnOracle测试结果

=== RUN   TestMattnOracle
--- PASS: TestMattnOracle (0.04s)
    oracle_test.go:86: 1 张三
    oracle_test.go:86: 2 李四
PASS

Process finished with exit code 0

六、错误问题及解决方案

1、OCI版本不一致,报错的相关内容:
In file included from D:/worktools/instantclient_11_2/sdk/include/oci.h:541, from ......\mattn\go-oci8\oci8.go:4: 
D:/worktools/instantclient_11_2/sdk/include/oratypes.h:236:25: error: expected ' =', ',', ';', 'asm' or 'attribute' before 'ubig_ora' typedef unsigned _int64 ubig_ora; ^~~~ 
D:/worktools/instantclient_11_2/sdk/include/oratypes.h:237:25: error: expected ' =', ',', ';', 'asm' or 'attribute' before 'sbig_ora' typedef signed _int64 sbig_ora; ^~~~ In file included from 
D:/worktools/instantclient_11_2/sdk/include/oci.h:3045, from ......\mattn\go-oci8\oci8.go:4: D:/worktools/instantclient_11_2/sdk/include/ociap.h:7459:40: error: unknown type name 'ubig_ora' OraText path, ubig_ora lenp ); ^~~~ 
D:/worktools/instantclient_11_2/sdk/include/ociap.h:7471:36: error: unknown type name 'ubig_ora' uword origin, ubig_ora offset, sb1 dir ); ^~~~ 
D:/worktools/instantclient_11_2/sdk/include/ociap.h:8278:23: error: unknown type name 'sbig_ora' sbig_ora bufferLength, sbig_ora returnLength, ^~~~ 
D:/worktools/instantclient_11_2/sdk/include/ociap.h:8278:46: error: unknown type name 'sbig_ora' sbig_ora bufferLength, sbig_ora returnLength

错误原因:OCI版本问题,我是在Oracle 11g版本下使用 OCI 及 SDK 11.2.0.4.0版本会报错
解决方法:改用 OCI 12.2.0.1.0 及 SDK 12.1.0.2.0就可以了。

2、fatal error: oci.h: No such file or directory
# github.com/mattn/go-oci8
/slview/nms/go/src/github.com/mattn/go-oci8/oci8.go:4:17: fatal error: oci.h: No such file or directory
 #include <oci.h>
                 ^
compilation terminated.

错误原因:D:\Tool\MinGW64\mingw64\lib\pkg-config\oci8.pc 配置文件中的路径未修改
解决方法:修改oci8.pc中的路径C:/instantclient_11_2为你的OCI存放路径,如下:

# Package Information for pkg-config
prefix=C:/instantclient_11_2
exec_prefix=C:/instantclient_11_2
libdir=${exec_prefix}
includedir=${prefix}/sdk/include/

Name: OCI
Description: Oracle database engine
Version: 11.2
Libs: -L${libdir} -loci
Libs.private: 
Cflags: -I${includedir}

有疑问加站长微信联系(非本文作者)

本文来自:简书

感谢作者:承诺一时的华丽

查看原文:Golang + Oracle 11g + GORM(改) + go-oci8 的数据库驱动测试(超详细)

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

2318 次点击  
加入收藏 微博
1 回复  |  直到 2019-03-14 16:50:56
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传