Java SDK API reference

obvec_jdbc is a Java SDK specifically designed for seekdb vector storage scenarios and JSON Table virtual table scenarios. This topic explains how to use obvec_jdbc.

Installation

You can install obvec_jdbc using either of the following methods.

Maven dependency

Add the obvec_jdbc dependency to the pom.xml file of your project.

<dependency>
  <groupId>com.oceanbase</groupId>
  <artifactId>obvec_jdbc</artifactId>
  <version>1.0.4</version>
</dependency>

Source code installation

Install obvec_jdbc.

# Clone the obvec_jdbc repository.
git clone https://github.com/oceanbase/obvec_jdbc.git
# Go to the obvec_jdbc directory.
cd obvec_jdbc
# Install obvec_jdbc.
mvn install

Add the dependency.

<dependency>
  <groupId>com.oceanbase</groupId>
  <artifactId>obvec_jdbc</artifactId>
  <version>1.0.4</version>
</dependency>

API definition and usage

obvec_jdbc provides the ObVecClient object for working with seekdb's vector search features and JSON Table virtual table functionalities.

Use vector search

Create a client

You can use the following interface definition to construct an ObVecClient object:

# uri: the connection string, which contains the address, port, and name of the database to which you want to connect.
# user: the username.
# password: the password.
public ObVecClient(String uri, String user, String password);

Here is an example:

import com.oceanbase.obvec_jdbc.ObVecClient;

String uri = "jdbc:oceanbase://127.0.0.1:2881/test";
String user = "root@test";
String password = "";
String tb_name = "JAVA_TEST";

ObVecClient ob = new ObVecClient(uri, user, password);

ObFieldSchema class

This class is used to define the column schema of a table. The constructor is as follows:

# name: the column name.
# dataType: the data type.
public ObFieldSchema(String name, DataType dataType);

The following table describes the data types supported by the class.

Data type	Description
BOOL	Equivalent to TINYINT
INT8	Equivalent to TINYINT
INT16	Equivalent to SMALLINT
INT32	Equivalent to INT
INT64	Equivalent to BIGINT
FLOAT	Equivalent to FLOAT
DOUBLE	Equivalent to DOUBLE
STRING	Equivalent to LONGTEXT
VARCHAR	Equivalent to VARCHAR
JSON	Equivalent to JSON
FLOAT_VECTOR	Equivalent to VECTOR

tip

For more complex types, constraints, and other functionalities, you can use seekdb JDBC's interface directly instead of using obvec_jdbc.

The interface is defined as follows:

API	Description
String getName()	Obtains the column name.
ObFieldSchema Name(String name)	Sets the column name and returns the object itself to support chain operations.
ObFieldSchema DataType(DataType dataType)	Sets the data type.
boolean getIsPrimary()	Specifies whether the column is the primary key.
ObFieldSchema IsPrimary(boolean isPrimary)	Specifies whether the column is the primary key.
ObFieldSchema IsAutoInc(boolean isAutoInc)	Specifies whether the column is auto-increment. Notice IsAutoInc takes effect only if IsPrimary is true.
ObFieldSchema IsNullable(boolean isNullable)	Specifies whether the column can contain NULL values. Notice IsNullable is set to false by default, which is different from the behavior in MySQL.
ObFieldSchema MaxLength(int maxLength)	Sets the maximum length for the VARCHAR data type.
ObFieldSchema Dim(int dim)	Sets the dimension for the VECTOR data type.

IndexParams/IndexParam

IndexParam is used to set a single index parameter. IndexParams is used to set a group of vector index parameters, which is used when multiple vector indexes are created on a table.

tip

obvec_jdbc supports only the creation of vector indexes. To create other indexes, use seekdb JDBC.

The constructor of IndexParam is as follows:

# vidx_name: the index name.
# vector_field_name: the name of the vector column.
public IndexParam(String vidx_name, String vector_field_name);

The interface is defined as follows:

API	Description
IndexParam M(int m)	Sets the maximum number of neighbors for each vector in the HNSW algorithm.
IndexParam EfConstruction(int ef_construction)	Sets the maximum number of candidate vectors for search during the construction of the HNSW algorithm.
IndexParam EfSearch(int ef_search)	Sets the maximum number of candidate vectors for search in the HNSW algorithm.
IndexParam Lib(String lib)	Sets the type of the vector library.
IndexParam MetricType(String metric_type)	Sets the type of the vector distance function.

