How to use SQLDeveloper to connect to embedded Derby database

Question

I have a project using derby and JPA. I can connect to the database fine within my application. I would like to connect to the embedded database with SQL Developer so I can easily browse/query the data in the database.

Here is the derby dependency I'm using:

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.7.1.1</version>
</dependency>

Here is the the connection info I'm using for JPA:

<property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:derby:fs-hash-database;create=true"/>

Using SQL Developer 3.2.09 I tried to add the driver in Tools > Preferences > Database > Third Party JDBC Drivers with the following entry:

C:\Users\axiopisty\.m2\repository\org\apache\derby\derby\10.7.1.1\derby-10.7.1.1.jar

But when I try to create a new connection, SQL Developer still only shows me the tabs for Oracle and Access. I can't get the JDBC tab for the 3rd party driver.

I think the problem is that the jar that I am adding as the 3rd party driver is the entire derby jar, not just the driver. Yet the driver is contained in the jar.

Does anyone know how to configure SQL Developer to use the org.apache.derby.jdbc.EmbeddedDriver contained within derby-10.7.1.1.jar?

Answer 1

SQL Developer does not support Apache Derby. It only supports a very limited (and fixed) set of DBMS as documented on the WebSite:

• Oracle (obviously)
• MySQL (obviously)
• SQL Server
• DB2
• MS Access
• Sybase
• Teradata

(All third party DBMS are listed as "read-only" on the website - whatever that means)

You will need a "real" general purpose JDBC client to use it against Derby like Squirrel, DbVisualizer or SQL Workbench/J.

Answer 2

Oracle SQL Developer can be manually configured to work with Derby using the drivers that come with the JDK as of Java 8.

Step 1: In Oracle SQL Developer, include Derby related libraries.

Oracle SQL Developer -> Tools -> Preferences -> Databases -> Third Party JDBC Drivers. I simply [Add Entry...] all libraries under C:\Program Files\Java\jdk1.8.0_92\db\lib

Step 2: Manually edit connections.xml

Edit C:\Users\USERNAME\AppData\Roaming\SQL Developer\system4.1.2.20.64\o.jdeveloper.db.connection.12.2.1.0.42.151001.541\connections.xml

In this example I am using embedded Derby driver.

<Reference name="DerbyConn" className="oracle.jdeveloper.db.adapter.DatabaseProvider" xmlns="">
  <Factory className="oracle.jdevimpl.db.adapter.DatabaseProviderFactory1212"/>
  <RefAddresses>
     <StringRefAddr addrType="OracleConnectionType">
        <Contents>BASIC</Contents>
     </StringRefAddr>
     <StringRefAddr addrType="RaptorConnectionType">
        <Contents>Microsoft SQL Server</Contents>
     </StringRefAddr>
     <StringRefAddr addrType="customUrl">
        <Contents>jdbc:derby:firstdb;create=true</Contents>
     </StringRefAddr>
     <StringRefAddr addrType="driver">
        <Contents>org.apache.derby.jdbc.EmbeddedDriver</Contents>
     </StringRefAddr>
     <StringRefAddr addrType="subtype">
        <Contents>SQLServer</Contents>
     </StringRefAddr>
     <StringRefAddr addrType="ConnName">
        <Contents>DerbyDB</Contents>
     </StringRefAddr>
  </RefAddresses>

Note:
1. The XML setting can be as plain as shown above.
2. customUrl is jdbc:derby:firstdb;create=true. This will initialize firstdb schema in C:\sqldeveloper\bin\firstdb. You can use jdbc:derby:D:\\Project\\derbydb\\firstdb to initialize schema to an absolute location.