The little pit the JDBC encountered when connecting to MySQL

Recently from the MS SQL Server to MySQL, is already 8.11 version, the installation seems to have used a new way of identity authentication and so on, the connection process is also stumbling, encountered a lot of weird problems, recorded in this.

Driver loading:

When you used JDBC previously, you imported the appropriate JDBC driver jar package and then used the

Class.forName (drivername);

Load the driver, and then the database connection, when using the MySQL 8.11 version and its corresponding connector, if you use the above code to load Com.mysql.jdbc.Driver, the console will output a line of information:

Loading class ' Com.mysql.jdbc.Driver '. This is deprecated. The new driver class is ' com.mysql.cj.jdbc.Driver '. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

It probably means that the loaded class has been deprecated and the new driver class is Com.mysql.cj.jdbc.Driver, which is automatically registered via the SPI and usually does not need to be loaded manually. The output will not be generated after commenting out the Class.forName and running.

URL when connecting to a database:

The first half of the URL is fixed and requires additional parameters that are attached to the querystring by a similar URL in the following format:

jdbc:<_database>://<address>:<port>/<database_name>[?_key1=_value1[&_key2=_value2[ &_KEY3=_VALUE3] [...]

Only a few parameters are listed here:

When you connect without parameters, you first output a line of prompt information:

Tue 20:16:40 CST 2018 warn:establishing SSL connection without server ' s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must is established by default if explicit Opti On isn ' t set. For compliance with existing applications not using SSL, the Verifyservercertificate property was set to ' false '. You need either to explicitly disable SSL by setting Usessl=false, or set usessl=true and provide truststore for server CE Rtificate verification.

Probably mean is not recommended in the absence of server authentication to establish SSL connection , and so on, because I do not know about SSL, just follow the prompts to add the parameter usessl=false, it will not produce the output.

The next error that is encountered is a time zone value of Sqlexception:the server .

java.sql. SQLException: The server time zone value ' Öð¹ú±êx¼ê±¼ä ' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the Servertimezone configuration) to use a more specifc Time zone value if you want to utilize time zone support.

Probably mean the server time zone value has a problem, I this is a group of garbled, follow the hint with parameter Servertimezone=hongkong, will not produce this exception, for this parameter value can consult relevant data obtained, known East 8 can be Hongkong, Asia/shanghai, or Asia/hongkong as a parameter value .

sqlnontransientconnectionexception: Public Key retrieval are not allowed

At this point, the program may already be able to connect to the database, but I am connected again the next day after the debug (no changes to the code), thrown an exception and the connection failed, multiple attempts have no effect, the exception is as follows:

sqlnontransientconnectionexception: Public Key retrieval are not allowed

