How to test connection to Oracle Database using Java

Question

Is there a way to test my connection to oracle database using Java? Here's my code.

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.

You should basically try to connect to one database on one IP and if the connection object is null.... then try to Connect to another.... maybe the connection code should be a recursive call till you get a valid connection — AurA
– AurA, Commented Sep 12, 2013 at 6:03
yes you need to handle the exception and call the recursive function again from the catch block — AurA
– AurA, Commented Sep 16, 2013 at 8:15
It's also not working. I cannot understand why I've tried "try catch" but still it will stop working. — Bimbz
– Bimbz, Commented Sep 16, 2013 at 9:38

Subhrajyoti Majumder · Accepted Answer · 2013-09-12 05:49:09Z

22

DriverManager#getConnection it 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 SQLException if a database access error occurs.

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 conn = DriverManager.getConnection(url, username, password);
boolean reachable = conn.isValid(10);// 10 sec

edited Sep 12, 2013 at 5:49

answered Sep 12, 2013 at 5:40

Subhrajyoti Majumder

41.3k13 gold badges84 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bimbz Over a year ago

It says "The method IsValid() is undefined for the type connection?

Subhrajyoti Majumder Over a year ago

Connection#isValid method is available with Connection class since java 1.6. What jdk version you are using?

Bimbz Over a year ago

I see my JDK is still 1.4 hahahaha!! I'll update my JDK first. Thanks!!

Bimbz Over a year ago

will it affect anything on my codes or applications or anything on my machine if I change my JDK? like will eclipse reset? sorry for that very noobish question but I am really a newbie in java. hahaha thanks

Subhrajyoti Majumder Over a year ago

catch the exception, what I mean is DriverManager#getConnection throws SQLException, hadle this exception properly...

|

Joshua Huber · Accepted Answer · 2014-06-17 17:16:30Z

Don't reinvent the wheel. Oracle's JDBC driver already has this functionality built-in.

This is useful: 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:

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)))

Anthony O. · Accepted Answer · 2016-06-21 14:06:40Z

10

Simple Java code to check connection to Oracle DB:

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    
        }    
      }    
    }            
  }    
}

edited Jun 21, 2016 at 14:06

Anthony O.

25k23 gold badges115 silver badges177 bronze badges

answered Jun 16, 2014 at 18:55

xDBA

4214 silver badges5 bronze badges

3 Comments

xxks-kkk Over a year ago

Upvote for providing a very use example on constructing connection url.

Tiago Cardoso Over a year ago

Works like a charm. Just need to add the lib reference to ojdbc6.jar as described in stackoverflow.com/questions/17803818/…

Vignesh Vino Over a year ago

This url doesn't work for me. I referred over here docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/… . I had to replace / with colon(:)

RogerIsDead · Accepted Answer · 2018-03-29 22:55:26Z

3

I wrote a mini command line app to do what above code samples do.

https://github.com/aimtiaz11/oracle-jdbc-tester

Saves anyone coding it up. Just build (with maven) and run it.

answered Mar 29, 2018 at 22:55

RogerIsDead

1253 silver badges11 bronze badges

Comments

Rahul · Accepted Answer · 2013-09-12 05:41:29Z

0

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.

answered Sep 12, 2013 at 5:41

Rahul

45.2k11 gold badges89 silver badges108 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:54:26Z

0

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

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Sep 12, 2013 at 5:43

Lendl Leyba

2,2874 gold badges34 silver badges49 bronze badges

Comments

Luca Basso Ricci · Accepted Answer · 2013-09-12 05:44:38Z

0

You can catch SQLException from DriverManager.getConnection() and looks for ORA-12543.
Read SQLException documentation about vendor code.

answered Sep 12, 2013 at 5:44

Luca Basso Ricci

18.5k2 gold badges51 silver badges75 bronze badges

Comments

Luca Basso Ricci · Accepted Answer · 2013-09-12 05:56:11Z

0

Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(“url”,”username”,”password ″);

look more at here:http://leezk.com/tag/jdbc

edited Sep 12, 2013 at 5:56

Luca Basso Ricci

18.5k2 gold badges51 silver badges75 bronze badges

answered Sep 12, 2013 at 5:48

liunkor

11 bronze badge

Comments

Community · Accepted Answer · 2017-05-23 12:34:15Z

0

"... 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.

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Sep 12, 2013 at 6:12

halfbit

3,4641 gold badge23 silver badges28 bronze badges

Comments

Olexander Yushko · Accepted Answer · 2018-11-30 10:50:28Z

For testing connection i will create and use 2 methods: for connection to db and for test this connection:

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.

Collectives™ on Stack Overflow

How to test connection to Oracle Database using Java

10 Answers 10

8 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

8 Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related