[Q#19536162][A#19537440] How to connect JDBC to tns oracle

https://stackoverflow.com/q/19536162

I can connect from plsql to database using tns file Now I want to connect to the database from my Java using JDBC. I search google and I find that I have to using this connection String: My computer name is myPC The port that is written in the tnsfile is 5151 So I tried this connection String but I got this Exception

Answer

https://stackoverflow.com/a/19537440

You have to set a property named oracle.net.tns_admin to point to the location of the folder containing your tnsnames.ora file. Then you specify the entry from that file after the @ sign in your DB URL. Check example below. You can find more information here: Data sources and URLs - Oracle Documentation Example entry from tnsnames.ora file: Where my_net_service_name string is what you have to subsitite for ENTRY_FROM_TNSNAMES from my Java example.

APIzation

import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    System.setProperty("oracle.net.tns_admin", "C:/app/product/11.2.0/client_1/NETWORK/ADMIN");
    String dbURL = "jdbc:oracle:thin:@ENTRY_FROM_TNSNAMES";

    Class.forName ("oracle.jdbc.OracleDriver");

    Connection conn = null;
    Statement stmt = null;

    try {
      conn = DriverManager.getConnection(dbURL, "your_user_name", "your_password");

      System.out.println("Connection established");

      stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT dummy FROM dual");

      if (rs.next()) {
        System.out.println(rs.getString(1));
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      if (stmt != null) try { stmt.close(); } catch (Exception e) {}
      if (conn != null) try { conn.close(); } catch (Exception e) {}
    }
  }
}

package com.stackoverflow.api;

import java.sql.Connection;
import java.sql.DriverManager;

public class Human19537440 {

  public static Connection connectToOracle(
    String dbURL,
    String driver,
    String username,
    String password
  ) throws ClassNotFoundException {
    System.setProperty(
      "oracle.net.tns_admin",
      "C:/app/product/11.2.0/client_1/NETWORK/ADMIN"
    );
    Class.forName(driver);
    Connection conn = null;

    try {
      conn = DriverManager.getConnection(dbURL, username, password);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return conn;
  }
}

package com.stackoverflow.api;

import java.sql.*;

/**
 * How to connect JDBC to tns oracle
 *
 * @author APIzator
 * @see <a href="https://stackoverflow.com/a/19537440">https://stackoverflow.com/a/19537440</a>
 */
public class APIzator19537440 {

  public static void connectJdbc(String dbURL) throws Exception {
    System.setProperty(
      "oracle.net.tns_admin",
      "C:/app/product/11.2.0/client_1/NETWORK/ADMIN"
    );
    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn = null;
    Statement stmt = null;
    try {
      conn =
        DriverManager.getConnection(dbURL, "your_user_name", "your_password");
      System.out.println("Connection established");
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT dummy FROM dual");
      if (rs.next()) {
        System.out.println(rs.getString(1));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (stmt != null) try {
        stmt.close();
      } catch (Exception e) {}
      if (conn != null) try {
        conn.close();
      } catch (Exception e) {}
    }
  }
}