Core Java

Java DB Embedded Mode

Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 SDK.

Java DB has two deployment options: Embedded and Network Server.

This post is about the Embedded deployment or mode.

1. Embedded

 
In an Embedded mode:

  • The database is accessed from one application only.
  • There is no network connectivity between the application and the database.
  • The application and the database run in the same JVM (Java Virtual Machine).

The database is accessed from a Java application using JDBC (Java Database Connectivity). Typically, the database is started and stopped by the application that accesses it. And, there is no database administration required.

image

1.1. Starting Java DB

Start the Java DB from an application. First, load the database JDBC driver.  Next, connect to the database with a connection URL.

  • The driver is included in the Java DB: org.apache.derby.jdbc.EmbeddedDriver
  • The connection URL format is: jdbc:derby:databaseName;URLAttributes;
  • An example connection URL is jdbc:derby:SampleDB;create=true;, where, the database name is SampleDB and the ‘create=true’ is an attribute name/value pair.

The following is an example Java method that starts the database using JDBC:

/*
 * The method creates a Connection object. Loads the embedded driver,
 * starts and connects to the database using the connection URL.
 */
public static Connection createDatabaseConnection()
		throws SQLException, ClassNotFoundException {
	String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	Class.forName(driver);
	String url = "jdbc:derby:sampleDB";
	Connection c = DriverManager.getConnection(url);
	return c;
}

The Connection object created in the above method is used to access the database objects and data using SQL. sampleDB is the name of the database.

NOTE: The derby.jar library is required to be in the classpath of an application that uses Java DB Embedded. This library includes the driver program. This is included with the Java DB.

1.2. Accessing Java DB Database Interactively With ‘ij’

ij is a command line tool included with Java DB. ij is a JDBC tool used to run interactive queries on a Java DB database. This is in the \bin directory of Java DB installation.

Start ij, create a database and run SQL commands to create and access data:

os prompt> ij
ij> CONNECT 'jdbc:derby:testDB;create=true';
ij> Enter SQL commands here…

This creates a database named testDB in the current directory and connects to it. The Java DB database is stored in files within a directory with the same name as that of the database name.

2. Usage Example

An example usage is a Java Swing based desktop application that uses an Embedded database. The first time, the application starts, the database and its objects are created. Subsequently, the application creates or accesses data in the database. The application also has an online backup database function (i.e., make a backup copy while the database is open) and a restore from the backup database copy function. The database is closed with the application.

3. Connect to Java DB Embedded from Multiple Clients

Java DB in Embedded mode can be accessed from multiple clients. A Java Servlet application deployed on a web server and connects to the database, is an example. The Java DB database is configured as a resource of DataSource type, on the web server. The Servlet application starts the database and creates Connection objects. The Connection objects are created using the DataSource object, rather than the DriverManager. Multiple web clients access the database data through the application.

image2

3.1. An Example

  • Configure DataSource Resource on a Web Server
  • Access the Database from a Servlet (using the configured DataSource)

The configuration is for Apache Tomcat 6 web server. The following code samples use Java Servlet (Java EE), JNDI (Java Naming and Directory Interface) and JDBC API.

3.1.1. Configure DataSource Resource on Web Server

This is called as the context configuration. This defines the JNDI name of a JDBC data source as a resource for the Java DB database used in the web application (for JNDI lookup). Also, specifies the resource parameters.

The following entry is added to the file ‘/META-INF/context.xml’. The META-INF directory is to be in the root directory of the web application deployment WAR file.

<Context>
	<Resource
		name="jdbc/testDB"
		auth="Container"
		type="javax.sql.DataSource"
		username=""
		password=""
		driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
		url="jdbc:derby:testDB"/>
</Context>
  • name: is the JNDI name of the data source resource.
  • url: specifies the JDBC database connection URL of the format jdbc:derby:databaseName;URLAttributes;. And, testDB is the name of the Java DB database to connect to.

Note that, an application accesses the Embedded database from the Derby system directory. By default, the current directory (the JVM system property user.dir) is the system directory.

