URL string format for connecting to Oracle database with JDBC

user_122607 0
Viewed 268113

I'm a newbie to Java-related web development, and I can't seem to get a simple program with JDBC working. I'm using off-the-shelf Oracle 10g XE and the Eclipse EE IDE. From the books and web pages I've checked so far, I've narrowed the problem down to either an incorrectly written database URL or a missing JAR file. I'm getting the following error:

java.sql.SQLException: No suitable driver found for jdbc:oracle://127.0.0.1:8080

with the following code:

import java.sql.*;

public class DatabaseTestOne {
    public static void main(String[] args) {
        String url = "jdbc:oracle://127.0.0.1:8080";
        String username = "HR";
        String password = "samplepass";

        String sql = "SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE LAST_NAME='King'";
        Connection connection;
        try {
            connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println(statement.execute(sql));
            connection.close();
        } catch (SQLException e) {
            System.err.println(e);
        }
    }
}

What is the proper format for a database URL, anyways? They're mentioned a lot but I haven't been able to find a description.

EDIT (the resolution):

Based on duffymo's answer, I got ojdbc14.jar from Oracle's download site and dropped it in the Eclipse project's Referenced Libraries. Then I changed the start of the code to

...
// jdbc:oracle:thin:@<hostname>:<port>:<sid>
String url = "jdbc:oracle:thin:@GalacticAC:1521:xe";
...

and it worked.

oracle-xe jdbc oracle
8 Answers

Look here.

Your URL is quite incorrect. Should look like this:

url="jdbc:oracle:thin:@localhost:1521:orcl"

You don't register a driver class, either. You want to download the thin driver JAR, put it in your CLASSPATH, and make your code look more like this.

UPDATE: The "14" in "ojdbc14.jar" stands for JDK 1.4. You should match your driver version with the JDK you're running. I'm betting that means JDK 5 or 6.

user_372130 
String host = <host name>
String port = <port>
String service = <service name>
String dbName = <db schema>+"."+service
String url = "jdbc:oracle:thin:@"+host+":"+"port"+"/"+dbName
user_118038190

These are the valid, supported JDBC URLs that Oracle accepts:

Connect using the Oracle JDBC driver (oracle.jdbc.pool.OracleDataSource):

String url = "jdbc:oracle:thin:@tcp://my-host:1522/my-service";
OracleDataSource ods = new OracleDataSource();
ods.setUser(userName);
ods.setPassword(password);
ods.setURL(url);
Connection con = ods.getConnection();

The Oracle JDBC Driver supports the following URL formats:

  • EZConnect Format

    Since 19c, the connection properties can be added at the end of the URL. The syntax uses '?' to indicate the start of connection properties and '&' as a delimiter between each property.

    Syntax:

    jdbc:oracle:thin:@[[protocol:]//]host1[,host2,host3][:port1][,host4:port2] [/service_name][:server_mode][/instance_name][?connection properties]

    Examples:

    • jdbc:oracle:thin:@mydbhost:1521/mydbservice
    • jdbc:oracle:thin:@tcp://mydbhost:1521/mydbservice
    • jdbc:oracle:thin:@tcp://mydbhost1,mydbhost2:1521/mydbservice
    • jdbc:oracle:thin:@tcp://mydbhost1:5521,mydbhost2:1521/mydbservice
    • jdbc:oracle:thin:@tcp://mydbhost1:5521/mydbservice:dedicated
    • jdbc:oracle:thin:@mydbhost1:5521/mydbservice?oracle.net.httpsProxyHost=myproxy&oracle.net.httpsProxyPort=80
    • jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet
    • jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet&ssl_server_cert_dn="Server DN"

  • TNS URL Format

    This format is a more structured way of specifying the connect options and more descriptive.

    Syntax:

    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=<protocol>) (HOST=<dbhost>)(PORT=<dbport>)) (CONNECT_DATA=(SERVICE_NAME=<service-name>))

    Examples:

    • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=mydbhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=mydbservice))
    • jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=5221))) (CONNECT_DATA=(SERVICE_NAME=orcl)))
    • jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS)(PORT=1522)(HOST=myhost)) (CONNECT_DATA=(SERVICE_NAME=dbservicename)) (SECURITY=(ssl_server_cert_dn="CN=testcert.test.com, O=org,L=somecity,ST=state,C=US")))

  • TNS Alias Format

    To use the alias configured in the tnsnames.ora, use the below format. The location of the tnsnames.ora file can be configured using TNS_ADMIN parameter.

    Syntax:

    jdbc:oracle:thin:@<alias_name>

    Examples:

    • jdbc:oracle:thin:@prod_db?TNS_ADMIN=/work/tns/
    • jdbc:oracle:thin:@inst1?TNS_ADMIN=/work/tns/
user_21698450 
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());         
connection = DriverManager.getConnection("jdbc:oracle:thin:@machinename:portnum:schemaname","userid","password");
user_275240
user_350310
Related