oceanbase java 释放连接 oceanbase jdbc

前言

OceanBase 2.2 自官网提供试用下载后，受到不少数据库爱好者的关注。也有些数据库开发同学想看看OceanBase到底怎么用。最近用了近2个月时间编写《OceanBase 2.2 开发者指南》，详细面向开发同学介绍OceanBase 2.2的使用方法。包括连接方法、DDL、DML、事务、存储过程、PL/SQL、Package等常用对象的用法。文档预计下周可以对外发布。本文节选文档中几个测试示例简单介绍一下OceanBase 2.2在ORACLE兼容性方面的进展。

OceanBase 2.2.3 ORACLE 兼容性

列在这里的都是测试过的，可能有遗漏 。

一级 分类	二级分类	三级分类	功能点	备注
支持的 客户端	命令行客户端		mysql	支持5.5/5.6/5.7 用于连接集群的sys租户(兼容mysql)。
			obclient
	编程语言		Java	通过JDBC驱动连接OB。
			C/go	通过ODBC驱动连接OB。
表的 数据类型	数值类型	整形 定点 浮点	number number(p) number(p, s)	p和s都有表示定点数。p(precision)为精度，s(scale)表示小数点右边的数字个数，精度最大值为38，scale的取值范围为-84到127。 s为0表示整形。 p和s都不指定，表示浮点数，最大精度38。
	字符串 类型	定长	char(N)	最大256 字节，字符集默认UTF8MB4。
			binary	最大256 字节 ，字符集BINARY。
		变长	varchar(N)	最长256KB，字符集默认UTF8MB4。
			varbinary	最长256KB，字符集BINARY。
			enum	最多65535个元素，每个元素最长255个字符，字符集UTF8MB4。
			set	最多64个元素，每个元素最长255个字符，字符集UTF8MB4。
		国家字 符集	nchar	字符集由国家字符集变量确定，默认是 AL16UTF16 。
			nvarchar
	时间类型		date	YYYY-MM-DD，只包含日期。
			time	HH:MM:SS[.fraction]，只包含时间。
			datetime	YYYY-MM-DD HH:MM:SS[.fraction]，包含日期时间(不考虑时区)。
			Timestamp	日期时间(考虑时区)。
			year	YYYY,[1901, 2155]。
	大对象		Text / blob	最大64K。
			Longtext /longblob	最大48M。
DDL	表	普通表	create table	支持有主键、无主键。 支持分区表
			create table as select	只赋值表的数据、列类型但不赋值约束、索引、触发器等。
			alter table	支持新增列 支持部分列的属性变更(如值域扩大) 不支持加主键
		分区表	分区策略	支持一级分区：hash/range/range columns/list/list columns 支持二级分区：range-hash/list hash-range/list
			全局索引 本地索引
		复制表		在分布式数据库中间件里这个功能叫“广播表”。
	视图	普通视图	create or replace view
	序列		create sequence
			alter sequence
	同义词		create synonym
触发器	触发器 类型	行级触发器	create trigger
		伪劣	:NEW :OLD
		DML类型 检测	INSERTING UPDATING DELETING	表示本次触发是被INSERT/UPDATE/DELETE触发
	触发器 变更	启用/禁用 触发器	enable disable
		替换触发器	replace trigger
		删除触发器
