9

I have used JDBC to connect to many different relational systems over the years: H2, HSQLDB, MySQL, Oracle, Postgres, etc. And in every case, each system seems to have its own flavor of connection string syntax.

I can't imagine that a long-standing API like JDBC wouldn't have a defined, enforced grammar for connection string validation. For example:

Some valid HSQLDB connection strings:

jdbc:hsqldb:mem:mymemdb
jdbc:hsqldb:res:org.my.path.resdb
jdbc:hsqldb:file:/opt/db/testdb

MySQL:

jdbc:mysql://localhost/test

Postgres:

jdbc:postgresql://localhost/test

H2:

jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:tcp://dbserv:8084/~/sample

From all these examples, I gather the basic, generalized syntax:

jdbc:<vendor>:<vendor-specific-uri>

Where <vendor> is the name of the system (h2, mysql, etc.), and <vendor-specific-uri> is either a path or some vendor-specific way of determining the location of a database.

I've done a lot of digging, and for the life of me, I can't seem to find where JDBC defines valid connection string syntax. Specifically:

  • What is the general grammar/definition of a valid JDBC connection string?
  • What are the different names of each token/component of the connection string? For instance, is "jdbc:" called something, like the "JDBC protocol"? What is the proper name for my <vendor> and <vendor-specific-uri> segments?
IAmYourFaja
  • 55,468
  • 181
  • 466
  • 756

2 Answers2

13

The URL syntax is specified in the JDBC specification, specifically in section 9.4:

The format of a JDBC URL is :

jdbc:<subprotocol>:<subname>

where subprotocol defines the kind of database connectivity mechanism that may be supported by one or more drivers. The contents and syntax of the subname will depend on the subprotocol.

Note – A JDBC URL is not required to fully adhere to the URI syntax as defined in RFC 3986, Uniform Resource Identifier (URI): Generic Syntax

There is no more formality to it than this. The subprotocol usually is some identifier for the database or the driver. Subname is freeform, although drivers usually do follow URI-like syntax.

There is also no need for more formality. The DriverManager will simply offer the URL to each registered java.sql.Driver in turn and the first one to accept is used to connect.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
3

If you take a look into the source code, you will see that a JDBC driver implements java.sql.Driver. This interface has a method

boolean acceptsURL(String url) throws SQLException;

From the JavaDoc:

Retrieves whether the driver thinks that it can open a connection to the given URL. Typically drivers will return true if they understand the subprotocolspecified in the URL and false if they do not.

So the dirver for your database is responsible to implement this method. For H2, this implementation is

@Override
public boolean acceptsURL(String url) {
    if (url != null) {
        if (url.startsWith(Constants.START_URL)) {
            return true;
        } else if (url.equals(DEFAULT_URL)) {
            return DEFAULT_CONNECTION.get() != null;
        }
    }
    return false;
}

Other DBMS have different implementations.

Edit: For H2, the constant Constants.START_URL is "jdbc:h2:". So even the leading jdbc is not part of any formal grammar.

  • Thanks @Lutz Horn (+1) - that's actually a bit *shocking* to me. So, would it be safe to say that, based on `acceptsUrl(...)`'s JavaDoc, that the formal grammar/anatomy of a JDBC connection string is: `jdbc:/:`? – IAmYourFaja Dec 09 '14 at 14:03
  • The JDBC specification actually specifies `jdbc::`, but it is not enforced in `DriverManager` itself. – Mark Rotteveel Dec 09 '14 at 16:24
  • 1
    Also note that `DriverManager` doesn't actually use `acceptsURL`, it will simply call [`Driver.connect`](https://docs.oracle.com/javase/8/docs/api/java/sql/Driver.html#connect-java.lang.String-java.util.Properties-) on each registered driver and the first one not to return `null`, but either return a `Connection` or throw an `SQLException` is the right driver. – Mark Rotteveel Dec 09 '14 at 16:30