客户端访问 - 数据库驱动 - 《OceanBase v3.1.1 官方教程》

OCI 驱动
JDBC 驱动

在 OceanBase 数据库 MySQL 模式下，用户可以直接使用 MySQL 官方提供的 Connector 来使用 OceanBase 数据库（暂不支持 8.0 的驱动），在 OceanBase 数据库 Oracle 模式下，需要使用 OceanBase 自研的数据库驱动。OceanBase 数据库驱动同时支持 OceanBase 数据库的 MySQL/Oracle 两种协议，在使用时可以自动识别 OceanBase 数据库的运行模式是 MySQL 还是 Oracle，无需额外设置。OceanBase 数据库支持各类语言的数据库驱动，下面介绍几种常见的语言及标准。

OCI 驱动

OceanBase 提供了基于 OCI 接口的数据库驱动，兼容了 Oracle 数据库 OCI 接口的方法名和函数名，对于试用 OCI 接口编程的应用程序在进行移植时，无需做大量业务改造即可非常方便的适配到 OceanBase 数据库。

样例代码：

/**********************************************************
* Copyright(C) 2014 - 2020 Alibaba Inc. All Rights Reserved.
*
*  Filename: example.c
*  Description: ----
*  Create: 2020-07-07 10:14:59
*  Last Modified: 2020-07-07 10:14:59
***********************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include "oci.h"
/*声明句柄*/
OCIEnv *envhp;       /*环境句柄*/
OCISvcCtx *svchp;    /*服务环境句柄*/
OCIServer *srvhp;    /*服务器句柄*/
OCISession *authp;   /*会话句柄*/
OCIStmt *stmthp;     /*语句句柄*/
OCIDescribe *dschp;  /*描述句柄*/
OCIError *errhp;     /*错误句柄*/
OCIDefine *defhp[3]; /*定义句柄*/
OCIBind *bidhp[4];   /*绑定句柄*/
sb2 ind[3];          /*指示符变量*/
/*绑定select结果集的参数*/
int szpersonid;     /*存储personid列*/
text szsex[2];      /*存储sex列*/
text szname[10];    /*存储name列*/
text szemail[10];   /*存储mail列*/
char sql[256];      /*存储执行的sql语句*/
static text* SQL_DROP_TB   = (text*)"drop table person";
static text* SQL_CREATE_TB = (text*)"create table person(personid number, sex varchar2(256), name varchar2(256), email varchar2(256))";
void checkerr(OCIError *errhp, sword status, const char* filename, int line) {
  text errbuf[512];
  sb4 errcode = 0;
  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                       errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("%s:%d Error - OCI_SUCCESS_WITH_INFO now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
    break;
  case OCI_NEED_DATA:
    (void) printf("%s:%d Error - OCI_NEED_DATA\n", filename, line);
    break;
  case OCI_NO_DATA:
    (void) printf("%s:%d Error - OCI_NODATA\n", filename, line);
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                       errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("%s:%d Error - now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("%s:%d Error - OCI_INVALID_HANDLE\n", filename, line);
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("%s:%d Error - OCI_STILL_EXECUTE\n", filename, line);
    break;
  case OCI_CONTINUE:
    (void) printf("%s:%d Error - OCI_CONTINUE\n", filename, line);
    break;
  default:
    break;
  }
}
#define OCI_CHECK_RET(errhp, function) \
  checkerr(errhp, function, __FILE__, __LINE__)
/************************************************************************/
/*查询person表*/
/************************************************************************/
void query_tables() {
    sword status = OCI_SUCCESS;
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "select personid, name, email from person");
    /*准备SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT));
    /*绑定输出列*/
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &szpersonid,
                   sizeof(szpersonid), SQLT_INT, &ind[0], 0, 0, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1 *)szname,
                   sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1 *)szemail,
                   sizeof(szemail), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT));
    /*执行SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL,
                   OCI_DEFAULT));
    printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "EMAIL");
    while ((status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
        printf("%-10d", szpersonid);
        printf("%-10s", szname);
        printf("%-10s\n", szemail);
    }
    if (OCI_NO_DATA != status) {
        printf("error ! error ! error ! err=%d\n", status);
    } else {
        printf("finish fetch data\n");
    }
}
void insert_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "insert into person values(:personid,:sex,:name,:email)");
    /*准备SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /*绑定输入列*/
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
                  sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[1], errhp, (const OraText*)":sex", 4, szsex,
                  sizeof(szsex), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname,
                  sizeof(szname), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[3], errhp, (const OraText*)":email", 6, szemail,
                  sizeof(szemail), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
    /*设置输入参数*/
    szpersonid = 1;
    memset(szsex, 0, sizeof(szsex));
    strcpy((char*)szsex, "M");
    memset(szname, 0, sizeof(szname));
    strcpy((char*)szname, "obtest");
    memset(szemail, 0, sizeof(szemail));
    strcpy((char*)szemail, "t@ob.com");
    /*执行SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /*提交到数据库*/
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
    while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
        printf("%-10d", szpersonid);
        printf("%-10s", szsex);
        printf("%-10s", szname);
        printf("%-10s\n", szemail);
    }
    printf("finish insert tables\n");
}
void update_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "update person set sex='M',name='test',email='test@mail' WHERE personid=1");
    /*准备SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /*执行SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /*提交到数据库*/
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}
void delete_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "delete from person WHERE personid = :personid");
    /*准备SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /*绑定输入参数*/
    szpersonid = 1;
    OCI_CHECK_RET(errhp, OCIBindByPos(stmthp, &bidhp[0], errhp, 1, &szpersonid,
                 sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
    /*执行SQL语句*/
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /*提交到数据库*/
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}
int main(int argc, char *argv[]) {
    char strServerName[50];
    char strUserName[50];
    char strPassword[50];
    /*根据实际，设置服务器，用户名和密码*/
    strcpy(strServerName, "192.160.0.1/test");
    strcpy(strUserName, "test");
    strcpy(strPassword, "test");
    /*初始化OCI应用环境*/
    OCI_CHECK_RET(errhp, OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL));
    /*初始化环境句柄*/
    OCI_CHECK_RET(errhp, OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0));
    /*分配句柄*/
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0));
    /*服务器环境句柄*/
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0));
    /*服务器句柄*/
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0));
    /*会话句柄*/
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0));
    /*错误句柄*/
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0));
    /*描述符句柄*/
    /*连接服务器*/
    OCI_CHECK_RET(errhp, OCIServerAttach(srvhp, errhp, (text *)strServerName,
                    (sb4)strlen(strServerName), OCI_DEFAULT));
    /*设置用户名和密码*/
    OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strUserName,
               (ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp));
    OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strPassword,
               (ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp));
    /*设置服务器环境句柄属性*/
    OCI_CHECK_RET(errhp, OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
               (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp));
    OCI_CHECK_RET(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp,
               0, OCI_ATTR_SESSION, errhp));
    /*创建并开始一个用户会话*/
    OCI_CHECK_RET(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0));
    /*语句句柄*/
    /************************************************************************/
    /*创建person表*/
    /************************************************************************/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text*)SQL_DROP_TB, strlen((char *)SQL_DROP_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_COMMIT_ON_SUCCESS));
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, SQL_CREATE_TB, strlen((char *)SQL_CREATE_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT));
    /************************************************************************/
    /*查询person表*/
    /************************************************************************/
    query_tables();
    /************************************************************************/
    /*向person表插入一条数据*/
    /************************************************************************/
    insert_tables();
    query_tables();
    /************************************************************************/
    /*更新person表*/
    /************************************************************************/
    update_tables();
    query_tables();
    /************************************************************************/
    /*删除person表的ID为的数据,首先要在数据库中存在这条记录*/
    /************************************************************************/
    delete_tables();
    query_tables();
    /************************************************************************/
    /*结束释放资源*/
    /************************************************************************/
    //结束会话
    OCI_CHECK_RET(errhp, OCISessionEnd(svchp, errhp, authp, (ub4)0));
    //断开与数据库的连接
    OCI_CHECK_RET(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));
    //释放OCI句柄
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)dschp, OCI_HTYPE_DESCRIBE));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)authp, OCI_HTYPE_SESSION));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER));
    return 0;
}

JDBC 驱动

OceanBase 数据库提供了基于 Java JDBC 标准的数据库驱动，JDBC（Java Database Connectivity）是 Java 应用程序访问数据库的标准 API（应用程序编程接口），数据库驱动的实现会将 JDBC 标准编程接口转换成对应数据库厂商的 SQL 实现。OceanBase 数据库 JDBC 驱动兼容 JDBC 4.0、4.1、4.2 标准。

JDBC 连接串的前置为 jdbc:oceanbase，其他使用和标准 JDBC 方式保持一致。样例代码：

String url = "jdbc:oceanbase://host:port/SYS?useUnicode=true&characterEncoding=utf-8";
String username = "SYS@oracle";
String password = "";    
Connection conn = null;
try {
    Class.forName("com.alipay.oceanbase.jdbc.Driver");
    conn = DriverManager.getConnection(url, username, password);
    PreparedStatement ps = conn.prepareStatement("select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;");
    ResultSet rs = ps.executeQuery();
    rs.next();
    System.out.println("sysdate is:" + rs.getString(1));
    rs.close();
    ps.close();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (null != conn) {
        conn.close();
    }
}