Introduction of Derby

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.

System requirement

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

software requirements

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

Installation and configuration of Derby

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

NetworkServerControl sysinfo

For the ij tool, issue the command

set DERBY_OPTS=-Dij.protocol=jdbc:derby://localhost/

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

Creating a Derby database and running SQL statements

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:

ij

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

Windows

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.

CONNECT jdbc:derby:firstdb;create=true;

Description of connection command:

connect

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.

jdbc:derby:

The JDBC protocol specification for the Derby driver.

firstdb

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

;create=true

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,

NAME VARCHAR(12));

0 rows inserted/updated/deleted

4. Insert three records.

INSERT INTO FIRSTTABLE VALUES

(10,TEN),(20,TWENTY),(30,THIRTY);

3 rows inserted/updated/deleted

5. Perform a simple select of all records in the table.

SELECT * FROM FIRSTTABLE;

ID |NAME

------------------------

10 |TEN

20 |TWENTY

30 |THIRTY

3 rows selected

Perform a qualified select of the record with column ID=20.

SELECT * FROM FIRSTTABLE WHERE ID=20;

ID |NAME

------------------------

20 |TWENTY

1 row selected

Run SQL using the client driver

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
 

Windows

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

Start ij

3. Create and open a connection to the database using the client driver.

CONNECT jdbc:derby://localhost:1527/seconddb;create=true;

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

Exit ij.

exit;

Run a JDBC program using the embedded driver

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.

Link
Eng. Ravi Kumar Verma

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