Illinois State University



Embedded Database: Java DB

Vinod Kumar Bobba

Dr. Billy Lim

ITK 478

|Sr. No. |Table Of Content |Page no. |

| |Evaluation Report | |

|1 |Overview & History | 1 |

|2 |About Java DB |2 |

|3 |Why Java DB? |2 |

|4 |Architecture |3 |

|5 |Java DB tools |5 |

|6 |Java DB is now part of Sun’s JDK |6 |

|7 |Compare Java DB and HSQLDB |8 |

| | | |

| |Hands-on work : Documentation | |

|1 |Database connectivity in Java Studio Creator | |

|2 |Address Book demonstration | |

|3 |Accessing Embedded database without SQL | |

| | | |

Overview:

Java DB is Sun Microsystems branding of the Apache Derby database, it is a pure Java, small footprint, easy to use relational database engine that is based on the Java programming language and SQL. Java DB is a commercial release of the Apache Software Foundation's open source relational database project, Derby. The Java DB product includes very thing from Derby (Functionalities) without any modification. However, the technical support is available for purchase for the Java DB product through Sun. Simply we can say that the Java DB is Sun's version of the open-source Apache Derby Project database. That is the major relation and minor difference between them; moreover even the Java DB documentation refers to the core functionality as Derby.

Before talking more specific about Java DB, let’s talk about something more general about Sun and its database technology. There is a Database Technology Group within Sun that is responsible for database technology developments. This group not only evaluates databases fro internal Sun use, but also evaluates and tests other databases. In short, it works on new db technologies analyze, evaluate and test them for better development. The results of this group are HADB (Clustra) and Java DB (Derby).

Now, Java DB came into existence, when Sun decided to support the distribution of Derby. Open source database technology, Pure Java, easy to use, small foot print, standards based, complete relational design engine and secure these are few buzz words or feature of Derby that made Sun to go for it. So finally what came out is, three organizations (Apache, Sun, IBM), three brands (Apache Derby, Java, Cloudscape) and one product i.e. Java DB.

In short Java DB History is as below:

|Year |Description |

|1996 |Cloudscape founded |

|1997 |JBMS released |

|1999 |Cloudscape acquired for Informix |

|2001 |IBM acquired DB part of Informix |

|2004 |IBM donated Cloudscape to Apache(Derby) |

|July, 2005 |Derby Graduated from Apache Incubator |

|December, 2005 |Sun Announces Java DB |

|Right Now |Current release 10.1.3.1 |

| | |

Table 1 [1].

About Java DB:

Java DB is completely based on Java i.e. written in Java to take advantage of Java's write once, run anywhere (any hardware, any OS, any vendor). So Java’s buzz words do apply here and we can say it provides a robust, small-footprint Java database management system that is cost effective and simple to deploy. Due to Java portability, it can be used across Multi-platforms and it is easy to migrate an application using Java DB to other open standard databases. Database on-disk format is platform independent. It is fully transactional, secure, easy-to-use, standards-based -- SQL, JDBC API, and Java EE -- yet small, only 2MB. Java DB technology adheres to database standards such as JDBC and ANSI SQL standards, this make it to expect functionalities of relational database with SQL syntax and the other RDBMS functionalities like concurrency control, transaction management and triggers. Java DB SQL syntax is total based on SQL92, SQL99 and SQL2003.It also means that it is easy to upgrade an application using Java DB to other standards-based databases, such as Oracle and DB2.As an embedded database Java DB Database engine may run in application's virtual machine, which requires no additional process. Database requests are just method calls within the JVM where Startup & shutdown of database controlled by application. All we need is a library to Java applications (single jar file). All the underlying administration details are invisible to the user, so it is easy to use with zero maintenance.

It is a complete relational engine with full support to Tables, Indexes, Views, Triggers, Joins, Procedures (Java), Functions (Java), Temporary tables, foreign keys, constraints, Cursors, Transactions, Isolation levels and ACID. All these entities and aspects are totally applicable to multiple users, with Deadlock detection, Crash recovery, Backup & restore, Data caching, Statement caching, logging and Group commit. Thus Java DB provides multiple databases per system or multiple systems per read-only databases environment making life easier for application developers.

Why Java DB?

Java DB is considered ideal for Java application development and testing because it is easy to use, where you can fit on a laptop or on a mainframe and available for free under Apache license. It also a best fit for Java client-server applications, that need up to 24 x 7 support of database based on sophisticated standards and transactional SQL features that could protect against data corruption or systems crashes with minimal database administrator skills.

Embedding the database will always make life simple considering the scope for small applications. Likewise, even Java DB well suits as a local data store for on- or off-line Web applications. Embedding provide the simplicity where there is there is no need for the developer or the end-user to buy / download, install, administer the database separately from the application or IDE. Java DB also has the flexibility to support both embedded and client-server mode. In embedded mode, Java DB runs on the same JVM as the application and users may not even be aware that they are accessing a relational database. It requires no administration if embedded and little if used in client-server mode. We will be discussing some of these features further as well as in the demo.