PL/SQL 存储过程 函数 包	专有 数据类型		boolean
			record
			ref cursor
			自定义子类型
	声明	变量
		常量	constant
		类型	%type %rowtype	用于复制其他变量或者行列的类型
	变量赋值		:=
			select into
			fetch into
			returning into
	控制语句	条件选择	if then  else
			case when
		循环	for loop
			while loop
			loop exit when
	记录 (record)	声明	type ... is record
			type ... is ... %rowtype
		作为参数
	游标	属性	%FOUND %NOTFOUND %ROWCOUNT %ISOPEN
		声明游标	cursor c1 is ...
		声明游标 (带参数)	cursor c1(p1) is ...
		打开游标	open c1 open c1(p1)
		获取数据	fetch c1 into ...
		关闭游标	close c1
	游标变量	声明	Type c1 is ref cursor	游标变量跟游标类似，区别是不限于特定的查询。
		打开 游标变量	open c1 for ...
	关联数组 (表变量)	元素类型	PLS_INTEGER	也叫密集型数组。
			VARCHAR2	也叫稀疏型数组。
		声明 数组类型	type ... is table of ... index by ...
		填充数组	select into
			select bulk collect into
		属性	.COUNT	数组大小。
			.NEXT	取下一个元素，暂不支持。
	SQL	动态SQL	execute immediate ...
			open ... for query
	异常处理	自定义 异常声明	excp_name EXCEPTION	声明异常类型。
			PRAGMA EXCEPTION_INIT	将自定义异常关联到某个错误号。
		上抛异常	RAISE excp_name	当满足条件时上抛自定义异常。
			RAISE_APPLICATION_ERROR	上抛自定义异常，不能被异常处理捕获。
		异常捕捉	EXCEPTION WHEN ... THEN ...	捕捉各种异常。
		预定义异常	CASE_NOT_FOUND	CASE 语句中没有任何 WHEN 子句满足条件，且没有 ELSE 子句。
			CURSOR_ALREADY_OPEN	程序尝试打开一个已经打开的游标。
			NO_DATA_FOUND	SELECT INTO 语句没有返回数据，不包括调用聚合函数的SELECT INTO 语句。
			TOO_MANY_ROWS	SELECT INTO 语句返回多行数据。
			VALUE_ERROR	FETCH 语句的 LIMIT 子句表达式结果不是正整数。
			ZERO_DIVIDE	表达式尝试除以0 。
	自定义 函数		create or replace function	支持返回值
	存储过程		create or replace procedure	支持IN/OUT/IN OUT参数
NLS	日期和 时间	时间格式	NLS_DATE_FORMAT	如 YYYY-MM-DD HH24:MI:SS
			NLS_TIMESTAMP_FORMAT	如 YYYY/MM/DD HH.MI.SSXFF AM
			NLS_TIMESTAMP_TZ_FORMAT	如 YYYY/MM/DD HH.MI.SSXFF AM TZR
	字符串	字符集	NLS_CHARACTERSET	支持GBK,UTF8
		国家字符集	NLS_NCHAR_CHARACTERSE	支持AL16UTF16
		长度语义	NLS_LENGTH_SEMANTICS	默认是BYTE，可以选CHAR。国家字符集字长固定是CHAR。
事务		隔离级别	READ_COMMITTED
			SERIALIZABLE
		保存点	savepoint

测试细节

OceanBase数据库驱动

OceanBase 实现了自己的 JDBC 驱动，使 Java 能够将 SQL 语句发送到 OceanBase 的 MySQL 租户和 ORACLE 租户。OceanBase JDBC 支持为 Java 暴露 SQL 数据类型、STORED PROCEDURE 对象，并快速访问 SQL 数据。

OceanBase JDBC 驱动文件名为:oceanbase-client-[版本号].jar，可以从官网下载。

OceanBase 数据库驱动文件 1.0 相关版本的类名为:  com.alipay.oceanbase.obproxy.mysql.jdbc.Driver。

OceanBase 数据库驱动文件从 1.1.0 后类名更改为:

com.alipay.oceanbase.jdbc.Driver，原类名会保留，但是不推荐使用。 文章《 OceanBase 2.x体验：推荐用DBeaver工具连接数据库 》里有示例如何注册OceanBase数据库驱动。

OceanBase示例数据库

文章《 OceanBase 2.x 体验：示例数据库 》里演示了ORACLE租户的示例数据库脚本，里面也可以直接看到OceanBase存储过程的一些能力。

场景示例

外连接示例

select t1.id, t1.name, t2.id, t2.name from t1 join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 left join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 , t2 where t1.id=t2.id(+);
select t1.id, t1.name, t2.id, t2.name from t1 right join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 , t2 where t1.id(+)=t2.id ;

分区表的分页查询示例

分页查询场景在WEB应用里非常常见，ORACLE的分页查询通常需要两层嵌套查询才可以实现。

SELECT * FROM (
SELECT rownum rn,  t1.* FROM  (
SELECT h_w_id,h_d_id, w_name, h_c_id,h_c_d_id,h_c_w_id,h_amount,h_data
FROM ware a, hist b
WHERE a.w_id=b.h_w_id
AND a.w_id=2 
ORDER BY h_c_id ) t1
WHERE rownum < 100 
) t2
WHERE t2.rn > 90;

分析函数、窗口函数查询示例

取每个仓库里最早的5笔订单，相同时间的订单会并列，所以排名可能会跳跃。

SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;

取每个仓库里按进仓库时间排名前5的订单。相同时间的订单会并列排名，总的排名不会跳跃。

SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, dense_rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;

层次查询示例

select emp_id, emp_name, mgr_id,  lpad(' ',level*2,' ') ||emp_name,level from emp
start with emp_id=4
connect by prior emp_id=mgr_id
ORDER BY level ;

闪回查询

select * from t1;
select * from t1 as of timestamp to_timestamp('2020-04-04 12:49:10','YYYY-MM-DD HH24:MI:SS');

分区表示例

CREATE TABLE t_ordr_part_by_hash_range (
    o_w_id int 
, o_d_id int
, o_id int
, o_c_id int
, o_carrier_id int
, o_ol_cnt int
, o_all_local int
, o_entry_d date
, index idx_ordr(o_w_id, o_d_id, o_c_id, o_id) LOCAL
, primary key ( o_w_id, o_d_id, o_id, o_entry_d ) 
) PARTITION BY hash(o_w_id)
    SUBPARTITION BY RANGE(o_entry_d)
    SUBPARTITION template
(
    SUBPARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')) 
, SUBPARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD')) 
, SUBPARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, SUBPARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD')) 
, SUBPARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')) 
, SUBPARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD')) 
, SUBPARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD')) 
, SUBPARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD')) 
, SUBPARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD')) 
, SUBPARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD')) 
, SUBPARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, SUBPARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, SUBPARTITION MMAX VALUES LESS THAN MAXVALUE
)
partitions 16;

CREATE TABLE t_log_part_by_range_hash ( 
    log_id number NOT NULL 
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
, PRIMARY key(log_id, log_date)
) PARTITION BY RANGE(log_date)
SUBPARTITION BY HASH(log_id) SUBPARTITIONS 16 (
PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')) 
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD')) 
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD')) 
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')) 
, PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD')) 
, PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD')) 
, PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD')) 
, PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD')) 
, PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD')) 
, PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD')) 
, PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN MAXVALUE
);

游标遍历记录示例

delimiter /
CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;
/
delimiter ;


delimiter /
CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
AS  
PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
AS
BEGIN
            dbms_output.put_line('Print a record :');
            dbms_output.put_line('W_ID : ' || p_record.w_id
|| ', W_YTD : ' || p_record.w_ytd
|| ', W_TAX : ' || p_record.w_tax
|| ', W_NAME : ' || p_record.w_name
|| ', W_STREET_1 : ' || p_record.w_street_1
|| ', W_STREET_2 : ' || p_record.w_street_2
|| ', W_CITY : ' || p_record.w_city
|| ', W_STATE : ' || p_record.w_state
|| ', W_ZIP : ' || p_record.w_zip )
;
            dbms_output.put_line('');
END;

PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
IS
CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id ;
        r_ware  ware%ROWTYPE; 
BEGIN

OPEN c1(p_w_id);
        dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');

LOOP 
FETCH c1 INTO r_ware ;
            dbms_output.put_line('Fetch the cursor one time.');

EXIT WHEN c1%NOTFOUND ;

            sp_record_print_by_record(r_ware);
END LOOP;

    EXCEPTION
WHEN OTHERS THEN
            dbms_output.put_line('Raise an unkown exception !');          
END ;

END; 
/
delimiter ;


obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)

obclient> call pkg_ware_mgmt.sp_record_print(1);
Query OK, 0 rows affected (0.12 sec)

异常处理示例

delimiter /
CREATE OR REPLACE PROCEDURE sp_test_exception(p_w_id IN  ware.w_id%TYPE )
AS
TYPE TYPE_REFCURSOR IS REF CURSOR ;
    ref_cursor  TYPE_REFCURSOR;
    ware_name   ware.w_name%TYPE;
    sum_ytd     number(10,2) := -1;
BEGIN
OPEN ref_cursor FOR SELECT sum(w_ytd) sum_ytd  FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
FETCH ref_cursor INTO sum_ytd;
    dbms_output.put_line('Fetch a ware ytd sum : ' || to_char(nvl(sum_ytd, '-999.99')) || '.');
CLOSE ref_cursor;

OPEN ref_cursor FOR SELECT w_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
FETCH ref_cursor INTO ware_name ;
IF ref_cursor%NOTFOUND  THEN 
        dbms_output.put_line('Fetch a ware name but ref cursor return NOTFOUND.');
