Java数据库操作 JDBC连接

JDBC连接Mysql数据库8.0出现的问题

忘记MySQL密码，使用MySQL installer卸载，并删除data文件夹和my.ini.
重装MySQL8(新版事真多),完全按照推荐默认设置进行,记住root密码.新建数据库test.记住密码
MySQL 8.0 Command Line Client闪退打开MySQL workbench 验证是否可以正常登录;以管理员身份运行cmd,注册mysqld.exe,即MySQL服务,启动服务,登录.
下载并在项目的库中添加mysql-connector-java-8.0.13.jar(对应MySQL8),
(或者新建lib文件夹,将mysql-connector-java-8.0.13.jar复制到其中,并放到%JAVA_HOME%\lib\ext一份,并添加到CLASSPATH中?以后验证是否有效)把数据库驱动com.mysql.jdbc.Driver改为com.mysql.cj.jdbc.Driver
jdbc:mysql://127.0.0.1:3306/test 连接失败,很大串异常,
改为jdbc:mysql:127.0.0.1:3306/test,抛出异常为

java.sql.SQLException: No suitable driver found for jdbc:mysql:127.0.0.1:3306/test
at java.sql.DriverManager.getConnection(DriverManager.java:689)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at mysql_test.Conn.getConnection(Conn.java:23)
 at mysql_test.Conn.main(Conn.java:33)

(异常少并不意味着距离正确近,完全走不到正确的地方了)
看视频得知是URL错误异常 url缺少//,添上

Sat Mar 09 21:14:02 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.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
 at com.mysql.jdbc.Util.getInstance(Util.java:387)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2332)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:795)
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at Conn.getConnection(Conn.java:21)
 at Conn.main(Conn.java:33)
Caused by: java.lang.NullPointerException
 at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:3005)
 at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1916)
 at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1845)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1215)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2255)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286)
 ... 14 more

原因:新的MySQL默认开启SSL(Secure Sockets Layer 安全套接层):网络通信提供安全及数据完整性的一种安全协议。其继任者为TSL (Transport Layer Security)
默认情况下必须建立SSL连接。您需要通过设置useSSL=false显式禁用SSL.
添加useSSL=false
,另,找资料发现MySQL8需要注明时区,注明后url为
“jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false”,无异常抛出,可以正常运行(最少条件?),
为什么有的注明了编码字符集,即useUnicode=true&characterEncoding=utf-8
作用:指定字符的编码、解码格式,例如数据库与项目数据库不同时.
rewriteBatchedStatements=true
把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL
附源码

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

/**
 * @description
 * @Author Darl Lee
 * @Version 0.0
 * @Date 2/25/2019
 */
public class Conn {
    Connection con;
    public Connection getConnection(){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
        try {
            String URL="jdbc:mysql://127.0.0.1:3306/test" +
        "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8" +
        "&useSSL=false&rewriteBatchedStatements=true";
	con= DriverManager.getConnection(URL,"root","Password1!");
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(con);
        return con;
        //con.close();
    }

    public static void main(String[] args) {
        Conn c=new Conn();
        c.getConnection();
    }
}