Security is something that we should consider in every aspect of today’s application or web development. Even in this aspect Java DB provides a numbers of security mechanisms including database file encryption, authentication through either external LDAP directory or authorization. Use within browser-based, Web (2.0) applications for easy distribution, one-click install, secure local data storage, and data persistence if the Internet connection is lost or for use off-line. Java DB is also apt for applications running in a J2ME CDC (e.g., PDA) environment that need a small size (2MB) without sacrificing functionality like full SQL support, transaction management, stored procedures, triggers, concurrency, and backups.

Architecture:

Architecture of Java DB is considered pretty solid with straight, solid state-of- the art technology. Basically it is a modular architecture Aries (a recovery algorithm designed to work with a no-force, steal database approach. ARIES is a popular algorithm used by IBM DB2, Microsoft SQL server) and Indices as B-trees. There is no separate SQL virtual machine to deal with SQL queries. The following diagram describes the procedure, where SQL related information is compiled into java byte-code and is made to run on standard JVM. Thus everything is converted into byte-code and a single VM to deal with this all, reminds us of Java DB as 100% Java based.

[pic]

However it has its own cons and pros. Compiling SQL to byte-code makes it faster using hotspot compiler or Just In Time (JIT) compilers, where they compile interpreted byte code to native machine code. But on the other hand this extra compilation and class loading is burden on the Virtual machine as it is also acting like a db engine. So to overcome this, other option is to consider pluggable storage architecture. This is both flexible and modular. This Pluggable Storage Architecture provides a standard set of server, drivers, tools, management, and support services that are leveraged across all the underlying storage engines. Moreover this pluggable storage architecture has a pluggable storage layer which enables to mix and match storage (memory), file system, jar file (read only) or just use what we need for an efficient optimized footprint.

[pic]

Figure [1].

We have already discussed and I have mentioned that Java DB provide all features like complete relational engine. So we can have multiple systems for a single read-only database or multiple databases for a single system. In that aspect, we have to mention about, embedded and client-server mode in which Java DB operates. The flexibility to support both embedded and client-server mode allows Java DB to adapt to diverse deployment scenarios.

[pic] [pic]

Figure Embedded [1]. Figure Client/Server [1].

In embedded mode, Java DB runs on the same JVM as the application and users may not even be aware that they are accessing a relational database i.e. database accessible from only a single JVM. However there can be multiple applications per JVM like in application server. This method is easy to use, no administrator skills required and faster. There is one more option with respect to embedding where it adds more flexibility, where embedding network server. Even this does not require administrator skill and no need to change application i.e. there is no need to change the code of the application, all we need to do is to enable by setting the corresponding properties. This provides access to database from outside the application's VM through DRDA standard protocol. Moreover it also adds DB reporting and debugging capabilities to stand-alone applications.

In client/server mode, many applications use only one database. Network server uses embedded driver against derby. Standard protocol DRDA (industry standard for database access interoperability), may use drivers from other vendors where scripts are provided to stop and start network server. So does require a little administration.

Java DB tools:

ij : This is a SQL scripting tool, which is JDBC neutral and can be used against other JDBC drivers. It is a simple utility for running scripts against a Derby database. You can also use it interactively to run ad hoc queries. ij provides several commands for ease in accessing a variety of JDBC features. ij can be used in an embedded or a client/server environment.

 

sysinfo : sysinfo provides information about your version of Derby and your environment.

 

dblook : schema extraction tool for derby .dblook is Derby's Data Definition Language (DDL) Generation Utility, also called a schema dump tool. It is a simple utility for the dumping the DDL of a user-specified database to either a console or to a file. The generated DDL can then be used for such things as recreating all or parts of a database, viewing a subset of a database's objects (for example, those which pertain to specific tables and schemas), or documenting a database's schema.

JVM and classpath for Derby tools

ij, sysinfo, and dblook are tools that can be used in an embedded or a client/server environment.

Java 2 Platform, Standard Edition, Version 1.3

All Derby tools require Java 2 Platform, Standard Edition, Version 1.3 or later.

Derby class path requirements:

|• |  |To use ij, you must have derbytools.jar in your classpath. |

| | |If you are using the embedded driver, you must also include derby.jar. |

|• |  |To use sysinfo, either derby.jar or derbytools.jar must be in your classpath. |

|• |  |To use Derby tools from a client with the Derby Network Server, you must have derbyclient.jar and derbytools.jar in your |

| | |classpath. |

There are also some popular tools that Support Java DB – Net Beans and Java Studio creator, which we will be using with our demo applications.

Java DB Scaling:

In general there is no restriction and it is unlimited with no architectural constraints. Java Db has already been tested by sun with databases up to 300GB and up to 100 active connections. Talking about horizontal scaling and high availability there is no built-in support, but can be achieved by making Java DB interact with other technologies. About memory, Java Db caches data in memory. However, durability is achieved at a cost with performance penalty as we try to persist more data to disk and it is very risky to persist at shutdown. But lack of durability is not a problem as it is possible to run Java DB with less durability. We will set the following property to get going with it in case we find there is lack of durability -Dderby.system.durability = test.With SQL RDBMS functionality, B-trees were designed for efficient disk storage & retrieval in Java DB and finally atomic transactions(ACID).

Java DB is now part of Sun's JDK:

Finally, it’s official that Java developers will have the convenience of a fully functional, 100% Java database shipping with the Sun JDK. Java DB 10.2 will be available with Mustang (Sun's JDK for Java SE 6) as part of the JDK bundles. It is a great thing for java developers as we have a database that you can build and test against that implements the latest version of JDBC, and which, if you so choose, you can take and deploy your application with, free of charge. We can find it under the db directory of your JDK install. As a reminder, this is Sun's redistribution of Apache Derby and the Java DB community is very active and you get quick responses from Derby developers and users. It's free, it's open source, and now it's part of the JDK.

We already said that for a great out-of-the-box development experience with database applications, the final Java SE 6 development kit – though not the Java Runtime Environment (JRE) – will co-bundle the all-Java JDBC database, Java DB based on Apache Derby. Developers will get the updated JDBC 4.0, a well-used API that focuses on ease of use many additional features like special support for XML as an SQL datatype and better integration of Binary Large OBjects (BLOBs) and Character Large OBjects (CLOBs) into the APIs. Additional features that improve ease of use include some of the new annotations that make SQL strings embed better into your JDBC application – like decorating your getAllUsers() method with an @Query(sql="select * from user") annotation, and that being all you need. This allows Java developers to build applications even more rapidly and easily by having access to a Java Database which implements many features from the latest JDBC4 API specification, directly out of the JDK.

Java DB is not like the XML parser situation. The XML parser was included in java core, and its classes were loaded automatically when the VM started up. If you wanted to use another XML parser, it was a real problem. That's not true with Java DB. You have to explicitly put derby.jar in your classpath if you want to use it. The advantage is well, now there is a database in Sun's JDK that allows people to exercise the JDBC APIs. Tutorials can refer to it. Demos can use it. It helps people get comfortable with JDBC. It provides early access to the new JDBC APIs. All these things I think have a lot of value to a certain class of developers.

Since you have to explicitly say you want to use Java DB (opt in, not opt out) and it's not like we are being forced to download some huge database that requires 20 steps to install. It's a couple of megabytes; it is a completely silent install, and just sits there doing nothing until and if you decide to use it. In Java DB 10.2 which is bundled with JDK 6 these are the features that are more specific Scrollable updatable result sets, JDBC 4,Grant/Revoke,Online backup, Stronger Network Authentication, XML.

Concentrating more on performance, Sun has studied and compared the performance of Derby, MySQL, PostgreSQL. I would like provide you with those snap shots (graphical statistics).

[pic]

Figure [1].In-Memory DB (DB 10MB, Buffer 50MB, 400 Branches)

[pic]

Figure [1]. Disk-bound DB (DB 10GB, buffer 64 MB, 400 branches)

These are some of the hints they came up to improve the performance of Java DB.

1. Use (and reuse) prepare statements

2. Put DB log and data on separate disks

3. Tune page cache size (default 4 MB) using derby.storage.pageCacheSize

4. Use indexes to avoid table scans by checking query plans using derby.language.logQueryPlan=true

Comparing Java DB with HSQLDB:

I would like to compare Java DB with HSQLDB.HSQLDB is a relational database management system which is also written in java. It is based on Thomas Mueller's discontinued Hypersonic SQL Project. The software is available under a BSD License.

It has a JDBC driver and supports a rich subset of SQL-92, SQL-99, and SQL 2003 standards. It offers a fast, small (less than 100k in one version) database engine which offers both in-memory and disk-based tables. Embedded and server modes are available.

Most of the functionalities are pretty much same as Java DB. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets). HSQLDB is currently being used as a database and persistence engine in many open source software projects, such as OpenOffice Base, as well as in commercial projects and products, such as InstallShield or InstallAnywhere (starting with version 8.0).

HSQLDB is best known for its small size, ability to execute completely in memory, and its speed. It can also run on free Java runtimes such as Kaffe (virtual machine).

The following are some of the comparing issues that I found about Java DB and HSQLDB

1) Transactions:

HSQLDB does not support transaction isolation. All transactions run in read uncommitted (dirty read mode). Transactions read dirty data (uncommitted data) which is not what you want in case of update transactions. Example: let’s say in a bank application.

Java DB supports all transaction isolation levels. The default transaction isolation that it runs in is READ_COMMITTED. Java DB is guaranteeing that if you run in read committed mode, you will not be reading dirty data.

2) In-memory database:

HSQLDB by default creates table in memory, so if you use CREATE TABLE the table is in memory. This means with large amounts of data there is high memory utilization and the application may be limited to the amount of memory available and may perform slow if table does not fit in memory. This results in memory errors and even scalability issues. Java DB is disk based. Derby uses a page cache to keep recently used pages in memory and writes data to disk. Thus the memory consumption is stable and can be used for large amounts of data.

This difference is important to note as the speed in these 2 cases are different. This seems to be reason why it is not ideal to compare speed differences here.

3) Reliability:

Java DB is guaranteeing that if your system crashes in any way that committed transactions will remain committed. This requires that when a transaction commits, logs are synced to the disk. Syncing to the disk takes time. But on the other hand, it seems like HSQLDB is not fail safe as the log file is not flushed (synced) to the disk after a commit.

Thus it seems necessary to consider these differences before one compares raw numbers.