For this abnormal online information is very few, many for English, stumbled down unexpectedly solved this problem, method to add a parameter allowpublickeyretrieval=true, can connect normally, and after successful connection, Deleting this parameter can still connect normally (not all metaphysics) ..., this method comes from the following comments from the next Dalao (https://bugs.mysql.com/bug.php?id=75670):

[2015 19:57] Daniel soadded The following entry to the Connector/j 6.0 changelog: "If the MySQL server ' s default authentication method was SHA256 but neither one of the connector/j connection properties Allowpublickeyretrieval and Serverrsapublickeyfile was Set, the authentication failed with a transientconnectionexception, complaining then the public key could not being retrieve D. With this fix, authentication continues in the situation, allowing other enabled authentication methods to be tried. "

However, it is not understood.

The other is the character encoding problem , my server by default encoding for utf-8mb4,eclipse default encoding is used UTF-8, so did not deal with the encoding problem.

Additional parameters Useunicode=true and Characterencoding=utf-8are required for encoding conversions.

Other important parameters (turn from: 41148047):

Parameter name	Parameter description	Default value	Minimum version requirements
User	Database user name (used to connect to database)		All versions
Password	User password (used to connect to database)		All versions
Useunicode	If the Unicode character set is used, the value of this parameter must be set to True if the parameter characterencoding is set to gb2312 or GBK	False	1.1g
Characterencoding	When Useunicode is set to True, the character encoding is specified. For example, can be set to gb2312 or GBK	False	1.1g
AutoReConnect	Is the connection automatically reconnected when the database connection is interrupted abnormally?	False	1.1
Autoreconnectforpools	Whether to use a reconnection policy for database connection pooling	False	3.1.3
Failoverreadonly	Is the connection set to read-only after the automatic reconnection is successful?	True	3.0.12
Maxreconnects	When AutoReConnect is set to true, the number of times to retry the connection	3	1.1
Initialtimeout	When AutoReConnect is set to true, the time interval between two re-interconnects, in seconds	2	1.1
ConnectTimeout	Time-out, in milliseconds, when establishing a socket connection with the database server. 0 means never timeout, for JDK 1.4 and later	0	3.0.1
Sockettimeout	Socket operation (Read-write) timeout, in milliseconds. 0 means never time out	0	3.0.1

Special attention! In some cases, such as an XML configuration file, where the URL is specified through a configuration file, the isolation symbol for the parameters after the URL & need to use &amp; escape for &

Special characters commonly used in HTML (Character entities)Number

Show Results	Description	Entity Name	Entity
	Show a space	 	 
<	Less than	&lt;	&#60;
>	Greater than	&gt;	&#62;
&	& Symbols	&amp;	&#38;
“	Double quotes	&quot;	&#34;

Other commonly used character entities (Character entities)Number

Show Results	Description	Entity Name	Entity
?	Copyright	©	©
?	Registered trademarks	®	®
X	Multiplication sign	×	×
÷	Division Sign	÷	÷

Pits encountered in the Java Web:

No suitable driver found for jdbc:mysql://...

As mentioned earlier, MySQL-enabled legacy drivers use a new auto-enroll driver via SPI, which works well under normal Java programs, but when running in a Java Web project in a Tomcat8.5 environment, database connection operations can throw exceptions:

java.sql. SQLException: No suitable driver found for jdbc:mysql://...

Probably means that the appropriate JDBC driver could not be found, and after checking that the drive jar package used has been placed in the Tomcat Lib directory, the build path in eclipse also contains the jar package, and the driver class used to print to the console before performing the connection operation also exists:

	System.out.println(com.mysql.cj.jdbc.Driver.class);

/*
*output：
**
*class com.mysql.cj.jdbc.Driver
*
*/

The answers that have been searched are basically the same (turn from: http://www.blogjava.net/w2gavin/articles/217864.html):

Today the code appeared no suitable driver found for jdbc, but also searched the online data, basically said is three questions: one is: The connection URL format has a problem (Connection conn= Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/xx", "root", "XXXX") two are: drive string error ( Com.mysql.jdbc.Driver) Three is classpath did not join the appropriate MYSQL_JDBC drive through my careful examination, these three kinds of mistakes I have not committed, why? try to add Mysql-connector-java-3.1.14-bin.jar jar package to C:\Program Files\java\jre1.6.0_02\lib\ext folder, solve the problem!!    

For these three statements, I have to check the first two, and there is no error, the third person is not correct, Tomcat will automatically load Lib jar package, and the above in the print driver class class object can also see that the class has been loaded, for the following The drive jar package into the system JRE under the Square (Xuan) method (science) also did not try , but the connection to the database operation before the active loading of the driver class code after the god (Xuan) strange (Learn) good :

	System.out.println(com.mysql.cj.jdbc.Driver.class);
			Class.forName(driverName);
			System.out.println("connecting...");
			conn = DriverManager.getConnection(URL, uName, uPwd);
			System.out.println("connect success!");
/*
*output：
**
*class com.mysql.cj.jdbc.Driver
*connecting...
*connect success!
*
*/

You can see that the class has already been registered before the manual load, so this metaphysical issue also wants to know the Dalao advice one or two!

At present, the problems encountered in these, in the future encounter new problems continue to add, the text understand if there are errors also ask Dalao to correct, welcome everyone's discussion and exchange.

The little pit the JDBC encountered when connecting to MySQL