Apache Derby Querying...

Intro...

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

creation script. 

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 

for querying.

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