Some more issues that I came across are that many feel that HSQLDB is great but has significant performance problems when dealing with huge tables.

These are:

"select count(*)" is linear in time with respect to table size

adding/dropping columns is very slow for large tables

"select ... IN ( ... )"   is slow, even if the column is indexed

"select distinct a_col from a_table"  is slow, even if the column is indexed

But if your tables are small, or you don't need these operations then HSQL will work very well. Basic querying and joins etc. run very fast.

Other issues:

But however Java DB is certainly less mature. It currently lacks "alter table

drop column ..." functionality. HSQL has a single data file, limited at 8GB. For large dbs this could be an issue. Derby has a data file for each table, so still has a limitation, but may not so major. Derby (or Java DB) is becoming part of the JDK, so should be a good long term bet. I think they both are pretty stable. In Derby, users code functions and procedures in Java. Both support basic B-Tree indices natively. Both these databases don’t support materialized view i.e. in which the query result is stored as a concrete table that is infrequently updated from the original base tables.

Finally, I believe Derby will be the better choice if you have lots of big tables; HSQLDB has more features when it comes to having tables loaded in ram only and things like that.

But if your tables are limited in size I think HSQLDB would be just as good,

and as I said, HSQLDB has some extra features like ram tables, reading csv files

for example.

The following table will give us more brief comparison of the two.

|Maintainer |First public release|Latest stable version |Software license | |

| |date | | | |

|SUN |2004 |10.1.3.3 |Apache License |Derby( Java DB) |

|HSQL Development Group|2001 |1.8.0 |BSD |HSQLDB |

| | | | | |

|Operating System |All |Database availability depends | |Derby( Java DB) |

|support | |upon JVM not on OS | | |

| |All |Database availability depends | |HSQLDB |

| | |upon JVM not on OS | | |

|RDBMS Features | | | | |

|ACID |Referential |Transactions |Unicode | |

| |Integrity | | | |

|YES |YES |YES |YES |Derby( Java DB) |

|YES |YES |YES |YES |HSQLDB |

| | | | | |

| |Temporary table |Materialized view | | |

| |YES |NO | |Derby( Java DB) |

| |YES |NO | |HSQLDB |

| | | | | |

|Cursor |Trigger |Function |Procedure | |

|YES |YES |YES |YES |Derby( Java DB) |

|NO |YES |YES |YES |HSQLDB |

| | | | | |

As final words, Java DB will certainly give us the overview that how big it can be even though it is an embedded database. I think Java DB will definitely change the way in mobile applications and in small desktop applications as I find them a perfect suit. I am sure that a few years from now we will see many Java DB-backed interactive applications with zero downtime. More importantly, Java DB is addressing many of the functionality problems for mobile applications such as data replication. So let’s see how it goes.

As part of my hands-on work, I worked from different perspective to get the feel of how Java DB exactly works. In this zeal, I came up with 3 demo applications which I found in the Sun Microsystems releases and its Journals and try to implement and deploy with some necessary changes. It really worked out well with the way things went and this gave me an opportunity to view Java DB from different tools like Net Beans, Java Studio Creator and in different context like as an embedded database and how well it suits as a portable database. I hope these demonstrations coming up will certainly give you an idea about the core Java DB.

Demonstration 1: Documentation

Database Connectivity: Working with Java DB

Java Studio Creator 2 comes with the Sun Java System Application Server, and this application server includes the Java DB. As we already mentioned that, Java DB is a pure Java Relational Database Management System (RDBMS) for client-server networked applications.

JDBC API:

The Java Database Connectivity provides database-independent connectivity among Java applications and a wide range of SQL databases, even access to tabular data sources and flat files. The JDBC API is written in Java and thus allows my database applications to benefit from all the great features of Java. This allows for my applications to maintain security throughout the database transaction, and also for my applications to maintain portability across various platforms (and databases). It's write once, run anywhere technology. Non-Java applications used ODBC. This API is written in C, and is thus not appropriate for use with Java, as my applications would inherit many of the drawbacks C contains in security and portability.

Working with Sample databases:

I have worked with the sample databases that are included with Java Studio Creator and then try to implement a simple database application using the IDE.

First I start a new project. This below screen shows the welcome screen in Studio Creator. Click on the Create New Project button.

[pic]

Then, look at the Servers window for a list of the available data sources. These include Jump Start Cycles, Travel and VIR (Vehicle Incident Reporter). The sample data sources I mention above are actually all contained within one database called "Sample." The Java DB sample database is located in the SunAppServer8/derby folder. Log files and a few utilities programs are also included there.

[pic]

 

If the bundled database server isn't already running, we need to start it up and take a look at the tables available in that database. We can do this by right-clicking the Bundled Database Server node. If the database is stopped, the option to stop the database server is grayed out and a red icon appears next to the node, and we start it as described above. Once the bundled database is started, we can see the tables, views, and columns within tables, in the sample databases shown below.

[pic] [pic]

Developing a database application:

For our experiment let us decided to experiment with an application that would pull in data from several tables and display the results. So, since we have Flight table. Trip table and a employee table in our sample database, lets try to produce a report from these database tables that displays flights for particular trips and people. We will also try to include a certain piece of data in the report, and for the data columns to appear in a particular order, with column headings that will make sense to the user.

 

