Java DB

FAQs for Java DB

This collection of FAQs provides brief answers to the most common questions about Java DB. It also links to more detailed information available from this web site and others.

General

Installation and Configuration

SQL Functionality

JDBC

System Limitations

Database Administration Tools

What tools are available for database backup and maintenance?

Advanced Topics

Troubleshooting

General

Is Java DB an object database?

No, Java DB is a relational database management system (RDBMS). Data is stored and queried via SQL or JDBC. Like other RDBMSs, Java DB can also be accessed through a persistence layer such as JPA, Hibernate, or JDO.

Is Java DB a fork of Apache Derby?

No, Java DB is Sun's supported version of Apache Derby and contains the same binaries as Apache Derby.

Can I get support for Java DB?

Yes, support is available from Sun. You can also contact the Apache Derby user community with questions and bug reports.

Does Java DB support multiple and concurrent users?

Absolutely. Java DB is a multi-user database engine and supports concurrent users.

Installation and Configuration

Where can I download Java DB?

You can download Java DB from the Downloads page on this site. Java DB is also bundled as part of Sun's JDK 6 as well as in GlassFish.

How do I install and configure Java DB/Derby?

The Java DB download page has installation instructions. The Apache Derby Tutorial is a good place to begin. Additional resources are listed in the Reference section of this site.

SQL Functionality

What data types does Java DB provide?

Java DB supports all SQL standard numeric and character data types, as detailed at SQL-99 and SQL-2003 features including data types are mapped to Derby, sections E011 and E021. Support for the BOOLEAN data type is under discussion for a future release.

Does Java DB have strong SQL-92 support?

Yes, Java DB supports almost all SQL92 features. You can find details in the section of the Derby Reference Manual titled "Derby support for SQL-92 features."

Does Java DB support stored procedures?

Yes, see the Derby SQL Routines wiki page for information on how to implement procedures and function in Java and how to invoke them.

10.

Does Java DB support subqueries in WHERE and FROM clauses?

Java DB supports subqueries in WHERE clause, FROM clause, IN predicate, and quantified comparison predicate, but does not currently support updatable queries with subqueries.

11.

Does Java DB support built-in string functions?

Yes, Java DB supports upper, lower, concat, substring, like, length, trim; see SQL-99 and SQL-2003 features including data types are mapped to Derby, section E021.

12.

Does Java DB provide functions to extract components of date/time values?

Yes, Java DB provides special functions that allow you to extract the year, month, day, hour, minute, and second from a DATE, TIME, or TIMESTAMP value. The returned values are large integers, so you can perform arithmetic operations on them.

13.

Does Java DB support date/time arithmetic?

Yes, Java DB supports date/time arithmetic through use of the functions TIMESTAMPADD and TIMESTAMPDIFF on a TIMESTAMP value.

14.

Which aggregate functions does Java DB support?

Java DB supports the SQL standard aggregate functions: COUNT, MIN, MAX, AVG, and SUM.

15.

Does Java DB have an equivalent of the LIMIT clause or ROWNUM function for pagination queries?

Java DB does not currently support this feature, but ROW_NUMBER is currently under development.

JDBC

16.

What versions of JDBC does Java DB support?

Beginning with version 10.2.2.0, Java DB supports JDBC 4.0. Earlier versions support JDBC 3.0.

17.

Does Java DB support connection pooling and JNDI data source?

Yes, Java DB supports these features.

18.

Does Java DB support the metadata API?

Yes, Java DB provides a full JDBC metadata API implementation ( java.sql.DatabaseMetaData).

19.

Does Java DB have XA support?

Yes, Java DB has XA support.

20.

Does Java DB allow multiple active result sets on the same connection?

Yes, Java DB supports allows this.

System Limitations

21.

What is the size limit of a database?

Java DB stores each base table and each index in a single file, so the data size limit is the file size limit of the JVM and OS on which it runs. Derby is coded against the Java 64 bit interfaces to access these files, so internally the maximum table size is approximately 2**64. The number of tables is only limited by the 64 bit length of table identifiers, for an approximate limit of 2**64 total tables. In practice, system resources such as disk space are a more likely source of size limitations. Java DB is tested on a database using over 350 GB of raw disk space with tables containing over 500 million tuples.

22.

What is the maximum allowable size of a binary object?

The maximum size of a CLOB or BLOB stored in Java DB is 2 GB.

23.

What platforms does Java DB run on?

Java DB runs on any platform with a certified JVM. It is tested on wide variety of platforms (see the 10.3 platform testing wiki for details).

Database Administration Tools

24.

What tools are available for database backup and maintenance?

The interactive SQL scripting tool provided with Derby, ij, can be used for backup and administration tasks. Currently, Java DB does not include a GUI tool for administration, but there is interest for it in the community, and may be developed in the future. There are open source and commercial GUI tools that support Derby, such as SQuirreL SQL Client. These work well for most users, given Derby's commitment to ease of use.

Advanced Topics

25.

How does "disk caching" impact database performance? data durability?

On many platforms, including Windows and Linux, the disk write cache is enabled by default. This improves disk performance, but can result in data loss or corruption in case of power outage or equipment failure. For more information, please read " The story of the write cache and half a worm."

26.

Can I use Java DB with JPA/Hibernate/JDO?

Yes. For help, see Using JPOX JDO with Derby, Initializing Embedded Derby using JPA, and the HibernateHelp wiki pages.

27.

How do I use Java DB with Java ME?

Java DB can be run with the following Java ME technologies: CDC 1.1, Foundation Profile 1.1, JDBC Optional Package for CDC/Foundation Profile (JSR169). See the JavaMESupport wiki page for more information.

28.

Is there a clustering solution for Java DB?

There is currently no built-in clustering support in Java DB. Sequoia is an open source, Apache licensed, clustering solution that works with Java DB.

Troubleshooting

29.

Why can't Java DB find my table/column?

SQL identifiers containing special characters or identifiers that you wish to be case-sensitive must be enclosed in double quotes. Unquoted identifiers will be case insensitive. See Capitalization and special characters in the Derby Reference Manual for more information.

30.

How do I improve performance with Java DB?

The Java DB Performance presentation gives an overview of how to configure Java DB to achieve optimal performance while being able to handle failure scenarios without losing data. The PerformanceDiagnosisTips wiki page shows how to investigate performance issues that might occur between Derby releases.