如何使用 Java 测试与 Oracle 数据库的连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18756113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test connection to Oracle Database using Java
提问by Bimbz
Is there a way to test my connection to oracle database using Java? Here's my code.
有没有办法使用 Java 测试我与 oracle 数据库的连接？这是我的代码。
public class OracleConnection {
public static void main(String[] args) throws Exception {
//connect to database
Class.forName("oracle.jdbc.driver.OracleDriver");
String serverName = "00.000.0.000";
String portNumber = "1521";
String sid = "My Sid";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "UNAME";
String password = "PASSWORD";
Connection conn = DriverManager.getConnection(url, username, password);
}
}
What I want to do is test the database if it is reachable and if its not then the program will connect to the next database. I have 8 production database.
我想要做的是测试数据库是否可访问，如果不可访问，则程序将连接到下一个数据库。我有 8 个生产数据库。
采纳答案by Joshua Huber
Don't reinvent the wheel. Oracle's JDBC driver already has this functionality built-in.
不要重新发明轮子。Oracle 的 JDBC 驱动程序已经内置了这个功能。
This is useful: http://www.orafaq.com/wiki/JDBC
这很有用：http: //www.orafaq.com/wiki/JDBC
import java.util.ArrayList;
import java.sql.*;
public class OracleConnection {
public static void main(String[] args) throws Exception {
//connect to database
Class.forName("oracle.jdbc.driver.OracleDriver");
ArrayList<String> serverNames = new ArrayList<String>();
serverNames.add("yourhostname1");
serverNames.add("yourhostname2");
serverNames.add("yourhostname3");
serverNames.add("yourhostname4");
String portNumber = "1521";
String sid = "ORCLSID";
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)" ;
for (String serverName : serverNames) {
url += "(ADDRESS=(PROTOCOL=tcp)(HOST="+serverName+")(PORT="+portNumber+"))";
}
url += ")(CONNECT_DATA=(SID="+sid+")))";
String username = "USERNAME";
String password = "PASSWORD";
// System.out.println(url); // for debugging, if you want to see the url that was built
Connection conn = DriverManager.getConnection(url, username, password);
}
}
The above code actually builds and uses url that looked like this (as example belows). I got this explicitly by uncommenting the debugging line near the end of the code:
上面的代码实际上构建并使用了如下所示的 url（如下例所示）。我通过取消注释代码末尾附近的调试行明确地得到了这一点：
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=
(LOAD_BALANCE=ON)(FAILOVER=ON)
(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname2)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname3)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname4)(PORT=1521))
)(CONNECT_DATA=(SID=ORCLSID)))
回答by Subhrajyoti Majumder
DriverManager#getConnectionit self attempts to establish a connection to the given database URL. The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers. and thorws SQLExceptionif a database access error occurs.
DriverManager#getConnection它自己尝试建立到给定数据库 URL 的连接。DriverManager 尝试从注册的 JDBC 驱动程序集中选择合适的驱动程序。SQLException如果发生数据库访问错误，则抛出异常。
you can test you connection is valid or not with Connection#isValid(int timeout)returns true if the connection has not been closed and is still valid.
您可以测试您的连接是否有效，Connection#isValid(int timeout)如果连接尚未关闭并且仍然有效，则返回 true。
...
Connection conn = DriverManager.getConnection(url, username, password);
boolean reachable = conn.isValid(10);// 10 sec
回答by SudoRahul
You can have an array of the your database server ips and iterate over them. For every failed connection attempt, you can proceed to the next ip from the array and try again. In case of a successful connection, you break the loop there and use the current connection which was established.
您可以拥有一组数据库服务器 ips 并对其进行迭代。对于每次失败的连接尝试，您都可以从阵列中转到下一个 ip 并重试。如果连接成功，您可以在那里中断循环并使用已建立的当前连接。
回答by Lei Leyba
Maybe you should ping the IP address of the server. You should check this out: Ping function returns that all pinged IP addresses is reachable
也许您应该 ping 服务器的 IP 地址。您应该检查一下： Ping 函数返回所有 ping 的 IP 地址都可以访问
回答by Luca Basso Ricci
You can catch SQLExceptionfrom DriverManager.getConnection()and looks for ORA-12543.
Read SQLException documentation about vendor code.
你能赶上的SQLException从DriverManager.getConnection()并查找ORA-12543。
阅读有关供应商代码的 SQLException 文档。
回答by liunkor
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(“url”,”username”,”password ″);
look more at here:http://leezk.com/tag/jdbc
在这里查看更多：http: //leezk.com/tag/jdbc
回答by halfbit
"... and if its not then the program will connect to the next ..." I wonder if a cluster connection string containing multiple server addresses could work for you. (I did not try this myself.) Look at Oracle Connection String for RAC Environment.
“ ...如果不是，那么程序将连接到下一个...” 我想知道包含多个服务器地址的集群连接字符串是否适合您。（我自己没有尝试过。）查看Oracle Connection String for RAC Environment。
回答by xDBA
Simple Java code to check connection to Oracle DB:
检查与 Oracle DB 的连接的简单 Java 代码：
import java.sql.*;
public class Test {
private final static String DB_URL = "jdbc:oracle:thin:@//192.168.1.105:1521/MYORA";
private final static String USER = "myuser";
private final static String PASS = "mypwd";
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// ignore
}
}
}
}
}
回答by RogerIsDead
I wrote a mini command line app to do what above code samples do.
我编写了一个迷你命令行应用程序来执行上述代码示例的操作。
https://github.com/aimtiaz11/oracle-jdbc-tester
https://github.com/aimtiaz11/oracle-jdbc-tester
Saves anyone coding it up. Just build (with maven) and run it.
保存任何人编码。只需构建（使用 maven）并运行它。
回答by Alexander Yushko
For testing connection i will create and use 2 methods: for connection to db and for test this connection:
对于测试连接，我将创建并使用 2 种方法：用于连接到 db 并测试此连接：
Class Connector {
private static final String CONNECTION_STRING = "jdbc:oracle:thin:@//%s:%d/%s";
private static final String QUERY_IS_CONNECTED = "SELECT * FROM table WHERE field = 'example'";
private static final Log LOG = LogFactory.getLog(Connector.class);
public Connection createConnection() {
Connection connection = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection(String.format(CONNECTION_STRING, "127.0.0.1", "1521", "dbName"), "userName", "password");
} catch (Exception e) {
LOG.error("createConnection: connection error");
}
return connection;
}
public boolean isConnected() {
try (Connection connection = createConnection()) {
if (connection.isClosed()) {
return false;
}
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(QUERY_IS_CONNECTED)) {
if (resultSet == null) {
return false;
}
} catch (Exception e) {
LOG.error("isConnected: Query error", e);
return false;
}
} catch (Exception e) {
LOG.error("isConnected: Check connection error", e);
return false;
}
return true;
}
}
createConnection() - default connection to your db. Values of ip,port,dbName,userName and password will be yours.
isConnected() - first part check correct your connection and second part checks the correctness of working with the database. As there can be a connection, but not to be access for requests.
createConnection() - 到您的数据库的默认连接。ip、port、dbName、userName 和 password 的值将是您的。
isConnected() - 第一部分检查连接是否正确，第二部分检查使用数据库的正确性。因为可以有连接，但不能访问请求。