• First, we create a new application as described before.

• To do this, we choose New Project from the File menu (or we can click on Create New Project from the Welcome screen that defaults open when Creator starts).

• Then we choose JSF Web Application from the New Project dialog window that pops open.

 

Then we go to the Palette window (next to the Servers window by default) and drop a Table component onto the visual designer. This displays a generic table with several columns and rows, as shown in the Figure below.

[pic]

Since we want the report to focus on the flight data, we then go to the Servers window and open the table node for the Travel database.

Next, drag the FLIGHT table from the Servers window onto this generic table in the visual designer. Make sure that the entire outline of the generic table is highlighted (the outline actually changes to a blue color) before you drop the FLIGHT table onto it.

 

|[pic] |

| |

 

My flight data is now bound to the page's display, and the generic table has changed to show me the columns or fields of the FLIGHT table, as shown in Figure above.

Now if we run the application by clicking the green button in the menu (Run). You'll see that what you get is the data from this FLIGHT table in a simple HTML table, nicely formatted with shaded rows.

But this is not want we are looking for. So let keep going.

 

Using the Query Editor:

 

To add trip and employee data, we need to use Java Studio Creator's Query Editor.

 In the Outline window, you expand on the SessionBean1 node, and notice the flightRowSet entry. Check the figure below.

This rowset contains the SQL command associated with the table, and was automatically placed into the Session Bean when we dragged the FLIGHT table onto the generic table in the visual designer.

This makes it available across your application session. We'll now right-click on it and select Edit SQL Statement—or double-click on it—to open it in the Query Editor.

| [pic] |

 The Query Editor window is divided into four panels.

At the top is a graphical display of the table.

Below, the table columns are displayed: the table name, sort order, and so on.

The IDE also shows us the SQL query itself.

We can test the queries by writing and running them in this query editor, we can run the query by right clicking Run Query in the top panel.

The data is displayed in the table in the bottom panel of the window.

 

|[pic] |

| |

 

Now, we need to add the PERSON and TRIP tables to the Query Editor.

We can easily do that by right-clicking in the top panel and choosing Add Table, and then selecting multiple tables in the dialog box that opens. But the simplest way is to drag the tables from the Servers window and drop them onto the topmost panel in the Query Editor.

The IDE then adds these tables to the visual display, and also adds their fields to its table display and to the SQL query itself. Since the TRIP table includes foreign keys to the PERSON and FLIGHT tables, the Query Editor shows these connections graphically.

 

Columns that will be displayed in a query have a check mark next to the column name. By default, when we add a table to the query, all the columns in that table are set to display.

 

 

|[pic] |

| |

 

Changing the Table Appearance:

 

Now our remaining task is to change the appearance of the data in the application, particularly the column headings and column order, and to specify which data should appear at run-time. To do this, we go back to the visual designer. (We can easily toggle back and forth between the visual designer and the query editor using the tabs at the top.) We then right-click on the table (being sure that the whole table is outlined) and choose Table Layout.

 

|[pic] |

|Figure 7: Changing Column Headings and Repositioning Columns |

 

Here, the Columns tab in the dialog lets you choose the columns you would like to display, rename column headings and easily move the position of the columns. We make changes, and then use the Apply button to view the results.

 

Then we choose the Options tab, shown in the next Figure, to enable pagination for this application. With paging enabled, the IDE automatically takes care of displaying the specified number of rows of data per page. Some very cool features can be added to our table at run-time. These features include added controls and links that allow us to page through the data, expand the table to include all data, and create complex sorting between various rows by clicking on them. We can get the feel of these features once we deploy and run our application.

 

|[pic] |

 

Running the Application

 

Now we are ready to run the application. To do this, you simply select Run Main Project from the Run menu, and then wait for the IDE to compile, build, and deploy this application. Then the IDE opens a browser window and we can view the data retrieved by the application, formatted the way we wanted. The figure below gives a snippet of data from application

 

|[pic] |

 

Demonstration 2: Documentation- Creating the Address Book Demo:

Installing Java DB

The easiest way to get Java DB is to download a copy from the Sun Developer Network's Java DB site. The binary distribution provides everything you need to begin working with embedded database applications. After you download the binary distribution, you'll find a Java DB directory structure that contains the following subdirectories:

• The demo subdirectory has two demonstration programs. One example shows how to create a trivial embedded application. The other shows how to use Java DB in a client-server environment.

• The frameworks subdirectory contains utilities for setting up environment variables and for creating and working with databases. This directory is not useful for our demo because our application will be entirely self-contained. No outside utilities will be used for the Address Book application.

• The javadoc subdirectory contains API documentation. This directory is particularly useful if you configure your IDE to point to it as the Java DB API Javadoc.

• The docs subdirectory contains documentation about the Java DB product itself: setup, administrator, and reference guides.

• Finally, the lib subdirectory contains the Java DB libraries packaged as JAR files. Read the Java DB documentation to find out about the various libraries. For an embedded database application, we will use only the derby.jar library file.

Installing Java DB for development requires only that you make the derby.jar file part of your application classpath. It's that simple. You can set the CLASSPATH variable of your Solaris, Linux, Windows, or other host environment to include the JAR file, or you can include the file as part of your command-line options when compiling or running.

