Apache derby one of the popular relational databases purely written in Java. Derby supports running in both Embedded as well as client/server models. In the embedded mode derby runs within the JVM and provides only single connection for applications, while supporting multi-threded, multi-connection and multi-user modes. In server mode derby allows multiple connections depending on the threading and mode configurations. While providing fully transactional, secure and standard (SQL, JDBC API, and Java EE) database system, still developer community is able to maintain small foot print less than 3MB for base engine and database. Features like stored procedures, triggers, views, referential integrity constraints, multi-user capable and cost-based query optimization also supported by Apache Derby.
1. Table creation
CREATE TABLE STUDENT ( REG_ID SMALLINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, FIRST_NAME VARCHAR(20) NOT NULL, LAST_NAME VARCHAR(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT GENDER_CONSTRAINT CHECK (Gender IN ('M', 'F')) );
2. Insert Values into table
insert into STUDENT (FIRST_NAME, LAST_NAME, GENDER) values ('Aravinda', 'Madusanka', 'M');
Since REG_ID column is set as auto generated, we only have to set other column values as mentioned above.
Note that GENDER column can only have to values 'M' and 'F' according to the constrain added in the table
3. Simple select Operation
select * from STUDENT where FIRST_NAME='aravinda' AND GENDER='M';
Note that column names are not case sensitive. Both 'first_name' and 'FIRST_NAME' are valid column names
4. Update a row.
update STUDENT set LAST_NAME = 'Nimal', LAST_NAME = 'Sujeewa' where REG_ID = 202;
5. Delete a row.
delete from STUDENT where REG_ID = 202;
6. Delete multiple rows.
delete from STUDENT where REG_ID in (2,5,202);
7. Like operation to search for similar content.
select * from STUDENT where LAST_NAME like '%aCbBbnN%'
This will search for LAST_NAME 's which contains 'aCbBbnN' string. That is 'xxxaCbBbnN', 'xxaCbBbnNxx' and
'aCbBbnNxxx' will satisfy above condition.
8. Search for multiple values
select * from STUDENT where FIRST_NAME IN ('MIKE','TOM');
This will search for first names 'MIKE' and 'TOM'
9. Sorting the results
>> Ascending Order
select * from STUDENT order by FIRST_NAME
>> Descending Order
select * from STUDENT order by FIRST_NAME desc
>> Sort by FIRST_NAME and if two values are equal LAST_NAME Descending Order will use as the next ordering
select * from STUDENT order by FIRST_NAME,LAST_NAME desc
10. Limit the result set (no of rows)
select * from STUDENT fetch first 10 rows only
11. Setting starting index for select operation
select * from STUDENT offset 2 rows
12. Handling pagination
You can combine above two operations to implement pagination.
select * from STUDENT offset n rows fetch first 10 rows only
Changing n value as 0,1,2.. will give you the paginated results with 10 rows in a page.
Thoughts are welcome....