Getting Started With Apache Derby

Features and information are the salient feature of any project, whether it is a single desktop application or a cloud application, that is distributed over several machines, and treating them is often considered frenetic. Hence, to make it manageable, storing them in a database is favourable alternative. Database helps in keeping the information in a systematic manner. It helps in managing large amount of information in small time. It is basically an organized collection of data. Meanwhile, Relational Database, more restrictively, is a collection of schema, tables, queries, reports, views, and other elements.

A software system that enables users to define, create, maintain and control access to the database, is defined as a Database Management System (DBMS). And a DBMS based on relational model is Relational Database Management System (RDBMS). So Apache Derby is open source RDBMS entirely implemented in Java. Below are some key advantages :

1. Apache Derby has a small footprint and an embedded Java Database Connectivity (JDBC) driver.
2. Apache Derby is based on the Java, SQL and JDBC standards.
3. Apache Derby provides an embedded JDBC driver that lets you embed Apache Derby in any Java-based environment.
4. Apache Derby supports the more familiar client/server mode.
5. Apache Derby is easy to install, use and deploy.

In this article, we will go through the complete process of installing and understanding the functionalities of Apache Derby.

Installing Apache Derby

Prerequisite: java 1.8 or above.

For windows, download Apache Derby zip and extract it. After extraction, copy the folder that contains the bin folder, to root directory and rename it to java-derby (renaming is just for ease of locating it). Now, add path, till the bin folder, in PATH environment variable. Add a new variable, in the system variable section and user variable section, named as DERBY_HOME and add the path, till the parent directory of the bin folder, as its value. Now move to a directory, say C:\work\derby, and type startnetworkserver. If everything goes well, your prompt will look like this:

Apache Derby Server listening at port 1527

Note : If a firewall prompt appears, select allow. If you any face problem, turn off the firewall.

Verification: Using another prompt move to derby folder, again. Create another folder named as database. You don’t need to move in it. We will ask the Apache Derby server to create database files in the database folder. Now, while staying in the c:\work\derby folder type ij, you will see a derby prompt. In that, type:

connect ‘jdbc:derby://localhost:1527/database/places;create=true’;

By doing this, we are creating a JDBC connectivity and creating a database named as places and “create=true” specifies we need to create a new one. Every time, while connecting, we won’t be writing this. It is a one time act. If you have executed everything correctly, then on the next line the derby prompt will appear as follows:

The database structure has been created in database folder. Type quit; to exit from Apache Derby Client.

Now, we are ready to jump to the implementation part, where we will create a database and control it accordingly.

Creating Tables:

For creating table, run the Apache Derby ij client tool and connect, as done previously. This time we won’t specify “create=true” as the database has already been created. After connecting, write these SQL statements :

create table country (code int primary key generated always as identity (start with 1,increment by 1), 
name char(30) not null unique); 
create table state (code int primary key generated always as identity (start with 1,increment by 1), 
name char(30) not null unique, 
country_code int not null, 
foreign key (country_code) references country); 
create table city (code int primary key generated always as identity (start with 1,increment by 1), 
name char(30) not null unique, 
state_code int not null, 
foreign key (state_code) references state);

Once you are done, your Apache client prompt will look like this :

SQL for creating tables

Through these statements, we created 3 tables : country, state, city, with some constraints such as primary key, not null, unique, foreign key. Some of these can be column-level constraints as well as table-level constraints.

Column-level constraints :

NOT NULL: Specifies that this column cannot hold NULL values (constraints of this type are not nameable)
PRIMARY KEY: Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.
UNIQUE: Specifies that values in the column must be unique. NULL values are not allowed.
FOREIGN KEY: Specifies that the values in the column must correspond
to values in a referenced primary key or unique key column or that they are NULL.
CHECK: Specifies rules for values in the column.

Table-level constraints :

PRIMARY KEY: Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.
UNIQUE: Specifies that values in the columns must be unique. The identified columns must be defined as NOT NULL.
FOREIGN KEY: Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.
Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
CHECK: Specifies a wide range of rules for values in the table.

Now, once we are completed with creating tables, we will now perform CRUD operations on these tables, using Apache Client prompt.

Performing CRUD Operations using Apache Derby Client:

Inserting:

We will start by adding data to the table. But while practicing, for enhancing your understanding, you should try to insert, update or delete records which violates primary key or not null or foreign key constraints. Like, inserting countries with same name would look like this :