The constructor of IndexParams is as follows:

public IndexParams();

The interface is defined as follows:

API	Description
void addIndex(IndexParam index_param)	Adds an index definition.

ObCollectionSchema class

When creating a table, you need to rely on the configuration of the ObCollectionSchema object. Below are its constructors and interfaces.

The constructor of ObCollectionSchema is as follows:

public ObCollectionSchema();

The interface is defined as follows:

API	Description
void addField(ObFieldSchema field)	Adds a column definition.
void setIndexParams(IndexParams index_params)	Sets the vector index parameters of the table.

Drop a table

The constructor is as follows:

# table_name: the name of the target table.
public void dropCollection(String table_name);

Check whether a table exists

The constructor is as follows:

# table_name: the name of the target table.
public boolean hasCollection(String table_name);

Create a table

The constructor is as follows:

# table_name: the name of the table to be created.
# collection: an ObCollectionSchema object that specifies the schema of the table.
public void createCollection(String table_name, ObCollectionSchema collection);

You can use ObFieldSchema, ObCollectionSchema, and IndexParams to create a table. Here is an example:

import com.oceanbase.obvec_jdbc.DataType;
import com.oceanbase.obvec_jdbc.ObCollectionSchema;
import com.oceanbase.obvec_jdbc.ObFieldSchema;
import com.oceanbase.obvec_jdbc.IndexParam;
import com.oceanbase.obvec_jdbc.IndexParams;

# Define the schema of the table.
ObCollectionSchema collectionSchema = new ObCollectionSchema();
ObFieldSchema c1_field = new ObFieldSchema("c1", DataType.INT32);
c1_field.IsPrimary(true).IsAutoInc(true);
ObFieldSchema c2_field = new ObFieldSchema("c2", DataType.FLOAT_VECTOR);
c2_field.Dim(3).IsNullable(false);
ObFieldSchema c3_field = new ObFieldSchema("c3", DataType.JSON);
c3_field.IsNullable(true);
collectionSchema.addField(c1_field);
collectionSchema.addField(c2_field);
collectionSchema.addField(c3_field);

# Define the index.
IndexParams index_params = new IndexParams();
IndexParam index_param = new IndexParam("vidx1", "c2");
index_params.addIndex(index_param);
collectionSchema.setIndexParams(index_params);

ob.createCollection(tb_name, collectionSchema);

Create a vector index after table creation

The constructor is as follows:

# table_name: the name of the table.
# index_param: an IndexParam object that specifies the vector index parameters of the table.
public void createIndex(String table_name, IndexParam index_param)

Insert data

The constructor is as follows:

# table_name: the name of the target table.
# column_names: an array of column names in the target table.
# rows: the data rows. ArrayList<Sqlizable[]>, each row is an Sqlizable array. Sqlizable is a wrapper class that converts Java data types to SQL data types.
public void insert(String table_name, String[] column_names, ArrayList<Sqlizable[]> rows);

The supported data types for rows include:

SqlInteger: wraps integer data.
SqlFloat: wraps floating-point data.
SqlDouble: wraps double-precision data.
SqlText: wraps string data.
SqlVector: wraps vector data.

Here is an example:

import com.oceanbase.obvec_jdbc.SqlInteger;
import com.oceanbase.obvec_jdbc.SqlText;
import com.oceanbase.obvec_jdbc.SqlVector;
import com.oceanbase.obvec_jdbc.Sqlizable;

ArrayList<Sqlizable[]> insert_rows = new ArrayList<>();
Sqlizable[] ir1 = { new SqlVector(new float[] {1.0f, 2.0f, 3.0f}), new SqlText("{\"doc\": \"oceanbase doc 1\"}") };
insert_rows.add(ir1);
Sqlizable[] ir2 = { new SqlVector(new float[] {1.1f, 2.2f, 3.3f}), new SqlText("{\"doc\": \"oceanbase doc 2\"}") };
insert_rows.add(ir2);
Sqlizable[] ir3 = { new SqlVector(new float[] {0f, 0f, 0f}), new SqlText("{\"doc\": \"oceanbase doc 3\"}") };
insert_rows.add(ir3);
ob.insert(tb_name, new String[] {"c2", "c3"}, insert_rows);

Delete data

