OceanBase使用範例

http://www.mysqlops.com/2011/08/31/oceanbase-use.html

 

OceanBase的使用類似於關係型資料庫，需要預先建立schema,關於schema的格式，請參見schema說明。

假如我們有以下schema:

[app_name]name=studentmax_table_id=1002

[student]table_id=1001max_column_id=22table_type=1#rowkey=class_id(8bytes) + student id(8bytes)rowkey_is_fixed_length=1column_info=1,2,gmt_created,create_timecolumn_info=1,3,gmt_modified,modify_timecolumn_info=1,16,student_name,varchar,128column_info=1,17,student_sex,intcolumn_info=1,18,student_age,intcolumn_info=1,19,student_addr,varchar,128column_info=1,20,cplusplus,intcolumn_info=1,21,oceanbase,intcolumn_info=1,22,english,intjoin=rowkey[7,15]%score:cplusplus$cplusplus,oceanbase$oceanbase,english$englishrowkey_max_length=16rowkey_split=8[score]table_id=1002table_type=1max_column_id=18#rowkey=student id(8bytes)rowkey_is_fixed_length=1column_info=1,2,gm_create,create_timecolumn_info=1,3,gm_modified,modify_timecolumn_info=1,16,cplusplus,intcolumn_info=1,17,oceanbase,intcolumn_info=1,18,english,introwkey_max_length=8rowkey_split=0

這裡一共有兩張表，student和score,student表冗餘了score表的一些欄位，有join關係。

OceanBase目前只支援java client，原始碼在svn中可以下載。用戶端需要知道叢集rootserver的地址和連接埠。

0. 關於Rowkey

在OceanBase中資料是按行儲存的，每行由rowkey唯一標識，rowkey是binary stream形式，OceanBase不會對其進行解釋。 rowkey可以由多段組成，應用對其進行解釋，比如上兩張表的rowkey為：

//student的rowkey由二個部份組成，第一部份為班級id,第三部份為學生id。//該rowkey為定長16位元組//在查詢的時候可以只給出班級id,表示尋找該班級下的所有學生。class StudentRowkey extends Rowkey {public StudentRowkey(long classid,long student_id) {    byteBuffer.putLong(classid);    byteBuffer.putLong(student_id);}    final ByteBuffer byteBuffer = ByteBuffer.allocate(16);    public byte[] getBytes() {    return byteBuffer.array();    }};//score的rowkey為student id，定長8位元組class ScoreRowkey extends Rowkey {public ScoreRowkey(byte item_type,long item_id) {    byteBuffer.put(item_type);    byteBuffer.putLong(item_id);}    final ByteBuffer byteBuffer = ByteBuffer.allocate(8);    public byte[] getBytes() {    return byteBuffer.array();    }}

1. 初始化：

private ClientImpl client;client = new ClientImpl();client.setIp(ip); //the ip of the rootserverclient.setPort(port); //the port of the rootserverclient.setTimeout(2000); //逾時時間，單位毫秒client.init();

2. 寫入

在目前的版本中，OB的update和insert語義是相同的。更新的時候需要提供表名、rowkey、以及各列的值。

final private static String STUDENT_TABLE_NAME="student";final private static String STUDENT_NAME="student_name";final private static String STUDENT_AGE="student_age";final private static String STUDENT_SEX="student_sex";final private static String STUDENT_ADDR="student_addr";final private static String SCORE_CPLUSPLUS="cplusplus";final private static String SCORE_OCEANBASE="oceanbase";final private static String SCORE_ENGLISH="english";/** * for OB semantics , update & insert is identical * DB semantics is not support yet. */void UpdateDemo() {List<ObMutatorBase> mutatorList = new ArrayList<ObMutatorBase>();                for (Long i = 0L; i < 100; i++) {                      UpdateMutator mutator = new UpdateMutator(STUDENT_TABLE_NAME, new StudentRowkey(0, i));                      mutator.addColumn(USER_NICK, new  Value() {{ setString("YOUR_VALUE"); }},false);                      mutator.addColumn(STUDENT_SEX, new Value() {{setNumber(1L);}},false);                      mutator.addColumn(STUDENT_AGE, new Value() {{setNumber(10L);}},false);                      mutator.addColumn(STUDENT_ADDR, new Value() {{setString("ADDR");}},false);                      mutatorList.add(mutator);      //分數只能在分數表中更新                }                Result<Boolean> ret = client.update(mutatorList);                if (ret == null || !ret.getResult()){             System.err.println("update failed");                }}        void InsertDemo() {            for (Long i = 0L; i < 100; i++) {                InsertMutator mutator = new InsertMutator(STUDENT_TABLE_NAME, new StudentRowkey(i, (byte)0, i));                mutator.addColumn(USER_NICK, new  Value() {{ setString("YOUR_VALUE"); }},false);                mutator.addColumn(STUDENT_SEX, new Value() {{setNumber(1L);}},false);                mutator.addColumn(STUDENT_AGE, new Value() {{setNumber(10L);}},false);                mutator.addColumn(STUDENT_ADDR, new Value() {{setString("ADDR");}},false);//類似update,insert也可以做批量插入                Result<Boolean> ret = client.insert(mutator);                if (ret == null || !ret.getResult()){                System.err.println("update failed " + ret.getCode());                }            }}

3. 查詢

查詢分為get和scan,get是指定rowkey進行查詢，而scan是範圍查詢。

void queryDemo() {QueryInfo query_info = new QueryInfo();query_info.setStartKey(new StudentRowkey(0L, 0L));query_info.setEndKey(new StudentRowkey(0L,100L));query_info.addColumn(STUDENT_NAME);query_info.addColumn(STUDENT_SEX);Result<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);System.out.println("get " + result.getResult().size() + "items");}void getDemo() {Set<String> columns = new HashSet<String>();columns.add(STUDENT_NAME);columns.add(STUDENT_SEX);Result<RowData> ret = client.get(STUDENT_TABLE_NAME, new StudentRowkey(0L,1L), columns);if (ret == null) {System.err.println("get failed ");} else {System.err.println("get " + ret.getResult().get(STUDENT_NAME));}}

4. orderby & where

void queryDemoWhere() {QueryInfo query_info = new QueryInfo();query_info.setStartKey(new StudentRowkey(0L,0L));query_info.setEndKey(new StudentRowkey(0L,100L));query_info.addColumn(STUDENT_NAME);query_info.addColumn(STUDENT_SEX);ObSimpleFilter filter = new ObSimpleFilter();filter.addCondition(new ObSimpleCond(STUDENT_SEX,ObSimpleCond.ObLogicOperator.EQ,new Value() {{setNumber(0L);}}));query_info.setFilter(filter);Result<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);System.out.println("get" + result.getResult().size() + "items");}void queryDemoOrderby() {QueryInfo query_info = new QueryInfo();query_info.setStartKey(new StudentRowkey(0L, 0L));query_info.setEndKey(new StudentRowkey(0L, 100L));query_info.addColumn(STUDENT_NAME);query_info.addColumn(STUDENT_SEX);query_info.addOrderBy(STUDENT_SEX, true); //order: true -ASC false - DESCResult<List<RowData>> result = client.query(STUDENT_TABLE_NAME, query_info);System.out.println("get" + result.getResult().size() + "items");}