在做数据库过程中新手通常会遇到若干bug,如下是我遇到的几例,记录一下。
我先照课本上输入代码,然而run的时候却报错。常常是解决了一个bug,又出来一个bug,这样搞不下五六次。平均每次debug都要用上1-2个小时。(tip:如果你debug搞得天昏地暗,没有什么头绪的话,不妨关上电脑睡一觉,也许第二天就有想法了。)
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
这个问题通过百度发现是没有下载mysql-connector-java.jar,需要建立maven依赖,然后import。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
<scope>runtime</scope>
</dependency>
载入后没有报这个错了。下一个bug是这样的:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
这个网上查了很多解决方案,但是都不符合我的情况,也没有用满8小时(我感觉也不是这个问题)。最后发现是URL中冒号用的是中文输入,晕……
url = "jdbc:mysql://localhost:3306/hello mysql
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/
对应代码 conn = DriverManager.getConnection(url,USER_NAME,PASSWORD)
这个问题也搞了不少时间,Class.forName()我也加了,我也尝试过添加classpath环境变量以及把mysql-connector-java.jar复制到bin/ext目录下,然而都没什么用。最后发现是驱动版本不匹配,我的MySQL用的是8.0.13的,以前驱动mysql-connector-java.jar在配maven依赖的时候用的却是5.1.47版的,当把maven依赖的版本改成8.0.13就好了(当然你要先去maven中央仓库https://search.maven.org/中查是否有这个版本。)
java.sql.SQLSyntaxErrorException: Unknown database 'localhost:3306/demo'
这个是输入的细节问题,我最开始输入的是url = "jdbc:mysql:/localhost:3306/hello mysql,注意mysql:后面要用两个/
url = "jdbc:mysql://localhost:3306/hello mysq
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.
这个报错解决比较容易,直接告诉你这是高版本要把`com.mysql.jdbc.Driver'换成`com.mysql.cj.jdbc.Driver'。
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.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
这个报错翻译过来就是高版本要在URL后面加入时区。
url = "jdbc:mysql://localhost:3306/hello mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false"
如果设置severTimezone=UTC则是美国,比中国早8小时。如果想设置为中国就写
serverTimezone=Asia/Shanghai
或者
serverTimezone=Asia/Hongkong
终于能够运行了!
附上最终完整代码。
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class 数据库查询 {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/hello mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false";
private static String user = "root";
private static String password = "123456";
public 数据库查询() {
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
String sql = "SELECT 姓名,工号,密码 FROM 员工账户";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()) {
String num = rs.getString("工号");
String name = rs.getString("姓名");
String pd = rs.getString("密码");
System.out.println(num + " " + name + " " + pd);
}
} catch (Exception var16) {
var16.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception var15) {
var15.printStackTrace();
}
}
}
}
数据库连上后下一步增删改查就会so easy!