This topic describes how to connect to OceanBase Database in the Go client and use the database in the client by calling specific APIs.
Connect to OceanBase Database
You can use the Go client to connect to OceanBase Database. For more information, see Connect to OceanBase Database by using the Go client.
Execute the CREATE TABLE statement to create a table.
CREATE TABLE IF NOT EXISTS `test_varchar_table_00` (
`c1` varchar(20) NOT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` varchar(20) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
insert
You can call this API to insert a row. If a primary key conflict occurs, which means that the target row already exists, the insertion fails.
Here is an example:
long insert(String tableName, Object rowkey, String[] columns, Object[] values);
long insert(String tableName, Object[] rowkeys, String[] columns, Object[] values);
// The rowkey in the sample code corresponds to the primary key of the table.
client.insert( "testHash",
new Object[] { 1L, "partition".getBytes(), timeStamp },
new String[] { "V" },
new Object[] { "value".getBytes() }
);
The parameters are described as follows:
tableName: the name of the table.
rowkey: the value of the primary key.
columns: the names of columns to be inserted. You can insert one or more columns.
values: the column values to be inserted.
long: the return value that indicates the number of inserted columns. In this example, 1 row is inserted.
get
You can call this API to retrieve a row. If the target row exists, the row is retrieved. Otherwise, the result set is empty.
Here is an example:
Map<String, Object> get(String tableName, Object rowkey, String[] columns);
Map<String, Object> get(String tableName, Object[] rowkeys, String[] columns);
// rowkey is the primary key.
Map<String, Object> res = client.get("testHash",
new Object[] { 1L, "partition".getBytes(), timestamp },
new String[] { "K", "Q", "T","V" }
);
// If the returned value is an object, you can perform an explicit type conversion, such as Long k = (Long) res.get("K");
The parameters are described as follows:
tableName: the name of the table.
rowkey: the value of the primary key.
columns: the name of the column to be retrieved. You can retrieve one or more columns.
Map<String, Object>: the return value. If the target column exists, the column is retrieved. Otherwise, the result set is empty.
delete
You can call this API to delete a row. If the target row does not exist, affectrows = 0 is returned. Otherwise, the number of deleted rows, 1 row in this case, is returned.
Here is an example:
long delete(String tableName, Object rowkey);
long delete(String tableName, Object[] rowkeys);
// The rowkey in the sample code corresponds to the primary key of the table.
client.delete("testHash", new Object[] { 1L, "partition".getBytes(), timeStamp });
// The return value indicates the number of affected rows. In this example, the return value is 1L, which indicates one row.
The parameters are described as follows:
tableName: the name of the table.
rowkey: the primary key to be deleted.
long: the return value that indicates the number of deleted rows. In this sample, 1 row is deleted.
update
You can call this API to update a row. If the target row does not exist, affectrows = 0 is returned. Otherwise, the number of updated rows, 1 row in this case, is returned.
Here is an example:
long update(String tableName, Object rowkey, String[] columns, Object[] values);
long update(String tableName, Object[] rowkeys, String[] columns, Object[] values);
// The rowkey in the sample code corresponds to the primary key of the table.
client.update("testHash",
new Object[] { 1L, "partition".getBytes(), timeStamp },
new String[] { "V" },
new Object[] { "value1L".getBytes() }
);
// The return value indicates the number of affected rows. In this example, the return value is 1L, which indicates one row.
The parameters are described as follows:
tableName: the name of the table.
rowkey: the name of the updated primary key.
columns: the name of the column to be updated. You can update one or more columns.
values: the column values to be updated. You can specify the values of one or more columns.
long: the return value, which indicates the number of deleted rows. In this sample, 1 row is deleted.
replace
You can call this API to replace a row. The following three cases may exist:
If the target row does not exist, the new row is inserted.
If the target row already exists, which means a primary key conflict, the original row is deleted and the new row is inserted.
If a unique index conflict takes place, the conflicting rows are deleted and the new row is inserted.
Here is an example:
long replace(String tableName, Object rowkey, String[] columns, Object[] values);
long replace(String tableName, Object[] rowkeys, String[] columns, Object[] values);
// The rowkey in the sample code corresponds to the primary key of the table.
client.replace("testHash",
new Object[] { 1L, "partition".getBytes(), timeStamp },
new String[] { "V" },
new Object[] { "value1L".getBytes() }
);
// The return value indicates the number of affected rows. In this example, the return value is 1L, which indicates one row.
The parameters are described as follows:
tableName: the name of the table.
rowkey: the name of the primary key.
columns: the name of the column to be replaced. You can replace one or more columns.
values: the column values to be replaced. You can specify the values of one or more columns.
long: the return value that indicates the number of replaced rows. In this sample, 1 row is replaced, which indicates one row.
insertOrupdate
You can call this API to insert or update a row. The following three cases may exist:
If the target row does not exist, the new row is inserted.
If the target row already exists, which means a primary key conflict, the target row is updated.
If the insertion encounters a unique index conflict, the target row is also updated.
Here is an example:
long insertOrUpdate(String tableName, Object rowkey, String[] columns, Object[] values);
long insertOrUpdate(String tableName, Object[] rowkeys, String[] columns, Object[] values);
// The rowkey in the sample code corresponds to the primary key of the table.
client.insertOrUpdate("testHash",
new Object[] { 1L, "partition".getBytes(), timestamp },
new String[] { "V" },
new Object[] { "bb".getBytes() }
);
// The return value indicates the number of affected rows. In this example, the return value is 1L, which indicates one row.
The parameters are described as follows:
tableName: the name of the table.
rowkey: the name of the primary key.
columns: the name of the column to be operated. You can specify one or more columns.
values: the column values to be inserted or updated. You can specify the values of one or more columns.
long: the return value that indicates the number of inserted or updated rows. In this sample, 1 row is operated.
increment
You can call this API to atomically increment the value of the specified column by an increment value. The increment value can be negative. The following three cases may exist:
If the calculation result overflows the value range of the column type, an error is returned.
If the target row does not exist, the new row is inserted and the increment value is set as the initial value, which is equivalent to having an original value of 0.
If the target row exists but the specified column value is NULL, the increment value is set as the initial value, which is equivalent to having an original value of 0.
Here is an example:
Map<String, Object> increment(String tableName, Object rowkey, String[] columns,Object[] values, boolean withResult);
Map<String, Object> increment(String tableName, Object[] rowkeys, String[] columns, Object[] values, boolean withResult);
// The rowkey in the sample code corresponds to the primary key of the table. The default value is null. So, the final results are c2=1, c3=2.
Client.increment("test_increment",
"test_null",
new String[] { "c2", "c3" },
new Object[] { 1, 2 }, true );
The parameters are described as follows:
tableName: the name of the table.
rowkey: the value of the primary key.
columns: the name of the column whose value is to be incremented. You can specify one or more columns.
values: the value to be incremented. You can specify the values of one or more columns.
withResult: specifies whether to return the auto-increment result set.
Map<String, Object>: the return value. If withResult was set to false, the returned result is Map.empty.
append
You can call this API to atomically append a string to the value of the specified column. The following three cases may exist:
If the calculation result overflows the value range of the column type, an error is returned.
If the target row does not exist, the new row is inserted and the appended string is set as the initial value, which is equivalent to having an original value of an empty string.
If the target row exists but the specified column value is NULL, the appended string is set as the initial value, which is equivalent to having an original value of an empty string.
Here is an example:
Map<String, Object> append(String tableName, Object rowkey, String[] columns, Object[] values,boolean withResult);
Map<String, Object> append(String tableName, Object[] rowkeys, String[] columns, Object[] values, boolean withResult);
// Insert the row c2 = {1}, c3 = {"P"}.
client.insert("test_append", "test_normal", new String[] { "c2", "c3" }, new Object[] { new byte[] { 1 }, "P" });
// After the append operation, c2 = {1, 2}, c3 = {"PY"}.
Map<String, Object> res = client.append("test_append", "test_normal", new String[] { "c2", "c3" }, new Object[] { new byte[] { 2 }, "Y" }, true);
The parameters are described as follows:
tableName: the name of the table.
rowkey: the value of the primary key.
columns: the name of the column to which the string is to be appended. You can specify one or more columns.
values: the string to be appended. You can specify the strings for one or more columns.
withResult: specifies whether to return the result set of the appended string.
Map<String, Object>: the return value. If withResult was set to false, the returned result is Map.empty.
scan
You can call this API to perform a multi-range scan or reverse scan. This API supports range scan based on the primary key or the prefix of the primary key. When the primary key is not specified, this API also supports range scan based on the index or index prefix.
Here is an example:
// Example
TableQuery tableQuery = client.query("table_name"); // Create an object to be scanned based on the table name.
re"sultSet = tableQuery.select("column2").primaryIndex().addScanRange("min1", "max1).addScanRange("min2", "max2").setBatchSize(batch_size).execute();
while(resultSet.next()) {
Map<String, Object> value = resultSet.getRow();
}
The parameters are described as follows:
table_name: the name of the table to be scanned.
primaryIndex: specifies to scan based on the primary key.
column2: the column from which the results are retrieved. For example, a table contains columns 1 to 4, and the value column2 indicates that only the data of column2 is returned.
addScanRange: the range of the scan. min and max respectively indicate the minimum and maximum value of the range.
batch_size: the number of rows to be returned in each batch. Only the rows that meet the scan conditions are returned.
batch
You can call this API to collectively perform multiple operations.
Here is an example:
TableBatchOps batchOps = client.batch("test_varchar_table");
batchOps.insert("foo", new String[] { "c2" }, new String[] { "bar" });
batchOps.get("foo", new String[] { "c2" });
batchOps.delete("foo");
List<Object> results = batchOps.execute();
// In the list of results, the insert operation returns affectedRows, the get operation returns map, and the delete operation returns affectedRows.The parameters are described as follows:
TableBatchOps: the object to be operated on, which is created by using ObTableClient. In the preceding example, it is client.batch(table_name).
List<Object>: the return values. The result set includes the results of all operations, such as insert, get, and delete.