JDBC捕获登录失败异常并重新提示输入 [英] JDBC catching login failure exception and re-promts for input
问题描述
try{
Class.forName ("oracle.jdbc.driver.OracleDriver"); // identify
Connection con = DriverManager.getConnection
("jdbc:oracle...",userid,password);
// create Statement and execute sql statement after
} catch (SQLException ex) {
Logger.getLogger(Transcript.class.getName()).log(Level.SEVERE, null, ex);
}
我可以找到的唯一异常是SQLException，但是如何将其与登录失败和错误的sql语句区分开?
The only exception I can find is SQLException, but how can it distinguish from login failure and incorrect sql statement?
我希望它再次提示用户输入ID和密码的机会，我可以将其放在catch块中吗?
I want it to re-promts the user for another chance of entering id and password, do I put that in the catch block?
如果是这样，我是否需要另一个嵌套的try-catch来处理异常?
if so, do i need another nested try-catch to handle the exception?
由于我想分别处理这两种情况，但是似乎只能使用一个SQLException.
As I want to handle the two situations separately, but it seems like there is only a SQLException I can use.
推荐答案
实际上，您还会得到更多:
Actually, you'll get a few more:
If you don't have the right Oracle driver in the classpath in Class.forName:
Exception in thread "main" java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
If DriverManager.getConnection fails because of a wrong jdbc url syntax:
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle...
If login/password fails at DriverManager.getConnection
Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied
In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLException: Invalid column name
您可以将登录失败与SQL错误分开，因为它们发生在代码的不同位置.
You can separate the login failure from SQL failures because they happen at different place in your code.
public class Oracle {
public static void main(final String[] args) throws ClassNotFoundException, SQLException {
final Connection con;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch (final Exception e) {
throw new RuntimeException("Driver failure");
}
try {
con = DriverManager.getConnection ("jdbc:oracle:thin:schema/password@host:port:sid");
} catch (final Exception e) {
throw new RuntimeException("Login failure");
}
try {
final Statement stmt = con.createStatement();
final String sql = "select 1 from dual";
final ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
// do something with the data
}
rs.close();
} catch (final Exception e) {
throw new RuntimeException("SQL failure");
}
}
}
注意:这是一个蛮力的示例，在生产代码中，您可能需要更精确的异常处理程序，并且应确保始终关闭资源.例如，如果在读取resut集时由于发生SQL错误而由于引用无效列而导致错误，则可能需要关闭语句和结果集.
Note: it is a brute-force example, in a production code you may need more precise exception handlers, and you shall ensure that resources are always closed. For example, in case of an SQL error while reading a resut set and having an error because of referring to an invalid column, you may need to close the statement as well as the result set.
另外两个针对Oracle的提示:
Two more Oracle-specific hints:
-
不要在DriverManager.getConnection中将登录名和密码作为参数传递. Oracle连接具有更多参数，构造包含以下所有内容的JDBC URL更为容易:"jdbc:oracle:thin:schema/password @ host:port:sid"
Do not pass login and password as a param in DriverManager.getConnection. An Oracle connection has a lot more parameters, it is easier to construct a JDBC url which contains everything: "jdbc:oracle:thin:schema/password@host:port:sid"
- 请注意，需要host:port.很多时候，多个Oracle服务器实例可以共享同一端口.您可以使用服务ID(SID)来区分它们.
对于真正的Oracle异常，该异常被包装到SQLException或SQLSyntaxErrorException中，有时是这种情况，有时是这种情况.我发现在此之上放置自己的逻辑非常方便:
In case of a real Oracle exception, the exception is wrapped to an SQLException or an SQLSyntaxErrorException, sometimes this, sometimes that. I found pretty handy to put an own logic on top of this:
-
我用try..catch捕获了任何异常.如果错误消息以ORA-开头，则是Oracle错误，我将其包装到自定义异常类中并重新抛出.
I catch any exception with try..catch. If the error message starts with ORA-, then it is an Oracle error, and I wrap it to a custom exception class and re-throw.
在自定义异常中解析Oracle错误代码可能很方便.您可以使用它来区分自定义Oracle异常(-20000 ..- 20999)您在PL/SQL中抛出的异常(并可以表示业务级别错误).其余错误代码始终会指示技术错误，即您的代码或数据库结构中有问题.
It might be handly to parse the Oracle error code in your custom exception. You can use that to differentiate between custom Oracle exceptions (-20000..-20999) what you throw in your PL/SQL (and can signal business level error). The rest of error codes always tell a technical error, i.e. something is wrong in your code or in the database structure.
要关闭资源(java7之前的版本)，请最终使用try..finally.请注意，当实际关闭资源失败时，对可能的其他异常的悲观处理.
To close a resource (pre-java7), use try..finally. Note the pessimistic handling of possible further exceptions when actually closing a resouce fails.
Statement stmt=null;
ResultSet rs=null;
try {
try {
final Statement stmt = con.createStatement();
final String sql = "select 1 from dual";
rs = stmt.executeQuery(sql);
while(rs.next()) {
// do something with the data
}
rs.close();
rs=null;
stmt.close();
stmt=null;
} catch (final Exception e) {
throw new RuntimeException("SQL failure");
}
} finally {
if (rs!=null) {
try {
rs.close();
} catch (Exception e) {
// ignore - we can't do too much
}
}
if (stmt!=null) {
try {
stmt.close();
} catch (Exception e) {
// ignore - we can't do too much
}
}
}
要在Java7及更高版本上关闭资源，您可以受益于尝试使用资源块.请参见如何在JDBC中使用try-with-resources? /a>
To close the resources on Java7 and above, you can benefit of the try with resource block. See How should I use try-with-resources with JDBC?
这篇关于JDBC捕获登录失败异常并重新提示输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