For Java DB, the Derby system directory can be specified (optional), by setting the JVM system property derby.system.home. This is set when using the ‘java’ command from operating system command prompt (for example, java -Dderby.system.home=D:\mydatabases MyApplication) or from a Java program (using java.lang.System class’s setProperty()).

NOTE: The Embedded database driver (derby.jar) is to be placed in ’CATALINA_HOME\lib’ directory. CATALINA_HOME is the Tomcat installation directory.

3.1.2. Access the Database from Servlet

The following Java code shows a servlet class accessing the Java DB database using the configured DataSource resource. The code assumes that the database is already created (using another application or interactively using ij).

public class TestServlet extends HttpServlet {
private Connection conn;
/*
 * Method executes when the servlet is created.
 	 * Accesses the DataSource object and creates the Connection object.
 	 */
@Override
public void init()
			throws ServletException {
		InitialContext cxt = null;
		DataSource ds = null;
		try {
			cxt = new InitialContext();
			ds = (DataSource)cxt.lookup("java:/comp/env/jdbc/testDB");
		}
		catch (NamingException ex) {
			throw new ServletException("naming context error", ex);
		}
		try {
			conn = ds.getConnection();
		}
		catch (SQLException ex) {
			throw new ServletException("connection error", ex);
		}
} // init()
/*
 	 * Method to handle a Http Get request.
 	 * The Connection object created in init() is used to access the
 	 * database data using JDBC API. 
 */
      @Override
      public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		...
		Statement stmnt = conn.createStatement();
		ResultSet rs = stmnt.executeQuery("SELECT * FROM test_table");
		...
      }
} // TestServlet

4. Network Server (aka Server)

In this mode, Java DB is accessed from multiple clients over a network – a client-server configuration. The database and the applications run in different JVMs. The database on the server is accessed from a Java application using JDBC.

The following is an example of the Server usage.

4.1. Start the Server

Start and stop the server from the Windows command prompt using the provided batch programs (these are in the \bin directory of Java DB installation): startNetworkServer.bat and stopNetworkServer.bat.

4.2. Access Java DB from Client Application

Load the driver and connect to the database with a connection URL.

  • The driver is: org.apache.derby.jdbc.ClientDriver
  • The connection URL format is: jdbc:derby://server:port/databaseName;URLAttributes;
  • server is the host name (or ip address), and port is the port number (1527 is the default) where the server is listening for client requests.

The following is an example Java method that starts the database using JDBC:

/*
 * The method creates a Connection object. Loads the client driver,
 * starts and connects to the database using the connection URL.
 */
public static Connection createDatabaseConnection()
		throws SQLException, ClassNotFoundException {
	String driver = "org.apache.derby.jdbc.ClientDriver";
	Class.forName(driver);
	String url = "jdbc:derby://localhost:1527/sampleDB";
	Connection c = DriverManager.getConnection(url);
	return c;
}

The Connection object created in the above method is used to access the database objects and data using SQL. sampleDB is the name of the database.

NOTE: The derbyclient.jar library is required to be in the classpath of the client application to start and access the Java DB database. This library includes the driver program. This is included with the Java DB.

5. References

  • Apache Derby > Documentation (10.8 Manuals):  http://db.apache.org/derby/manuals/index.html
  • Oracle’s Java DB: http://www.oracle.com/technetwork/java/javadb/overview/index.html
  • Java DB Backup and Restore blog post’s by this author: http://www.javaquizplayer.com/blog.html
  • A Java Swing based desktop application developed and deployed (and uses Java DB Embedded) by this author: http://www.scribonotes.com

Prasad Saya

Prasad Saya is a software engineer with over ten years’ experience in application development, maintenance, testing and consulting on various platforms. He is also a certified Java and Java EE developer. At present his interest is in developing Java applications.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
vatsal desai
vatsal desai
10 years ago

