Home > Articles

  • Print
  • + Share This
This chapter is from the book

A High-Level View of the Apache Derby Database

Apache Derby is a relational database management system that is written in Java. Java and the open source movement go hand-in-hand. Java is an attractive language in which to write such software because its "write once, run anywhere" mantra allows for seamless portability. People that like Java will love Apache Derby because the engine is very portable: again, from Mac to mainframe, and all parts in between. For example, Apache Derby is heavily leveraged on mainframes for applications that require a lightweight meta-data storage engine.

The moment many application developers or project managers hear the word "database," they start looking for a database administrator (DBA), and questions along the line of "how do I implement an incremental backup" and so forth arise. The Apache Derby database is not for DBAs (and they’re likely not to like it very much because they’ll be looking for the wrong things). In fact, Apache Derby is unique in that it does not require professional DBAs, or DBA skills for that matter, to run. It is truly a "lights-out" database management system. Think of Apache Derby as that "black box" in the corner. But don’t let the "behind the scenes" characteristics of the Apache Derby platform fool you: there are customers today with Cloudscape databases that hold tens of gigabytes of data.

Apache Derby delivers an efficient "next to no compromise" database engine in a "fingerprint-sized" engine of only 2 MB. Along with its tiny size, it has generally minimized resource -requirements too. For example, in some environments, it can run with as little as 4 MB of RAM on a J2SE/J2EE 1.3 or later platform. In addition, the number and size of tables are pretty much limited only by the disk space available to them. (The maximum number of indexes per table is 32,767, and the maximum number of columns in a table is 1,012.) This makes Apache Derby not only easy to deploy but also easy to embed within applications as well. In fact, it literally disappears into its hosting application.

Apache Derby packs a lot of punch in its tiny 2 MB allocation. It provides a fully relational database engine, including complete support for SQL92E and JDBC (2.0 and 3.0), and partial support for SQL99. It has many advanced features that experienced database developers may be accustomed to with larger databases, such as:

  • Identity columns (sometimes referred to as auto-incremented columns) for key generation

  • Fast query compilation through a cost-based optimizer that supports hash joins, sort avoidance, and row- or table-level locking based on a percentage of data selected

  • Automated statistics collection, automatic table reorganization and space reclamation, programmatic backups (no DBA required), and more

  • Binary large objects (BLOBs) for complex non-traditional data handling

  • Referential constraints (primary and foreign key), general constraints (unique and check), and default values for business rules enforcement

  • Transactional processing (it’s ACID-compliant; see the "Why Use a Relational Database?" section for more details) with isolation levels such as repeatable-read and uncommitted-read

  • Objects to encapsulate business logic and promote code reuse and best practices, such as stored procedures, user-defined functions (UDFs), and triggers

  • Views

  • Bulk load utility

  • Scrollable cursors for more efficient result set processing

  • Concurrent access by any number of program threads or processes

  • Multi-user access

  • JDBC (both an embedded driver and an external driver that’s the same one used with DB2 UDB for z/OS and DB2 UDB for Linux, Unix, and Windows—called the Java Common Client), ODBC, CLI, .NET, PHP, and Perl connectivity (add-on interfaces are available from the IBM Cloudscape Web site). In fact, IBM contributed the Apache Derby Network Client during the writing of this book—learn more at: http://incubator.apache.org/derby/faq.html#netclient.

  • Rich security features, such as signed Java Archive (.JAR) files, encryption of stored data (through the Java Cryptography Extension, or JCE, and other providers), ability to run with a Java 2 security manager enabled, optional LDAP authentication, and more

  • Unlimited table size (though many operating system’s 2 GB file limits could theoretically come into play because Cloudscape stores each table as a separate file)

  • The IBM Integration Plug-in for Apache Derby; a free Eclipse-based plug-in to implement Apache Derby functionality as user interface component (http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501cline/)

  • National language support for program integrated information (for example, error messages, program output, and so on), and documentation for the following languages: Spanish (es), German (de_DE), French (fr), Italian (it), Brazilian Portuguese (pt_BR), Korean (ko_KR), Japanese (ja_JP), Traditional Chinese (zh_TW), and Simplified -Chinese (zh_CN)

Officially, Apache Derby supports any standard JVM (J2SE 1.3 or higher). Whereas most applications detail operating system support prerequisites, the JVM is really the run-time platform for this database.

All Apache Derby components and future enhancements will adhere to the Apache Derby Charter. This charter is a statement of direction or purpose. It dictates a course of action that everyone involved in this community will adhere to in order to create the best possible underlying data store for their applications. Specifically, it decrees that any contributions to the Apache Derby code base must be completely written in Java, easy to use, have a small footprint, and be secure.

Details on SQL Support in Apache Derby and IBM Cloudscape

Apache Derby and IBM Cloudscape implement the SQL92E language standard and many features that are found in the SQL99 specification (with extensions for Java). Although not complete, the following list details most of the features that you’re likely to leverage when building your own application:

  • Basic database types, such as: CHAR, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT

  • Datetime data types (from SQL92T): DATE, TIME, TIMESTAMP (with JDBC date/time escape syntax)

  • Other types: BIGINT, VARCHAR, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, BLOB, CLOB

  • Basic operations: +,*,-,/,unary +,unary -

  • Basic comparisons: <,>,<=,>=,<>,=

  • Datetime literals

  • Built-in functions: ABS or ABSVAL, CAST, LENGTH, CONCATENATION (||), -NULLIF and CASE expressions, CURRENT_DATE, CURRENT_ISOLATION, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DATE, DAY, HOUR, IDENTITY_VAL_LOCAL, LOCATE, LCASE or LOWER, LTRIM, MINUTE, MOD, MONTH, RTRIM, SECOND, SESSION_USER, SQRT, -SUBSTR, TIME, TIMESTAMP, UCASE or UPPER, USER, YEAR

  • Basic predicates: BETWEEN, LIKE, NULL

  • Quantified predicates: IN, ALL, ANY or SOME, EXISTS

  • CREATE and DROP SCHEMA

  • CREATE and DROP TABLE

  • Check constraints

  • ALTER TABLE: ADD COLUMN and ADD or DROP CONSTRAINT

  • CREATE and DROP VIEW

  • Constraints: NOT NULL, UNIQUE, PRIMARY KEY, CHECK, FOREIGN KEY

  • Cascade delete

  • Column defaults

  • Delimited identifiers

  • Updatable cursors (through JDBC)

  • Dynamic SQL (through JDBC)

  • INSERT, UPDATE, and DELETE statements

  • Positioned updates and deletes

  • WHERE qualifications

  • GROUP BY

  • HAVING

  • ORDER BY

  • UNION and UNION ALL

  • Subqueries as expressions (from SQL92F)

  • Joins in the WHERE clause

  • Joins (SQL92T): INNER, RIGHT OUTER, LEFT OUTER, named column join, conditional join

  • Aggregate functions (with DISTINCT): AVG, COUNT, MAX, MIN, SUM

  • SELECT *, SELECT table.* (SQL92T), SELECT DISTINCT, select expressions

  • Named select columns

  • SQLSTATE

  • UNION in views (SQL92T)

  • CAST (SQL92T)

  • INSERT expressions (SQL92T): insert into T2 (COL) select col from T1

  • VALUES expressions: select * from (values (1, 2)) as foo(x, y), and so on

  • Triggers

  • + Share This
  • 🔖 Save To Your Account