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