반응형
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의 일부입니다.
언급URL : https://stackoverflow.com/questions/61409688/how-to-differentiate-between-incorrect-login-credentials-and-server-unavailable
반응형
'programing' 카테고리의 다른 글
| MySQL DB를 사용한 Django 날짜 필드 (0) | 2023.08.19 |
|---|---|
| C에서 다른 과정을 어떻게 생성합니까? (0) | 2023.08.19 |
| MariaDb를 사용하여 json(긴 텍스트) 열을 쿼리하여 키 아래에 지정된 값이 있는지 확인하려면 어떻게 해야 합니까? (0) | 2023.08.19 |
| bitnami mariadb의 Python 업데이트 쿼리 (0) | 2023.08.19 |
| 스택리스 파이썬의 단점은 무엇입니까? (0) | 2023.08.19 |