exception - 如何使用 JDBC 区分不正确的登录凭据和服务器不可用
问题描述
我正在编写一个程序来通过 JDBC 连接到 MariaDB 数据库。使用正确的凭据(用户名/密码),它可以正常连接。但是我试图在它没有连接时产生适当的错误消息,这就是我遇到问题的地方。无论问题是服务器不可用(停止)还是凭据不正确,我都会遇到相同的异常。
在这两种情况下,它都给出:
SQL State: 42000
Error Code: -1
Message: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
Cause: java.sql.SQLSyntaxErrorException: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
因此,我无法向我的用户解释他们是否需要重新输入密码或联系 IT,因为服务器已关闭,因为我无法区分这两者。
SQL 状态告诉我它可能是语法错误或访问规则违规。我在“-1”的错误代码上找不到任何内容。
我怎样才能在这里做出这种区分?
编辑
我以前不必制作最小可重现的例子,所以希望这很接近。
这是一个 JavaFX FXML 项目,这是登录屏幕的控制器。您会看到我在哪里测试凭据和底部的连接,此外我还评论了catch
我正在努力解决的特定部分。
public class ControllerLogin extends AnchorPane {
//Initialise java fields
public String UN;
public String PW;
private boolean credentialsAccepted;
//Initialise fx fields
@FXML private TextField username;
@FXML private PasswordField password;
//Initialise fx labels
@FXML private Label userMessage;
//Constructor creates an FXML loader, set its properties and attempts to load the FXML file.
public ControllerLogin() {
FXMLLoader fxmlLoader = new FXMLLoader();
fxmlLoader.setLocation(getClass().getResource("/fxml/LoginScreen.fxml"));
fxmlLoader.setRoot(this);
fxmlLoader.setController(this);
try {
fxmlLoader.load();
}
catch (IOException exception) {
throw new RuntimeException(exception);
}
userMessage.setVisible(false);
}
//Getters for username and password.
public void getUsername(TextField name) {
UN = name.getText();
}
public void getPassword(PasswordField pass) {
PW = pass.getText();
}
//Event actions.
public void run(ActionEvent event) {
userMessage.setVisible(false);
getUsername(username);
getPassword(password);
//Check field inputs.
if (UN.isBlank()) {
userMessage.setText("Username cannot be blank");
userMessage.setVisible(true);
return;
} else if (PW.isBlank()) {
userMessage.setText("Password cannot be blank");
userMessage.setVisible(true);
return;
}
DataSourceFactory loginCheck = new DataSourceFactory();
Connection con = null;
try {
credentialsAccepted = false;
con = loginCheck.createMariaDBPoolDataSource(UN, PW).getConnection();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT CURRENT_USER");
rs.first();
final String currentUser = rs.getString("CURRENT_USER");
if (currentUser.startsWith(UN + "@")) {
credentialsAccepted = true;
}
stmt.close();
con.close();
} catch (SQLException e) {
System.out.println("Statement Error");
System.err.println("SQL State: " + ((SQLException)e).getSQLState());
System.err.println("Error Code: " + ((SQLException)e).getErrorCode());
System.err.println("Message: " + ((SQLException)e).getMessage());
System.err.println("Cause: " + ((SQLException)e).getCause());
return;
}
//The exception below does not tell me the difference between server offline
//and credentials incorrect, which is a problem for me because I want the
//user response message to be different depending on the outcome.
//I am not sure how to make it do what I want.
} catch (SQLException e) {
System.out.println("Login Failure");
System.err.println("SQL State: " + ((SQLException)e).getSQLState());
System.err.println("Error Code: " + ((SQLException)e).getErrorCode());
System.err.println("Message: " + ((SQLException)e).getMessage());
System.err.println("Cause: " + ((SQLException)e).getCause());
return;
}
if (credentialsAccepted) {
try {
Stage homeStage = new Stage();
ControllerHome home = new ControllerHome();
homeStage.setScene(new Scene(home));
homeStage.setTitle("Equipment Management Tool");
homeStage.setResizable(true);
homeStage.setWidth(1625);
homeStage.setHeight(925);
homeStage.setMaximized(true);
homeStage.show();
//Get the current stage (loginStage) and hide it so it disappears once you are logged in
Stage stage = (Stage) getScene().getWindow();
stage.hide();
} catch(Exception e) {
e.printStackTrace();
return;
}
return;
} else {
userMessage.setText("There was an unexpected error");
userMessage.setVisible(true);
return;
}
}
}
这是数据源类:
public class DataSourceFactory {
public DataSource createMariaDBPoolDataSource(String username, String password) {
Properties props = new Properties();
InputStream inputStream;
MariaDbPoolDataSource mariaDbPoolDS = null;
try {
inputStream = getClass().getResourceAsStream("/properties/db.properties");
props.load(inputStream);
mariaDbPoolDS = new MariaDbPoolDataSource();
mariaDbPoolDS.setUrl(props.getProperty("MARIADB_DB_URL"));
mariaDbPoolDS.setLoginTimeout(Integer.parseInt(props.getProperty("MARIADB_DB_LOGIN_TIMEOUT")));
mariaDbPoolDS.setUser(username);
mariaDbPoolDS.setPassword(password);
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (NumberFormatException e) {
e.printStackTrace();
}
return mariaDbPoolDS;
}
}
这些分别是使用不正确的登录详细信息和无法联系的服务器返回的堆栈跟踪。
java.sql.SQLSyntaxErrorException: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.MariaDbPoolDataSource.getConnection(MariaDbPoolDataSource.java:239)
at emtmodule/com.outlook.sensicalapp.emt.ControllerLogin.run(ControllerLogin.java:93)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at com.sun.javafx.reflect.Trampoline.invoke(MethodUtil.java:76)
at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at javafx.base/com.sun.javafx.reflect.MethodUtil.invoke(MethodUtil.java:273)
at javafx.fxml/com.sun.javafx.fxml.MethodHelper.invoke(MethodHelper.java:83)
at javafx.fxml/javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1782)
at javafx.fxml/javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1670)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Node.fireEvent(Node.java:8890)
at javafx.controls/com.sun.javafx.scene.control.behavior.TextFieldBehavior.fire(TextFieldBehavior.java:184)
at javafx.controls/com.sun.javafx.scene.control.behavior.TextInputControlBehavior.lambda$keyMapping$62(TextInputControlBehavior.java:330)
at javafx.controls/com.sun.javafx.scene.control.inputmap.InputMap.handle(InputMap.java:274)
at javafx.base/com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Scene$KeyHandler.process(Scene.java:4070)
at javafx.graphics/javafx.scene.Scene.processKeyEvent(Scene.java:2121)
at javafx.graphics/javafx.scene.Scene$ScenePeerListener.keyEvent(Scene.java:2597)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$KeyEventNotification.run(GlassViewEventHandler.java:217)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$KeyEventNotification.run(GlassViewEventHandler.java:149)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleKeyEvent$1(GlassViewEventHandler.java:248)
at javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:412)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleKeyEvent(GlassViewEventHandler.java:247)
at javafx.graphics/com.sun.glass.ui.View.handleKeyEvent(View.java:547)
at javafx.graphics/com.sun.glass.ui.View.notifyKey(View.java:971)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:830)
Caused by: java.sql.SQLSyntaxErrorException: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:171)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.pool.Pool.getConnection(Pool.java:413)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.MariaDbPoolDataSource.getConnection(MariaDbPoolDataSource.java:237)
... 58 more
java.sql.SQLSyntaxErrorException: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.MariaDbPoolDataSource.getConnection(MariaDbPoolDataSource.java:239)
at emtmodule/com.outlook.sensicalapp.emt.ControllerLogin.run(ControllerLogin.java:93)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at com.sun.javafx.reflect.Trampoline.invoke(MethodUtil.java:76)
at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at javafx.base/com.sun.javafx.reflect.MethodUtil.invoke(MethodUtil.java:273)
at javafx.fxml/com.sun.javafx.fxml.MethodHelper.invoke(MethodHelper.java:83)
at javafx.fxml/javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1782)
at javafx.fxml/javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1670)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Node.fireEvent(Node.java:8890)
at javafx.controls/javafx.scene.control.Button.fire(Button.java:203)
at javafx.controls/com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:206)
at javafx.controls/com.sun.javafx.scene.control.inputmap.InputMap.handle(InputMap.java:274)
at javafx.base/com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Scene$MouseHandler.process(Scene.java:3862)
at javafx.graphics/javafx.scene.Scene.processMouseEvent(Scene.java:1849)
at javafx.graphics/javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2590)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:409)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:299)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:447)
at javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:412)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:446)
at javafx.graphics/com.sun.glass.ui.View.handleMouseEvent(View.java:556)
at javafx.graphics/com.sun.glass.ui.View.notifyMouse(View.java:942)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:830)
Caused by: java.sql.SQLSyntaxErrorException: No connection available within the specified time (option 'connectTimeout': 5,000 ms)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:171)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.internal.util.pool.Pool.getConnection(Pool.java:413)
at org.mariadb.jdbc@2.6.0/org.mariadb.jdbc.MariaDbPoolDataSource.getConnection(MariaDbPoolDataSource.java:237)
... 58 more
注意:因为这是一项正在进行的工作,所以这里混合了不同的异常处理样式。一旦事情正常工作,它们将被修复。
解决方案
经过大量的 Google-foo 之后,我能够找到这个特定问题的根源。
这是 MariaDB Connector/J 的一个(现在)已知错误。
错误报告:https ://jira.mariadb.org/browse/CONJ-885和 https://github.com/mariadb-corporation/mariadb-connector-j/pull/172
已创建一个修复程序,现在是版本 3.0.1-beta 和 3.0 的一部分。2-rc 的连接器。
推荐阅读
- javascript - 将 IdentityServer 与 ASP.NET + 嵌入式 SPA JS 框架一起使用
- ios - Facebook 无法从 iTunes App Store 检索我的 iPhone Store ID
- javascript - 如何在 javascript 中关闭/隐藏 html 导航选项卡?
- javascript - materializeCSS - 带有标签作为页面链接的扩展导航栏
- java - 并行流的行为与流不同
- vue.js - 命名空间的 Vuex 模块 getter 注册为数字而不是模块名称
- c# - 选择下拉列表时,它应该显示表中列的值..如何实现?
- mongodb - Java-Spark-Mongo:filter(dataset.col(newTime).$greater(oldTime)) 未在完整数据集上运行
- java - 从 Selenium 中的属性文件获取值时获取 NullPointerException
- python-3.x - for循环没有遍历python中的所有列表元素