Thursday, July 22, 2021

JDBC - How to connect Eclipse to Oracle Database - Step by Step Guide Example

Though I prefer Toad or Oracle SQL Developer tool to connect Oracle database, sometimes it's useful to directly connect Eclipse to Oracle using JDBC using its Data Source Explorer view. This means you can view data, run SQL queries to the Oracle database right from your Eclipse window. This will save a lot of time wasted during switching between Toad and Eclipse or Oracle SQL Developer and Eclipse. Eclipse also allows you to view the Execution plan in both text and Graphical mode, which you can use to troubleshoot the performance of your SQL queries.

In this article, I'll tell you the steps to connect Eclipse to the Oracle database. Since Eclipse uses Java to connect to the Oracle database, it asks for JDBC driver information. Since connecting using a thin Oracle JDBC driver is much easier, as you just need to drop a JAR file in the classpath, we will be using JDBC thin driver to connect Oracle from Eclipse.

Depending upon your Oracle version e.g. Oracle 10g or Oracle 11g you need to download Oracle JDBC thin driver like ojdbc14.jar file. Just download this JAR file and configure it on Eclipse IDE. Enter the username, password, and sid and you are ready to execute the SQL query to the Oracle database from Eclipse.

In order to connect to the Oracle database instance, you need two things:

1) Eclipse IDE for Java EE developers, because that contains the database development plugin which doesn't come along with Eclipse IDE for Java developers. You can download the latest version of Eclipse IDE for Java EE developers here.

2) Oracle JDBC JAR, since Eclipse uses JDBC to connect to the Oracle database it needs JDBC driver JAR for Oracle. You can use any type of JDBC driver, but I prefer Oracle JDBC thin driver like ojdbc14.jar file to connect to Oracle 11g instance. If you don't have one, you can download it from here.

Once you have these two things, we are ready to connect to any local or remote Oracle database instance from Eclipse IDE.   I am using Eclipse Java EE IDE for Web Developers, Version: Kepler Service Release 2.




Steps to connect Oracle database from Eclipse

The steps are exactly similar to what I have shown you before while connecting to Microsoft SQL Server from Eclipse IDE, here, the only difference is, this time, we are choosing Oracle from the list of databases and installing Oracle JDBC driver instead of SQL Server JDBC driver.

Here are the exact steps to connect the Oracle instance from Eclipse:
  1. Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
  2. Create Connection Profile, Chose Oracle
  3. Choose JDBC Driver and specify its location
  4. Specify connection detail e.g. host, port, username, and password
  5. Test Connection

Tips like this save a lot of time and improve productivity and that's why I have always said that a good understanding of Eclipse IDE is a must to become an expert Java J2EE developer and developers should spend some time learning their tools e.g. Eclipse.

If you are primarily a Java EE developer then you can also take a look at Ram Kulkarni's Java EE Development with Eclipse book, which will help you to write code, debug, test, and troubleshoot Java EE 7 applications right from the Eclipse IDE.




Now, let's see each step in little bit of detail with a screenshot if you are ready to connect to the Oracle database from Eclipse, follow it along:

Step 1: Open Eclipse IDE and Select Database Perspective
In order to open the Database Development perspective go to Windows >> Open Perspective >> Other >> Database Development, as shown below:

How to connect Eclipse to Oracle database



Step 2:  Create Connection Profile
For creating a new connection profile, select the "Database Connection" at the top left in the Database Development view, right-click and select "New Connection". This will open the New Connection Profile window as shown below. Now type Oracle and give your connection profile a meaningful name e.g. combination of application, host, and instance.

Steps to connect Eclipse to Oracle 11g



Step 3: Choose JDBC Driver and specify its location
Once you have selected the database, it's time to select the JDBC driver. To configure JDBC driver, just click the setting icon on the previous screen as shown below:

Connect to Oracle 11g from eclipse using JDBC

After that, the following screen will open which will list down all JDBC drivers for supported Oracle versions e.g. Oracle 10g, Oracle 11g, and JDBC thin driver or other drivers. I have chosen the Oracle Thin Driver to connect Oracle 11g as seen below:

JDBC driver JAR for Oracle 11g


Once you select a particular driver, you can see the error message appear at the top saying "Unable to locate JAR/ZIP in the file system as specified by the driver definition: ojdbc14.jar", this means you need to locate the path of this JAR in your local file system, as shown below. If you don't specify the correct JAR, you will get "java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver" error.


Once you are done, click Ok and then it will take you to the previous screen. Click next there.


Step 4:  Specify Database Connection details
Now, it's time to specify the database connection details e.g. host, port, sid, username, and password required to connect to an Oracle database instance.



Step 5: Test Connection
Once you are done with putting connect detail, just click the "Test Connection" button, if everything is good then you will see a ping succeed message, if not then you will see a "ping failed" message.


That's all about how to connect Eclipse to Oracle database using Java and JDBC. This is an immensely helpful tip if your application is using Oracle database at the backend and you frequently need to select, update or delete data.