Demo Description:

The Address Book demo uses Java DB to store address information. This demo stores names, phone numbers, email addresses, and postal addresses. It allows you to create new address entries and to save, edit, and delete them. The database is embedded with the application, so there is no need to set up or manage a separate server or system. To deploy this embedded database application, we need only the application JAR file and the database library JAR file. Figure shows the demo's user interface (UI).

|[pic] |

| |

Address Book's main frame window is an AddressFrame class that extends a Java Foundation Classes/Swing (JFC/Swing) JFrame. The AddressFrame is a container for other graphical components and also acts as a controller by handling various events generated by the child components. The child components are JPanel subclasses, each with a different responsibility:

• AddressPanel represents an address record. It also provides the UI for editing existing records and creating new records. It contains text fields for all the major properties of an Address object.

• AddressActionPanel provides buttons for all the major use cases that the application supports. This panel generates events that AddressFrame must handle. For example, when the user clicks Save, this panel generates an event. AddressFrame listens to and handles all important events from this panel.

• AddressListPanel provides a scrollable list of names on the far left of the AddressFrame. The list holds ListEntry objects. A ListEntry stores a database record's unique identifier. The record identifier (ID) allows the application to retrieve an entire record's contents into the AddressPanel.

The application uses a Data Access Object (DAO) to isolate the database-specific code. The DAO encapsulates database connections and statements. A DAO is a useful design pattern that allows loose coupling between an application and the underlying persistence-storage mechanism. The application's AddressDao class is an example of a DAO.

Integrating Java DB With NetBeans IDE 5.0

Most IDEs provide a way to add libraries to the development classpath. Follow these instructions to add the Java DB libraries to NetBeans IDE 5.0:

1. From the Tools menu, select Library Manager, as shown in Figure 1.

|[pic] |

| |

|Figure1: The library manager lets you add third-party libraries to your project. |

| |

2. In the Library Manager window, create a new library named JavaDBEmbedded, as shown in Figure 2. Click OK.

|[pic] |

| |

|Figure 2: Name the set of libraries that your project will need. |

| |

3. To add the derby.jar file to the JavaDBEmbedded library, click on Add JAR/Folder... in the Library Manager window. Navigate the file chooser to the derby.jar file and select it as shown in Figure 3.

|[pic] |

| |

|Figure 3: Add the derby.jar file to the JavaDBEmbedded library. |

| |

4. In the same Library Manager window for the JavaDBEmbedded library, select the Javadoc tab. Add the javadoc subdirectory from your Java DB installation. Now, the Java DB API Javadoc is available when you use the JavaDBEmbedded library in your NetBeans IDE projects.

You can now add the JavaDBEmbedded library to your NetBeans IDE 5.0 project by using the project's property settings. When you compile, debug, and run the application within the IDE, the IDE will be able to find the needed derby.jar file.

Loading the Database Driver

Loading the JDBC technology driver starts the database management system. Java DB's drivers come with the derby.jar file, so you don't need to download anything else. Load the JDBC driver by referencing it using the Class.forName method. The embedded driver name is org.apache.derby.jdbc.EmbeddedDriver, and you can load it as you would other JDBC drivers.

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

 

The Address Book demo reads the driver name from a configuration property file and passes the name to a loadDriver method. Additionally, as mentioned earlier, Address Book encapsulates all database functionality into a Data Access Object (DAO) used to access data from a variety of sources. The DAO pattern works equally well for Java SE applications like Address Book. The following code snippet shows how AddressDao reads the driver name and loads the driver:

|private Properties bProperties = null; |

| |

|public AddressDao(String addressBookName) { |

|this.dbName = addressBookName; |

|setDBSystemDir(); |

|dbProperties = loadDBProperties(); |

|String driverName = dbProperties.getProperty("derby.driver"); |

|loadDatabaseDriver(driverName); |

|... |

|} |

| |

|private Properties loadDBProperties() { |

|InputStream dbPropInputStream = null; |

|dbPropInputStream = |

|AddressDao.class.getResourceAsStream("Configuration.properties"); |

|dbProperties = new Properties(); |

|try { |

|dbProperties.load(dbPropInputStream); |

|} catch (IOException ex) { |

|ex.printStackTrace(); |

|} |

|return dbProperties; |

|} |

| |

|private void loadDatabaseDriver(String driverName) { |

|// Load the Java DB driver. |

|try { |

|Class.forName(driverName); |

|} catch (ClassNotFoundException ex) { |

|ex.printStackTrace(); |

|} |

|} |

 

Connecting to the Java DB Database

A JDBC technology connection identifies a specific database and allows you to perform administrative tasks. Tasks include starting, stopping, copying, and even deleting a database. The driver manager provides all database connections.

Retrieve a connection from the driver manager by providing a URL string that identifies the database and a set of properties that influence the connection's interaction with the database. A very common use of properties is to associate a user name and password with a connection.

All connection URLs have the following form:

jdbc:derby:[propertyList]

The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system. The easiest way to manage your database location in an embedded environment is to set the derby.system.home system property. This property tells Java DB the default home location of all databases. By setting this property, the Address Book demo ensures that Java DB always finds the correct application database. The application database is named DefaultAddressBook, and it will exist within the directory indicated by the derby.system.home property. The connection URL for this database would look like this:

jdbc:derby:DefaultAddressBook

To connect to the DefaultAddressBook database, the demo must first set the derby.system.home system property. The demo uses the .addressbook subdirectory of the user's home directory. Use the System class to find out the user's home directory. Then use the class again to set the derby.system.home property:

|private void setDBSystemDir() { |

|// Decide on the db system directory: /.addressbook/ |

|String userHomeDir = System.getProperty("user.home", "."); |

|String systemDir = userHomeDir + "/.addressbook"; |

| |

|// Set the db system directory. |

|System.setProperty("derby.system.home", systemDir); |

|} |

 

Once the application has specified where all databases will exist, it can retrieve a database connection. In this example, notice that I have appended connection properties to the database URL.

|Connection dbConnection = null; |

|String strUrl = "jdbc:derby:DefaultAddressBook;user=dbuser;password=dbuserpwd"; |

|try { |

|dbConnection = DriverManager.getConnection(strUrl); |

|} catch (SQLException sqle) { |

|sqle.printStackTrace(); |

|} |

 

Alternatively, you can put those properties into a Properties object. Pass the Properties object as an argument when retrieving a connection:

|Connection dbConnection = null; |

|String strUrl = "jdbc:derby:DefaultAddressBook"; |

| |

|Properties props = new Properties(); |

|props.put("user", "dbuser"); |

|props.put("password", "dbuserpwd"); |

|try { |

|dbConnection = DriverManager.getConnection(strUrl, props); |

|} catch(SQLException sqle) { |

|sqle.printStackTrace(); |

|} |

 

Creating the Database

The Address Book demo application does not have a ready-made database. Instead, the application must create the database when it starts. One of the biggest advantages of using an embedded database is that the application -- not the user -- takes care of the details of setting up a database. The application can control where the database exists, what tables exist, and how permissions are handled.

Address Book creates a database called DefaultAddressBook in a subdirectory of the user's home directory, and it does so without asking the user for any additional information. You can create a new database in Java DB by using the create=true property when retrieving a connection to a database. Because our application will use the DefaultAddressBook database, we should first create this database. Assuming that the application has already set the derby.system.home property as discussed earlier, the application creates the database and connects to it like this:

|Connection dbConnection = null; |

|String strUrl = "jdbc:derby:DefaultAddressBook;create=true"; |

| |

|try { |

|dbConnection = DriverManager.getConnection(strUrl); |

|} catch (SQLException ex) { |

|ex.printStackTrace(); |

|} |

 

Because the create=true property is included, Java DB will attempt to create the database files for the first time. Creating the database doesn't actually create any application tables. However, you should now be able to find a new subdirectory named .addressbook/DefaultAddressBook in your home directory.

After it has created the database, the application creates the tables. The demo uses only one ADDRESS table in the default application APP schema. The following SQL code creates the ADDRESS table:

|CREATE table APP.ADDRESS ( |

|ID INTEGER NOT NULL |

|PRIMARY KEY GENERATED ALWAYS AS IDENTITY |

|(START WITH 1, INCREMENT BY 1), |

|LASTNAME VARCHAR(30), |

|FIRSTNAME VARCHAR(30), |

|MIDDLENAME VARCHAR(30), |

|PHONE VARCHAR(20), |

|EMAIL VARCHAR(30), |

|ADDRESS1 VARCHAR(30), |

|ADDRESS2 VARCHAR(30), |

|CITY VARCHAR(30), |

|STATE VARCHAR(30), |

|POSTALCODE VARCHAR(20), |

|COUNTRY VARCHAR(30) ) |

 

Each record has a record identifier or ID field. Java DB generates this value for each new record that it adds to the database. The ID field is the primary key for each address record.All remaining address record fields contain varchar elements of various lengths. For example, the LASTNAME field can contain a maximum of 30 varchar characters. The varchar type is equivalent to a UTF-16 Java char code unit.

The Java technology code that uses the above SQL statement to create the ADDRESS table looks like the following code. The dbConnection is the same as the one shown in the previous code. We simply pass it into createTables, create a new Statement, and call the execute method to run the SQL code on the newly formed database. The strCreateAddressTable instance variable holds the SQL statement text.

|private boolean createTables(Connection dbConnection) { |

|boolean bCreatedTables = false; |

|Statement statement = null; |

|try { |

|statement = dbConnection.createStatement(); |

|statement.execute(strCreateAddressTable); |

|bCreatedTables = true; |

|} catch (SQLException ex) { |

|ex.printStackTrace(); |

|} |

| |

|return bCreatedTables; |

|} |

 

At this point, the database and the ADDRESS table exist in a subdirectory named .addressbook/DefaultAddressBook in your home directory. Although you can browse this subdirectory, avoid modifying any files. If you edit or delete any of these database files directly, you can destroy the integrity of your database.

Our next step would be to get all the code ready to use the database i.e. Once the database and its tables have been created, your application can create new connections and statements to add, edit, delete, or retrieve records.

