LexBuild

Java DB (Derby, 10.3.1.4)

Description

Sun Java DB is based on the open source Apache Derby project. 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 2MB. 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

  • Pure Java: write once, run anywhere to supports 2SE, J2EE and J2ME standards
  • Standards-based: JDBC and ANSI SQL standards
  • Provides both embedded and client-server modes
  • Secure: encryption, authentication through either external LDAP directory or built-in repository, and authorization
  • Sophisticated – with triggers and stored procedures
  • Small foot-print: With a foot-print of 2MB
  • Implements an SQL-92 core subset, as well as some SQL-99 features

License

Usage Summary

  • Download and install javadb to ${DB_DIR}/JavaDb/javadb
  • 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)
      	
  • Use network (server) mode
  • Start & shutdown server:
    • shell> java -Dderby.system.home=${DB_DIR}/JavaDb/data -jar $DERBY_HOME/lib/derbyrun.jar server start -h <host> -p <portNumber> &
    • shell> java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown
  • Start client:
    • shell> ij
    • 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:
    • JDBC & setup:
      • Use JDBC in Java to perform database query
      • jdbc driver: org.apache.derby.jdbc.ClientDriver
      • jdbc url: jdbc:derby:${DB_NAME}
    • 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:
    CharacterEscape CharacterNotes
    '''Two single quotes

References

Please refer to JavaDB Web site.