Download as PDF, PPTX
![Copyright © 2019 Oracle and/or its affiliates. Compatible with [Java 8] Java 9+ Supports MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 incl. X Protocol Connector/J 8.0 Compatible with [Java 5] Java 6 Java 7 [Java 8] Java 9+ Supports MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Connector/J 5.1 Side-by-side [Java n] – Required for compiling](https://image.slidesharecdn.com/connectorjfeaturereviewandupgrading-190924094406/75/MySQL-Connector-J-Feature-Review-and-How-to-Upgrade-from-Connector-J-5-1-11-2048.jpg)
![Copyright © 2019 Oracle and/or its affiliates. New scheme – mysqlx: – for X DevAPI sessions Authority section of connection strings (user and hosts information) supports multiple syntaxes: The {host} part can be written as: IPv6 addresses must be written between square brackets: [::1] Improved Connection Strings scheme://[usr[:pwd]@]{host},[usr[:pwd]@][{host},{host},...],.../db?key=value&... hostOrIP:portNumber address=(host=hostOrIP)(port=portNumber)(key=value)(...) (host=hostOrIP,port=portNumber,key=value,...) (address=hostOrIP:portNumber,key=value,...)](https://image.slidesharecdn.com/connectorjfeaturereviewandupgrading-190924094406/75/MySQL-Connector-J-Feature-Review-and-How-to-Upgrade-from-Connector-J-5-1-39-2048.jpg)
![Copyright © 2019 Oracle and/or its affiliates. com.mysql.jdbc.exceptions.jdbc4. CommunicationsException com.mysql.cj.jdbc.exceptions. CommunicationsException com.mysql.jdbc.exceptions.[jdbc4.] MySQLDataException java.sql.SQLDataException com.mysql.jdbc.exceptions.[jdbc4.] MySQLIntegrityConstraintViolationException java.sql. SQLIntegrityConstraintViolationException com.mysql.jdbc.exceptions.[jdbc4.] MySQLInvalidAuthorizationSpecException java.sql.SQLInvalidAuthorizationSpecException com.mysql.jdbc.exceptions.[jdbc4.] MySQLNonTransientConnectionException java.sql.SQLNonTransientConnectionException com.mysql.jdbc.exceptions.[jdbc4.] MySQLNonTransientException java.sql.SQLNonTransientException com.mysql.jdbc.exceptions.[jdbc4.] MySQLQueryInterruptedException com.mysql.cj.jdbc.exceptions. MySQLQueryInterruptedException Changes in Exceptions/2](https://image.slidesharecdn.com/connectorjfeaturereviewandupgrading-190924094406/75/MySQL-Connector-J-Feature-Review-and-How-to-Upgrade-from-Connector-J-5-1-45-2048.jpg)
![Copyright © 2019 Oracle and/or its affiliates. com.mysql.jdbc.exceptions. MySQLStatementCancelledException com.mysql.cj.jdbc.exceptions. MySQLStatementCancelledException com.mysql.jdbc.exceptions.[jdbc4.] MySQLSyntaxErrorException java.sql.SQLSyntaxErrorException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTimeoutException java.sql.SQLTimeoutException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransactionRollbackException java.sql.SQLTransactionRollbackException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransientConnectionException java.sql.SQLTransientConnectionException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransientException java.sql.SQLTransientException m.mysql.jdbc.exceptions.[jdbc4.] MySQLIntegrityConstraintViolationException java.sql. SQLIntegrityConstraintViolationException Changes in Exceptions/3](https://image.slidesharecdn.com/connectorjfeaturereviewandupgrading-190924094406/75/MySQL-Connector-J-Feature-Review-and-How-to-Upgrade-from-Connector-J-5-1-46-2048.jpg)
MySQL Connector/J Feature Review and How to Upgrade from Connector/J 5.1
- 1. Copyright © 2019Oracle and/or its affiliates.
- 2. Copyright © 2019Oracle and/or its affiliates. MySQL Connector/J 8.0 Feature Review and How to Upgrade from Connector/J 5.1 Connector/J Developer and Team Lead MySQL Middleware and Clients September 17, 2019 Filipe Silva
- 3. The following isintended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2019 Oracle and/or its affiliates.
- 4. Copyright © 2019Oracle and/or its affiliates. Filipe Silva Connector/J Developer and Team Lead MySQL Middleware and Clients September 19, 2019 filipe.silva@oracle.com
- 5. Feature Review // Topic:1 // Topic: 2 // Topic: 3 // Topic: 4 From Connector/J 5.1 to 8.0 Copyright © 2019 Oracle and/or its affiliates. MySQL Connector/J Overview Installing and Using Connector/J Features Showcase Multi-host Connections // Topic: 5 // Topic: 6 // Topic: 7 // Topic: 8 What changed? The Time Zones Conundrum X DevAPI Sneak Peek Closing Comments, Q & A
- 6. Copyright © 2019Oracle and/or its affiliates. Feature Review With more than two decades old, MySQL Connector/J is still MySQL’s flagship connector for Java programming language, now combining the traditional JDBC API and the new X DevAPI in one single driver.
- 7. Let’s get startedwith the basics Copyright © 2019 Oracle and/or its affiliates. MySQL Connector/J Overview
- 8. MySQL Connector/Jstory starts in 1998, by the crafty hands of Mark Matthews, it’s creator and lead developer for many years. Currently, MySQL develops and maintains two Connector/J versions – 8.0 and 5.1 Open source hosted in GitHub https://github.com/mysql/mysql-connector-j Binaries are available in MySQL downloads page and maven Central Repository Connector/J 8.0 is GA and the recommended version Copyright © 2019 Oracle and/or its affiliates. Some History and Facts
- 9. Available sinceApr. 2007 Type 4 JDBC driver Compliant with JDBC 4.2 (Java 8.0) Requires Java 5 or above Supports MySQL Protocol Compatible with MySQL 5.1 and above Copyright © 2019 Oracle and/or its affiliates. MySQL Connector/J 5.1
- 10. Available sinceAug. 2015 Type 4 JDBC driver Compliant with JDBC 4.2 (Java 8.0) Requires Java 8 or above Supports MySQL Protocol and X Protocol Implements the X DevAPI Compatible with MySQL 5.5 and above Copyright © 2019 Oracle and/or its affiliates. MySQL Connector/J 8.0
- 11. Copyright © 2019Oracle and/or its affiliates. Compatible with [Java 8] Java 9+ Supports MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 incl. X Protocol Connector/J 8.0 Compatible with [Java 5] Java 6 Java 7 [Java 8] Java 9+ Supports MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Connector/J 5.1 Side-by-side [Java n] – Required for compiling
- 12. Copyright © 2019Oracle and/or its affiliates. Installing and Using Connector/J
- 13. Copyright © 2019Oracle and/or its affiliates. MySQL Connector/J depends on Google’s protobuf java library for compiling and for running X DevAPI code Java applications using the X DevAPI via Connector/J 8.0.17 must have at least the following libraries in their Classpath: mysql-connector-java-8.0.17.jar protobuf-java-3.6.1.jar Installations via Linux packages or Windows installer require manual download: https://repo1.maven.org/maven2/com/google/protobuf/protobuf-java/3.6.1/ Maven enabled projects manage and download all dependencies automatically Library Dependencies
- 14. Copyright © 2019Oracle and/or its affiliates. Download from the official MySQL Downloads page: https://dev.mysql.com/downloads/connector/j/8.0.html Portable library bundle Linux packages and Windows installer Configure as a maven dependency https://search.maven.org/artifact/mysql/mysql-connector- java/8.0.17/jar “Installing” MySQL Connector/J Library <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</version> </dependency>
- 15. Copyright © 2019Oracle and/or its affiliates. Customized builds and testing Connector/J code Required tools: Git client; Apache Ant 1.8.2+; JDK 8+ Required third-party libraries: Junit 4.12+; Javassist 3.19+; Protcol Buffers Java API 3.6.1; C3P0 0.9.1; JBoss common JDBC wrapper 3.2.3; Simple Logging Facade API 1.6.1 Compile: “Installing” from Source $ git clone --branch release/8.0 https://github.com/mysql/mysql-connector-j.git $ ant -Dcom.mysql.cj.build.jdk=<path_to_jdk_1.8> -Dcom.mysql.cj.extra.libs=<path_to_libraries> dist
- 16. Connector/J packs loaded withnon-standard features that can be enabled or tuned via connection string options. Here are some of them. Copyright © 2019 Oracle and/or its affiliates. Features Showcase
- 17. Copyright © 2019Oracle and/or its affiliates. User-defined key:value pairs that get stored into P_S.SESSION_CONNECT_ATTRS JDBC 4.0’s Connection.get/setClientInfo() implementation; appends info data as query comments by default Instead of failing, creates the database identified in the connection string if it doesn’t exist Switch between CATALOG and SCHEMA to define what Connection or MetaData methods and arguments will used when referring to a database connectionAttributes clientInfoProvider createDatabaseIfNotExist databaseTerm General Properties
- 18. Copyright © 2019Oracle and/or its affiliates. Monitor and act on connection creation, destruction, commit, rollback and more, potentially altering the execution of these commands Ability to influence queries and Statement objects prior to their execution and modify results afterwards Exceptions thrown by the driver can be intercepted upon their creation giving the user the ability to inject side effects when errors occur connectionLifecycleInterceptors queryInterceptors exceptionInterceptors User-defined Interceptors
- 19. Copyright © 2019Oracle and/or its affiliates. List of the classes implementing the interface AuthenticationPlugin for custom or redefined authentication workflow Define what authentication plugin to use as default when the server suggests an unsupported one Authentication plugins not allowed in the connection Should an expired password cause connection close or open in sandbox mode, allowing to reset the pwd What character encoding to use in the password authenticationPlugins defaultAuthenticationPlugin disabledAuthenticationPlugins disconnectOnExpiredPasswords passwordCharacterEncoding On Authentication
- 20. Copyright © 2019Oracle and/or its affiliates. Sets the given list of key=value pairs as session variables once the connection is established The character encoding to use when dealing with strings; by default detects the server’s character set The character set should use on results Sets the server character set and collation; overrides characterEncoding sessionVariables characterEncoding characterSetResults connectionCollation Session Specific
- 21. Copyright © 2019Oracle and/or its affiliates. The name of the class the that creates client sockets; can be used to change default socket behavior, e.g., use proxies, firewall or replace by Unix Domain Sockets The time the client is willing to wait on socket connect and reads, respectively The maximum size protocol packet can have Enable protocol packets compression Name or IP address of the SOCKS host to connect through and its corresponding port socketFactory connectTimeout/socketTimeout maxAllowedPacket useCompression socksProxyHost/socksProxyPort Network Tweaking
- 22. Copyright © 2019Oracle and/or its affiliates. Enables multiple queries in a single statement execution; may return multiple ResultSets; doesn’t affect batched statements Batched statement execution continues even in case of errors Sets the option to kill the connection instead of canceling the query in case of execution timeout Allows disabling escape codes in queries allowMultiQueries continueBatchOnError queryTimeoutKillsConnection enableEscapeProcessing Related to Statements
- 23. Copyright © 2019Oracle and/or its affiliates. Switches between server-side prepared statements or client-side emulation of prepared statements Fall-back to client-side prepared statements when preparing server-side prepared statements fails Allows disabling escape codes in queries that are prepared useServerPrepStmts emulateUnsupportedPstmts processEscapeCodesForPrepStmts Related to Prepared Statements
- 24. Copyright © 2019Oracle and/or its affiliates. Empty strings are converted to 0 or throw an exception when read as number Allow keeping result sets open after statement close The maximum number of rows to return from the statements running on this connection Pad CHAR type columns with spaces, up to their size emptyStringsConvertToZero holdResultsOpenOverStatementClose maxRows padCharsWithSpace Related to Result Sets
- 25. Copyright © 2019Oracle and/or its affiliates. Switch between using INFORMATION_SCHEMA and getting meta data from MYSQL schema and SHOW Indicates if DatabaseMetaData.getProcedures() and similar should return metadata for both stored procedures and functions or just stored procedures When searching metadata, a null value in catalog or schemaPattern means current database or doesn’t apply the filter; see also databaseTerm useInformationSchema getProceduresReturnsFunctions nullDatabaseMeansCurrent Related to Metadata
- 26. Copyright © 2019Oracle and/or its affiliates. Set how connections must be secured: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY The list of TLS protocols to allow: TLS_V1 - TLS_V1.2 The list of cipher suites to allow Set of key store details for trust certificates Set of key store details for client certificates Enables getting server’s public key through the wire Enables queries using LOAD DATA LOCAL INFILE sslMode enabledTLSProtocols enabledSSLCipherSuites trustCertificateKeyStore* clientCertificateKeyStore* allowPublicKeyRetrieval allowLoadLocalInfile On Securing Connections
- 27. Copyright © 2019Oracle and/or its affiliates. State what time zone the server is in when doing all date time conversions Define what to do when MySQL returns all-zero date time values: EXCEPTION, ROUND or CONVERT_TO_NULL Truncate fractional seconds part when setting date time values Treat the MySQL type YEAR as Date or as short serverTimezone zeroDateTimeBehavior sendFractionalSeconds yearIsDateType Date-time Processing
- 28. Copyright © 2019Oracle and/or its affiliates. Keep a session state local reference for auto-commit and transaction isolation values to reduce the number of transaction handling queries to send Cache server variables and collation data from first connection for using in the following connections Either always send or only if different, transaction isolation settings, in Connection.setTransactionIsolation() Either always send or only if different, session auto- commit settings, in Connection.setAutocommit() useLocalSessionState cacheServerConfiguration alwaysSendSetIsolation elideSetAutoCommits Tweaking Performance/1
- 29. Copyright © 2019Oracle and/or its affiliates. Sets server-side read-only state or manages it entirely on client-side Cache the parsing stage of client-side prepared statements and server-prepared statements instances Rewrite batched inserts efficiently and/or use multi- queries to reduce the number of queries to send Combined with Statement.setFetchSize() instructs the server to user cursor based server-side prepared statements executions readOnlyPropagatesToServer cachePrepStmts rewriteBatchedStatements useCursorFetch Tweaking Performance/2
- 30. Copyright © 2019Oracle and/or its affiliates. Class name that implements a log interface Trace and log queries and their execution time Log queries that take longer than a set threshold Instead of checking the execution time against a threshold, log the ones outside the 99th percentile When slow queries are logged, also include their execution plan Log protocol packets as Hex dumps into the log Logger profileSQL logSlowQueries autoSlowLog explainSlowQueries traceProtocol Debug and Profiling
- 31. Fail-over, load-balancing, replication-aware, …plenty to choose from to achieve improved high-availability Copyright © 2019 Oracle and/or its affiliates. Multi-host Connections
- 32. Support forlogic Connection instances backed up by multiple MySQL servers Underlying physical connections have their own sessions that cannot be tracked, shared or copied Internal switches between physical connections mean switches between sessions There are no physical connection switches within a transaction boundary, switches occur only between transactions Three types of multi-host connections: failover, load-balancing and replication-aware Copyright © 2019 Oracle and/or its affiliates. Multi-host Connections
- 33. Connection-related errorstrigger fail-over to a secondary host Connection errors are propagated to the client, that has to handle them Fall-back to primary host occurs transparent and automatically, based on: secondsBeforeRetryMaster – the time to wait before falling back queriesBeforeRetryMaster – number of queries before falling back With autoReconnect=true the same Statement objects can be reused, otherwise new ones must be created Copyright © 2019 Oracle and/or its affiliates. Fail-over Connections jdbc:mysql://primary:port,secondary1:port,secondary2:port,.../database?props...
- 34. Load isdistributed among the listed hosts based on customizable load-balancing strategies - ha.loadBalanceStrategy: Pre-defined: random, bestResponseTime and serverAffinity (pairs with serverAffinityOrder) Custom made: implementation of com.mysql.cj.jdbc.JdbcConnection.BalanceStrategy Load balancing/fail-over occurs on: Transaction boundaries: explicit commit/rollback Connection exceptions: SQL State “08xxx” Conditions defined by user: loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker Auto-commit triggers: loadBalanceAutoCommitStatementThreshold and loadBalanceAutoCommitStatementRegex Hosts list can be managed through JMX and shared between connections loadBalanceConnectionGroup & ha.enableJMX Connection and transaction statistics, add/remove hosts Copyright © 2019 Oracle and/or its affiliates. Load-balancing Connections jdbc:mysql:loadbalance://host1:port,host2:port,host3:port,.../database?props...
- 35. Connections areinitialized with two sub-connections: One load-balanced master(s) connection One load-balanced slaves connection Connection.setReadOnly() switches between masters and slaves sub-connections Replication-aware connections specific options: allowMasterDownConnections – use slaves if no masters or all down, on connection initialization allowSlavesDownConnections – use masters if no slaves or all down, on connection initialization readFromMasterWhenNoSlaves – user the masters connection, in read-only, if no slaves or all down Support for multi-master replication topologies Hosts list can be managed through JMX and shared between connections replicationConnectionGroup & ha.enableJMX Connection and transaction statistics, add/remove hosts, promoting/demoting hosts, etc. Copyright © 2019 Oracle and/or its affiliates. Replication-aware Connections jdbc:mysql:replication://master:port,slave1:port,slave2:port,.../database?props...
- 36. Copyright © 2019Oracle and/or its affiliates. From Connector/J 5.1 to 8.0 With progress comes change, with change comes sacrifice. Not all decisions were easily made, but we believe we did them for our users benefit.
- 37. From minimum JVMversion 8 to new temporal data handling defaults, sometimes upgrading is not just a drop- in replacement. Copyright © 2019 Oracle and/or its affiliates. What changed?
- 38. Copyright © 2019Oracle and/or its affiliates. Runs on Java 8; users are advised to check the Java incompatibility information, provided by Oracle, before upgrading Improved connection strings syntax Several connection properties were removed, a few renamed and some added Major code refactoring and overhaul for improved performance Packages structure changed entirely, some classes were deprecated Exceptions updated to match JDBC standard Default temporal data types changed regarding time zones handling Ant build and testing procedures were restructured Overall Changes
- 39. Copyright © 2019Oracle and/or its affiliates. New scheme – mysqlx: – for X DevAPI sessions Authority section of connection strings (user and hosts information) supports multiple syntaxes: The {host} part can be written as: IPv6 addresses must be written between square brackets: [::1] Improved Connection Strings scheme://[usr[:pwd]@]{host},[usr[:pwd]@][{host},{host},...],.../db?key=value&... hostOrIP:portNumber address=(host=hostOrIP)(port=portNumber)(key=value)(...) (host=hostOrIP,port=portNumber,key=value,...) (address=hostOrIP:portNumber,key=value,...)
- 40. Copyright © 2019Oracle and/or its affiliates. Legacy Date and Time handling: dynamicCalendars, noTzConversionForTimeType, noTzConversionForDateType, cacheDefaultTimezone, useFastIntParsing, useFastDateParsing, useJDBCCompliantTimezoneShift, useLegacyDatetimeCode, useSSPSCompatibleTimezoneShift, useTimezone, useGmtMillisForDatetimes Connection properties removed: useBlobToStoreUTF8OutsideBMP, utf8OutsideBmpExcludedColumnNamePattern, and utf8OutsideBmpIncludedColumnNamePattern – utf8mb4 is now supported in all maintained MySQL versions Changes in Connection Properties/1
- 41. Copyright © 2019Oracle and/or its affiliates. Connection properties removed: useDynamicCharsetInfo – no more cached custom charset mappings useJvmCharsetConverters – JVM charsets now used in all cases dumpMetadataOnColumnNotFound – redundant feature relaxAutoCommit – for MySQL versions with no transactions support strictFloatingPoint – was used on deprecated test suite runningCTS13 – was used on deprecated test suite retainStatementAfterResultSetClose – not JDBC compliant nullNamePatternMatchesAll – not JDBC compliant Changes in Connection Properties/2
- 42. Copyright © 2019Oracle and/or its affiliates. Main package is now com.mysql.cj.jdbc Entry class Driver moved from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver; old one was deprecated Commonly used interfaces that moved: Changes in the Connector/J API/1 com.mysql.jdbc.ExceptionInterceptor com.mysql.cj.exceptions.ExceptionInterceptor com.mysql.jdbc.StatementInterceptorV2 com.mysql.cj.interceptors.QueryInterceptor com.mysql.jdbc. ConnectionLifecycleInterceptor com.mysql.cj.jdbc.interceptors. ConnectionLifecycleInterceptor
- 43. Copyright © 2019Oracle and/or its affiliates. Commonly used interfaces that moved (continuation): Changes in the Connector/J API/2 com.mysql.jdbc.AuthenticationPlugin com.mysql.cj.protocol.AuthenticationPlugin com.mysql.jdbc.jdbc2.optional. MysqlDataSource com.mysql.cj.jdbc.MysqlDataSource com.mysql.jdbc.jdbc2.optional. MysqlDataSourceFactory com.mysql.cj.jdbc.MysqlDataSourceFactory com.mysql.jdbc.jdbc2.optional. MysqlConnectionPoolDataSource com.mysql.cj.jdbc. MysqlConnectionPoolDataSource com.mysql.jdbc.jdbc2.optional. MysqlXADataSource com.mysql.cj.jdbc.MysqlXADataSource com.mysql.jdbc.jdbc2.optional.MysqlXid com.mysql.cj.jdbc.MysqlXid Not only changes in package names but also on methods signatures
- 44. Copyright © 2019Oracle and/or its affiliates. Most exceptions were duplicated before: Pre-JDBC 4 versions in package com.mysql.jdbc.exceptions Post-JDBC 4 versions in package com.mysql.jdbc.exceptions.jdbc4 Due to multi-JVM and multi-JDBC support in Connector/J Duplicated exceptions are now merged in one All Connector/J exceptions extend or were replaced by the corresponding standard JDBC exceptions Changes in Exceptions/1
- 45. Copyright © 2019Oracle and/or its affiliates. com.mysql.jdbc.exceptions.jdbc4. CommunicationsException com.mysql.cj.jdbc.exceptions. CommunicationsException com.mysql.jdbc.exceptions.[jdbc4.] MySQLDataException java.sql.SQLDataException com.mysql.jdbc.exceptions.[jdbc4.] MySQLIntegrityConstraintViolationException java.sql. SQLIntegrityConstraintViolationException com.mysql.jdbc.exceptions.[jdbc4.] MySQLInvalidAuthorizationSpecException java.sql.SQLInvalidAuthorizationSpecException com.mysql.jdbc.exceptions.[jdbc4.] MySQLNonTransientConnectionException java.sql.SQLNonTransientConnectionException com.mysql.jdbc.exceptions.[jdbc4.] MySQLNonTransientException java.sql.SQLNonTransientException com.mysql.jdbc.exceptions.[jdbc4.] MySQLQueryInterruptedException com.mysql.cj.jdbc.exceptions. MySQLQueryInterruptedException Changes in Exceptions/2
- 46. Copyright © 2019Oracle and/or its affiliates. com.mysql.jdbc.exceptions. MySQLStatementCancelledException com.mysql.cj.jdbc.exceptions. MySQLStatementCancelledException com.mysql.jdbc.exceptions.[jdbc4.] MySQLSyntaxErrorException java.sql.SQLSyntaxErrorException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTimeoutException java.sql.SQLTimeoutException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransactionRollbackException java.sql.SQLTransactionRollbackException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransientConnectionException java.sql.SQLTransientConnectionException com.mysql.jdbc.exceptions.[jdbc4.] MySQLTransientException java.sql.SQLTransientException m.mysql.jdbc.exceptions.[jdbc4.] MySQLIntegrityConstraintViolationException java.sql. SQLIntegrityConstraintViolationException Changes in Exceptions/3
- 47. For a longtime the lack of support for time-zone information in date-time values has been causing problems for many. It isn’t fixed yet, but we hope we are a step closer. Copyright © 2019 Oracle and/or its affiliates. The Time Zones Conundrum
- 48. Use Case Clients indifferent time zones store and read a DATETIME value that doesn’t carry TZ information Client 1 TZ: UTC-7 Client 2 TZ: UTC+1 Client 3 TZ: UTC-4 Backend Systems Application Server TZ: irrelevant Data Store TZ: UTC-4 MySQL TZ: System Copyright © 2019 Oracle and/or its affiliates. 2019-09-19 11:50:002019-09-19 11:50:00 2019-09-19 14:50:002019-09-19 14:50:00 2019-09-19 19:50:00 2019-09-19 14:50:002019-09-19 14:50:00
- 49. MySQL doesn’tsupport time zone info in date-time values By default Connector/J discovers the server time zone: By default MySQL server refers to system time zone, that mostly uses abbreviations Time zone abbreviations aren’t deterministic “The server time zone value 'PDT' is unrecognized or represents more than one time zone.” Client has to set what is the target and source time zone when storing and retrieving temporal data, respectively Time zones that observe DST have time gaps and overlaps Copyright © 2019 Oracle and/or its affiliates. So, what’s the problem?
- 50. All temporaldata in PreparedStatment getters and setters are subject to time zone adjustments Client application has to choose: 1. Adjust to server time zone when stored and adjust back to the client time zone when loaded default or serverTimezone=<server_TZ> 2. Store and read as-is, i.e., no time zone adjustment serverTimezone=<client_TZ> 3. Adjust to/from a neutral time zone serverTimezone=UTC Recommendation is to set neutral TZ, w/o DST, adjustments Copyright © 2019 Oracle and/or its affiliates. Date-time Handling Policies in Connector/J 8.0
- 51. Are you readynew database programming API? Give it a try and experiment the MySQL X DevAPI. Copyright © 2019 Oracle and/or its affiliates. X DevAPI Sneak Peek
- 52. Copyright © 2019Oracle and/or its affiliates. Synchronous and Asynchronous operation executions Secure by default (TLS+SHA2) Transactions, save points and row-locking Auto-magic prepared statements Connection pooling and fail- over Integrated in the MySQL Shell Enables MySQL Document Store to client applications Offers schema-less data storage development Fluent API Programming language agnostic Intuitive CRUD methods Raw SQL also available Domain specific expression language X DevAPI Key Features
- 53. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: JDBC/1 Connection conn = DriverManager .getConnection("jdbc:mysql://localhost/alcatraz", "demousr", "demopwd"); Statement stmt = conn.createStatement(); stmt.execute("CREATE TABLE facilities (id INT AUTO_INCREMENT PRIMARY KEY, " + "designation VARCHAR(100), capacity INT)"); PreparedStatement pstmt = conn .prepareStatement("INSERT INTO facilities VALUES (NULL, ?, ?)"); pstmt.setString(1, "B-206"); pstmt.setInt(2, 1); pstmt.execute(); pstmt.setString(1, "Mess Hall"); pstmt.setInt(2, 250); pstmt.execute(); pstmt.close();
- 54. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: JDBC/2 rs = stmt.executeQuery("SELECT * FROM facilities"); while (rs.next()) { System.out.printf("%d: %s (%d)%n", rs.getInt(1), rs.getString(2), rs.getInt(3)); } | 1: B-206 (1) | 2: Mess Hall (250) stmt.execute("DROP TABLE facilities"); stmt.close(); conn.close();
- 55. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: X DevAPI Raw SQL/1 SessionFactory sf = new SessionFactory(); Session sess = sf.getSession("mysqlx://demousr:demopwd@localhost:33060/alcatraz"); sess.sql("CREATE TABLE facilities (id INT AUTO_INCREMENT PRIMARY KEY, " + "designation VARCHAR(100), capacity INT)").execute(); SqlStatement stmt = sess.sql("INSERT INTO facilities VALUES (NULL, ?, ?)"); stmt.bind(Arrays.asList("B-206", 1)).execute(); stmt.clearBindings().bind(Arrays.asList("Mess Hall", 250)).execute();
- 56. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: X DevAPI Raw SQL/2 sess.sql("SELECT * FROM facilities").execute().forEach(r -> System .out.printf("%d: %s (%d)%n", r.getInt(0), r.getString(1), r.getInt(2))); | 1: B-206 (1) | 2: Mess Hall (250) sess.sql("DROP TABLE facilities").execute(); sess.close();
- 57. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: X DevAPI CRUD/1 SessionFactory sf = new SessionFactory(); Session sess = sf.getSession("mysqlx://demousr:demopwd@localhost:33060/alcatraz"); sess.sql("CREATE TABLE facilities (id INT AUTO_INCREMENT PRIMARY KEY, " + "designation VARCHAR(100), capacity INT)").execute(); Table tbl = schm.getTable("facilities"); tbl.insert("designation", "capacity").values("B-206", 1).values("Mess Hall", 250) .execute();
- 58. Copyright © 2019Oracle and/or its affiliates. X DevAPI CRUD X DevAPI SQL JDBCX DevAPI Sneak Peek Side by Side: X DevAPI CRUD/2 tbl.select().execute().forEach(r -> System .out.printf("%d: %s (%d)%n", r.getInt(0), r.getString(1), r.getInt(2))); | 1: B-206 (1) | 2: Mess Hall (250) sess.sql("DROP TABLE facilities").execute(); sess.close();
- 59. Let’s wrap thisup… Copyright © 2019 Oracle and/or its affiliates. Closing Comments, Q & A
- 60. Use MySQLConnector/J 8.0, MySQL Connector/J 5.1 may be EOLed at any time Extensive connection options set; consult the documentation Built-in support for multi-host connections MySQL Connector/J 8.0: Is GA and the recommend version to use Runs on Java 8 and above Complete refactoring and overhaul More consistency in vendor custom APIs JDBC for MySQL and MySQL Document Store support in same driver X DevAPI out-of-the-box Copyright © 2019 Oracle and/or its affiliates. Closing Comments
- 61. Q & A https://dev.mysql.com Copyright© 2019 Oracle and/or its affiliates.
- 62. The preceding isintended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2019 Oracle and/or its affiliates.
- 63. Session Survey Help usmake the content even better. Please complete the session survey in the Mobile App. Copyright © 2019 Oracle and/or its affiliates.
- 64. Thank You Copyright ©2019 Oracle and/or its affiliates. Connector/J Developer and Team Lead MySQL Middleware and Clients September 17, 2019 Filipe Silva