OceanBase Connector/J

OceanBase Database uses OceanBase Connector/J to provide connections for client applications that are developed in Java. Java Database Connectivity (JDBC) is an API that provides a Java standard and can be used to connect a Java application to a relational database. OceanBase Connector/J is a driver that can be used to call the JDBC API.

The JDBC standard was defined by Sun Microsystems and is implemented by the standard java.SQL API. Developers can implement and extend the standard by using their JDBC drivers. JDBC is also a Call Level Interface (CLI) based on X/Open SQL.

Features

OceanBase Connector/J provides the following main components:

• DriverManager: loads the driver and returns the corresponding connections based on requests.

• Driver: handles requests and returns the corresponding connections. The driver is loaded to DriverManager.

• Connection: communicates with a database, provides a connection environment for executing SQL statements and handling transactions, and creates and executes statements.

• Statement:

  • Statements: performs a single SQL query or update operation.

  • PreparedStatement: executes cached statements with predetermined execution paths. Cached statements can be repeatedly executed to improve execution efficiency.

  • CallableStatement: executes stored procedures in a database.

• SQLException: displays errors that occurred when a database connection was created or closed or when an SQL statement was executed.

OceanBase Connector/J as a driver

OceanBase Connector/J is a JDBC Type 4 driver that can be connected to a database engine by using local protocols. OceanBase Database Proxy (ODP) and OBServer nodes support OceanBase Connector/J and are fully compatible with MySQL Connector Java, the native JDBC driver for MySQL. OceanBase Connector/J is fully compatible with MySQL JDBC. OceanBase Connector/J automatically determines whether OceanBase Database runs in MySQL or Oracle mode and supports both modes at the protocol layer.

OceanBase Connection/J is compatible with the OB2.0 protocol.

OceanBase Connector/J is compatible with standard JDBC APIs and Oracle drivers. OBServer nodes in Oracle mode are compatible with most Oracle syntaxes.

MySQL protocol

The MySQL protocol is used to connect the MySQL driver and the MySQL server. The protocol provides identical serialization and deserialization regulations to allow each data sender and receiver to accurately and efficiently make and receive remote procedure calls (RPCs). Wireshark supports decoding based on the MySQL protocol and can capture packets with ease.

The preceding figure clearly shows the interaction process and packet transmission between the driver and the relevant OBServer node or ODP.

Based on the MySQL protocol, OBServer nodes are compatible with MySQL JDBC and other MySQL protocol-based drivers, such as MariaDB JDBC. OBJDBC, which is developed based on MySQL JDBC and MariaDB JDBC, also supports the Oracle mode in OceanBase Database.

Text protocol and binary protocol

The MySQL protocol is related to text protocols and binary protocols.

• Text protocol: All actions of JDBC are converted into standard SQL statements, which are sent as strings to OBServer nodes. Then, OBServer nodes return the execution results.

• Binary protocol: The protocol uses the COM_STMT_PREPARE command to prepare SQL statements. An OBServer node parses an SQL statement and returns parameter descriptions. The driver completes the parameter settings and returns the parameters to the OBServer node.

If an OBServer node uses the text protocol, the OBServer node only executes SQL statements, without the need to prepare statements. If an OBServer node uses the binary protocol, the OBServer node can execute an SQL statement multiple times after the statement is prepared.

The useServerPrepStmts parameter specifies the protocol type. If the parameter is set to False, the text protocol is used. If the parameter is set to True, the binary protocol is used.

Here is an example:

PreparedStatement ps = conn.prepareStatement("select * from selecttest");
ResultSet rs = ps.executeQuery();

The operation to be performed for the preceding code varies based on the value of the useServerPrepStmts parameter.

Request Prepare Statement
Response
Request Execute Statement
Response

Based on the preceding packet data that is captured, if useServerPrepStmts is set to true, an SQL statement is prepared and executed. If useServerPrepStmts is set to false, an SQL statement is executed.

Request Query {select * from selecttest}
Response

Differences among the versions of OBJDBC

• 1.x is developed based on MySQL JDBC. The open source protocol used is GPL and the required JDK version is 7 to 8.

• 2.x is developed based on MariaDB JDBC. The open source protocol used is LGPL and the required JDK version is 1.8.

Implementation of the JDBC standard

Connections

Establish a connection