Violating unique name constraint for country table

For inserting and retrieving , SQL statements are :

insert into country (name) values (‘India’);
insert into country (name) values ('Russia');
insert into country (name) values ('USA');
select * form country; //"*" refers to "All Columns"

Now, if we observe the above output, we can notice that “Russia” got code as 3 and not 2. This is because, we tried to insert “India” again, it failed because of unique constraint applied on name column, but the value 2 got used up while we tried to insert “India” and hence, the next country inserted got the code 3.

Now, some SQL statements for inserting data to state and city tables are :

insert into state (name,country_code) values (‘Maharashtra’,1); insert into state (name,country_code) values (‘Madhya Pradesh’,1); insert into state (name,country_code) values (‘Moscow’,3);
insert into state (name,country_code) values (‘Something’,4);
insert into state (name,country_code) values (‘Florida’,4); 
insert into state (name,country_code) values (‘Texas’,4);
insert into city (name,state_code) values('Mumbai',1); 
insert into city (name,state_code) values('Pune',1); 
insert into city (name,state_code) values('Bhopal',2); 
insert into city (name,state_code) values('Indore',2);
insert into city (name,state_code) values('Klin',3);
insert into city (name,state_code) values('Los Angeles',5);
insert into city (name,state_code) values('Plant City',5); 
insert into city (name,state_code) values('Arlington',6);

State name “Something” is added purposely, so that we can update it later. Now the tables are data entry looks like:

Data Entry

Updating:

Now, we will update the previous added state named as “something” and after the changes are reflected, we will delete the same. SQL Statements are:

update state set name = ’Karnataka’,country_code = 1 where name = ’Something’;

This SQL will find the state with the name as “Something” and alter the column(s) of the row as specified. Once you fire this SQL, your table looks like :

State table before and after updating columns

Deleting:

Note the code of the state and remember it for the delete statement. SQL statement for deleting a record :

delete from state where code = 4;

This statement will find the row where code is 4 and once found, it will be deleted. Once you fire this SQL, your table looks like :

State table before and after deleting a record

Now, we know how to insert, update and delete a record. There can be number of variations using multiple clause but the basic plot remains the same.

Fetching:

When we want to retrieve information from database, a query is fired. A query is a question or inquiry for a set of data. We use SQL, to retrieve data from databases. Like, retrieving all the states or maybe of the all the states of a country. This can be done by using clause like a “where” clause. For better understanding, see the outputs of following SQL statements:

select * from country; // all the Countries
select * from city where state_code=1; // all cities of Maharashtra

Output:

The above queries produces output in the form of table and look like this :

Similarly, queries can be used to get the data from multiple tables, or it can have a sub-query deciding the condition for the where clause. Some more interesting SQL statements:

select * from state where country_code=(select code from country where name=’India’); 
// we will get a table with all the states of India
select * from city where state_code in (select code from state where country_code=(select code from country where name='India'));
// we will get a table with all the cities of India

In the second query, initially the query to get the code for the country “India” get executed. Then, next query results in a table containing all the state codes, where country code is corresponding to that of “India”. Now, by applying “in” clause, the query for getting the cities, is executed multiple time, depending upon the number of rows in the table with state codes. Hence, all the cities of “India” will be received as the final output. And this is how a sub-query works.

Now, for retrieving the complete data to display all the cities corresponding to their states and all the state to their countries, fire the SQL statement :

select city.name as “City”,state.name as “State”,country.name as “Country” from city,state,country where city.state_code=state.code and state.country_code=country.code;

And you will see a table with column as City, State, Country and the data accordingly. Also, you can use “order by” clause to get the column in a lexicographic order, like:

select city.name as “City”,state.name as “State”,country.name as “Country” from city,state,country where city.state_code=state.code and state.country_code=country.code order by country.name,state.name,city.name;

Multiple arguments after the “order by” clause is to break the tie. Primary comparison would be on the basis of Country name. For same country names, state name will be a tie breaker and for same state names, city name and due to unique constraint no two cities would have same name, so the data will be sorted in lexicographic order.

Conclusion:

In this article, we understood the importance of database, installing Apache Derby and working on Apache Derby prompt for creating databases, tables, schema and controlling the information. Hope this was helpful and feel free to add notes for any questions or suggestions.