【MySQL】最新版8.0.15遇到的一些坑

最近在学习maven，作为入门的第一个小例子，写了一个从MySQL数据库读取数据的demo，第一次运行遇到一些问题，特此记录下来。

#1-> pom.xml添加MySQL-connector

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
    <scope>runtime</scope>
</dependency>

#2-> 加载驱动类的时候需要注意不同版本写法有细微差别

// 加载驱动类
Class.forName("com.mysql.jdbc.Driver"); //mysql5之前的版本
Class.forName("com.mysql.cj.jdbc.Driver");  //MySQL6之后的版本新写法

 #3->报错：未指定serverTimezone

解决方法有两种，一种是在初始化connection对象的时候加上指定的serverTimezone，第二种是在my.ini配置文件中直接指定serverTimezone。

#3-1->初始化connection对象时指定serverTimezone=UTC

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/maven_db?" +
                    "serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false" +
                    "&user=root&password=psd");

#3-2->修改[my.ini]配置文件，找到[mysqld]添加如下代码

# 设置默认时区
default-time-zone = '+08:00'

【完整代码】

项目结构

#->com.yy.domain -> Users.java

package com.yy.domain;

public class Users {

    private int user_id;
    private String username;
    private String birthdate;

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(String birthdate) {
        this.birthdate = birthdate;
    }

    @Override
    public String toString() {
        return "Users{" +
                "user_id=" + user_id +
                ", username='" + username + '\'' +
                ", birthdate='" + birthdate + '\'' +
                '}';
    }
}

#->com.yy.dao -> [interface] UsersDao.java

package com.yy.dao;

import com.yy.domain.Users;

import java.util.List;

public interface UsersDao {

    public List<Users> findAll() throws Exception;
}

#->com.yy.dao.impl -> UsersDaoImpl.java

package com.yy.dao.impl;

import com.yy.dao.UsersDao;
import com.yy.domain.Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl implements UsersDao {
    public List<Users> findAll() throws Exception {

        List<Users> list = new ArrayList<Users>();

        //先获取Connection对象
        Connection connection = null;
        //获取真正操作数据库的对象
        Statement stmt = null;
        //返回结果集
        ResultSet rs = null;
        try {
            // 加载驱动类
//            Class.forName("com.mysql.jdbc.Driver"); //mysql5之前的版本
            Class.forName("com.mysql.cj.jdbc.Driver");  //MySQL6之后的版本新写法
            //先获取Connection对象
            //--必须要加上 serverTimezone=UTC 不然会报错 找不到serverTimezone
            connection = DriverManager.getConnection("jdbc:mysql://localhost/maven_db?" +
                    "serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false" +
                    "&user=root&password=1007");
            //获取真正操作数据库的对象
            stmt = connection.createStatement();
            rs = stmt.executeQuery("select * from user");

            //数据库结果集转成java的list结合
            while (rs.next()) {
                Users user = new Users();
                user.setUser_id(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setBirthdate(rs.getString("birthdate"));

                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭结果集合
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) {
                } // ignore

                rs = null;
            }
            // 关闭操作对象
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) {
                } // ignore

                stmt = null;
            }
            // 关闭数据库连接
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException sqlEx) {
                } // ignore

                connection = null;
            }
        }

        return list;
    }
}

#->com.yy.test -> UsersTest.java

package com.yy.test;

import com.yy.dao.UsersDao;
import com.yy.dao.impl.UsersDaoImpl;
import com.yy.domain.Users;
import org.junit.Test;

import java.util.List;

public class UsersTest {
    @Test
    public void findAll() throws Exception{
        UsersDao usersDao = new UsersDaoImpl();
        List<Users> list = usersDao.findAll();
        for (Users users : list) {
            System.out.println(users.toString());
        }
    }
}

#->pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.yy</groupId>
    <artifactId>maven_mysql</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <!--    <maven.compiler.source>1.7</maven.compiler.source>-->
        <!--    <maven.compiler.target>1.7</maven.compiler.target>-->
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

【运行效果】