OceanBase Database distinguishes the Oracle mode from MySQL mode by using database tenants. The MySQL mode is similar to MySQL Server. A client can be directly connected to an OBServer node by using MySQL JDBC. The following information describes only the Oracle mode.

The process of establishing a MySQL connection is called a handshake. The following list describes the process after a TCP connection is established at the underlying layer:

1. The OBServer node proactively sends a handshake packet to the client. The packet carries the version, character set, and supported capabilities of the OBServer node.

2. The client constructs a handshake response packet based on the information carried in the handshake packet, and sends the response packet to the OBServer node for authentication.

3. After the authentication is passed, the OBServer node returns an OK packet to the client. The connection is established.

The OK packet carries the two-byte status flags, which indicate the status of the current connection. The status flags contain 16 bits, and each bit indicates a capability.

In the handshake response packet, the four-byte capability flags describe the capabilities supported by the client. The capability flags contain 32 bits, and each bit indicates a capability. Currently, the first 26 bits are used in MySQL. In OceanBase Database, the 27th bit indicates whether the client supports the Oracle mode. The following code shows the detailed information:

const (
 clientLongPassword clientFlag = 1 << iota
 clientFoundRows
 clientLongFlag
 clientConnectWithDB
 clientNoSchema
 clientCompress
 clientODBC
 clientLocalFiles
 clientIgnoreSpace
 clientProtocol41
 clientInteractive
 clientSSL
 clientIgnoreSIGPIPE
 clientTransactions
 clientReserved
 clientSecureConn
 clientMultiStatements
 clientMultiResults
 clientPSMultiResults
 clientPluginAuth
 clientConnectAttrs
 clientPluginAuthLenEncClientData
 clientCanHandleExpiredPasswords
 clientSessionTrack
 clientDeprecateEOF
 clientSupportOracleMode = 1 << 27
)

After the OBServer node receives the handshake response packet, the OBServer node parses the tenant information from the username. If the tenant is an Oracle tenant and the 27th bit of the capability flags is 1, the OBServer node returns an OK packet to the client. Otherwise, an error packet is returned to the client, carrying the error message “Oracle tenant for current client driver is not supported”. In the returned OK packet, the two-byte status flags indicate the status of the current connection. The status flags contain 16 bits, and the third bit is not used in MySQL. OceanBase Database reuses this bit to indicate whether the current connection is in Oracle mode.

Initialize session variables

After a session is established between OBJDBC and an OBServer node or ODP, variables are initialized. Initialized variable values are obtained from the default configuration and can be overwritten by the configuration in the URL.

In most cases, an SQL statement is executed to initialize variables. The sample code is as follows:

set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
set names utf8

Initialize local variables

OBJDBC stores values of some variables to ensure consistency with the session of the OBServer node, such as ReadOnly and txlsolation. After session variables are initialized, the variable values are retrieved to initialize local variables. When an API is called, variable values are modified to ensure information consistency between the OBServer node and JDBC.

Here is an example:

SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment,@@tx_isolation AS tx_isolation,@@session.tx_read_only AS tx_read_only from dual
select count(*) from V$TIMEZONE_NAMES // This statement is special and is used to check whether the server has an imported time zone table.

Connection

Connection is the most basic interface and is used to create objects and obtain information about connections. Commonly used methods of the Connection interface include the following ones:

• createStatement

• prepareStatement

• prepareCall

• getMetaData

• commit/rollback

Statement

Statement is an interface with sole features. This interface is used to execute SQL statements and query data. The way of using this interface is the same as that of using a command-line tool such as OBClient to execute SQL statements.

Commonly used methods of the Statement interface include the following ones:

• execute

• executeQuery

• setQueryTimeout

• addBatch

• executeBatch

• setFetchSize

PreparedStatement

PreparedStatement is one of the commonly used interfaces of JDBC and can implement all features of Statement. A PreparedSatement object represents a precompiled SQL statement. An SQL statement is precompiled and stored in a PreparedStatement object. An encapsulated SQL statement indicates a type of operation. The dynamic parameter “?” can be used in SQL statements. Values can be dynamically assigned to the parameter during statement execution.

In actual precompilation scenarios, you must set useServerPreparedStmts to true in url option. Otherwise, the text protocol takes effect instead of the binary protocol, even if you used the PrepareStatement interface.

The following sample code shows the syntax of the PrepareStatement interface:

ps = conn.prepareStatement("insert into pstest values(?,?)");
ps.setString(1,"string1");
ps.setInt(2,1);
ps.execute();
ps.setString(1,"string2");
ps.setInt(2,2);
ps.execute();

If your SQL statements are precompiled, you need only to call the execute method. In this way, the processing efficiency is improved.

Commonly used methods of the PrepareStatement interface include the following ones:

• addBatch

• executeBatch

• setFetchSize

CallableStatement

The CallableStatement interface is used to execute procedures and functions, set IN and OUT parameters, and obtain return values. CallableStatement is a subclass of PrepareStatement and can be used as PrepareStatement.

If CallableStatement is used to call the PrepareCall method of Connection, CallableStatement parses the corresponding SQL statement. To parse an SQL statement that is used to call a procedure or a function, CallableStatement uses a parsing tool to query the procedure or function name, obtains the IN or OUT type from all_argument by using the name, and then organizes the description of the procedure or function based on the obtained information.

Commonly used methods of the CallableStatement interface include the following ones:

• registerOutParameter

• getXXX

ResultSet

A ResultSet object is a data table that represents a database result set, which is usually generated by executing a statement that queries data from a database.

A ResultSet object maintains a cursor that points to a specific data row. The cursor initially points to the first row. After the next method is called, the cursor is moved to the next row. If no more rows exist in the ResultSet object, False is returned. Therefore, the next method can be used in a while loop to traverse the corresponding ResultSet object.

A default ResultSet object cannot be updated and has a cursor that moves only in a forward direction. In this case, you can traverse the object only once from the first row to the last row. However, a ResultSet object that can be scrolled or updated will be generated.

The ResultSet interface provides getter methods, such as GetBoolean and GetLong, that can be used to retrieve column values from the current row. Values can be retrieved by using column indexes or column names. We recommend that you use column indexes. Column indexes can be used to retrieve values in a more efficient manner than column names. Column indexes are numbered starting from 1. To achieve maximum portability, the result set columns in each row must be read from left to right, and each column must be read only once.

When the JDBC driver calls a getter method, the JDBC driver attempts to convert the underlying data into the Java type specified in the getter method and returns valid Java values.

Column names used as the input of getter methods are case-insensitive. If a getter method is called by using column names and several columns have the same name, the value of the first matching column is returned. If column names are used in the SQL statement that generated the result set, you can use the column name option. For columns that are not explicitly named in the query, we recommend that you use column numbers. If column names are used, the developer can use the AS clause to ensure that each name is referenced by only one column.

Commonly used methods of the ResultSet interface include the following ones:

• next absolute previous

• getMetaData

ResultSetMetaData

The ResultSetMetaData interface can be used to obtain information about the types and properties of columns in a ResultSet object. ResultSetMetaData contains various methods that can be used to obtain rich information.

Compatibility with Oracle

The compatibility of OceanBase Connector/J with Oracle involves two aspects: data types and features. The standard implementation described in the preceding sections also ensures that OceanBase Connector/J is compatible with Oracle. For example, the return values of the methods of ResultSetMetaData must be adjusted to ensure compatibility with specific data types of Oracle. You must use the checksum feature based on the original MySQL protocol. The status flags in the handshake packet are used as the Oracle mode flags.

Supported Oracle data types

The following data types of Oracle are supported by OceanBase Database:

• Character data types

  • VARCHAR2

  • NVARCHAR2

  • CHAR

  • NCHAR

• Numeric data types

  • NUMBER

  • NUMBER_FLOAT

  • BINARY_FLOAT

  • BINARY_DOUBLE

• Date and time data types

  • DATE

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL data types

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

• LOB data types

  • CLOB

  • BLOB

• Complex data types

  • Struct

  • Array

  • RefCursor

• RAW

• ROWID

Supported Oracle features

PS checksum

In Oracle mode, checksum verification by using PS is supported. You can use the UseServerPsStmtChecksum parameter to enable or disable this feature.

A checksum is generated based on OBCrc32C during the Prepare process, and is sent to an OBServer node during the Execute process. If a checksum error occurs, the OBServer node reports the error. OBCrc32C is a pure Java implementation of the CRC32 checksum algorithm and uses CRC32-C polynomials. The polynomials used in iSCSI are the same as those used for Intel chipsets that support SSE4.2.

More information

For more information about OceanBase Connector/J, see the OceanBase Connector/J documentation.