The constructor is as follows:

# table_name: the name of the target table.
# primary_key_name: the name of the primary key column.
# primary_keys: an array of primary key column values for the target rows.
public void delete(String table_name, String primary_key_name, ArrayList<Sqlizable> primary_keys);

Here is an example:

ArrayList<Sqlizable> ids = new ArrayList<>();
ids.add(new SqlInteger(2));
ids.add(new SqlInteger(1));
ob.delete(tb_name, "c1", ids);

ANN queries

The constructor is as follows:

# table_name: the name of the target table.
# vec_col_name: the name of the vector column.
# metric_type: the type of the vector distance function. l2: corresponds to the L2 distance function. cosine: corresponds to the cosine distance function. ip: corresponds to the negative inner product distance function.
# qv: the vector value to be queried.
# topk: the number of the most similar results to be returned.
# output_fields: the projected columns, that is, the array of the fields to be returned.
# output_datatypes: the data types of the projected columns, that is, the data types of the fields to be returned, for direct conversion to Java data types.
# where_expr: the WHERE condition expression.
public ArrayList<HashMap<String, Sqlizable>> query(
      String table_name,
      String vec_col_name,
      String metric_type,
      float[] qv,
      int topk,
      String[] output_fields,
      DataType[] output_datatypes,
      String where_expr);

Here is an example:

ArrayList<HashMap<String, Sqlizable>> res = ob.query(tb_name, "c2", "l2", 
            new float[] {0f, 0f, 0f}, 10,
            new String[] {"c1", "c3", "c2"},
            new DataType[] {
            DataType.INT32,
            DataType.JSON,
            DataType.FLOAT_VECTOR,
            "c1 > 0"});
if (res != null) {
    for (int i = 0; i < res.size(); i++) {
        for (HashMap.Entry<String, Sqlizable> entry : res.get(i).entrySet()) {
            System.out.printf("%s : %s, ", entry.getKey(), entry.getValue().toString());
        }
        System.out.print("\n");
    }
} else {
    System.out.println("res is null");
}

Use the JSON table feature

The JSON table feature of obvec_jdbc relies on seekdb's ability to handle JSON data types (including JSON_VALUE/JSON_TABLE/JSON_REPLACE, etc.) to implement a virtual table mechanism. Multiple users (distinguished by user ID) can perform DDL or DML operations on virtual tables over the same physical table while ensuring data isolation between users. Admin users can perform DDL operations, while regular users can perform DML operations.

This design combines the structured management capabilities of relational databases with the flexibility of JSON, showcasing seekdb's multi-model integration capabilities. Users can enjoy the power and ease of use of SQL while also handling semi-structured data, meeting the diverse data model requirements of modern applications. Although operations are still performed on "tables," data is stored in a more flexible JSON format at the underlying level, better supporting complex and varied application scenarios.

How it works

User operations: Users still interact with the system using familiar standard SQL statements (such as CREATE TABLE to create table structures, INSERT to insert data, and SELECT to query data). They do not need to worry about how data is stored at the underlying level, just like operating ordinary relational database tables. The tables created by users using SQL statements are logical tables, which correspond to two physical tables (meta_json_t and data_json_t) within seekdb.
JSON Table SDK: Within the application, there is a JSON Table SDK (Software Development Kit). This SDK is the key that connects users' SQL operations and seekdb's actual storage. When SQL statements are executed, the SDK intercepts these requests and intelligently converts them into read and write operations on seekdb's internal tables meta_json_t and data_json_t.
seekdb internal storage:
- meta_json_t (stores table schema): stores the metadata of the logical tables created by users, which is the schema information of the table (for example, which columns are created and what data type each column is). When CREATE TABLE is executed, the SDK records this schema information in meta_json_t.
- data_json_t (stores row data as JSON type): stores the actual inserted data. Unlike traditional relational databases that directly store row data, the JSON Table feature encapsulates each row of inserted data into a JSON object and stores it in a column of the data_json_t table. This allows for efficient storage even with flexible data structures.
Data query: When query operations such as SELECT are executed, the SDK reads JSON-format data from data_json_t and combines it with the schema information from meta_json_t to re-parse and present the JSON data in a familiar tabular format, returning it to your application.

The meta_json_t table stores the metadata of the JSON table, which is the logical table schema defined by the user using the CREATE TABLE statement. It records the column information of each logical table, with the following schema:

Field	Description	Example
`user_id`	The user ID, used to distinguish the logical tables of different users.	`0`, `1`, `2`
`jtable_name`	The name of the logical table.	`test_count`
`jcol_id`	The column ID of the logical table.	`1`, `2`, `3`
`jcol_name`	The column name of the logical table.	`c1`, `c2`, `c3`
`jcol_type`	The data type of the column.	`INT`, `VARCHAR(124)`, `DECIMAL(10,2)`
`jcol_nullable`	Indicates whether the column allows null values.	`0`, `1`
`jcol_has_default`	Indicates whether the column has a default value.	`0`, `1`
`jcol_default`	The default value of the column.	`{'default': null}`

When a user executes the CREATE TABLE statement, the JSON table SDK parses and inserts the column definition information into the meta_json_t table.

The data_json_t table stores the actual data of the JSON table, which is the data inserted by the user using the INSERT statement. It records the row data of each logical table, with the following schema:

Field	Description	Example
`user_id`	The user ID, used to distinguish the logical tables of different users.	`0`, `1`, `2`
`admin_id`	The administrator user ID.	`0`
`jtable_name`	The name of the logical table, used to associate the metadata in `meta_json_t`.	`test_count`
`jdata_id`	The data ID, a unique identifier for the JSON data, corresponding to each row in the logical table.	`1`, `2`, `3`
`jdata`	A column of the JSON type, used to store the actual row data of the logical table.	`{"c1": 1, "c2": "test", "c3": 1.23}`

Examples

Create a client

The constructor is as follows:

# uri: the connection string, which contains the address, port, and name of the database to which you want to connect.
# user: the username.
# password: the password.
# user_id: the user ID.
# log_level: the log level.
public ObVecJsonClient(String uri, String user, String password, String user_id, Level log_level);

Here is an example:

import com.oceanbase.obvec_jdbc.ObVecJsonClient;

String uri = "jdbc:oceanbase://127.0.0.1:2881/test";
String user = "root@test";
String password = "";
ObVecJsonClient client = new ObVecJsonClient(uri, user, password, 0, Level.INFO);

Execute DDL statements

You can directly call the parseJsonTableSQL2NormalSQL interface and pass in the specific SQL statements.

Create a table

String sql = "CREATE TABLE `t2` (c1 INT NOT NULL DEFAULT 10, c2 VARCHAR(30) DEFAULT 'ca', c3 VARCHAR NOT NULL, c4 DECIMAL(10, 2), c5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";
client.parseJsonTableSQL2NormalSQL(sql);

ALTER TABLE CHANGE COLUMN

sql = "ALTER TABLE t2 CHANGE COLUMN c2 changed_col INT";
client.parseJsonTableSQL2NormalSQL(sql);

ALTER TABLE ADD COLUMN

sql = "ALTER TABLE t2 ADD COLUMN email VARCHAR(100) default 'example@example.com'";
client.parseJsonTableSQL2NormalSQL(sql);

ALTER TABLE MODIFY COLUMN

sql = "ALTER TABLE t2 MODIFY COLUMN changed_col TIMESTAMP NOT NULL DEFAULT current_timestamp";
client.parseJsonTableSQL2NormalSQL(sql);

ALTER TABLE DROP COLUMN

sql = "ALTER TABLE t2 DROP c1";
client.parseJsonTableSQL2NormalSQL(sql);

ALTER TABLE RENAME

sql = "ALTER TABLE t2 RENAME TO alter_test";
client.parseJsonTableSQL2NormalSQL(sql);

Installation​

Maven dependency​

Source code installation​

API definition and usage​

Use vector search​

Create a client​

ObFieldSchema class​

Notice

Notice

IndexParams/IndexParam​

ObCollectionSchema class​

Drop a table​

Check whether a table exists​

Create a table​

Create a vector index after table creation​

Insert data​

Delete data​

ANN queries​

Use the JSON table feature​

How it works​

Examples​

Contents

Installation

Maven dependency

Source code installation

API definition and usage

Use vector search

Create a client

ObFieldSchema class

IndexParams/IndexParam

ObCollectionSchema class

Drop a table

Check whether a table exists

Create a table

Create a vector index after table creation

Insert data

Delete data

ANN queries

Use the JSON table feature

How it works

Examples