Java DB

At a Glance

The following FAQs are no longer current. They remain here for archival purposes. For the latest information on JavaDB/Derby please visit The Apache DB Project.

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.

FAQs for Java DB

Open all Close all

    General

  • Q: Is Java DB an object database?

    A: 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. Java objects can be stored in the columns of Java DB tables.

  • Q: Is Java DB a fork of Apache Derby?

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

  • Q: Can I get support for Java DB?

    A: Yes, support is available from Oracle via a JDK support contract. You can also contact the Apache Derby user community with questions and bug reports.

  • Q: Does Java DB support multiple and concurrent users?

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

    Installation and Configuration

  • Q: Where can I download Java DB?

    A: Java DB is bundled in the db subdirectory of the Java 7 JDK.

  • Q: How do I install and configure Java DB/Derby?

    A: See the Getting Started Guide here.

    SQL Functionality

  • Q: What data types does Java DB provide?

    A: Java DB provides data types corresponding to all of the Java primitive types (except for byte), plus Strings, byte arrays, date and time stamps, and user-defined objects.

  • Q: Does Java DB have strong SQL support?

    A: Yes, Java DB supports almost all SQL92 features as well as features from SQL99, SQL2003, and SQL2008. For more information, see SQL-99 and SQL-2003 features mapped to Derby.

  • Q: Does Java DB support stored procedures?

    A: Yes. For more information on how to implement and invoke procedures and functions, see Derby SQL Routines. Note that procedures and functions are written in Java just like application code.

  • Q: Does Java DB support subqueries in WHERE and FROM clauses?

    A: Java DB supports subqueries in WHERE clauses, FROM clauses, IN predicates, and quantified comparison predicates, but does not currently support updatable queries with subqueries. Subqueries can contain OFFSET/FETCH windows and return ORDER BY results.

  • Q: Does Java DB support built-in string functions?

    A: Yes, Java DB supports upper, lower, concat, substring, like, length, and trim.

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

    A: 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.

  • Q: Does Java DB support date/time arithmetic?

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

  • Q: Which aggregate functions does Java DB support?

    A: Java DB supports the SQL standard COUNT, MIN, MAX, AVG, and SUM aggregates, which may be grouped using GROUP BY and ROLLUP clauses.

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

    A: Yes, Java DB supports SQL standard FETCH/OFFSET syntax. Scrollable JDBC ResultSets can also be used for pagination and positioned updates.

    JDBC

  • Q: What versions of JDBC does Java DB support?

    A: Java DB supports JDBC 4.1

  • Q: Does Java DB support connection pooling and JNDI data source?

    A: Yes, Java DB supports these features.

  • Q: Does Java DB support the metadata API?

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

  • Q: Does Java DB have XA support?

    A: Yes, Java DB has XA support.

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

    A: Yes, Java DB supports allows this.

    System Limitations

  • Q: What is the size limit of a database?

    A: 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.

  • Q: What is the maximum allowable size of a binary object?

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

  • Q: What platforms does Java DB run on?

    A: Java DB runs on CDC/FP 1.1, Java 5, Java 6, and Java 7.

    Database Administration Tools

  • Q: What tools are available for database backup and maintenance?

    A: Java DB/Derby provides system procedures for backup and administration tasks. Various open source and commercial GUI tools support Derby administration, such as SQuirreL SQL Client. These work well for most users, given Derby's commitment to ease of use.

    Advanced Topics

  • Q: How does "disk caching" impact database performance? data durability?

    A: 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, see Configuring Apache Derby for Performance and Durablity.

  • Q: Can I use Java DB with JPA/Hibernate/JDO?

    A: Yes. For help, see this JPOX help, this JPA blog, and this Hibernate page.

  • Q: How do I use Java DB with Java ME?

    A: 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 Using Derby on Java ME Platforms.

  • Q: Is there a clustering solution for Java DB?

    A: Java DB supports asynchronous replication to an offline slave database.

    Troubleshooting

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

    A: 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. For more information, see the "Capitalization and special characters" section in the Derby Reference Manual here.

  • Q: How do I improve performance with Java DB?

    A: For an overview of how to configure Java DB to achieve optimal performance while being able to handle failure scenarios without losing data, see Configuring Apache Derby for Performance and Durability. For tips on investigating performance issues that might occur between Derby releases, see Performance and Diagnosis Tips.