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 features:
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 occur when a database connection is created or closed or when an SQL statement is 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. OBProxies and OBServers 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.
Notice
An OBServer determines whether OceanBase Database runs in MySQL or Oracle mode based on the tenant name that is used when the driver is connected to the database service. Tenants in Oracle mode can use only Oracle-compatible SQL syntax.
OceanBase Connector/J is compatible with standard JDBC APIs and Oracle Driver. OBServers in Oracle mode are compatible with most Oracle syntax.
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 packets transmission between the driver and the OBServer (ODP).
Based on the MySQL protocol, OBServers 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 OBServers. Then, OBServers return the execution results.
Binary protocol: The protocol uses the COM_STMT_PREPARE command to prepare SQL statements. An OBServer parses an SQL statement and returns parameter descriptions. The driver completes the parameter settings and returns the parameters to the OBServer.
If an OBServer uses the text protocol, the OBServer only executes SQL statements, without the need to prepare statements. If an OBServer uses the binary protocol, the OBServer 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.
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 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:
The OBServer proactively sends a handshake packet to the client. The packet carries the OBServer version, character set, and supported capabilities.
The client constructs a handshake response packet based on the information carried in the handshake packet, and sends the response packet to the OBServer for authentication.
After the authentication is passed, the OBServer 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 receives the handshake response packet, the OBServer 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 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 OBServer 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. Sample code:
set autocommit=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
set names utf8
Initialize local variables
OBJDBC stores some variable values to ensure consistency with the session of the OBServer, 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 and JDBC.
Sample code:
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.
Interface 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:
createStatement
prepareStatement
prepareCall
getMetaData
commit/rollback
Interface 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:
execute
executeQuery
setQueryTimeout
addBatch
executeBatch
setFetchSize
Interface 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. This way, the processing efficiency is improved.
Commonly used methods of the PrepareStatement interface:
addBatch
executeBatch
setFetchSize
Interface 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:
registerOutParameter
getXXX
Interface 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 start 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 statement, 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:
next absolute previous
getMetaData
Interface 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
VARCHAR2
NVARCHAR2
CHAR
NCHAR
Numeric value
NUMBER
NUMBER_FLOAT
BINARY_FLOAT
BINARY_DOUBLE
Date and time
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
Interval
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
LOB
CLOBs
BLOB
Complex data type
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 during the Execute process. If a checksum error occurs, the OBServer 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 OceanBase Connector/J.