ELSE 
        dbms_output.put_line('Fetch a ware name : ' || ware_name || '.');
END IF;

CLOSE ref_cursor;

SELECT w_name INTO ware_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
    dbms_output.put_line('Fetch again a ware name  : ' || ware_name || '.');

EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
        dbms_output.put_line('Handle a CURSOR_ALREADY_OPEN exception. ');
WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line('Handle a NO_DATA_FOUND exception. ');
WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('Handle a TOO_MANY_ROWS exception. ');
WHEN OTHERS THEN 
        dbms_output.put_line('Raise an unkown exception !');
        RAISE ;
END;
/
delimiter ;


obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)

obclient> select w_id,w_ytd,w_name from ware;
+------+-------+------------+
| W_ID | W_YTD | W_NAME     |
+------+-------+------------+
|    2 |  1200 | L6xwRsbDk  |
|    1 |  1200 | n1P4zYo8OH |
+------+-------+------------+
2 rows in set (0.01 sec)


obclient> call sp_test_exception(NULL);

obclient> call sp_test_exception(1);

obclient> call sp_test_exception(3);

用触发器实现表的自增列填充

CREATE TABLE account(id number NOT NULL PRIMARY KEY
, name varchar2(50) NOT NULL UNIQUE 
, value number NOT NULL
, gmt_create date DEFAULT sysdate NOT NULL 
, gmt_modified date DEFAULT sysdate NOT NULL  );
CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
, acc_id number NOT NULL 
, acc_name varchar2(50) NOT NULL
, old_value number NULL
, new_value number  NULL
, gmt_create date DEFAULT sysdate NOT NULL );
CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;

delimiter /
CREATE OR REPLACE TRIGGER trg_before_ins_account
BEFORE INSERT 
ON account
FOR EACH ROW  
BEGIN 
select seq_account.nextval INTO :NEW.id FROM DUAL ;
END;
/
delimiter ;

用PL/SQL实现批处理

将一个原始指令表里的记录分拆为2笔记录写入到目标表中。

CREATE TABLE raw_list(
      id number NOT NULL PRIMARY KEY
, debit_id number NOT NULL
, credit_id number NOT NULL
, op_amount number NOT NULL
, op_time date NOT NULL 
);
CREATE SEQUENCE seq_op_list START WITH 1 ;
CREATE TABLE op_list(
      op_id number NOT NULL PRIMARY KEY
, op_code varchar2(20) NOT NULL
, account_id number NOT NULL
, amount number NOT NULL
, gmt_create date DEFAULT sysdate NOT NULL
);


delimiter /
DECLARE
CURSOR c1 IS SELECT * FROM raw_list ;
TYPE T_RAW  IS TABLE OF raw_list%ROWTYPE INDEX BY binary_integer ;
    t   T_RAW;
    row_cnt binary_integer  := 0;
    array_size  NUMBER := 100;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO t LIMIT array_size ;
EXIT WHEN t.COUNT = 0 ;

        FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', t(i).credit_id, t(i).op_amount);

        FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', t(i).debit_id, t(i).op_amount);

COMMIT;
END LOOP; 
END;
/
delimiter ;


CREATE TABLE raw_list(
      id number NOT NULL PRIMARY KEY
, debit_id number NOT NULL
, credit_id number NOT NULL
, op_amount number NOT NULL
, op_time date NOT NULL 
);
CREATE SEQUENCE seq_op_list START WITH 1 ;
CREATE TABLE op_list(
      op_id number NOT NULL PRIMARY KEY
, op_code varchar2(20) NOT NULL
, account_id number NOT NULL
, amount number NOT NULL
, gmt_create date DEFAULT sysdate NOT NULL
);


delimiter /
DECLARE
CURSOR c1 IS SELECT * FROM raw_list ;
TYPE T_RAW  IS TABLE OF raw_list%ROWTYPE INDEX BY binary_integer ;
    t   T_RAW;
    row_cnt binary_integer  := 0;
    array_size  NUMBER := 100;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO t LIMIT array_size ;
EXIT WHEN t.COUNT = 0 ;

        FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', t(i).credit_id, t(i).op_amount);

        FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', t(i).debit_id, t(i).op_amount);

COMMIT;
END LOOP; 
END;
/
delimiter ;