Shulou(Shulou.com)05/31 Report--

What should I pay attention to when connecting to a Mysql database using JDBC? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

First of all, clarify several concepts:

JDBC:java database connection is a set of standard interfaces designated by Orical Company.

Java database driver: the implementation class of JDBC, provided by the corresponding database vendor, which can be used to operate different databases.

In the java- database, all packages in jdbc-api are java.sql or javax.sql

Steps for JDBC:

(1) Establishment of databases and tables

(2) create a project

(3) Import driver jar package

(4) Registration driver

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

(5) get a connection

Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost: Port number / project name", "login name", "password")

Preface

Recently, a mysql version 8.0 database has been installed, and it can be said that there is a lot of condition when connecting in the program. Some problems have been encountered before, so here is a summary of the problems that may occur when connecting to mysql using JDBC.

Before that, explain the environment:

Development tool: IDEA

Mysql version: 8.0.12 for Win64 on x86x64 (MySQL Community Server-GPL)

Mysql driver package: 8.0.12

Changes to the driver package URL

Abnormal 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 data on the Internet, it is found that starting from mysql6, the driver package begins to use the new driver url. If you use the old version 5. 0 driver package, you don't need to drive URL, but there may be some unexpected problems if you use the old driver. Therefore, it is recommended to upgrade the driver package, and then change the value of the driver URL.

Solution method

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.45mm, 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.

It is not recommended to establish a SSL connection without server authentication. According to MySQL 5.5.45, if the explicit option is not set, the SSL connection required by 5.6.26 + and 5.7.6 + must be established by default. For existing applications that do not use SSL, the ValuyServer certificate 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`.

Solution method

Generally speaking, there is no need to use SSL connection in development, just add the useSSL=false parameter after the connection string. But if there is a real need for a SSL connection, add the useSSL=true parameter after driving the URL.

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

Time zone problem

Abnormal information

Java.sql.SQLException: the host in the The server time zone value 'list is the most expensive 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

It is also due to the time zone difference between the new version database and the system after the version upgrade, so it is necessary to specify the time zone serverTimezone.

Solution method

Add the parameter & serverTimezone=GMT%2B8 after the connection string, and 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, and enter the command and its output in turn 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)

Escape of & in XML configuration file

Abnormal information

Org.mybatis.generator.exception.XMLParserException: XMLParser Error on line 16: the reference to the entity "useSSL" must end with the'; 'delimiter.

Reason

This is an error I made when using mybatis generator. I wanted to add the useSSL parameter after the connection string, but since & is prohibited in the XML file, I use its escape & instead when I need to use it.

Solution method

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 are using XML as the configuration file, you need to change the & symbol in the connection string to &

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.