Derby is a open source relational database management system that is based on Java technology and SQL and it full featured database.
Derby is written and implemented completely in the Java programming language. Derby provides users with a small-footprint standards-based database engine that can be tightly embedded into any Java based solution. Derby ensures data integrity and provides sophisticated transaction support. In the default configuration there is no separate database server to be installed or maintained by the end user.
The on-disk database format used by Derby is portable and platform-independent. You can move Derby databases from machine to machine without needing to modify the data. A Derby application can include a pre-built, populated database if it needs to, and that database will work in any Derby configuration.
For more information about Derby, visit the Derby website at http://db.apache.org/derby. The website provides pointers to the Derby Wiki and other resources, such as the derby-users mailing list, where you can ask questions about issues not covered in the documentation.
Derby runs in the same Java virtual machine (JVM) as the application. It must needed JDK version 6 and above. Derby only used with JDBC to SQL
This tutorial uses a series of activities designed to demonstrate the use of Derby in the embedded and client/server configurations. After performing these activities, you will find Derby to be an easy-to-use and fully functional relational database management system (RDBMS).
To perform the tutorial activities, you do not need to have any prior knowledge of Java software, the Java Database Connectivity (JDBC) API, or SQL. Each activity provides the complete command syntax that you need to use for each operation. Instructions include the syntax for both a UNIX Korn shell and a Windows command prompt.
This tutorial demonstrates, but does not teach, the Java, JDBC and SQL code that is presented. If you want a deeper understanding of these topics, you will need to consult additional reference materials.
To perform the tutorial activities, you must have:
A Java Development Kit (JDK) version 6 or higher installed on your computer
The binary (bin) installation of Apache Derby version 10.13 installed on your computer
A basic knowledge of the computer command line interface:
How to start a command shell or window
How to navigate the file system hierarchy
Navigate your Web browser to http://db.apache.org/derby/derby_downloads.html. The downloads page provides links to the distributions of Derby This page also contains a link to http://db.apache.org/derby/dev/derby_source.html, where you can find information on how to use the Subversion version control system to access the Derby source code for a specific branch or for the development trunk.
Download the ZIP or TAR archive for the distribution that you want to use.
Extract the downloaded package. The extracted installation contains several subdirectories:
The demo subdirectory contains the demonstration programs.
The bin subdirectory contains the scripts for executing utilities and setting up the environment.
The javadoc subdirectory contains the api documentation that was generated from source code comments.
The docs subdirectory contains the Derby documentation.
The lib subdirectory contains the Derby .jar files.
The test subdirectory contains regression tests for Derby.
Setting the environment variables
UNIX (Korn Shell) export DERBY_HOME=/opt/Derby_10
Windows set DERBY_HOME=c:Derby_10
Running the scripts with the Network Server
To run the scripts with the Network Server, use the following commands:
For the sysinfo tool, issue the command
For the ij tool, issue the command
and then start ij by issuing the command ij.
For the dblook tool, call the script and specify the -d option and the full connection URL to the Network Server database. For example:
dblook -d jdbc:derby://localhost/myDB;user=usr
Verifying the Derby system configuration
Verify that the java executable file, version 6 or higher, is in your command execution PATH by opening a command window and running the java -version command. The output from the command looks something like this:
java version "1.7.0_07"
Java(TM) SE Runtime Environment (build 1.7.0_07-b10)
Java HotSpot(TM) Client VM (build 23.3-b01, mixed mode, sharing)
The output you see might be different from what is shown here because the java -version command outputs vendor-specific information. If the command produces an error or the version listed is not 6 or higher, install a JDK before continuing.
Verify that the DERBY_HOME environment variable is set and points to the root directory of the Derby 10.13 installation. Open a command window and run the appropriate command for your system. If you installed Derby in the /opt/Derby_10 directory on UNIX or the c:Derby_10 directory on Windows, the command that you use and the expected output are shown in the following table.
Table 1. Verifying the DERBY_HOME setting
Operating System Command Output
UNIX (Korn Shell) echo $DERBY_HOME /opt/Derby_10
Windows echo %DERBY_HOME% C:Derby_10
Derby embedded driver to create and connect to the firstdb database
1 . Run the Derby ij tool.
If you included the DERBY_HOME/bin directory in your PATH environment variable, type:
Otherwise, you can use the java command to start the ij tool, as shown in the following table.
Table 1. Command to start the ij tool
Operating System Command
UNIX (Korn Shell)
java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.13
java -jar %DERBY_HOME%libderbyrun.jar ij
ij version 10.13
2. Create the database and open a connection to the database using the embedded driver.
Description of connection command:
The ij command to establish a connection to a database. The Derby connection URL is enclosed in single quotation marks. An ij command can be in either uppercase or lowercase.
The JDBC protocol specification for the Derby driver.
The name of the database. The name can be any string. Because no filepath is specified, the database is created in the default working directory (DERBYTUTOR).
The Derby URL attribute that is used to create a database. Derby does not have an SQL create database command.
The semicolon is the ij command terminator.
3. Create a table with two columns using standard SQL.
CREATE TABLE FIRSTTABLE
(ID INT PRIMARY KEY,
0 rows inserted/updated/deleted
4. Insert three records.
INSERT INTO FIRSTTABLE VALUES
3 rows inserted/updated/deleted
5. Perform a simple select of all records in the table.
SELECT * FROM FIRSTTABLE;
3 rows selected
Perform a qualified select of the record with column ID=20.
SELECT * FROM FIRSTTABLE WHERE ID=20;
1 row selected
1 . Open a command window (Shell-1) and change to the DERBYTUTOR directory.
UNIX (Korn Shell)
java -jar $DERBY_HOME/lib/derbyrun.jar server start Thu Sep 13 16:45:36 EDT 2012 : Security manager installed using the Basic server security policy. Thu Sep 13 16:45:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) started and ready to accept connections on port 1527
java -jar %DERBY_HOME%libderbyrun.jar server start Thu Sep 13 16:45:36 EDT 2012 : Security manager installed using the Basic server security policy. Thu Sep 13 16:45:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) started and ready to accept connections on port 1527
2. Open another command window (Shell-2). Change to the DERBYTUTOR directory
3. Create and open a connection to the database using the client driver.
Remember: A client connection URL contains a hostname and a port number. For example: //localhost:1527/
Create a table with two columns (ID and NAME) using the following SQL statement:
CREATE TABLE SECONDTABLE (ID INT PRIMARY KEY, NAME VARCHAR(14)); 0 rows inserted/updated/deleted
Insert three records into the table. INSERT INTO SECONDTABLE VALUES (100,ONE HUNDRED),(200,TWO HUNDRED),(300,THREE HUNDRED); 3 rows inserted/updated/deleted
Select all of the records in the table. SELECT * FROM SECONDTABLE; ID |NAME ------------------------ 100 |ONE HUNDRED 200 |TWO HUNDRED 300 |THREE HUNDRED 3 rows selected Select a subset of records from the table by specifying a WHERE clause. SELECT * FROM SECONDTABLE WHERE ID=200; ID |NAME ------------------------ 200 |TWO HUNDRED 1 row selected
The Java compiler and runtime use the classpath, specified by the CLASSPATH environment variable, to locate the binary files (jar files and class files) that are needed to run Derby and other Java applications. Before performing this activity, you need to set the classpath and compile the WwdEmbedded.java program.
1 .Copy the program files into the DERBYTUTOR directory and set the CLASSPATH environment variable
UNIX (Korn Shell) cp $DERBY_HOME/demo/programs/workingwithderby/* . export CLASSPATH=$DERBY_HOME/lib/derby.jar:. Windows copy %DERBY_HOME%demoprogramsworkingwithderby* . set CLASSPATH=%DERBY_HOME%libderby.jar;.
2. Compile the program source files. The sample program is contained in two source files: WwdEmbedded.java and WwdUtils.java. Issue the following command to compile both at the same time:
javac WwdEmbedded.java WwdUtils.java
Run the program. The WwdEmbedded.java program populates a table with wish-list items
java WwdEmbedded org.apache.derby.jdbc.EmbeddedDriver loaded. Connected to database jdbcDemoDB . . . . creating table WISH_LIST Enter wish-list item (enter exit to end): a peppermint stick __________________________________________________ On 2012-09-14 09:46:37.77 I wished for a peppermint stick __________________________________________________ Enter wish-list item (enter exit to end): a long vacation __________________________________________________ On 2012-09-14 09:46:37.77 I wished for a peppermint stick On 2012-09-14 09:46:51.654 I wished for a long vacation __________________________________________________ Enter wish-list item (enter exit to end): exit Closed connection Database shut down normally Getting Started With Derby JDBC program ending.
Simple and dept learning technology is very important part in software development. Same story has been implemented at this site, so learn and implement the inovative things, Thanks
Ravi Kumar Verma