BUG采坑记录(1) JDBC操作流程以及常见的几种异常报错和解决方法

JDBC主要流程

通过JDBC操作数据库流程通常就是以下六个步骤：
1.注册驱动
2.获取连接
3.获取操作数据库的预处理对象
4.执行SQL语句并得到结果集
5.遍历结果集
6.释放资源
例如以下代码：

package com.lipiao.jdbc;

import java.sql.*;

/**
 * 通过JDBC代码分析程序的耦合
 */
public class JdbcDemo1 {

    //psvm快捷main函数
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1.注册驱动
        //法一
        // DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //法二 单例模式 节省资源 更推荐
        // Class.forName("com.mysql.jdbc.Driver");
        //较新的
         Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false","root","23573");
        //3.获取操作数据库的预处理对象
        PreparedStatement pstm =conn.prepareStatement("SELECT * from account");
        //4.执行SQL语句并得到结果集
        ResultSet rst=pstm.executeQuery();
        //5.遍历结果集
        while(rst.next()){
            //sout快捷输出函数
            System.out.println(rst.getString("name"));
        }
        //6.释放资源
        rst.close();
        pstm.close();
        conn.close();
    }
}

当然这是已经可以在我电脑上正常运行的代码了，在这之前遇到了一些问题，记录如下：
可能遇到的问题：

1.Class.forName(“com.mysql.jdbc.Driver”);This is deprecated

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.

根据提示换用：

//旧的Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

2.Establishing SSL connection without server’s identity verification is not recommended异常

Fri Jul 12 22:34:49 CST 2019 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.
Exception in thread "main" java.sql.SQLException: Access denied for user 'lipiao'@'localhost' (using password: YES)

在数据库连接的url中添加useSSL=false;或者url中添加useSSL=true，并且提供服务器的验证证书。具体如下

//2.获取连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false","lipiao","23573");

3.server time zone value异常

Exception in thread "main" 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.

使用Navicat在需要使用的数据库中点击查询，然后新建查询，如图所示

拷贝下面这句话后点击运行

SHOW VARIABLES LIKE '%time_zone%'

然后拷贝下面句话后运行，设置其时间为中国区

SET GLOBAL time_zone='+8:00'

运行效果如下

最终成功执行查询语句，并将查询数据库到的demo数据输出至控制台。