In the article you mentioned that embedded DB can be connected by more than one client however if I try to connect it …it does not allow to do so simultaneously.Is there any work around or configuration to connect to the embedded db by more than one client?.

PSaya
PSaya
10 years ago
Reply to  vatsal desai

Thanks for reading the article.

Yes, it is possible to access embedded database data from multiple clients, when used along with an application server or a web server. Please provide some details, like: the client code and the server environment.

Prasad.

Suresh atta
9 years ago

That’s nice to read. Good info. JCG rocks.

huaqeeli@hotmail.com
huaqeeli@hotmail.com
8 years ago

I use this code to backup embedded database public void backUpDatabase(Connection conn)throws SQLException { // Get today’s date as a string: java.text.SimpleDateFormat todaysDate = new java.text.SimpleDateFormat(“yyyy-MM-dd”); String backupdirectory = “c:/mybacksup/” + todaysDate.format((java.util.Calendar.getInstance()).getTime()); try (CallableStatement cs = conn.prepareCall(“CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)”)) { cs.setString(1, backupdirectory); cs.execute(); cs.close(); } System.out.println(“backed up database to “+backupdirectory); } and this code to restore it public Connection restoreDatabaseRoutine() throws SQLException, ClassNotFoundException { String backupPath = “C:\\mybacksup\\2015-05-02\\OOS_db”; String restoreUrl = “jdbc:derby:OOS_db;restoreFrom=” + backupPath; String driver = “org.apache.derby.jdbc.EmbeddedDriver”; Class.forName(driver); // throws ClassNotFoundException Connection conn = DriverManager.getConnection(restoreUrl); System.out.println(“The database has been successfully restored”); return conn; } and I don’t have and error… Read more »

Prasad Saya
Prasad Saya
8 years ago

Thanks for your interest in my post. Please try these:
– Is the backup created in the specified directory; please verify the OOS_db directory has files in it (or empty).
– Try using slash instead of back-slash for backup path in restore function: String backupPath = “C:/mybacksup/2015-05-02/OOS_db”;

Oghojafor Ejiroghene
Oghojafor Ejiroghene
8 years ago

Thanks this vital information about JB Embedded database, but i would like to know if there is a way an embedded derby database in an applicstion can have an external backup in case of system failure… Thanks

Prasad Saya
Prasad Saya
8 years ago

The backup database can be stored on an external drive or on a network drive. Also, an offline backup is another option, is described below: The database is backed up using an operating system command. This makes a copy of the database directory. The database must be shut down prior to performing an offline backup. On Windows, the operating system command xcopy (copies files and directory trees) makes a backup of the database. For example, a database with the name sampleDb and located in d:\databases is copied to the directory d:\dbbackups\, using the following: os prompt> xcopy d:\databases\sampleDb d:\dbbackups\sampleDb /s… Read more »

Adi
Adi
7 years ago

Hi There! Thank you for this detailed explanation. Although I would like to know, (I am using netbeans IDE 8) why in embedded mode I cant view the tables created and the values entered. All I can do is run the select queries and get the results in the output window. If the DB is created, and I know the location where its created, I still cant get the table view, similar to the one I get if I am using a derby network connection.

Prasad Saya
Prasad Saya
7 years ago
Reply to  Adi

Adi,
Thanks for reading my article. I think what you have mentioned is a feature specific to NetBeans IDE (not Java DB).

You could use a GUI SQL tool to view tables, query, update, and view data. There are quite a few tools available. Here are some links:
https://en.wikipedia.org/wiki/Comparison_of_database_tools
http://stackoverflow.com/questions/4424892/is-there-a-tool-other-than-ij-to-look-into-a-local-derby-database-folder

Also, NetBeans has more info regarding using Java DB: https://netbeans.org/kb/docs/ide/java-db.html

Hope this helps.
– Prasad

Prasad Saya
Prasad Saya
5 years ago

As of Java 9 Apache Derby (Java DB) is no longer included in the JDK (like in earlier versions). This can be downloaded from the website: https://db.apache.org/derby/

Back to top button