So we need to write code to create a new address record and the options to delete, edit, save and cancel the record. This would include java code with some SQL statements to access and manipulate data in the records. Finally once we have the necessary JAR files set in class path, we would be able to successfully run the application and use Java DB as an embedded database in this desktop application.

Demonstration 3: Accessing Java DB without SQL

Demo Description:

While Java DB provides various features, of most interest to the developer are the abilities to create XML schemas on the fly using XML files and insert rows into a table without using SQL. Traditionally developers used SQL to process DML statements from Java, but Java DB enables them to accomplish the same task without SQL.

This report demonstrates in detail these features with a sample application that stores and retrieves user settings in an embedded database using Swing. Java makes it easier to process and navigate the xml documents using the DdlUtils Component, where Java parsers provide an easy way to parse XML documents. The DdlUtils component is based on the concept of, providing utilities to enable direct data navigation and manipulation inside database tables from Java code. You can create an entire database schema in XML on the fly using DdlUtils, and DdlUtils also supports reverse engineering of existing schemas into XML or Java objects, but this demo is not exploring the other way around. So while Java DB takes care of database-related functionality such as transaction management, concurrency, backups, etc., DdlUtils handles interactions with the database.

Create XML Schemas:

Java DB provides features for creating a database when an application starts and deleting it by simply deleting the data files directory. With this approach, the database is no longer a separate entity. Instead, it is part of your code and you have exclusive control over it. This demo considers the creation of a golf course finder application. When you launch the sample application, it also will launch an embedded Java database. And when the application shuts down, the database does also. Although it is a desktop application, you can run it on any platform because it is standard Java (e.g., it makes a lot of sense as a mobile app).

1. First, get the Java code ready for the user interaction using swings. We also need derby.jar ( for Java DB) and DdlUtils.jar( for utilities to manipulate database). We also need 6 jar files, as there is no binary distribution available at this time for DdlUtils, you have to download all Jakarta Commons packages : commons-beanutils.jar, commons-betwixt-0.7.jar,commons-collections-3.1.jar,commons-digester-1.7.jar,commons-lang-2.1.jar’commons-logging.jar. They help to compile the code.

2. Next, create a database schema XML file which is well formed and valid according to schema rules published in . Use DdlUtils to read and parse the XML into a Database model Java object. The following code snippet represents in XML the database schema of the golf course finder application:

The following code snippet constructs a Database model Java object from the above XML:

// filename represents the database schema in XML format

public Database readDatabaseFromXML(String fileName)

{

return new DatabaseIO().read(fileName);

}

Finally, create database tables using the Platform class:

// derby is synonym for Java DB

Platform platform = PlatformFactory.createNewPlatformInstance("derby");

platform.createTables(conn, database, true, true);

Insert Data into Tables without SQL

DdlUtils uses the DynaBean class to alter table data. A typical bean has fixed properties and has setter and getter methods for those properties. A DynaBean, as the name indicates, allows dynamic creation of the bean properties. A DynaBean is a Java object that supports properties whose names and data types, as well as values, may be dynamically modified. In this case, the properties are nothing but the column names and column values of the underlying tables.

The following code creates a Java object of table data and inserts it into the table using a platform class:

//insert data into table without SQL insert statement

DynaBean userData = database.createDynaBeanFor("userData", false);

userData.set("userName", "tiger");

userData.set("password", "singh");

platform.insert(conn, database, userData);

This code technique eliminates the hassle of creating a java.sql.Statement and a java.sql.ResultSet. More importantly, it eliminates having to verify that they are all closed to prevent the application from failing with too many open cursors.

Platform and Database Classes

Among others, the platform and database classes are the important ones in the derby.jar. The platform interface provides the database-related functionality for processing DML statements such as queries and manipulations. It also contains a SQL builder that is specific to that particular platform. DdlUtils provides separate implementation classes for each database platform. In the sample application, for instance, the platform was "Derby".

The database class represents the database model (i.e., the tables in the database). It also contains the corresponding dyna classes for creating DynaBeans for the objects stored in the tables. A Table class, as the name indicates, represents a Java object for the database table. All DDL statements can be processed using Database and Table classes.

Running the Application

If you are running from command line, include all the packages in the class path to compile MySimpleApp.java and run.

Compile: > javac MySimpleApp.java.

Run: > java MySimpleApp DBSchema.xml.

MySimpleApp.java is the Java file that we are using which has all the code for userinterface, database connectivity and processing xml document.

DBSchema.xml is the xml file that we are using to create the database schema.

Runs:

When the application starts, it instantiates the database. It then reads the database schema from the XML file and creates the schema. Data related to two users is inserted using DynaBeans. The application has a user interface for capturing the username and password from the user and validates it against the password stored in the embedded database (see figure below).

[pic]

Initial Screen Using tiger/singh or vijay/woods to Login

Upon successful login, the next screen provides a list of available famous golf courses on the left-hand side and a list of golf courses the user has played on the right-hand side (see figure below).

[pic]

Golf Courses Listed on the Right Are Pulled from Java DB

The application reads the list of golf courses the user has played from the database while launching the screen in above snap shot. As the database is used in embedded mode, it will shut down when the application is terminated. Thus this demo shows how we can even access without SQL intervention.

References:

[1] Sun Tech day 2005-2006, a worldwide developer’s conference



[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download