JDBC初探之遇到的问题以及解决方法

初学Java，记录一下第一次JDBC数据库操作遇到的问题和解决方法，如有错误，还请大神们不吝指正~

程序非常简单，读取本地mysql数据库 ( 本地安装mysql数据库可参考：https://blog.csdn.net/zuolixiangfisher/article/details/74000294 ) 中infouser表里的共五条记录，数据如下：

以下代码参考了网上其他同学的代码，详见参考资料

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;

public class MysqlDemo1 {
    // JDBC 驱动名以及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/db_test";

    // 数据库用户名和密码
    static final String USER = "test";
    static final String PASSWORD = "testyhl";

    public static void main(String[] args) throws Exception{
        Connection conn = null;
        Statement stmt = null;

        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            // 执行查询
            System.out.println("实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT userid, age, gender, keywords, topics FROM infouser";
            ResultSet rs = stmt.executeQuery(sql);

            // 展开结果集数据
            while(rs.next()){
                String userid = rs.getString("userid");
                int age = rs.getInt("age");
                String gender = rs.getString("gender");
                String keywords = rs.getString("keywords");
                String topics = rs.getString("topics");

                System.out.print("UserId: " + userid);
                System.out.print(" Age: " + age);
                System.out.print(" Gender: " + gender);
                System.out.print(" Keywords: " + keywords);
                System.out.print(" Topics: " + topics);
                System.out.println();
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch (SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch (Exception e){
            // 处理 class.forName 错误
            e.printStackTrace();
        }finally {
            // 关闭资源
            try{
                if(stmt!=null){
                    stmt.close();
                }
            }catch (SQLException se2){
                // do nothing
            }
            try{
                if(conn!=null) {
                    conn.close();
                }
            }catch (SQLException se){
                se.printStackTrace();
            }
            System.out.println("Goodbye!");
        }

    }
}

1、第一次运行报错

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

问题原因：没有引入mysql-connector-java-x.x.x-bin.jar

解决方案：增加mysql-connector-java配置，在pom.xml文件里加入以下代码，然后重新刷新IDEA加载 Jar包

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

修改完后，再次运行

2、第二次运行报错

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.
Tue Dec 25 17:52:32 GMT+08:00 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 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.

这个报错信息里其实包含了几个问题
第一个小问题

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.

问题原因： mysql-connector包使用了新的驱动

解决方法1：jdbc.driver的属性值从com.mysql.jdbc.Driver换为com.mysql.cj.jdbc.Driver.

解决方法2：在代码中去掉Class.forName(driver);，因为驱动会自动加载。

第二个小问题

Tue Dec 25 17:52:32 GMT+08:00 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 be established by default if explicit option isn't set.

问题原因：连接的URL中需要增加时区信息

解决方法：增加serverTimezone属性并设置时区值，测试UTC（世界标准时间）和GMT（格林威治时间）都可以。

static final String DB_URL = "jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT";

完成上面两步，程序已经可以正常输出结果，如下：

连接数据库...
Tue Dec 25 18:28:15 GMT+08:00 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 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.
实例化Statement对象...
UserId: 101 Age: 10 Gender: 2 Keywords: 鲁迅 Topics: 文学
UserId: 102 Age: 20 Gender: 1 Keywords: 牛顿 Topics: 科学
UserId: 103 Age: 23 Gender: 1 Keywords: 杜甫 Topics: 文学
UserId: 104 Age: 43 Gender: 2 Keywords: 乔峰 Topics: 小说
UserId: 105 Age: 29 Gender: 0 Keywords: 阿杜 Topics: 明星
Goodbye!

可以看到，还有一个WARN，为了程序健壮性以及消除强迫症，必须把这个WARN干掉。

第三个小问题

问题原因：关于SSL连接的Warning

解决方法：再在URL后面添加一个属性useSSL，并设置为false

static final String DB_URL = "jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT"&useSSL=false;

再次执行，完美

连接数据库...
实例化Statement对象...
UserId: 101 Age: 10 Gender: 2 Keywords: 鲁迅 Topics: 文学
UserId: 102 Age: 20 Gender: 1 Keywords: 牛顿 Topics: 科学
UserId: 103 Age: 23 Gender: 1 Keywords: 杜甫 Topics: 文学
UserId: 104 Age: 43 Gender: 2 Keywords: 乔峰 Topics: 小说
UserId: 105 Age: 29 Gender: 0 Keywords: 阿杜 Topics: 明星
Goodbye!

总结一下

创建一个以JDBC连接数据库的程序，包含7个步骤

• 加载JDBC驱动程序
• 提供JDBC连接的URL
• 创建数据库的连接
• 创建一个Statement
• 执行SQL语句
• 处理结果
• 关闭JDBC对象

最后完整的代码如下：

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;

public class MysqlDemo1 {
    // JDBC 驱动名以及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT&useSSL=false";

    // 数据库用户名和密码
    static final String USER = "test";
    static final String PASSWORD = "testyhl";

    public static void main(String[] args) throws Exception{
        Connection conn = null;
        Statement stmt = null;

        try{
            // 注册 JDBC 驱动
            // Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            // 执行sql查询
            System.out.println("实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT userid, age, gender, keywords, topics FROM infouser";
            ResultSet rs = stmt.executeQuery(sql);

            // 展开结果集数据
            while(rs.next()){
                String userid = rs.getString("userid");
                int age = rs.getInt("age");
                String gender = rs.getString("gender");
                String keywords = rs.getString("keywords");
                String topics = rs.getString("topics");

                System.out.print("UserId: " + userid);
                System.out.print(" Age: " + age);
                System.out.print(" Gender: " + gender);
                System.out.print(" Keywords: " + keywords);
                System.out.print(" Topics: " + topics);
                System.out.println();
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch (SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch (Exception e){
            // 处理 class.forName 错误
            e.printStackTrace();
        }finally {
            // 关闭资源
            try{
                if(stmt!=null){
                    stmt.close();
                }
            }catch (SQLException se2){
                // do nothing
            }
            try{
                if(conn!=null) {
                    conn.close();
                }
            }catch (SQLException se){
                se.printStackTrace();
            }
            System.out.println("Goodbye!");
        }

    }
}

参考资料：

[1] http://www.runoob.com/java/java-mysql-connect.html
[2] https://yq.aliyun.com/articles/614458
[3] https://blog.csdn.net/booloot/article/details/76223004
[4] http://www.cnblogs.com/hongten/archive/2011/03/29/1998311.html