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

• It is free under Apache 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:

  Character	Escape Character	Notes
  '	''	Two single quotes

References

Please refer to JavaDB Web site.