1. 添加 Maven 依赖
    1. <dependency>
    2. <groupId>com.alipay.oceanbase</groupId>
    3. <artifactId>oceanbase-client</artifactId>
    4. <version>1.0.9</version>
    5. </dependency>
    6. <dependency>
    7. <groupId>com.google.guava</groupId>
    8. <artifactId>guava</artifactId>
    9. <!--推荐版本18,经过测试14~18都可以-->
    10. <version>18.0</version>
    11. </dependency>
    1. 修改连接字符串

    连接串的前缀需要设置为 jdbc:oceanbase ,其他部分的使用方式与原生的 MySQL 使用方式保持一致。

    1. String url = "jdbc:oceanbase://192.168.1.101/TPCC?useUnicode=true&characterEncoding=utf-8";
    2. String username = "TPCC@obbmsql#obdemo";
    3. String password = "123456";
    4. Connection conn = null;
    5. try {
    6. Class.forName("com.alipay.oceanbase.obproxy.mysql.jdbc.Driver");
    7. conn = DriverManager.getConnection(url, username, password);
    8. PreparedStatement ps = conn.prepareStatement("select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;");
    9. ResultSet rs = ps.executeQuery();
    10. rs.next();
    11. System.out.println("sysdate is:" + rs.getString(1));
    12. rs.close();
    13. ps.close();
    14. } catch (Exception e) {
    15. e.printStackTrace();
    16. } finally {
    17. if (null != conn) {
    18. conn.close();
    19. }
    20. }

    注意事项:

    1. 目前驱动与服务端交互使用的是文本写协议部分,为了兼容 Oracle 文本协议的 SQL 语法,驱动在PreparedStatement 的 setTimestamp 类型中都会在前面增加 timestamp 的字面量,因此针对 PreparedStatement 模式下,timestamp 参数不支持字面量。
    2. ServerPreparedStatement支持还不完善,因此请不要设置 useServerPrepStmts 和 cachePrepStmts 参数。
    3. 不支持 Oracle 的连接参数,需要和 MySQL 的保持一致。如 oracle.jdbc.ReadTimeout 需要修改成 socketTimeout,oracle.net.CONNECT_TIMEOUT 需要修改成 connectTimeout。
    4. 仅支持 Oracle 的错误码,不支持 Oracle 的 Java 异常类。
    5. 对于 Druid 框架,如果没有使用 DriverManager,需要直接指定 DrvierClass,如下:
    1. <property name"driverClassName" value="com.alipay.oceanbase.obproxy.mysql.jdbc.Driver">
    1. 由于目前开源连接池并未原生支持 OB Oracle 驱动,在使用开源连接池时需要特别注意加上ExceptionSorter。以下是以 Druid 连接池为例,需要定制 OracleExceptionSorter,请参考代码:
    1. import com.alibaba.druid.pool.vendor.OracleExceptionSorter;
    2. import com.alibaba.druid.support.logging.Log;
    3. import com.alibaba.druid.support.logging.LogFactory;
    4. import java.sql.SQLException;
    5. import java.sql.SQLRecoverableException;
    6. import java.util.HashSet;
    7. import java.util.Properties;
    8. import java.util.Set;
    9. public class ObOracleExceptionSorter extends OracleExceptionSorter {
    10. private final static Log LOG = LogFactory.getLog(OracleExceptionSorter.class);
    11. private static final long serialVersionUID = -9146226891418913174L;
    12. private Set<Integer> fatalErrorCodes = new HashSet<Integer>();
    13. public ObOracleExceptionSorter(){
    14. configFromProperties(System.getProperties());
    15. }
    16. public void configFromProperties(Properties properties) {
    17. String property = properties.getProperty("druid.oracle.fatalErrorCodes");
    18. if (property != null) {
    19. String[] items = property.split("\\,");
    20. for (String item : items) {
    21. if (item != null && item.length() > 0) {
    22. try {
    23. int code = Integer.parseInt(item);
    24. fatalErrorCodes.add(code);
    25. } catch (NumberFormatException e) {
    26. LOG.error("parse druid.oracle.fatalErrorCodes error", e);
    27. }
    28. }
    29. }
    30. }
    31. }
    32. public Set<Integer> getFatalErrorCodes() {
    33. return fatalErrorCodes;
    34. }
    35. public void setFatalErrorCodes(Set<Integer> fatalErrorCodes) {
    36. this.fatalErrorCodes = fatalErrorCodes;
    37. }
    38. @Override
    39. public boolean isExceptionFatal(SQLException e) {
    40. if (e instanceof SQLRecoverableException) {
    41. return true;
    42. }
    43. final String sqlState = e.getSQLState();
    44. final int error_code = Math.abs(e.getErrorCode()); // I can't remember if the errors are negative or positive.
    45. if (sqlState != null && sqlState.startsWith("08")) {
    46. return true;
    47. }
    48. switch (error_code) {
    49. case 28: // your session has been killed
    50. case 600: // Internal oracle error
    51. case 1012: // not logged on
    52. case 1014: // Oracle shutdown in progress
    53. case 1033: // Oracle initialization or shutdown in progress
    54. case 1034: // Oracle not available
    55. case 1035: // ORACLE only available to users with RESTRICTED SESSION privilege
    56. case 1089: // immediate shutdown in progress - no operations are permitted
    57. case 1090: // shutdown in progress - connection is not permitted
    58. case 1092: // ORACLE instance terminated. Disconnection forced
    59. case 1094: // ALTER DATABASE CLOSE in progress. Connections not permitted
    60. case 2396: // exceeded maximum idle time, please connect again
    61. case 3106: // fatal two-task communication protocol error
    62. case 3111: // break received on communication channel
    63. case 3113: // end-of-file on communication channel
    64. case 3114: // not connected to ORACLE
    65. case 3134: // Connections to this server version are no longer supported.
    66. case 3135: // connection lost contact
    67. case 3136: // inbound connection timed out
    68. case 3138: // Connection terminated due to security policy violation
    69. case 3142: // Connection was lost for the specified session and serial number. This is either due to session
    70. // being killed or network problems.
    71. case 3143: // Connection was lost for the specified process ID and thread ID. This is either due to session
    72. // being killed or network problems.
    73. case 3144: // Connection was lost for the specified process ID. This is either due to session being killed
    74. // or network problems.
    75. case 3145: // I/O streaming direction error
    76. case 3149: // Invalid Oracle error code, Cause: An invalid Oracle error code was received by the server.
    77. case 6801: // TLI Driver: listen for SPX server reconnect failed
    78. case 6802: // TLI Driver: could not open the /etc/netware/yellowpages file
    79. case 6805: // TLI Driver: could not send datagram SAP packet for SPX
    80. case 9918: // Unable to get user privileges from SQL*Net
    81. case 9920: // Unable to get sensitivity label from connection
    82. case 9921: // Unable to get information label from connection
    83. // TTC(Two-Task Common) ERROR CODE
    84. case 17001: // Internal Error
    85. case 17002: // Io exception
    86. case 17008: // Closed Connection
    87. case 17009: // Closed Statement
    88. case 17024: // No data read
    89. case 17089: // internal error
    90. case 17409: // invalid buffer length
    91. case 17401: // Protocol violation
    92. case 17410: // No more data to read from socket
    93. case 17416: // FATAl
    94. case 17438: // Internal - Unexpected value
    95. case 17442: // Refcursor value is invalid
    96. case 25407: // connection terminated
    97. case 25408: // can not safely replay call
    98. case 25409: // failover happened during the network operation,cannot continue
    99. case 25425: // connection lost during rollback
    100. case 29276: // transfer timeout
    101. case 30676: // socket read or write failed
    102. return true;
    103. default:
    104. if (error_code >= 12100 && error_code <= 12299) { // TNS issues
    105. return true;
    106. }
    107. break;
    108. }
    109. final String error_text = (e.getMessage()).toUpperCase();
    110. // Exclude oracle user defined error codes (20000 through 20999) from consideration when looking for
    111. // certain strings.
    112. if ((error_code < 20000 || error_code >= 21000)) {
    113. if ((error_text.contains("SOCKET")) // for control socket error
    114. || (error_text.contains("套接字")) // for control socket error
    115. || (error_text.contains("CONNECTION HAS ALREADY BEEN CLOSED")) //
    116. || (error_text.contains("BROKEN PIPE")) //
    117. || (error_text.contains("管道已结束")) //
    118. ) {
    119. return true;
    120. }
    121. }
    122. if (error_text.indexOf("COMMUNICATIONS LINK FAILURE") > -1
    123. || error_text.indexOf("COULD NOT CREATE CONNECTION") > -1
    124. || error_text.indexOf("ACCESS DENIED FOR USER") > -1
    125. || error_text.indexOf("NO DATASOURCE") > -1
    126. || error_text.indexOf("NO ALIVE DATASOURCE") > -1) {// errorCode忽略并且异常信息为连接出错
    127. return true;
    128. }
    129. return fatalErrorCodes.contains(error_code);
    130. }
    131. }