applicationContext.xml连接JDBC数据库时碰到的错误及解决方法

使用applicationContext.xml连接JDBC数据库的时候碰到一些问题，记录一下。

下面是我最开始对着书敲的代码，需要改正和特别注意的地方用红框圈出来了：

改正后换成和MySQL一致的版本→

放一张最终运行成功的applicationContext.xml代码图片及改正后的值：

value="com.mysql.cj.jdbc.Driver"

value="jdbc:mysql://localhost/spring?useSSL=false&serverTimezone=UTC&&allowPublicKeyRetrieval=true"

下面是运行时出现的具体错误及改正方法：

1.首先运行出现如下错误：

Sun Jul 05 21:55:52 CST 2020 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 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.

错误原因：MySQL在高版本需要指明是否进行SSL(保障Internet数据传输安全利用数据加密)

参考博客：https://blog.csdn.net/xhangs/article/details/88635358

解决方法：把<property name="url" value="jdbc:mysql://localhost/spring"/>中的value加上?useSSL=false

                  如：<property name="url" value="jdbc:mysql://localhost/spring?useSSL=false"/>

2.第二次运行出现如下错误：

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
......

//重点是这里，我的MySql版本是8.0的，但是导入jar包的时候是5.1版本的
//并且数据驱动写的是value="com.mysql.jdbc.Driver"，8.0版本的应该是value="com.mysql.cj.jdbc.Driver"
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

错误提示：Could not create connection to database server.

错误原因：我的MySql版本是8.0的，但是导入jar包的时候是5.1版本的，并且数据驱动写的是value="com.mysql.jdbc.Driver"，8.0版本的应该是value="com.mysql.cj.jdbc.Driver"。

解决方法：jar包换成8.0版本的。并且value改成value="com.mysql.cj.jdbc.Driver"。

3.第三次运行出现错误：

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
......
Caused by: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

发生错误：Public Key Retrieval is not allowed。

错误原因：如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥；可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=True参数以允许客户端从服务器获取公钥；但是需要注意的是 AllowPublicKeyRetrieval=True可能会导致恶意的代理通过中间人攻击(MITM)获取到明文密码，所以默认是关闭的，必须显式开启。

参考博客：https://blog.csdn.net/u013360850/article/details/80373604。

解决方法：在value="jdbc:mysql://localhost/spring?useSSL=false"后添加allowPublicKeyRetrieval=true

              如：value="jdbc:mysql://localhost/spring?useSSL=false&amp;allowPublicKeyRetrieval=true"

注意：这里有些人是用&连接的，如：useSSL=false&allowPublicKeyRetrieval=true"，但是我在这里用&会报错，应该改成&amp;

报错提示：对实体 "allowPublicKeyRetrieval" 的引用必须以 ';' 分隔符结尾。

Exception in thread "main" org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 13 in XML document from class path resource [applicationContext.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 13; columnNumber: 103; 对实体 "allowPublicKeyRetrieval" 的引用必须以 ';' 分隔符结尾。
......
Caused by: org.xml.sax.SAXParseException; lineNumber: 13; columnNumber: 103; 对实体 "allowPublicKeyRetrieval" 的引用必须以 ';' 分隔符结尾。

4.第四次运行出现错误：

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Access denied for user '022278'@'localhost' (using password: YES)
......
Caused by: java.sql.SQLException: Access denied for user '022278'@'localhost' (using password: YES)

错误原因：我的username是022278，要改成root。（自己创建MySql数据库的时候用户名是022278，我以为是要用这个）

解决方法：<property name="username" value="022278"/>改成<property name="username" value="root"/>

5.第五次运行错误：

Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is 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.
......
Caused by: 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.
......
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: 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.

解决方法：在value="jdbc:mysql://localhost/spring?useSSL=false&amp;&amp;allowPublicKeyRetrieval=true"后加上&amp;serverTimezone=UTC

如：value="jdbc:mysql://localhost/spring?useSSL=false&amp;serverTimezone=UTC&amp;&amp;allowPublicKeyRetrieval=true"

6.第六次。。。终于运行成功了！！！（欢呼！！！）