Java DB (Derby, 10.14.2.0)

Description

Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0. It provides a full-featured, robust, small-footprint Java database management system that is cost effective and simple to deploy.

Java DB is Sun's supported distribution of the open source Apache Derby 100% Java technology database. It is fully transactional, secure, easy-to-use, standards-based SQL, JDBC API, and Java EE yet small, only 2.5 MB. The Apache Derby project has a strong and growing community that includes developers from large companies such as Sun Microsystems and IBM as well as individual contributors.

Features

• Full-featured, easy-to-use, SQL database.
• Protection against data corruption and system crashes.
• Free under the Apache license.
• Pure Java: write once, run anywhere to supports 2SE, J2EE and J2ME standards
• Runs everywhere from PDAs to laptops to mainframes.
• Embeddable in applications.
• Portable and 100% Java, CDC, SQL, and JDBC compliant.
• Easy, one-click installation.
• Small footprint of 2.6 Mb for the base engine and embedded JDBC driver
• Standards-based: Java, JDBC and ANSI SQL standards
• Provides both embedded and client-server modes
• Is easy to install, deploy, and use.
• Secure: encryption, authentication through either external LDAP directory or built-in repository, and authorization
• Sophisticated: with triggers and stored procedures
• Implements an SQL-92 core subset, as well as some SQL-99 features

License

• It is free under Apache license

Usage Summary

• Installation
  • Requires J2SE 1.4.2 or higher
  • Download db-derby-version.bin.tar.gz
  • Install javadb:
    • install to ${DB_DIR} => /usr/local/Database
    • shell> move db-derby-version.bin.tar.gz to ${DB_DIR}/JavaDb/db-derby-version.bin.tgz
    • shell> cd ${DB_DIR}/JavaDb/
    • shell> gtar -xzvf db-derby-version.bin.tgz
    • Set environment variables in ~/.cshrc:
      • setenv DERBY_HOME ${DB_DIR}/JavaDb/javadb
        -> /usr/local/Database/JavaDb/javadb.10.14.2.0
      • CLASSPATH should include "${DERBY_HOME}/lib/derby.jar:${DERBY_HOME}/lib/derbytools.jar:"
    • shell>source ~/.cshrc
    • shell>java org.apache.derby.tools.sysinfo (verify)
• ij Basics (JavaDb interface tool)
  • Start up ij
    shell>java org.apache.derby.tools.ij
    => run ij
    => generate derby.log
  • Create a database
    ij> connect 'jdbc:derby:MyDbTest;create=true';
    => create a new database called MyDbTest
    => generate a directory MyDbTest
  • connect to a database
    shell>java org.apache.derby.tools.ij
ij> connect 'jdbc:derby:MyDbTest';
    => "jdbc:derby:" is the protocol
    => "MyDbTest" is the database

    or, we could specify the protocol with a property:
    shell>java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij> connect 'MyDbTest';

  • database home
    shell>java org.apache.derby.tools.ij
ij> connect 'jdbc:derby:/home/database/MyDbTest';
    => "/home/database/MyDbTest" is the complete directory path of the database

    or, we could specify the Derby system home with a property:
    shell>java -Dderby.system.home=/home/databases org.apache.derby.tools.ij
ij> connect 'jdbc:derby:MyDbTest';

  • Execute SQL statements
    ij> create table derbyDB(num int, addr varchar(40));
ij> insert into derbyDB values (1956,'Webster St.');
ij> insert into derbyDB values (1910,'Union St.');
ij> update derbyDB set num=180, addr='Grand Ave.' where num=1956;
ij> select * from derbyDb;
  • Disconnect form a database
    ij> disconnect;
  • Exit ij
    ij> exit;
• Embedded Derby
  • Definition:
    Embedded mode is when the Derby database engine runs inside the same Java Virtual Machine (JVM) as the application.
  • Load the Embedded JDBC Driver:
public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
...
Class.forName(driver).newInstance();
  • Get an Embedded Connection
public String protocol = "jdbc:derby:";
...
conn = DriverManager.getConnection(protocol + "derbyDB;create=true", props);
  • Shut Derby down
DriverManager.getConnection("jdbc:derby:MyDbTest;shutdown=true");
or
DriverManager.getConnection("jdbc:derby:;shutdown=true");
• Derby Network Server
  • Definition:
    handles database requests from applications running in different JVMs on the same machine or on remote machines
  • Server Setup
    set CLASSPATH to include:
    • derbynet.jar: contains the code for the Derby Network Server and a reference to the engine jar file (derby.jar)
    • derbytools.jar: contains Derby tools
  • Start NewWork Server
    shell> java -Dderby.system.home=${DB_DIR}/JavaDb/data -jar ${DERBY_HOME}/lib/derbyrun.jar server start -h <host> -p <portNumber> &
    => Create data directory as ${DB_DIR}/JavaDb/data
    => All database should be created under this directory
    Edit derby.properties under ${DB_DIR}/JavaDb/data

    		derdy.connection.requireAuthentication=true
    		derdy.authentication.provider=BUILTIN
    		derby.user.username=password
    		derby.drda.portNumber=1110
    		derby.drda.host=myhost
    		derby.drda.host=my.host.ip.address (must be after myhost)
    		

  • Stop Network Server:
    shell> java -jar ${DERBY_HOME}/lib/derbyrun.jar server shutdown
  • Auto start JavaDb after reboot:
    use systemctl
    • /usr/lib/systemd/system/derby.service

      		...
      		Environment="JAVA_DB_DIR=/usr/local/Database/JavaDb"
      		Environment="JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64"
      		ExecStart=/bin/bash -c "/usr/local/Database/JavaDb/bin/startJavaDb"
      		ExecStop=/usr/local/Database/JavaDb/bin/shutdownJavaDb
      		..
      		User=chlu
      		...
      		

    • sudo systemctl start derby
    • sudo systemctl status derby
    • sudo systemctl stop derby
    • sudo systemctl restart derby
  • Client Setup
    set CLASSPATH to include:
    • derbyclient.jar: contains the JDBC driver
    • derbytools.jar: contains Derby tools
  • Use ij as Client:
shell>java org.apache.derby.tools.ij
ij>connect 'jdbc:derby://localhost:1527//home/database/name_of_database;create=true';
ij>connect 'jdbc:derby://localhost:1527/name_of_database;create=true';
ij>connect 'jdbc:derby://localhost:1527/name_of_database';
ij>connect 'jdbc:derby://localhost:1527/scrt';
ij>show tables;
  • Java APIs:
    • Load the Client JDBC Driver
      driver = "org.apache.derby.jdbc.ClientDriver";
...
Class.forName(driver).newInstance();

    • Get a Network Server Connection
      protocol = "jdbc:derby://localhost:1527/";
...
conn = DriverManager.getConnection(protocol + "derbyDB;create=true", props);
  • SQL: Query code:
    • AUTO_INCREMENT => GENERATED BY DEFAULT AS IDENTITY
    • TEXT => LONG VARCHAR (32,700)
    • TEXT => BLOB (2,147,483,647)

    • Use "CREATE INDEX LexRecordBaseIndex ON LEX_RECORD(base)" if there are more than one columns need to be indexed

    • Can't use '3' for INT, instead, use 3.
      For example: SELECT * FROM PROJECT WHERE ID = 3;
  • Table names:
    • USER => USERS
    • VIEW => VIEWS
    • Can't include characters of ', ", etc..
  • Escape characters for INSERT/UPDATE SQL:

    Character	Escape Character	Notes
    '	''	Two single quotes

References

Please refer to JavaDb web sites:

• Apache Derby
• Apache Derby Tutorial
• Oracle JavaDb