Database, JAVA, Programmimg Languages, Softwares, Tips

Connect to Apache Derby (Java DB) via JDBC

Apache Derby is a lightweight, portable database engine written purely in Java. Java DB is a just an Oracle’s distribution of Derby in their JDK.This article presents the steps to quickly get started with Derby, from downloading its JDBC driver to write code for making connections.

1.    Downloading Derby JDBC driver library

Download the latest version of Derby here (as of this writing, the latest release is 10.9.1.0). The distribution includes the following pieces of software component:

Component Jar files
Embedded database engine and JDBC driver derby.jar
Network client JDBC driver derbyclient.jar
Network server derbynet.jar, derbyrun.jar
Command line tools derbytools.jar
Localization messages derbyLocale_xx_YY.jar

If you are using JDK 1.7, then Derby is already included in JDK installation under the name Java DB in JDK_HOME\dbdirectory. The jar files are located in JDK_HOME\db\lib directory.In both case, you have to place appropriate jar file to the classpath:

    • derby.jar: for embedded driver.
    • derbyclient.jar: for network client driver.

2.    Loading Derby JDBC drivers

Derby differentiates two types of JDBC driver:

Type of driver JDBC Driver Class Name
Embedded driver org.apache.derby.jdbc.EmbeddedDriver
Network client driver org.apache.derby.jdbc.ClientDriver

So if you are planning to use everything of Derby in one machine, go with the embedded driver. Or if the JDBC client connects to Derby server on a remote machine, go with the network client driver.

If you are using Java 5.0 or earlier, you have to load the driver explicitly like this:

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

or

DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());

However, since Java 6.0 or later, loading JDBC driver as such becomes optional. The driver manager can load appropriate driver based on the database connection URL.

3.    Derby JDBC database connection URL for embedded driver

Following is the syntax of Derby JDBC database connection URL for the embedded driver:

  jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*
    • Where subsubprotocol tells where Derby should look for the database. It can be one of:
  • directory: looks for the database in the file system. The directory can be relative path or absolute path. For the relative path, Derby will look in the system directory (specified by the environment variable user.dir). This is the default location if subsubprotocol is not specified.
        • memory: looks for the database in memory. This may e useful in case we only use a temporary database.
        • classpath: looks for the database in the file system which is relative to the classpath directory. In this way the database is treated as in read-only mode.
        • jar: looks for the database inside a jar or zip file. Read-only mode.
    • attribute=value: specifies one or more additional attributes when making the connection. Some commonly used attributes are:
        • create=true: creates the database if it does not exist.
        • shutdown=true: closes the database. This must be used without database name.
        • user=<username>: specifies the username to connect.
        • password=<password>: specifies password of the username to connect.

Example connection URLs:

    • Connect and create a database called webdb under the directory codejava. The database path is relative to the system directory.

jdbc:derby:codejava/webdb;create=true

    • Connect to a database in the file system using absolute path:

jdbc:derby:E:/projects/codejava/webdb;create=true

    • Connect and create a database if not exist in the memory:

jdbc:derby:memory:codejava/webdb;create=true

    • Connect to a database presents in the classpath:

jdbc:derby:classpath:webdb

Where the absolute directory E:/projects/codejava is added to the classpath.

    • Connect to a database called webdb inside a jar file which is on the classpath:

jdbc:derby:jar:webdb

    • Connect to a database called webdb inside a jar file db.jar which is not on the classpath:

jdbc:derby:jar:(E:/projects/db.jar)webdb

    • Shutdown the current database:

jdbc:derby:;shutdown=true

 

4.    Derby JDBC database connection URL for network client driver

Here is the syntax of Derby JDBC database connection URL for the network client driver:

  jdbc:derby://server[:port]/databaseName[;attribute=value]*

The default port is 1527 if omitted. For example, to connect the user tom with password secret to the database webdb on the server dbserver, use the following URL:

jdbc:derby://dbserver/webdb;user=tom;password=secret

5.    Making Derby JDBC connection examples

With JDBC, there are three different ways to establishing a connection to the database, corresponding to three version of the method getConnection() of the DriverManager class:

  • Using only a database URL:
String dbURL = "jdbc:derby:codejava/webdb;create=true";
Connection conn = DriverManager.getConnection(dbURL);
  • Using a database URL with user and password:
String dbURL = "jdbc:derby://localhost/webdb;create=true";
String user = "Banku";
String password = "secret";
Connection conn = DriverManager.getConnection(dbURL, user, password);
And following is a full example program:
package net.codejava.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
 * This program demonstrates how to connect to Apache Derby (Java DB) database
 * for the embedded driver and network client driver.
 *
 */
public class JdbcDerbyConnection {
    public static void main(String[] args) {
        
        try {
            // connect method #1 - embedded driver
            String dbURL1 = "jdbc:derby:codejava/webdb1;create=true";
            Connection conn1 = DriverManager.getConnection(dbURL1);
            if (conn1 != null) {
                System.out.println("Connected to database #1");
            }
            
            // connect method #2 - network client driver
            String dbURL2 = "jdbc:derby://localhost/webdb2;create=true";
            String user = "tom";
            String password = "secret";
            Connection conn2 = DriverManager.getConnection(dbURL2, user, password);
            if (conn2 != null) {
                System.out.println("Connected to database #2");
            }
            // connect method #3 - network client driver
            String dbURL3 = "jdbc:derby://localhost/webdb3";
            Properties properties = new Properties();
            properties.put("create", "true");
            properties.put("user", "tom");
            properties.put("password", "secret");
            
            Connection conn3 = DriverManager.getConnection(dbURL3, properties);
            if (conn3 != null) {
                System.out.println("Connected to database #3");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
Advertisements

1 thought on “Connect to Apache Derby (Java DB) via JDBC”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s