Summary of problems that may occur when using JDBC to connect to Mysql database

First, clarify a few concepts:

• JDBC: Java database connection, a set of standard interfaces specified by Orical
• Java database driver: JDBC implementation class, provided by the corresponding database vendor, can be used to operate different databases through the driver
• In java-database, all packages in jdbc-api are java.sql or javax.sql

JDBC operation steps:

(1) Create a database and table

(2) Create a project

(3) Import the driver jar package

(4) Register the driver

Class.forName("com.mysql.jdbc.Driver");

(5) Get a connection

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:端口號/項目名", "登錄名", "密碼");

Preface

I recently installed a MySQL 8.0 database, and there were constant problems when connecting to it in the program. I have encountered some problems before. Here is a summary of the problems that may occur when using JDBC to connect to MySQL.

Before that, let me explain the environment:

• Development tool: IDEA
• MySQL version: 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL)
• mysql driver package: 8.0.12

Driver package URL changes

Exception 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.

reason

Through the exception, we can find that the new driver url is com.mysql.cj.jdbc.Driver. After consulting the information online, we found that starting from MySQL 6, the driver package began to use the new driver url. If you use the old 5.0 version of the driver package, you do not need to use the driver URL, but some unexpected problems may occur if you use the old driver. Therefore, it is recommended to upgrade the driver package and then change the value of the driver URL.

Workaround

Change the driver URL from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver

SSL Warning

Warning Message

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 be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

reason

The warning message is translated as follows.

Establishing an SSL connection without server authentication is not recommended. As of MySQL 5.5.45+, an SSL connection, required by 5.6.26+ and 5.7.6+, must be established by default if no explicit option is set. For existing applications that do not use SSL, the VerifyServerCertificate property is set to "false". You need to explicitly disable SSL by setting useSSL=false , or set useSSL=true and provide a truststore for server certificate verification`.

Workaround

Generally, you don't need to use SSL connection in development. Just add the useSSL=false parameter after the connection string. But if you really need an SSL connection, add the useSSL=true parameter after the driver URL.

jdbc:mysql://localhost:3306/dbname?characterEncoding=UTF-8&useSSL=false

Time zone issues

Exception information

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

reason

Also, after the version upgrade, there is a time zone difference between the new version database and the system, so the time zone serverTimezone needs to be specified

Workaround

Add the parameter &serverTimezone=GMT%2B8 after the connection string. The final connection string is as follows:

jdbc:mysql://localhost:3306/dbname?characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8

Modify the database time. First connect to the database through the command line, enter the commands and their outputs in sequence as follows

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.04 sec)

mysql> set global time_zone="+8:00";
Query OK, 0 rows affected (0.01 sec)

Escaping & in XML configuration files

Exception information

org.mybatis.generator.exception.XMLParserException: XML Parser Error on line 16: Reference to entity "useSSL" must end with a ';' delimiter.

reason

This is the error I get when using mybatis generator. At that time, I wanted to add the useSSL parameter after the connection string, but since & is prohibited in XML files, when & is needed, it must be replaced with its escape symbol &.

Workaround

Change the & symbol in the connection string to &

Detailed connection string reference

jdbc:mysql://127.0.0.1:3306/dbname?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&serverTimezone=GMT%2B8&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true

Of course, if you use XML as the configuration file, you need to change the & symbol in the connection string to &

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.