28 comments:

  1. Wow! It's really work. Thanks Javin for such a useful document. I tried for a connection and successfully connected eclipse to oracle database.

    ReplyDelete
    Replies
    1. I didn't find database development..what can I do??

      Delete
  2. after clicking add zar/zip on ojdbc14 a new filename is asked in my installation..what should i do?

    ReplyDelete
    Replies
    1. @Unknown, can you please elaborate? When you click add JAR option a dialog will open and you just need to choose the ojdbc14.jar and upload, that's it. It will then add the above mentioned JAR in the classpath.

      Delete
  3. I am receiving below error after clicking test connection
    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)

    ReplyDelete
  4. After clicking on test connection I am receiving below error:
    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)

    ReplyDelete
    Replies
    1. Hello @Abhinav, check if you are using correct SID and if that exists in Oracle server. Talking to a DBA may help.

      Delete
    2. you need to use service name not sid with later databases from oracle (18, 19 +)

      Delete
  5. How and where to find Sid,host, port etc

    ReplyDelete
    Replies
    1. You can find that from your Oracle instance of Oracle DBA.

      Delete
  6. Hi Javin,
    I am new to Oracle as well as Eclipse..
    I have Oracle 11g XE and Eclipse Oxygen installed on my Windows 10 OS.
    I am trying to connect to the database (using your guide) but keep on getting ping failed while testing connection. the details of the error are as below:
    java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:414)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
    at org.eclipse.datatools.enablement.internal.oracle.JDBCOracleConnectionFactory.createConnection(JDBCOracleConnectionFactory.java:27)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)

    can you help in resolving this?
    Thanks in advance
    Prasad

    ReplyDelete
    Replies
    1. Hi prasad may I know how this issue of you has been resolved?

      Delete
  7. How to create a java database connection with oracle using the oracle thin driver but with : the service name instead of SID. I can on netbeans but not in Eclipse ?????

    ReplyDelete
  8. I got ping failed several times and searched according to another website
    the tnsnames.ora file in the Oracle installation directory, it helped me to find the port, sid and host name to configure Eclipse.

    ReplyDelete
  9. how to write xml file to establish a connection to Oracle 11g in web dynamic project

    ReplyDelete
  10. hi what is SID,Username,Password...? (i am doing it for the first time)

    ReplyDelete
    Replies
    1. Hello Unknown, SID is oracle specific Id for a database instance and username, password are your authentication credentials to login into database. Please check with your team mates or DBA about that, they will have the details you need to login.

      Delete
  11. The Network Adapter could not establish the connection

    ReplyDelete
    Replies
    1. Hello Pavani, most likely the hostname and port combination is wrong in your case. Please check if Oracle database is actually up and listening on that port. You can use telnet command to check that.

      Delete
    2. I have mysql in my windows os but how to connect to eclipse ide

      Delete
  12. Hi , I am using ojdbc6.jar file and trying to insert some data. it was showing error like sql did not end properly.
    Insert into mns sel ids from mns_19; Can you please suggest me which ar file support this insert statement.

    ReplyDelete
  13. I m not getting the database development option(Windows >> Open Perspective >> Other >> Database Development).I have added jar file from the library. what should i do now.

    ReplyDelete
    Replies
    1. Which version of Eclipse are you using? Also are you using Java or J2EE one?

      Delete
  14. I have created database table named as admin_login and inserted id, username and password with 2 records.. and create a connection using servlet but can not getting the values in database table :
    see my servlet connection code below :

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    System.out.println("test2");
    try{
    response.setContentType("text/html");
    PrintWriter pw = response.getWriter();
    System.out.println("under try");
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");
    Statement st=con.createStatement();
    System.out.println("connection established successfully...!!");
    String sql = "select * from admin_login6";
    ResultSet rs=st.executeQuery(sql);

    System.out.println(rs.getRow()); // i get 0 rows

    while(rs.next())
    {
    System.out.println("under while");
    System.out.println(rs.getInt(1)+" "+rs.getString(2));
    }

    }catch(SQLException e) {
    System.out.println(e);
    }
    catch(ClassNotFoundException e) {
    System.out.println(e);
    }

    }

    ReplyDelete
    Replies
    1. you can double check your table name, select * From admin_login6 while you mentioned that your table name is "admin_login" then why "6", also you can direclty run your query first in SQL console like SQL Developer or TOAD for Oracle, Java code looks correct.

      Delete
  15. Hi, I had got test connection successful. still getting Error while running program:

    No suitable driver found for jdbc:oracle:thin:

    ReplyDelete
    Replies
    1. That's strange, I think there is some difference in the driver name, double check that.

      Delete
  16. Hi,

    I am using Windows 10 64 bit laptop,I am trying to connect Oracle 11g XE to Eclipse IDE Enterprise Java, i followed the steps as mentioned on your site, as follows :

    Open Eclipse IDE and Select Database Perspective (Windows >> Open Perspective >> Other >> Database Development).
    Create Connection Profile, Chose Oracle

    After selecting Oracle option from the list i click next and then i see most of the options faded, i click on the small circle option next to driver's bar, i see the option to add JAR file but all those tabs are faded/unclickable

    When i download the JAR file for Oracle 11G XE, do i need to relocate to certain folder? or am i missing any step which is why the eclipse is unable to detect the Oracle drivers?


    Please Help ..

    -Thanks,
    Yahya

    ReplyDelete

Feel free to comment, ask questions if you have any doubt.